ClickHouse MergeTree系列分析
VersionedCollapsingMergeTree
为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题,VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version,用于在乱序情况下记录状态行与取消行的对应关系。主键相同,且Version相同、Sign相反的行,在Compaction时会被删除。建表:
CREATE TABLE UAct
(
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8,
Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;
插入数据:
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1, 1);
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1, 1),(4324182021466249494, 6, 185, 1, 2);
上面执行了两个INSERT语句,创建了两个不同的数据片段。如果使用一个INSERT语句,ClickHouse将创建一个数据片段,并且将永远不会执行任何合并。
查询数据:
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │ 5 │ 146 │ 1 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │ 5 │ 146 │ -1 │ 1 │
│ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
通过两个INSERT语句,创建了两个数据片段。SELECT查询是在两个线程中执行的,我们得到了随机顺序的行。由于尚未合并数据片段,折叠还未发生。我们无法预测ClickHouse在何时执行数据片段的合并。
因此,我们需要使用聚合:
SELECT
UserID,
sum(PageViews * Sign) AS PageViews,
sum(Duration * Sign) AS Duration,
Version
FROM UAct
GROUP BY UserID, Version
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Version─┐
│ 4324182021466249494 │ 6 │ 185 │ 2 │
└─────────────────────┴───────────┴──────────┴─────────┘
如果不使用聚合,可以对FROM子句使用FINAL修饰符进行强制合并:
:) select * from UAct FINAL;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
SummingMergeTree
drop table summtt;
CREATE TABLE summtt
(
key String,
name String,
value Int32
)
ENGINE = SummingMergeTree()
ORDER BY key;
insert into summtt values('a', 'xiaoa1', 1);
insert into summtt values('a', 'xiaoa2', 2);
insert into summtt values('b', 'xiaob1', 10);
insert into summtt values('b', 'xiaob2', 20);
insert into summtt values('c', 'xiaob1', 10);
insert into summtt values('c', 'xiaob2', -10);
SELECT * FROM summtt;
┌─key─┬─name───┬─value─┐
│ a │ xiaoa1 │ 3 │
│ b │ xiaob1 │ 30 │
│ c │ xiaob1 │ 10 │
└─────┴────────┴───────┘
┌─key─┬─name───┬─value─┐
│ c │ xiaob2 │ -10 │
└─────┴────────┴───────┘
执行计划外的合并:
optimize table summtt;
查看表数据:
select * from summtt;
┌─key─┬─name───┬─value─┐
│ a │ xiaoa1 │ 3 │
│ b │ xiaob1 │ 30 │
└─────┴────────┴───────┘
select key, sum(value) from summtt group by key;
可以发现实现了根据key 累加的情况
ReplacingMergeTree
代码如下(示例):
示例1: 根据排序键去重
1. 准备数据
建表:
DROP TABLE replacingMergeTreeDemo;
CREATE TABLE replacingMergeTreeDemo
(
UserID UInt32,
CounterID UInt32,
UserName String,
EventDate Date
) ENGINE = ReplacingMergeTree()
ORDER BY (UserID, CounterID)
PRIMARY KEY (UserID);
2. 插入数据
分三批次插入数据, 为了观察效果, 下面的三段脚本依次按顺序分别执行, 不要一起执行:
批次1:
insert into replacingMergeTreeDemo values(1,100,'xiaohe1','2020-04-21');
insert into replacingMergeTreeDemo values(2,100,'xiaohe2','2020-04-22');
insert into replacingMergeTreeDemo values(3,102,'xiaohe3','2020-04-23');
批次2:
insert into replacingMergeTreeDemo values(3,103,'xiaohe3','2020-04-23');
insert into replacingMergeTreeDemo values(2,101,'xiaohe2','2020-04-24');
insert into replacingMergeTreeDemo values(1,100,'xiaohe1','2020-04-20');
批次3:
insert into replacingMergeTreeDemo values(2,100,'xiaohe2','2020-04-23');
查看数据:
xiaochen :) select * from replacingMergeTreeDemo;
SELECT *
FROM replacingMergeTreeDemo
┌─UserID─┬─CounterID─┬─UserName─┬──EventDate─┐
│ 1 │ 100 │ xiaohe1 │ 2020-04-21 │
│ 2 │ 100 │ xiaohe2 │ 2020-04-22 │
│ 2 │ 101 │ xiaohe2 │ 2020-04-24 │
│ 3 │ 102 │ xiaohe3 │ 2020-04-23 │
│ 3 │ 103 │ xiaohe3 │ 2020-04-23 │
└────────┴───────────┴──────────┴────────────┘
┌─UserID─┬─CounterID─┬─UserName─┬──EventDate─┐
│ 1 │ 100 │ xiaohe1 │ 2020-04-20 │
└────────┴───────────┴──────────┴────────────┘
┌─UserID─┬─CounterID─┬─UserName─┬──EventDate─┐
│ 2 │ 100 │ xiaohe2 │ 2020-04-23 │
└────────┴───────────┴──────────┴────────────┘
3. 手工执行计划外合并
执行命令:optimize table replacingMergeTreeDemo
xiaochen :) optimize table replacingMergeTreeDemo;
OPTIMIZE TABLE replacingMergeTreeDemo
Ok.
0 rows in set. Elapsed: 0.005 sec.
4. 查看合并后的数据
select * from replacingMergeTreeDemo;
SELECT * FROM replacingMergeTreeDemo
┌─UserID─┬─CounterID─┬─UserName─┬──EventDate─┐
│ 1 │ 100 │ xiaohe1 │ 2020-04-20 │
│ 2 │ 100 │ xiaohe2 │ 2020-04-23 │
│ 2 │ 101 │ xiaohe2 │ 2020-04-24 │
│ 3 │ 102 │ xiaohe3 │ 2020-04-23 │
│ 3 │ 103 │ xiaohe3 │ 2020-04-23 │
└────────┴───────────┴──────────┴────────────┘
5 rows in set. Elapsed: 0.007 sec.
从这个示例可以发现, ReplacingMergeTree是根据排序项对数据去重的,而不是根据主键。
CollapsingMergeTree
代码如下(示例):
eg:
建表语句:
CREATE TABLE UAct
(
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID
首先清空表:
truncate table UAct;
执行如下的数据插入语句:
INSERT INTO UAct VALUES (4324182021466249494, 1, 11, -1),(4324182021466249494, 2, 12, -1);
INSERT INTO UAct VALUES (4324182021466249494, 3, 13, 1);
INSERT INTO UAct VALUES (4324182021466249494, 4, 14, 1);
查看数据:
xiaochen001 :) select * from UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 1 │ 11 │ -1 │
│ 4324182021466249494 │ 2 │ 12 │ -1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 3 │ 13 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 4 │ 14 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
4 rows in set. Elapsed: 0.011 sec.
执行计划外的片段合并操作:
optimize table UAct;
查看折叠后的数据:
xiaochen001 :) select * from UAct;
SELECT *
FROM UAct
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 1 │ 11 │ -1 │
│ 4324182021466249494 │ 4 │ 14 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
2 rows in set. Elapsed: 0.005 sec.
从示例中可以观察到,最终的数据片段保留了第一个"cancel"行和最后一个"state"行。
================================================================================================
示例2: 聚合查询
================================================================================================
示例数据:
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ 1 │
│ 4324182021466249494 │ 5 │ 146 │ -1 │
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
建表:
DROP TABLE UAct;
CREATE TABLE UAct
(
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;
插入数据:
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1);
上面执行了两个INSERT语句,创建了两个不同的数据片段。如果使用一个INSERT语句,ClickHouse将创建一个数据片段,并且将永远不会执行任何合并。
查询数据:
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ -1 │
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
通过两个INSERT语句,创建了两个数据片段。SELECT查询是在两个线程中执行的,我们得到了随机顺序的行。由于尚未合并数据片段,折叠还未发生。我们无法预测ClickHouse在何时执行数据片段的合并。
因此,我们需要使用聚合:
SELECT
UserID,
sum(PageViews * Sign) AS PageViews,
sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │ 6 │ 185 │
└─────────────────────┴───────────┴──────────┘
如果不使用聚合,可以对FROM子句使用FINAL修饰符进行强制合并:
:) select * from UAct FINAL;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
1 rows in set. Elapsed: 0.008 sec.
这种数据查询的方法非常低效,不要在大表中使用这种方法。
================================================================================================
示例3: Cancel状态行取反的聚合查询
================================================================================================
示例数据:
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ 1 │
│ 4324182021466249494 │ -5 │ -146 │ -1 │
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
这个方法的核心思想是仅考虑关键字段,在"cancel"行中,可以指定负数,这些负数的值等于行的前一个版本的关键字段值的取反,这样在求和时就可以不使用Sign列。对于这种方法,必须更改PageViews和Duration字段的数据类型,从UIn8改成Int16。
建表语句:
CREATE TABLE UAct
(
UserID UInt64,
PageViews Int16,
Duration Int16,
Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID
插入数据,并测试:
insert into UAct values(4324182021466249494, 5, 146, 1);
insert into UAct values(4324182021466249494, -5, -146, -1);
insert into UAct values(4324182021466249494, 6, 185, 1);
select * from UAct final;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
使用聚合时,不需要使用Sign列:
SELECT
UserID,
sum(PageViews) AS PageViews,
sum(Duration) AS Duration
FROM UAct
GROUP BY UserID
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │ 6 │ 185 │
└─────────────────────┴───────────┴──────────┘
统计数量:
select count() FROM UAct
┌─count()─┐
│ 3 │
└─────────┘
手动执行计划外的合并:
optimize table UAct final;
select * FROM UAct
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
AggregatingMergeTree
DROP TABLE IF EXISTS test_aggregates;
CREATE TABLE test_aggregates
(
d Date,
sumV AggregateFunction(sum, UInt64),
uniqV AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY d;
INSERT INTO test_aggregates
SELECT
toDate('2020-06-01') AS d,
sumState(number) as sumV,
uniqState(number) AS uniqV
FROM
(
SELECT toUInt64(number%8) as number FROM system.numbers LIMIT 10
);
SELECT sumMerge(sumV), uniqMerge(uniqV) FROM test_aggregates;
示例2:物化视图的使用
drop table t_basic;
create table t_basic(key String, sign UInt8, userId String) ENGINE=MergeTree order by key;
drop table t_m_view;
CREATE MATERIALIZED VIEW t_m_view
ENGINE = AggregatingMergeTree() ORDER BY (key)
AS SELECT
key,
sumState(sign) AS sumSign,
uniqState(userId) AS uniqUsers
FROM t_basic
GROUP BY key;
插入3条记录:
insert into t_basic values('a', 1, '11'),
('a', 2, '22'),
('a', 3, '11');
select * from t_basic;
┌─key─┬─sign─┬─userId─┐
│ a │ 1 │ 11 │
│ a │ 2 │ 22 │
│ a │ 3 │ 11 │
└─────┴──────┴────────┘
select key, sumMerge(sumSign), uniqMerge(uniqUsers) from t_m_view group by key;
┌─key─┬─sumMerge(sumSign)─┬─uniqMerge(uniqUsers)─┐
│ a │ 6 │ 2 │
└─────┴───────────────────┴──────────────────────┘