Docker-搭建单机版-clickhouse-server

• ClickHouse数据查询

1

1.系统环境

docker1.8、CentOS 7

2. 部署clickhouse-server

2.1. 启动my-clickhouse-server容器实例

docker run -d --name my-clickhouse-server --ulimit nofile=262144:262144 clickhouse/clickhouse-server

2.2. 创建本地挂载目录

引用/home下空间比较大，所以映射路径都放在/home下

mkdir -p /home/clickhouse-server/
mkdir -p /home/clickhouse-server/etc/

2.3.my-clickhouse-server容器的配置文件、目录复制到本地挂载目录下

docker cp my-clickhouse-server:/var/log/clickhouse-server /home/clickhouse-server/
docker cp my-clickhouse-server:/var/lib/clickhouse /home/clickhouse-server/
docker cp my-clickhouse-server:/etc/clickhouse-server /home/clickhouse-server/etc/

2.4. 复制完成后，停止删除容器my-clickhouse-server

docker stop my-clickhouse-server
docker rm my-clickhouse-server

2.5.重新运行部署my-clickhouse-server

docker run -d --restart=always --privileged=true -p 8123:8123 -p 9000:9000 --name my-clickhouse-server --ulimit nofile=262144:262144 \
-v /home/clickhouse-server/clickhouse-server:/var/log/clickhouse-server \
-v /home/clickhouse-server/clickhouse:/var/lib/clickhouse \
-v /home/clickhouse-server/etc/clickhouse-server:/etc/clickhouse-server \
clickhouse/clickhouse-server

2.6 clickhouse-servers设置密码,修改完成后，重启容器。

        修改本地文件

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "clickhouse"; echo -n "clickhouse" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'

vim /home/clickhouse-server/etc/clickhouse-server/users.xml

users.xml  users.xml

2.7.DBeaver连接clickhouse

账号：default  密码：clickhouse

3.clickhouse-client连接server

docker run -it --rm --link my-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server --user default --password=clickhouse

4.clickhouse创建database

CREATE DATABASE IF NOT EXISTS flink_clickhouse

5.其他内容，参考官方文档

UK Property Price Paid | ClickHouse Docs

FAQ:

一些常见的bug 总结：

1、 浮点数 转换保留小数位时候，需要先加 toFloat64 转换，再加 round 函数进行转换；http://10.1.125.31:8080/browse/QYPTCS-4815 ，如截图， 不加 整数会出现多个小数位，加了 就自动去掉小数位

clickHouse日志查看

在集群模式下或者情况下无法找到ck日志时，输入

$ sudo journalctl -u clickhouse-server

• 1

查找相关.log结尾文件

select query  from system.query_log order by event_date  desc;

备份目录：

使用clickhouse-backup工具备份clickhouse数据库_gsls200808的博客-CSDN博客

/apps/clickhouse/data/backup

cat /etc/clickhouse-backup/config.xml

general: remote_storage: none # 默认为none，如果通过sftp上传到远程服务器，需要这个参数这只为sftp disable_progress_bar: false backups_to_keep_local: 7 # 本地备份的个数，大于7的自动删除旧的备份，默认为0，不删除备份 backups_to_keep_remote: 7 # 远程备份个数 log_level: info allow_empty_backups: false clickhouse: username: default password: "123456" host: 192.168.2.10 port: 9000 skip_tables: # 不需要备份的库 - system.* - default.* - INFORMATION_SCHEMA.* - information_schema.* timeout: 5m freeze_by_part: false

[root@qy_clickhouse backup]# cat back_script.sh
#!/bin/sh
backup_date=`date "+%Y%m%d"`
delete_date=`date +%Y%m%d -d "3 days ago"`

clickhouse-backup create bak${backup_date} -config /etc/clickhouse-backup/config.xml
clickhouse-backup delete local bak${delete_date} -config /etc/clickhouse-backup/config.xml

导入sql

[root@qy_clickhouse ~]# clickhouse-client --database=HIE --user=default --password=123456 --multiquery < 无标题.sql

ClickHouse 查询时报错：Memory limit (for query) exceeded: would use 9.38 GiB

ClickHouse 查询时报错：Memory limit (for query) exceeded: would use 9.38 GiB_clickhouse memory_limit_exceeded-CSDN博客