Hugh's Blog

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


参考

ClickHouse Documentation