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;
┌─id─┬─name─┬─sign─┐
│ 1 │ a │ -1 │
│ 1 │ a │ 1 │
└────┴──────┴──────┘