clickhouse引擎--CollapsingMergeTree引擎

CollapsingMergeTree引擎

• CollapsingMergeTree是一种通过以增代删的思路，支持行级数据修改和删除的表引擎，它通过定义一个sign标记位字段，记录数据行的状态，如果sign标记为1，则表示这是一行有效数据，如果sign标记为-1，则表示这行数据需要被删除，当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。
• 多行的排序相同的状态为1的数据会折叠成一行折叠实现数据的删除和更新

create table tb_coll_merge_tree1(
id Int8,
name String,
sign Int8
)engine=CollapsingMergeTree(sign)
order by id;

insert into tb_coll_merge_tree1 values(1,'a',1);
insert into tb_coll_merge_tree1 values(1,'b',-1);
insert into tb_coll_merge_tree1 values(2,'c',1);
select * from tb_coll_merge_tree1;
┌─id─┬─name─┬─sign─┐
│  1 │ b    │   -1 │
└────┴──────┴──────┘
┌─id─┬─name─┬─sign─┐
│  1 │ a    │    1 │
└────┴──────┴──────┘
┌─id─┬─name─┬─sign─┐
│  2 │ c    │    1 │
└────┴──────┴──────┘

optimize table tb_coll_merge_tree1;
select * from tb_coll_merge_tree1;
┌─id─┬─name─┬─sign─┐
│  2 │ c    │    1 │
└────┴──────┴──────┘


create table tb_coll_merge_tree2(
id Int8,
name String,
city String,
sign Int8
)engine=CollapsingMergeTree(sign)
partition by city
order by id;

insert into tb_coll_merge_tree2 values(1,'a','SH',1);
insert into tb_coll_merge_tree2 values(1,'a','NJ',-1);
insert into tb_coll_merge_tree2 values(2,'c','BJ',1);
insert into tb_coll_merge_tree2 values(2,'d','BJ',-1);
select * from tb_coll_merge_tree2;
┌─id─┬─name─┬─city─┬─sign─┐
│  2 │ d    │ BJ   │   -1 │
└────┴──────┴──────┴──────┘
┌─id─┬─name─┬─city─┬─sign─┐
│  2 │ c    │ BJ   │    1 │
└────┴──────┴──────┴──────┘
┌─id─┬─name─┬─city─┬─sign─┐
│  1 │ a    │ NJ   │   -1 │
└────┴──────┴──────┴──────┘
┌─id─┬─name─┬─city─┬─sign─┐
│  1 │ a    │ SH   │    1 │
└────┴──────┴──────┴──────┘

optimize table tb_coll_merge_tree2;
select * from tb_coll_merge_tree2;   //只有同一分区的数据才可以折叠
┌─id─┬─name─┬─city─┬─sign─┐
│  1 │ a    │ SH   │    1 │
└────┴──────┴──────┴──────┘
┌─id─┬─name─┬─city─┬─sign─┐
│  1 │ a    │ NJ   │   -1 │
└────┴──────┴──────┴──────┘

create table tb_coll_merge_tree3(
id Int8,
name String,
sign Int8
)engine=CollapsingMergeTree(sign)
order by id;

insert into tb_coll_merge_tree3 values(1,'a',-1);
insert into tb_coll_merge_tree3 values(1,'a',1);

optimize table tb_coll_merge_tree3;
select * from tb_coll_merge_tree3;   //-1在前面并不能折叠
┌─id─┬─name─┬─sign─┐
│  1 │ a    │   -1 │
│  1 │ a    │    1 │
└────┴──────┴──────┘