VersionedCollapsingMergeTree引擎
VersionedCollapsingMergeTree引擎
为了解决CollapsingMergeTree乱序写入情况下无法折叠(删除)问题,VersionedCollapsingMergeTree引擎在建表语句中新增了一列Version,用于在乱序情况下记录状态行与取消行的对应关系,主键排序相同,且Version相同,sign相反的行,在Compaction时,与CollapsingMergeTree类似,为了获得正确结果,业务层需要改写SQL,将count(),sum(col)分别改写为sum(Sign),sum(col*Sign)。
create table tb_version_merge_tree(
id UInt8,
name String,
version UInt8,
sign Int8
)engine=VersionedCollapsingMergeTree(sign,version)
order by id;
insert into tb_version_merge_tree values(1,'a',1,-1);
insert into tb_version_merge_tree values(1,'b',1,1);
insert into tb_version_merge_tree values(2,'c',1,-1);
insert into tb_version_merge_tree values(2,'d',2,1);
select * from tb_version_merge_tree;
┌─id─┬─name─┬─version─┬─sign─┐
│ 1 │ a │ 1 │ -1 │
└────┴──────┴─────────┴──────┘
┌─id─┬─name─┬─version─┬─sign─┐
│ 2 │ d │ 2 │ 1 │
└────┴──────┴─────────┴──────┘
┌─id─┬─name─┬─version─┬─sign─┐
│ 2 │ c │ 1 │ -1 │
└────┴──────┴─────────┴──────┘
┌─id─┬─name─┬─version─┬─sign─┐
│ 1 │ b │ 1 │ 1 │
└────┴──────┴─────────┴──────┘
optimize table tb_version_merge_tree;
select * from tb_version_merge_tree; //c,d版本号不一致就没有被折叠
┌─id─┬─name─┬─version─┬─sign─┐
│ 2 │ c │ 1 │ -1 │
│ 2 │ d │ 2 │ 1 │
└────┴──────┴─────────┴──────┘