ClickHouse SummingMergeTree 介绍与使用

SummingMergeTree 是继承于 MergeTree，区别在于，当在合并 SummingMergeTree 的数据片段(merge data parts)时，ClickHous 会把所有具有相同主键的行合并为一行(replace all the lows with the same primary key with one row)，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式为单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

建表语句：

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, ...]

SummingMergeTree 参数：

• columns: 包含了需要被汇总(sum)的列的列名的元组(tuple)。可选参数，所选的列必须是数值类型，并且不可位于主键中。

旧的对应建表语句如下，在新项目中不建议使用此方式：

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(date-column [, sampling_expression], (primary, key), index_granularity, [columns])

接下来举个例子，来说明 SummingMergeTree 是如何工作的。

我们创建如下一个 SummingMergeTree 表：

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

并向表中插入数据：

INSERT INTO default.summtt Values(5,5),(5,2),(4,6),(1,2),(2,2),(1,3),(3,2),(3,5),(1,6),(2,9),(4,7),(4,6),(43,5),(5,5),(3,6)
INSERT INTO default.summtt Values(43,5),(5,5),(3,6)

在未合并parts查询所有数据：

SELECT * FROM default.summtt;

这时输出的是未汇总的一条条数据：

此时，ClickHouse还没有汇总所有行，如果需要通过’key’进行汇总聚合，需要用到 sum 和 GROUP BY子句：

SELECT key, sum(value) FROM summtt GROUP BY key

结果如下:

key value
4	19
3	19
2	11
5	17
1	11
43	10

在ClickHouse进行合并parts之后，或者手动触发合并:

OPTIMIZE TABLE default.summtt

此时再查询所有数据：

SELECT * FROM default.summtt;

可见数据行数明显减少，相同主键的列进行了汇总：

从以上例子可以大概看出 SummingMergeTree 的数据处理过程。当数据被插入到表中时，他们将被原样保存。ClickHouse 定期合并插入的数据片段，并在这个时候对所有具有相同主键的行中的列进行汇总，将这些行替换为包含汇总数据的一行记录。

ClickHouse 会按 parts 进行合并数据，以致不同的 parts 中会包含具有相同主键的行，即单个汇总 parts 将会是不完整的。因此，当查询时，聚合函数 sum() 和 GROUP BY 子句是必须的。

汇总(Summation)规则：

• 建表语句中参数 columns 定义的列，并且是数值型数据会进行汇总(summarized)。
• 如果所有汇总的列都为0，那么这行数据会被删掉。(If the values were 0 in all of the columns for summation, the row is deleted.)
• 如果列不在主键中且无法被汇总，则会在现有的值中任选一个。(If column is not in the primary key and is not summarized, an arbitrary value is selected from the existing ones.)
• 主键所在的列中的值不会被汇总。(The values are not summarized for columns in the primary key.)

为了避免因为使用不正确的主键组合而导致丢失有价值的数据，官方推荐与MergeTree引擎一起使用。此文不对此进行扩展，接下来介绍日常使用中如何扩展汇总的columns。

扩展汇总的列(columns参数)，需要改动表结构，但是在 ALTER 语句中，并没有更改columns的方式，而且我们使用的是 ReplicatedSummingMergeTree 引擎，这就更加增加了复杂度。整个过程大概如下：

1. 把复制表转换为非复制表
   在这里就是把表引擎 ReplicatedSummingMergeTree 转换为 SummingMergeTree。

首先使用其他名称创建 SummingMergeTree 表，将具有 ReplicatedSummingMergeTree 表数据的目录中的所有数据移动到新表的数据目录中，然后删除 ReplicatedSummingMergeTree 表并重启服务。
此时zookeeper中对应的znode会消失(/clickhouse/tables/…/replicas/repxxx)。

其他副本可以进行删除，此时，表只剩一个服务，引擎是 SummingMergeTree。

2. SummingMergeTree 增加汇总列(summarized columns)
   首先进行detach：

   DETACH TABLE default.summtt

   更改表的元数据，增加需要汇总的列(或者列名)，如:

   vim /var/lib/clickhouse/metadata/default/summtt.sql

   最后对表进行attach：

   ATTACH TABLE default.summtt

   接下来创建一张新表，表结构与更改后(增加列名，增加汇总列等)的结构一致，假如新建的表名为 summtt_new ，使用如下命令从旧的表(summtt)复制数据:

   alter table default.summtt_new attach partition '2020-10-11' from default.summtt;

   上述语句中partition可通过如下语句查询:

   SELECT partition, name FROM `system`.parts WHERE table = 'summit';

   复制完成之后，对新旧表进行重命名:

   RENAME TABLE default.summtt default.summtt_old;
   RENAME TABLE default.summtt_new default.summtt;

3. SummingMergeTree 转换为 ReplicatedSummingMergeTree

重命名现有的 MergeTree 表(summtt to summtt_old)，然后使用旧名称创建 ReplicatedMergeTree 表(summtt)。 将数据从旧表移动到新表（/var/lib/clickhouse/data/default/summtt/）目录内的 ‘detached’ 目录中。然后在其中一个副本上运行ALTER TABLE ATTACH PARTITION，将这些数据片段添加到工作集中，如:

ALTER TABLE default.summtt ATTACH PARTITION '2020-10-11'

参考

• ClickHouse SummingMergeTree
• ClickHouse ALTER
• ClickHouse OPTIMIZE Statement
• ClickHouse Data Replication
• Add summarized column to SummingMergeTree