ClickHouse的MergeTree家族表引擎

ReplacingMergeTree

ReplacingMergeTree引擎是在MergeTree的基础上,添加了处理重复数据的功能,该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。其特点如下:

  1. 数据的去重只会在合并的过程中触发
  2. 以数据分区为单位删除重复数据,不同分区的重复数据不会删除
  3. 使用ORDER BY排序键作为判断重复的唯一键
  4. 找到重复数据的方式依赖数据已经ORDER BY排好序了
  5. 如果没有ver版本好,则保留重复数据的最后一行
  6. 如果设置了ver版本号,则保留重复数据中ver版本号最大的数据

创建表语句如下

1
2
3
4
5
6
7
8
create table replace_table(
id String,
code String,
create_time DateTime
)ENGINE=ReplacingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id,code)
PRIMARY KEY id;

插入语句

1
2
3
4
5
6
insert into replace_table values('A001','C1','2020-08-21 08:00:00');
insert into replace_table values('A001','C1','2020-08-22 08:00:00');
insert into replace_table values('A001','C8','2020-08-23 08:00:00');
insert into replace_table values('A001','C9','2020-08-24 08:00:00');
insert into replace_table values('A002','C2','2020-08-25 08:00:00');
insert into replace_table values('A003','C3','2020-08-26 08:00:00');

可通过optimize table replace_table;强制触发合并。然后通过select观察之后,去重是根据order by来的,并非primary key。然后在继续插入一条数据insert into replace_table values('A001','C1','2020-05-21 08:00:00');
在次通过观察,不同分区的数据不会去重。

SummingMergeTree

SummingMergeTree引擎继承MergeTree。区别在于,当合并SummingMergeTree表的数据片段时,ClickHouse会把所有具有相同聚合数据的条件Key的行合并为一行。该行包含了被合并的行中具有数值数据类型的列的汇总值。
如果聚合数据的条件Key的组合方式使得单个键值对应大量的行,则可以减少存储空间并加快数据的查询速度,对于不可加的列,会取一个最先出现的值。其特征如下

  1. 用ORDER BY排序键作为聚合数据的条件Key
  2. 合并分区时触发汇总逻辑
  3. 以数据分区为单位聚合数据,不同分区的数据不会被汇总
  4. 如果在定义引擎时执行了Columns汇总列(非主键)则SUM汇总这些字段
  5. 如果没有指定,则汇总所有非主键的数值类型字段
  6. SUM汇总相同的聚合Key的数据,依赖ORDER BY排序
  7. 同一个分区的SUM汇总过程中,非汇总字段的数据保留第一行取值
  8. 支持嵌套结构,但列字段名称必须以Map后缀结束

案例一:创建表语句如下

1
create table smt_table (date Date, name String, a UInt16, b UInt16) ENGINE=SummingMergeTree(date, (date, name), 8192, (a));

案例二:插入语句如下

1
2
3
4
5
6
insert into smt_table (date, name, a, b) values ('2021-07-01', 'a', 1, 2);
insert into smt_table (date, name, a, b) values ('2021-07-01', 'b', 2, 1);
insert into smt_table (date, name, a, b) values ('2021-07-02', 'b', 3, 8);
insert into smt_table (date, name, a, b) values ('2021-07-02', 'b', 3, 8);
insert into smt_table (date, name, a, b) values ('2021-07-02', 'a', 3, 1);
insert into smt_table (date, name, a, b) values ('2021-07-03', 'c', 1, 3);

等待一段时间或手动执行optimize table smt_table,然后select * from smt_table;发现2021-07-02,b的a列合并相加了,b列取了8(因为b列为8的数据最先插入)。

案例二:创建表语句如下:

1
create table summing_table( id String, city String, v1 UInt32, v2 Float64, create_time DateTime )ENGINE=SummingMergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (id,city) PRIMARY KEY id;

案例二:插入语句如下:

1
2
3
4
5
6
7
8
-- 插入语句
insert into table summing_table values('A000','beijing',10,20,'2021-07-01 08:00:00');
insert into table summing_table values('A000','beijing',20,30,'2021-07-02 08:00:00');
insert into table summing_table values('A000','shanghai',10,20,'2021-07-01 08:00:00');
insert into table summing_table values('A000','beijing',10,20,'2021-06-30 08:00:00');
insert into table summing_table values('A001','beijing',50,60,'2021-06-20 08:00:00');
-- 触发合并
OPTIMIZE TABLE summing_table;

通过select语句之后,根据ORDER BY排序键(id,city)作为聚合Key,因为在建表时指定SummingMergeTree的时候没有执行SUM列,所以把非主键数值类型的列都进行了SUM逻辑。

AggregateMergeTree

该引擎改变了数据片段的合并逻辑,ClickHouse会将相同主键的所有行(在一个数据片段内)替换为单个存储一系列的聚合函数状态的行。可以使用该引擎来做增量数据统计聚合,包括物化视图的数据聚合。
AggregateMergeTree引擎的表不能直接使用insert来查询写入数据。一般用insert select来写入数据,常用于该引擎做增量数据统计聚合,包括物化视图的数据聚合。
创建一个MergeTree引擎的基表,并写入数据

1
2
-- 创建表
create table arr_table_base (id String, city String, code String,value UInt32) engine=MergeTree partition by city order by (id,city);

创建物化视图

1
2
3
create materialized view agg_view engine=AggregatingMergeTree() partition by
city order by(id,city) as select id,city,uniqState(code) as code,
sumState(value) as value from arr_table_base group by id,city;

在之前创建的MergeTree表引擎中写入数据

1
insert into table arr_table_base values ('A000','wuhan','code1',1),('A000','wuhan','code2',200),('A000','zhuhai','code1',200);

执行OPTIMIZE TABLE arr_table_base;之后,再次查询AggregateMergeTree引擎表中的数据select id,sumMerge(value),uniqMerge(code) from agg_view group by id,city;

CollapsingMergeTree

CollapsingMergeTree会异步的删除特定列Sign有1和-1的值以外,其余所有字段的值都相等的成对的行。没有成对的行会被保留。该引擎可以降低存储量提高select查询效率。CollapsingMergeTree引擎有个状态列sign,这个值为1是”状态“行,-1为”取消“行。
对于数据而言只关心状态列为状态的数据,不关心状态列为取消的数据。
创建该引擎的sql如下

1
CREATE TABLE cmt_tab( sign Int8, date Date, name String, point String) ENGINE=CollapsingMergeTree(sign) PARTITION BY date ORDER BY (name) SAMPLE BY name;

插入语句如下

1
2
3
4
5
6
7
8
9
insert into cmt_tab(sign,date,name,point) values (1,'2021-07-01','cctv','100000');
insert into cmt_tab(sign,date,name,point) values (-1,'2021-07-01','cctv','100000');
insert into cmt_tab(sign,date,name,point) values (1,'2021-07-01','hntv','10000');
insert into cmt_tab(sign,date,name,point) values (-1,'2021-07-01','hntv','10000');
insert into cmt_tab(sign,date,name,point) values (1,'2021-07-01','hbtv','11000');
insert into cmt_tab(sign,date,name,point) values (-1,'2021-07-01','hbtv','11000');
insert into cmt_tab(sign,date,name,point) values (1,'2021-07-02','cctv','200000');
insert into cmt_tab(sign,date,name,point) values (1,'2021-07-02','hntv','15000');
insert into cmt_tab(sign,date,name,point) values (1,'2021-07-02','hbtv','16000');

通过该语句触发合并操作OPTIMIZE TABLE cmt_tab;然后通过查询语句select * from cmt_tab;可以看到日期值只会有2021-07-02的数据。

VersionedCollapsingMergeTree

该引擎和CollapsingMergeTree引擎类似,只是对CollapsingMergeTree引擎加了一个版本,适用于非实时用户在线统计,统计每个节点用户在在线业务。案例在这我不写了。

集成引擎

ClickHouse还可以指定jdbc,hdfs,mysql,kafka等相关的引擎,具体的可参考官网https://clickhouse.tech/docs/zh/engines/table-engines/