ClickHouse 之 一 mergeTree引擎

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─┐
│ 4324182021466249494514611 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 43241820214662494945146-11 │
│ 4324182021466249494618512 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
通过两个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─┐
│ 432418202146624949461852 │
└─────────────────────┴───────────┴──────────┴─────────┘
如果不使用聚合,可以对FROM子句使用FINAL修饰符进行强制合并:
:) select * from UAct FINAL;
​
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494618512 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

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─┐
│      1100 │ xiaohe1  │ 2020-04-21 │
│      2100 │ xiaohe2  │ 2020-04-22 │
│      2101 │ xiaohe2  │ 2020-04-24 │
│      3102 │ xiaohe3  │ 2020-04-23 │
│      3103 │ xiaohe3  │ 2020-04-23 │
└────────┴───────────┴──────────┴────────────┘
┌─UserID─┬─CounterID─┬─UserName─┬──EventDate─┐
│      1100 │ xiaohe1  │ 2020-04-20 │
└────────┴───────────┴──────────┴────────────┘
┌─UserID─┬─CounterID─┬─UserName─┬──EventDate─┐
│      2100 │ 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─┐
│      1100 │ xiaohe1  │ 2020-04-20 │
│      2100 │ xiaohe2  │ 2020-04-23 │
│      2101 │ xiaohe2  │ 2020-04-24 │
│      3102 │ xiaohe3  │ 2020-04-23 │
│      3103 │ 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─┐
│ 4324182021466249494111-1 │
│ 4324182021466249494212-1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 43241820214662494943131 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 43241820214662494944141 │
└─────────────────────┴───────────┴──────────┴──────┘
​
4 rows in set. Elapsed: 0.011 sec.
执行计划外的片段合并操作:
optimize table UAct;
查看折叠后的数据:
xiaochen001 :) select * from UAct;
​
SELECT *
FROM UAct
​
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494111-1 │
│ 43241820214662494944141 │
└─────────────────────┴───────────┴──────────┴──────┘
​
2 rows in set. Elapsed: 0.005 sec.
从示例中可以观察到,最终的数据片段保留了第一个"cancel"行和最后一个"state"行。

================================================================================================
示例2: 聚合查询
================================================================================================

示例数据:
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 432418202146624949451461 │
│ 43241820214662494945146-1 │
│ 432418202146624949461851 │
└─────────────────────┴───────────┴──────────┴──────┘
建表:
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─┐
│ 432418202146624949451461 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 43241820214662494945146-1 │
│ 432418202146624949461851 │
└─────────────────────┴───────────┴──────────┴──────┘
通过两个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─┐
│ 43241820214662494946185 │
└─────────────────────┴───────────┴──────────┘
如果不使用聚合,可以对FROM子句使用FINAL修饰符进行强制合并:
:) select * from UAct FINAL;
​
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 432418202146624949461851 │
└─────────────────────┴───────────┴──────────┴──────┘
​
1 rows in set. Elapsed: 0.008 sec.
这种数据查询的方法非常低效,不要在大表中使用这种方法。


================================================================================================
示例3: Cancel状态行取反的聚合查询
================================================================================================
示例数据:
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 432418202146624949451461 │
│ 4324182021466249494-5-146-1 │
│ 432418202146624949461851 │
└─────────────────────┴───────────┴──────────┴──────┘
这个方法的核心思想是仅考虑关键字段,在"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─┐
│ 432418202146624949461851 │
└─────────────────────┴───────────┴──────────┴──────┘
使用聚合时,不需要使用Sign列:
SELECT
    UserID,
    sum(PageViews) AS PageViews,
    sum(Duration) AS Duration
FROM UAct
GROUP BY UserID
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 43241820214662494946185 │
└─────────────────────┴───────────┴──────────┘
统计数量:
select count() FROM UAct
┌─count()─┐
│       3 │
└─────────┘
手动执行计划外的合并:
optimize table UAct final;
​
select * FROM UAct
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 432418202146624949461851 │
└─────────────────────┴───────────┴──────────┴──────┘

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   │    111     │
│ a   │    222     │
│ a   │    311     │
└─────┴──────┴────────┘


select key, sumMerge(sumSign), uniqMerge(uniqUsers) from t_m_view group by key;
┌─key─┬─sumMerge(sumSign)─┬─uniqMerge(uniqUsers)─┐
│ a   │                 62 │
└─────┴───────────────────┴──────────────────────┘
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值