ClickHouse MergeTree家族特殊表引擎

在前面的文章中，我们详细介绍了ClickHouse MergeTree表引擎的使用场景、原理、数据存储结构、建表语句以及索引优化。详见《ClickHouse MergeTree表引擎和建表语句》、《ClickHouse MergeTree二级索引/跳数索引》。

MergeTree引擎表是使用最为广泛的表，除了MergeTree引擎表以外，MergeTree家族还有一些特殊的表引擎，在一些特殊场景中能够表现出更好地性能。例如，统计电商平台每天的销售额等。

1. AggregatingMergeTree

作为MergeTree家族中的一员，AggregatingMergeTree 表引擎也继承自MergeTree引擎。从名字也可以看出，AggregatingMergeTree 主要是一种聚合表引擎，可以把一个数据片段内的原始数据根据主键（确切地说是排序键）按照给定的聚合函数进行增量聚合。聚合函数主要有两种类型：

（1）AggregateFunction

AggregateFunction类型聚合函数具有实现定义的中间状态，该状态可以序列化为AggregateFunction(…)数据类型，并通常通过物化视图存储在表中，类似于Tensorflow1中的静态图和Spark中的懒加载算子，只定义计算逻辑，但不执行。。

生成聚合函数状态的方法是在建表的时候定义 AggregateFunction 类型字段，插入数据的时候使用带有 State 后缀的函数写入，- State 函数返回的是状态，而不是最终值。换句话说，它们返回AggregateFunction类型的值。读取数据的时候使用带有 Merge 后缀的函数查询就可以了。例如：

建表，uniq、sum、quantiles 都是clickhouse支持的聚合函数
CREATE TABLE t
(
	column0 UInt16,
    column1 AggregateFunction(uniq, UInt64),
    column2 AggregateFunction(sum, Decimal32(2)),
    column3 AggregateFunction(quantiles(0.5, 0.9), UInt64)
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]

插入数据
INSERT INTO TABLE t SELECT 1, uniqState(10), sumState(toDecimal32(10000,2)), quantilesState(0.5, 0.9)(25);

查询数据
SELECT uniqMerge(column1), sumMerge(column2) FROM t GROUP BY ... ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

查询数据时，需使用 GROUP BY 子句并且要使用与插入时相同的聚合函数，带有Merge后缀的聚合函数接受一组状态(State)，将它们组合在一起，并返回完整数据聚合的结果。例如，以下两个查询返回相同的结果:

SELECT uniq(UserID) FROM table;
SELECT uniqMerge(state) FROM (SELECT uniqState(UserID) AS state FROM table GROUP BY RegionID);
1
2

（2）SimpleAggregateFunction

AggregateFunction 有一个有一个很大的缺点，需要存储数据的全部状态，对于sum、max等聚合操作完全可以对已有数据进行预计算，写入新的数据时，只需要把新的聚合结果和预聚合结果进行聚合更新就可以了，AggregateFunction 似乎完全体现不出 Aggregate 的优势。因此，对于这种可以预聚合的操作，clickhouse又提供了另一种数据类型——SimpleAggregateFunction。SimpleAggregateFunction的性能高于AggregateFunction，支持的聚合函数如下：

any
anyLast
min
max
sum
sumWithOverflow
groupBitAnd
groupBitOr
groupBitXor
groupArrayArray
groupUniqArrayArray
sumMap
minMap
maxMap
1
2
3
4
5
6
7
8
9
10
11
12
13
14

建表语句如下：

CREATE TABLE simple (id UInt64, val SimpleAggregateFunction(sum, Double)) ENGINE=AggregatingMergeTree ORDER BY id;
1

※注意： 生成SimpleAggregateFunction聚合函数值的常用方法是调用带有 SimpleState后缀的聚合函数，如 sumSimpleState。

（3）物化视图建表

可以发现上面这种形式是很麻烦的，一般情况下很少使用，更常用的方法是通过物化视图的形式创建 AggregatingMergeTree 表（关于物化视图的概念，我们后面介绍，可以参考其他数据库view理解，相当于一张虚拟表，但是与普通视图完全不同）。先建立一个 MergeTree 表，然后建立AggregatingMergeTree引擎的物化视图跟踪基础表。这样即保存了明细数据，不破坏原有数据结构，又可以跟踪聚合结果。例如：

建立基础表
CREATE TABLE test.visits
(
	CounterID UInt16,
	StartDate Date,
	Sign UInt8,
	UserID UInt32,
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(StartDate)
ORDER BY CounterID, StartDate;

创建AggregatingMergeTree引擎物化视图
CREATE MATERIALIZED VIEW test.basic
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECT
    CounterID,
    StartDate,
    sumState(Sign)    AS Visits,
    uniqState(UserID) AS Users
FROM test.visits
GROUP BY CounterID, StartDate;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在新增数据时，把明细数据写入基础表 test.visits 中，在查询聚合结果时，直接查物化视图 test.basic 即可：

SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM test.basic
GROUP BY StartDate
ORDER BY StartDate;
1
2
3
4
5
6
7

2. SummingMergeTree

SummingMergeTree 同样继承自 MergeTree，可以认为是 AggregatingMergeTree 的一种特殊方式，同样基于主键(或者更准确地说，使用相同的排序键)聚合，在使用时也多是基于一张明细表建立物化视图，这样不会丢失原有数据，如果发现聚合键不合适，还可以修改。另外，需要注意：

• SummingMergeTree 只能对非主键数值型字段进行聚合，如果用于汇总的所有列中的值均为0，则该行会被删除，对于不能聚合的数据类型，将随机选择一条记录。
• SummingMergeTree 会定期合并插入的数据片段，也就是说当查询的时候可能还有数据没有进行聚合处理，而且数据是分片段合并（不跨分区sum），一个聚合键会对应多条数据，所以使用过程中一定还要使用 sum() GROUP BY 查询，SummingMergeTree 起到的是优化效果。
• SummingMergeTree 也可处理嵌套类型数据，而且支持和 ORDER BY 字段组成复合key，查询的时候使用sumMap函数进行聚合操作。

建表语句如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
1
2
3
4
5
6
7
8
9
10

SummingMergeTree中的 [columns] 表示需要聚合的列，如果没有指定，表示对主键之外的所有数值型字段进行聚合。例如：

建表
CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key;

插入数据
INSERT INTO summtt Values(1,1),(1,2),(2,1);

查询
SELECT key, sum(value) FROM summtt GROUP BY key;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在前面介绍MergeTree表引擎的时候，我们有提到，一般情况下我们不需要单独指定主键字段，ORDER BY 字段就是主键字段。但是在 AggregatingMergeTree 和 SummingMergeTree 表中可以指定 PRIMARY KEY 和 ORDER BY 字段不同，因为如果不单独定义主键，而聚合键字段又比较多，就会导致主键过多，降低写效率，而且可能需要增加新的聚合维度，泛化能力降低。此时就可以只保留少量的列在主键当中用于提升扫描效率，将其余的维度列添加到排序键元组中。

例如：假设有一张表有col1，col2，col3，col4四个字段，我们需要按照col1，col2进行聚合，并经常需要通过col1字段过滤，则可以按照如下定义：

PRIMARY KEY col1
ORDER BY  (col1,col2)
1
2

3. ReplacingMergeTree

ReplacingMergeTree 引擎表会在后台合并时对 ORDER BY 字段相同的行进行去重。建表语句如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
1
2
3
4
5
6
7
8
9
10
11

※ 注意：

• ReplacingMergeTree 表是按照 ORDER BY 字段去重，不是 PRIMARY KEY 字段。
• ReplacingMergeTree 是在Merge的时候才会进行去重，所以不是绝对保证的。因此，ReplacingMergeTree 适用于在后台清除重复数据以节省空间，但不能保证没有重复。
• [ver] 是版本号字段参数，可以是 UInt*, Date, DateTime, DateTime64 类型。在Merge的时候会保留 版本号最大的那条记录，如果没有指定 [ver]，则保留最新的（最后写入的）记录。
• 合并去重是按照分区去重的，不会跨分区去重。

4. CollapsingMergeTree

CollapsingMergeTree 同样继承自 MergeTree 表引擎，主要用来删除数据。我们知道MergeTree 表改、删的效率是比较低的，CollapsingMergeTree 引擎采用了 “以增代删” 的思想。建表语句如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
1
2
3
4
5
6
7
8
9
10

CollapsingMergeTree 引擎表有一个 sign 参数，sign 是一个类型为 Int8 的字段名，可以取值 1 和 -1。1 表示正常插入的数据，-1表示删除上一条 sign = 1的记录，所以 CollapsingMergeTree 删除数据是通过插入一条新记录实现，这也是 Collapsing （折叠）的含义所在。对于同一个分区内（不能跨分区）ORDER BY 字段相同的记录，如果两条记录的sign先后分别是1和-1，则在Merge的时候，两条记录会同时被删除。注意删除（折叠）规则如下：

• 如果两条记录的写入顺序是 sign = -1 和 sign = 1，则两条记录都不会被删除。
• 如果 sign = 1 和 sign = -1 的记录数是相等的，且最后一行是 sign = 1 的记录，则保留第一条sign = -1 的记录和最后一条sign = 1的记录，可以发现第一条规则就是第二条规则的特殊情况。
• 如果 sign = 1 的记录数大于 sign = -1 的记录数，则保留最后一条 sign = 1 的记录。如果一种数据的数量比另一种多出两条或者两条以上，合并将照常进行，但ClickHouse将此情况视为逻辑错误，并将其记录在服务器日志中。
• 如果 sign = -1 的记录数大于 sign = 1 的记录数，则保留第一条 sign = -1 的记录。
• 其他情况不保留数据。

※注意： CollapsingMergeTree 也是后台操作，不是实时的，只有Merge的时候才会折叠（删除）数据，而且如果有多个任务在操作同一个表，可能会导致 sign 错乱，造成不可预知的结果，使用时要慎重考虑（建议使用下一节的VersionedCollapsingMergeTree表）。但是，CollapsingMergeTree 表无疑变相实现了delete和update操作，而且以空间换时间，提高了效率。当然，也可以通过 GROUP BY + HAVING sum(Sign) > 0 的形式查询：

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0
1
2
3
4
5
6
7

也可以为FROM子句使用FINAL修饰符，但是这种方法的效率比较低，尤其不要用在大表上面：

SELECT * FROM UAct FINAL
1

或者当插入 sign = -1 的记录时，把关注的数值列同样取反插入，这样当查询计算sum(数值列)的时候，也可以不关注sign。

5. VersionedCollapsingMergeTree

根绝第4节CollapsingMergeTree的介绍可知，CollapsingMergeTree对数据的插入顺序是有严格要求的，不合理的插入顺序可能会导致难以预测的结果。针对这种情况，clickhouse又推出了VersionedCollapsingMergeTree表引擎。VersionedCollapsingMergeTree 引擎表建表语句如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
1
2
3
4
5
6
7
8
9
10

VersionedCollapsingMergeTree 引擎参数 sign 和 CollapsingMergeTree 一样，Int8类型，1 表示“state”（插入有效数据），-1 表示“cancel”（删除上一次插入的数据）；参数 version 是版本控制字段，UInt* 类型。

VersionedCollapsingMergeTree 通过 version 列来控制乱序数据的折叠，对数据的写入顺序没有要求。但是 version 不同的记录不会相互折叠（删除），即使新的 version 更大，因为 VersionedCollapsingMergeTree 的实现方式是把 version 字段隐式加到 ORDER BY 最后。

同样，VersionedCollapsingMergeTree 也是在合并的时候才会进行折叠，因此在使用时最好通过聚合查询，例如统计数据量使用 sum(Sign)，而不是 count()，求和使用 sum(Sign * x) + HAVING sum(Sign) > 0 代替 sum(x)。

总之，对于需要频繁删除、修改的数据 CollapsingMergeTree、VersionedCollapsingMergeTree 是一种合理、高效的表引擎，但是务必要注意数据写入顺序、数量的管理。

6. GraphiteMergeTree

GraphiteMergeTree 引擎表是为存储 Graphite 后台数据而设计的，可以用来优化rollup，使用较少。Graphite 是一个企业级的监控工具，可以在廉价的硬件上良好运行。

7. Data Replication

MergeTree 和上述 6 中表引擎都有对应的 Replication 表引擎，除了功能和各自对应表引擎一样外，还实现了副本功能，换句话说就是数据HA。表引擎名称分别是 Replicated 加对应表引擎名称，例如：ReplicatedMergeTree、ReplicatedReplacingMergeTree 等。因为副本表往往是和分片一起创建（通过集群），因此，关于副本和分片的配置、使用我们将在下一篇文章中一起详细介绍。

※ 注意： 和HDFS不同，ClickHouse MergeTree 家族表的副本机制是表级别的，不是库级别的，但是可以通过集群配置。另外，Replication 表功能是实现数据副本备份，不是横向扩展能力（横向扩展是通过分片来实现的），两个节点可以组成两副本表，存储数据一样，但是存储能力并没有增加。

参考文章

[1] https://blog.csdn.net/u012551524/article/details/112118734