ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

官网点击这里 (opens new window)

• centos\redhat安装clickhouse-client

curl -o /etc/yum.repos.d/clickhouse.repo  https://packages.clickhouse.com/rpm/clickhouse.repo
yum install -y clickhouse-client 

clickhouse 部署

官方安装文档 (opens new window)

使用docker-compose部署

mkdir /data/clickhouse &&cd /data/clickhouse && vi docker-compose.yaml

version: '3'

#宿主机IP: 172.16.100.156
services:
  prod_clickhouse_server:
    restart: always
    image: clickhouse-server:21.8.57
    container_name: prod_clickhouse_server
    environment:
      - TZ=Asia/Shanghai
    ulimits:
      nofile:
        soft: "262144"
        hard: "262144"
    volumes:
      - /etc/localtime:/etc/localtime
      - ./clickhouse_server/clickhouse_database:/var/lib/clickhouse   #所属主：101 ，chown 101.101 clickhouse_database
      - ./clickhouse_server/clickhouse-server:/etc/clickhouse-server  #先注释启动容器之后，把目录拷贝出来，再关闭注释
      - ./clickhouse_server/clickhouse_log:/var/log/clickhouse-server
    ports:
      - "9002:9000"
      - "8123:8123"
      - "9004:9004"
    deploy:
      resources:
         limits:
            memory: 4G
         reservations:
            memory: 2G
    network_mode: bridge
#    networks:
#      clickhouse:
#        ipv4_address: 192.18.0.101
#networks:
#  clickhouse:
#    external:
#      name: "yfk_clickhouse_net"

• 启动

docker-compose -f docker-compose up -d

拷贝配置目录,拷贝之后修改yaml，重新加载，检测

docker cp prod_clickhouse_server:/etc/clickhouse-server/ ./clickhouse_server/

• 使用mysql命令连接

mysql --protocol tcp -u default -P 9004 -h 192.168.5.112

配置密码

修改：user.xml 配置文件

找到： 替换为：<password_sha256_hex></password_sha256_hex>

加密方法

[root@clickhouse-db01 ~]# echo -n 'CickhouseQwe1245' | openssl dgst -sha256 (stdin)= dc5f5b46cd396d91697aa7cf8d9425918056b0c46dcb9e801f6b3874fc040e7a

<password_sha256_hex>dc5f5b46cd396d91697aa7cf8d9425918056b0c46dcb9e801f6b3874fc040e7a</password_sha256_hex>

服务器意外断电，clickhouse起不来

该情况部署在容器或宿主机都有可能发生这样的异常

以下是clickhouse 启动其中一段报错

官方part_log (opens new window)

大概解决方法是： 找到 store 里面的一些空的目录，删除

• 查看会看到显示的目录下有许多的空文件：

find ./clickhouse_database/store/ -name checksums.txt -size 0 |sed -r 's#(.*)/checksums.txt#\1#g' |xargs ls -l

• 删除命令：(删除请谨慎！！！)

find ./clickhouse_database/store/ -name checksums.txt -size 0 |sed -r 's#(.*)/checksums.txt#\1#g' |xargs rm -rf

• 重启容器

docker restart prod_clickhouse_server

再次检测，容器没有出现异常重启

• 代理pgsql

代理pgsql文档地址 (opens new window)

clickhouse查询

• 查看进程id 相关信息

select query_id,user,read_rows,elapsed,memory_usage,peak_memory_usage,query from system.processes;

• 停止 指定的 query_id

KILL query where query_id='203970a2-5628-4e7f-8885-c896379cae29';

• 停止 default用户所有的query_id

KILL query where user='default'