ClickHouse之 MergeTree家族系列引擎(3)
一 MergeTree
参数解读 :
ENGINE = MergeTree()。该MergeTree引擎没有参数。
ORDER BY —排序键
如果PRIMARY KEY子句未明确定义主键,则ClickHouse会将排序键用作主键。
PARTITION BY— 分区键。
要按月进行分区,请使用toYYYYMM(date_column)表达式,其中的date_column是日期类型为Date的列
默认情况下,主键与排序键(由ORDER BY子句指定)相同
1 示例1
// 建表语句1==>指定指定排序
create table tb_merge_tree(
uid Int32 ,
name String ,
age UInt8 ,
birthday Date ,
gender String
)
engine=MergeTree() //没有参数
order by birthday ; // 排序字段
// 插入数据
insert into tb_merge_tree values(1,'zss',23,'1990-01-21','M'),(2,'lss',24,'1989-01-21','M');
insert into tb_merge_tree values(3,'mayun',43,'1969-01-21','M'),(4,'lxl',34,'1986-05-21','F');
// 表数据的查询
select * from tb_merge_tree;
//数据以插入为数据单元,以分区数据为block , 结果如下 ,得到两个数据块 :
┌─id─┬─name─┬───birthday─┐==>指定brithday字段排序
│ 4 │ jpp │ 1993-02-03 │
│ 3 │ tyy │ 1995-12-04 │
└────┴──────┴────────────┘
┌─id─┬─name─┬───birthday─┐ ==>指定brithday字段排序
│ 1 │ zss │ 1990-09-08 │
│ 2 │ lii │ 1998-10-04 │
└────┴──────┴────────────┘
// 人为的合并两个数据块(一般不需要人为的操作,会自动的进行合并) ,代码如下 :
optimize table tb_merge_tree ;
// 结果如下所示 :
┌─id─┬─name─┬───birthday─┐
│ 1 │ zss │ 1990-09-08 │
│ 4 │ jpp │ 1993-02-03 │
│ 3 │ tyy │ 1995-12-04 │
│ 2 │ lii │ 1998-10-04 │
└────┴──────┴────────────┘
**2 示例2 **
// 建表语句2===>排序和分区 ,指定排序字段和分区字段
create table tb_merge_tree3(
id Int32 ,
name String ,
address String
)
engine=MergeTree()
order by id
partition by address;
// 插入内容
insert into tb_merge_tree3 values(3,'yangying','BeiJing'),(2,'yangmi','NanJing'),(4,'yangcy','NanJing');
insert into tb_merge_tree3 values(5,'yangguo','BeiJing'),(9,'yangkang','NanJing'),(6,'yangerlang','NanJing');
// 表数据的查询
select * from tb_merge_tree3;
数据以插入为数据单元,以分区数据为block
结果如下 ,得到四个数据块 :
┌─id─┬─name─┬─address─┐ ===>以 id 字段分组 ,address 字段分区
│ 2 │ lii │ nanjin │
│ 4 │ lss │ nanjin │
└────┴──────┴─────────┘
┌─id─┬─name─┬─address─┐
│ 3 │ zss │ beijin │
└────┴──────┴─────────┘
┌─id─┬─name─┬─address─┐ ===>以 id 字段分组 ,address 字段分区
│ 6 │ yss │ nanjin │
│ 9 │ yuu │ nanjin │
└────┴──────┴─────────┘
┌─id─┬─name─┬─address─┐
│ 5 │ poo │ beijin │
└────┴──────┴─────────┘
// 人为的合并两个数据块, 然后**一次合并一个分区(**一般不需要人为的操作,会自动的进行合并) ,代码如下 :
optimize table tb_merge_tree3 ;
// 查询合并结果
select * from tb_merge_tree3;
┌─id─┬─name─┬─address─┐
│ 2 │ lii │ nanjin │
│ 4 │ lss │ nanjin │
└────┴──────┴─────────┘
┌─id─┬─name─┬─address─┐
│ 6 │ yss │ nanjin │
│ 9 │ yuu │ nanjin │
└────┴──────┴─────────┘
┌─id─┬─name─┬─address─┐====>第一次人为合并 ,同属 beijin 的地址数据块被合并了
│ 3 │ zss │ beijin │
│ 5 │ poo │ beijin │
└────┴──────┴─────────┘
二 ReplacingMergeTree(在 MergeTree 的基础上,添加了**“处理重复数据”**的功能)
会删除具有相同主键的重复项。数据的去重只会在合并的过程中出现。适用于在后台清除重复的数据以节省空间,但是它不保证没有重复的数据出现。
示例
// 创建表
create table tb_rep_merge_tree(
id Int8 ,
name String ,
ctime Date ,
version UInt8
)
engine=ReplacingMergeTree(version) // 参数version ,根据参数进行 "去重"
order by id // 根据 id 排序
partition by name // 根据 name 分区
primary key id; // 主键是 id
// 插入数据
insert into tb_rep_merge_tree values (1, 'zss','2020-08-05', 20);
insert into tb_rep_merge_tree values (1, 'b','2020-08-05', 30);
insert into tb_rep_merge_tree values (1, 'a','2020-08-05', 20);
insert into tb_rep_merge_tree values (1, 'a','2020-08-05', 30);
insert into tb_rep_merge_tree values (1, 'b','2020-08-05', 10);
// 查询
select * from tb_rep_merge_tree;
┌id ┬name┬──ctime─ ┬─version─┐
│ 1 │ zss │ 2020-08-05 │ 20 │
└────┴──────┴────────────┴─────────┘
┌id─┬name┬──ctime─┬─version┐
│ 1 │ a │ 2020-08-05 │ 20 │
└────┴──────┴────────────┴─────────┘
┌ d ┬name┬──ctime ─┬─version─┐
│ 1 │ b │ 2020-08-05 │ 30 │
└────┴──────┴────────────┴─────────┘
┌id─┬name┬──ctime─┬version┐
│ 1 │ a │ 2020-08-05 │ 30 │
└────┴──────┴────────────┴─────────┘
┌id┬name┬───ctime─┬version┐
│ 1 │ b │ 2020-08-05 │ 10 │
└────┴──────┴────────────┴─────────┘
//合并 手动自行命令, 然后查询
optimize table tb_rep_merge_tree ;
select * from tb_rep_merge_tree;
┌─id─┬─name─┬──────ctime─┬─version─┐
│ 1 │ b │ 2020-08-05 │ 30 │
└────┴──────┴────────────┴─────────┘
┌─id─┬─name─┬──────ctime─┬─version─┐
│ 1 │ b │ 2020-08-05 │ 10 │
└────┴──────┴────────────┴─────────┘
┌─id─┬─name─┬──────ctime─┬─version─┐
│ 1 │ a │ 2020-08-05 │ 30 │
└────┴──────┴────────────┴─────────┘
┌─id─┬─name─┬──────ctime─┬─version─┐
│ 1 │ zss │ 2020-08-05 │ 20 │
└────┴──────┴────────────┴─────────┘
//再合并 再查询.....最后得到的去重结果为 :
┌─id─┬─name─┬──────ctime─┬─version─┐
│ 1 │ zss │ 2020-08-05 │ 20 │
└────┴──────┴────────────┴─────────┘
┌─id─┬─name─┬──────ctime─┬─version─┐
│ 1 │ a │ 2020-08-05 │ 30 │
└────┴──────┴────────────┴─────────┘
┌─id─┬─name─┬──────ctime─┬─version─┐
│ 1 │ b │ 2020-08-05 │ 30 │
└────┴──────┴────────────┴─────────┘
总结 :
1 在数据合并的时候 根据主键分区内自动的去重主键重复的数据,我们可以指定一个字段作为数据的版本,当去除重复数据的时候保留版本大的数据!
2 ClickHouse 内部会自动的合并数据并去重重复数据 ,当然我们也可以手动的执行合并,但是每次处罚命令只能合并一个分区的数据,一般情况下等待他自己合并数据即可! 所以我们无法保证表中不存在重复主键数据.
三 SummingMergeTree
当合并 SummingMergeTree 表的数据片段时,**ClickHouse 会把所有具有相同主键的行合并为一行**,该行包含了被合并的行中具有数值数据类型的列的汇总值。
如果主键的组合方式使得单个键值对应于大量的行,则可以显著的减少存储空间并加快数据查询的速度,对于不可加的列,会取一个最先出现的值。
示例
// 建表
create table tb_summ_merge_tree(
id Int8 ,
name String ,
ctime Date ,
cost UInt8 )
engine=SummingMergeTree(cost) // 合并时 ,将 cost 值合并相加汇总
order by id // 排序
partition by name // 分区
primary key id; // 主键
// 插入数据
insert into tb_summ_merge_tree values (1, 'a','2020-08-05', 20);
insert into tb_summ_merge_tree values (1, 'b','2020-08-05', 30);
insert into tb_summ_merge_tree values (1, 'a','2020-08-05', 20);
insert into tb_summ_merge_tree values (1, 'a','2020-08-05', 30);
insert into tb_summ_merge_tree values (1, 'b','2020-08-05', 10);
// 查询数据
┌─id─┬─name─┬──────ctime─┬─cost─┐
│ 1 │ a │ 2020-08-05 │ 20 │
└────┴──────┴────────────┴──────┘
┌─id─┬─name─┬──────ctime─┬─cost─┐
│ 1 │ a │ 2020-08-05 │ 30 │
└────┴──────┴────────────┴──────┘
┌─id─┬─name─ ┬──────ctime─┬─cost─┐
│ 1 │ a │ 2020-08-05 │ 20 │
└────┴──────┴────────────┴──────┘
┌─id─┬─name─┬──────ctime─ ┬─cost─┐
│ 1 │ b │ 2020-08-05 │ 30 │
└────┴──────┴────────────┴──────┘
┌─id─┬─name─┬──────ctime─┬─cost─┐
│ 1 │ b │ 2020-08-05 │ 10 │
└───┴─────┴────────────┴──────┘
// 合并 查询
optimize table tb_summ_merge_tree;
select * from tb_summ_merge_tree;
┌─id─┬─name─┬──────ctime─┬─cost─┐
│ 1 │ b │ 2020-08-05 │ 30 │
└────┴──────┴──────────┴────┘
┌─id─┬─name─┬──────ctime─┬─cost─┐
│ 1 │ b │ 2020-08-05 │ 10 │
└────┴──────┴──────────┴────┘
┌─id─┬─name─┬──────ctime─┬─cost─┐
│ 1 │ a │ 2020-08-05 │ 70 │ ===> 相同区的( a区 ) cost 值在合并时被汇总
└────┴──────┴──────────┴─────┘
// 再合并 ,再查询
optimize table tb_summ_merge_tree;
select * from tb_summ_merge_tree;
┌─id─┬─name─┬──────ctime─┬─cost─┐
│ 1 │ b │ 2020-08-05 │ 40 │ ===> b 区结果在再次合并时 ,coat 值被汇总
└────┴──────┴──────────┴─────┘
┌─id─┬─name─┬──────ctime─┬─cost─┐
│ 1 │ a │ 2020-08-05 │ 70 │
└────┴──────┴──────────┴─────┘
总结: 以上示例 ,按 name 字段分区 ,相同主键 (id) 时 , 按照指定字段 (cost) 进行数据的汇总 .
四 CollapsingMergeTree
ClickHouse实现了CollapsingMergeTree来消除ReplacingMergeTree的限制(只删除小版本字段的问题)。该引擎要求在建表语句中指定一个标记列Sign,后台Compaction时会将主键相同、Sign相反的行进行折叠,也即删除。
CollapsingMergeTree将行按照Sign的值分为两类:Sign=1的行称之为状态行,Sign=-1的行称之为取消行。
每次需要新增状态时,写入一行状态行;需要删除状态时,则写入一行取消行。
在后台Compaction时,状态行与取消行会自动做折叠(删除)处理。而尚未进行Compaction的数据,状态行与取消行同时存在。
注意:
CollapsingMergeTree虽然解决了主键相同的数据即时删除的问题,但是状态持续变化且多线程并行写入情况下,状态行与取消行位置可能乱序,导致无法正常折叠。只有保证老的状态行在在取消行的上面, 新的状态行在取消行的下面! 但是多线程无法保证写的顺序!
五 VersionedCollapsingMergeTree
取消字段和数据版本同事使用,避免取消行数据无法删除的问题
为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题,VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version,用于在乱序情况下记录状态行与取消行的对应关系。主键相同,且Version相同、Sign相反的行,在Compaction时会被删除。
与CollapsingMergeTree类似, 为了获得正确结果,业务层需要改写SQL,将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。