ClickHouse 笔记
ClickHouse 是一款开源的 OLAP 列存储数据库，下面是一些使用笔记，做个记录。
常用语句
-- 创建表
create table temp (`EventDate` DateTime, `UserId` UInt32) engine = MergeTree() partition by toYYYYMM(EventDate) order by EventDate;
-- 创建分布式复制表
create table temp (`EventDate` DateTime, `UserId` UInt32) engine = ReplicatedMergeTree('/clickhouse/tables/{shard}/temp', '{replica}') partition by toYYYYMM(EventDate) order by EventDate;
-- 创建集群分布式表
-- 更多的引擎可以看文档
create table temp_all (`EventDate` DateTime, `UserId` UInt32) engine = Distributed('your_cluster', 'default', 'temp', UserId);
-- 物化视图
-- 数据会根据原始表更新
create materialized view temp_v (`EventDate` DateTime, `UserId` UInt32) engine = MergeTree() partition by toYYYYMM(EventDate) order by UserId populate as select * from temp;
-- 可以从 MySQL 中导入数据并建表
-- 注意要指定 order by
create table from_mysql ENGINE = MergeTree order by id as select * from mysql('127.0.0.1', 'db', 'table', 'user', 'password');
-- 可以先建好表，再从 MySQL 中导入
create table temp_test (`id` UInt32, `creaetd_at` UInt32) ENGINE = MergeTree order by id partition by toYYYYMM(toDateTime(creaetd_at));
insert into temp_test select id, created_at from mysql('127.0.0.1', 'db', 'table', 'user', 'password');
-- 可以从 ClickHouse 其他表导入数据
insert into temp_test select * from remote('127.0.0.1:9000', db, table);
insert into temp_test select * from remote('127.0.0.1:9000', db, table, 'user', 'password');
-- 删除/更新数据
-- 该命令是异步的，并不会马上执行
alter table tb_name delete where id > 0;
alter table tb_name update col1=val1, col2=val2 where id > 0;
-- 查看任务进度
select * from system.mutations;
kill mutation where mutation_id = 'trx_id';
-- 获取表的分区数据
select partition, name, active from system.parts where table = 'temp';
-- 分区操作
-- 详细的可以看文档
alter table test detach partition 202004 -- 卸载分区
alter table test attach partition 202004 -- 根据'分区键'挂载分区
alter table test_new attach partition 202004 from test -- 从其他表中挂载分区
alter table test attach part '202004_0_0_1' -- 根据'目录名'挂载分区
-- 查看表占用大小
select database, table, formatReadableSize(sum(bytes)) as size from system.parts group by database, table order by database, table;
-- 查看进程等
show processlist;
select query_id, user, address, elapsed, query from system.processes order by query_id;
kill query where query_id='query_id';
-- 查看连接数量
select * from system.metrics where metric like '%Connection%';
-- 查看集群信息
select * from system.clusters;
-- 优化表分区
optimize table test [PARTITION partition] [FINAL]
-- 系统配置
select * from system.settings;
set send_logs_level = 'debug'; -- 修改日志级别，如 trace|debug 等等
set insert_deduplicate = 0; -- 关闭重复数据自动删除，测试数据时关闭会比较好用
数据迁移
如果数据量不大的话，使用 insert into select 就行，如：
insert into dst_table select * from src_table;
insert into dst_table select * from remote('127.0.0.1:9000', default, src_table);
表结构一致的情况下，在同一个服务器之间，数据量较大的话，可以使用分区的方式来迁移，如：
alter table dst_table attach partition 202004 from src_table
如果不在同一个服务器，可以把表的分区数据文件移动到新的表数据文件目录下（一般在 /var/lib/clickhouse/data，可以到配置文件中找），然后手动挂载，这个速度会比较快，如：
cd /var/lib/clickhouse/data/default/temp
cp -R 201803_0_0_0 /dst/new_table/detached/
# 注意文件的权限
chown -r clickhouse:clickhouse /dst/new_table/detached/201803_0_0_0
# 挂载分区
# alter table new_table attach partition 201803;
如果在集群中迁移，表比较多的话，可以使用官方工具 clickhouse-copier。
内存不足
根据文档，建议将 max_memory_usage 设置为 max_bytes_before_external_group_by 的 2 倍大小。
set max_memory_usage = '40G';
set max_bytes_before_external_group_by = '20G';
JOIN 内存不足，需要调整算法规则，具体看：join_algorithm
set join_algorithm = 'auto';
set partial_merge_join_optimizations = 0; # 避免可能出现的错误