ClickHouse入门实践表引擎

MergeTree 系列表引擎

目前在 ClickHouse 中，按照特点可以将表引擎大致分成 6 个系列，分别是合并树、外部存储、内存、文件、接口和其他，每一个系列的表引擎都有着独自的特点与使用场景。在它们之中，最为核心的当属 MergeTree 系列，因为它们拥有最为强大的性能和最广泛的使用场合。

大家应该已经知道了 MergeTree 有两层含义：

其一，表示合并树表引擎家族；

其二，表示合并树家族中最基础的 MergeTree 表引擎。

而在整个家族中，除了基础表引擎 MergeTree 之外，常用的表引擎还有 ReplacingMergeTree、SummingMergeTree、AggregatingMergeTree、CollapsingMergeTree 和 VersionedCollapsingMergeTree。每一种合并树的变种，在继承了基础 MergeTree 的能力之后，又增加了独有的特性。其名称中的“合并”二字奠定了所有类型 MergeTree 的基因，它们的所有特殊逻辑，都是在触发合并的过程中被激活的。在本章后续的内容中，会逐一介绍它们的特点以及使用方法。

MergeTree

MergeTree 作为家族系列最基础的表引擎，提供了数据分区、一级索引和二级索引等功能。

数据 TTL

TTL 即 Time To Live，顾名思义，它表示数据的存活时间。在 MergeTree 中，可以为某个列字段或整张表设置 TTL。当时间到达时，如果是列字段级别的 TTL，则会删除这一列的数据；如果是表级别的 TTL，则会删除整张表的数据；如果同时设置了列级别和表级别的 TTL，则会以先到期的那个为主。无论是列级别还是表级别的 TTL，都需要依托某个 DateTime 或 Date 类型的字段，通过对这个时间字段的 INTERVAL 操作，来表述 TTL 的过期时间，例如：

TTL time_col + INTERVAL 3 DAY

上述语句表示数据的存活时间是 time_col 时间的 3 天之后。又例如：

TTL time_col + INTERVAL 1 MONTH 

上述语句表示数据的存活时间是 time_col 时间的 1 月之后。INTERVAL 完整的操作包括 SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 和 YEAR。

列级别 TTL

如果想要设置列级别的 TTL，则需要在定义表字段的时候，为它们声明 TTL 表达式，主键字段不能被声明 TTL。以下面的语句为例：

CREATE TABLE ttl_table_v1 (
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 10 SECOND,
    type UInt8 TTL create_time + INTERVAL 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id ;

其中，create_time 是日期类型，列字段 code 与 type 均被设置了 TTL，它们的存活时间是在 create_time 的取值基础之上向后延续 10 秒。现在写入测试数据，其中第一行数据 create_time 取当前的系统时间，而第二行数据的时间比第一行增加 10 分钟：

SELECT * FROM ttl_table_v1;

接着心中默数 10 秒，然后执行 optimize 命令强制触发 TTL 清理：

OPTIMIZE TABLE ttl_table_v1 FINAL;

再次查询 ttl_table_v1 则能够看到，由于第一行数据满足 TTL 过期条件（当前系统时间 >= create_time + 10 秒），它们的 code 和 type 列会被还原为数据类型的默认值：

如果想要修改列字段的 TTL，或是为已有字段添加 TTL，则可以使用 ALTER 语句，示例如下：

ALTER TABLE ttl_table_v1 MODIFY column code String TTL create_time + INTERVAL 1 DAY

目前 ClickHouse 没有提供取消列级别 TTL 的方法。

表级别 TTL

如果想要为整张数据表设置 TTL，需要在 MergeTree 的表参数中增加 TTL 表达式，例如下面的语句：

CREATE TABLE tt1_table_v2(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 1 MINUTE ,
    type UInt8
) ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY ;

ttl_table_v2 整张表被设置了 TTL，当触发 TTL 清理时，那些满足过期时间的数据行将会被整行删除。同样，表级别的 TTL 也支持修改，修改的方法如下：

ALTER TABLE tt1_table_v2 MODIFY TTL create_time + INTERVAL 3 DAY;

表级别 TTL 目前也没有取消的方法。

TTL 的运行机理

在知道了列级别与表级别 TTL 的使用方法之后，现在简单聊一聊 TTL 的运行机理。如果一张 MergeTree 表被设置了 TTL 表达式，那么在写入数据时，会以数据分区为单位，在每个分区目录内生成一个名为 ttl.txt 的文件。以刚才示例中的 ttl_table_v2 为例，它被设置了列级别 TTL：

code String TTL create_time + INTERVAL 1 MINUTE

同时被设置了表级别的 TTL：

TTL create_time + INTERVAL 1 DAY

那么，在写入数据之后，它的每个分区目录内都会生成 ttl.txt 文件：

进一步查看 ttl.txt 的内容：

通过上述操作会发现，原来 MergeTree 是通过一串 JSON 配置保存了 TTL 的相关信息，其中：
❑ columns 用于保存列级别 TTL 信息；
❑ table 用于保存表级别 TTL 信息；
❑ min 和 max 则保存了当前数据分区内，TTL 指定日期字段的最小值、最大值分别与 INTERVAL 表达式计算后的时间戳。

如果将 table 属性中的 min 和 max 时间戳格式化，并分别与 create_time 最小与最大取值对比：

则能够印证，ttl.txt 中记录的极值区间恰好等于当前数据分区内 create_time 最小与最大值增加 1 天（1 天 = 86400 秒）所表示的区间，与 TTL 表达式 create_time +INTERVAL 1 DAY 的预期相符。

在知道了 TTL 信息的记录方式之后，现在看看它的大致处理逻辑。
（1）MergeTree 以分区目录为单位，通过 ttl.txt 文件记录过期时间，并将其作为后续的判断依据。
（2）每当写入一批数据时，都会基于 INTERVAL 表达式的计算结果为这个分区生成 ttl. txt 文件。
（3）只有在 MergeTree 合并分区时，才会触发删除 TTL 过期数据的逻辑。
（4）在选择删除的分区时，会使用贪婪算法，它的算法规则是尽可能找到会最早过期的，同时年纪又是最老的分区（合并次数更多，MaxBlockNum 更大的）。
（5）如果一个分区内某一列数据因为 TTL 到期全部被删除了，那么在合并之后生成的新分区目录中，将不会包含这个列字段的数据文件（.bin 和．mrk）。

这里还有几条 TTL 使用的小贴士。
（1）TTL 默认的合并频率由 MergeTree 的 merge_with_ttl_timeout 参数控制，默认 86400 秒，即 1 天。它维护的是一个专有的 TTL 任务队列。有别于 MergeTree 的常规合并任务，如果这个值被设置的过小，可能会带来性能损耗。
（2）除了被动触发 TTL 合并外，也可以使用 optimize 命令强制触发合并。例如，触发一个分区合并：

optimize TABLE table_name;

触发所有分区合并：

optimize TABLE table_name FINAL;

（3）ClickHouse 目前虽然没有提供删除 TTL 声明的方法，但是提供了控制全局 TTL 合并任务的启停方法：

SYSTEM STOP/START TTL MERGES;

虽然还不能做到按每张 MergeTree 数据表启停，但聊胜于无吧。

ReplacingMergeTree

虽然 MergeTree 拥有主键，但是它的主键却没有唯一键的约束。这意味着即便多行数据的主键相同，它们还是能够被正常写入。在某些使用场合，用户并不希望数据表中含有重复的数据。ReplacingMergeTree 就是在这种背景下为了数据去重而设计的，它能够在合并分区时删除重复的数据。它的出现，确实也在一定程度上解决了重复数据的问题。为什么说是“一定程度”？此处先按下不表。

创建一张 ReplacingMergeTree 表的方法与创建普通 MergeTree 表无异，只需要替换 Engine：

ENGINE = ReplacingMergeTree(ver)

其中，ver 是选填参数，会指定一个 UInt*、Date 或者 DateTime 类型的字段作为版本号。这个参数决定了数据去重时所使用的算法。

接下来，用一个具体的示例说明它的用法。首先执行下面的语句创建数据表：

CREATE TABLE replace_table(
    id String,
    code String,
    create_time DateTime
) ENGINE = ReplacingMergeTree()
partition by toYYYYMM(create_time)
ORDER BY(id,code)
PRIMARY KEY id ;

注意这里的 ORDER BY 是去除重复数据的关键，排序键 ORDER BY 所声明的表达式是后续作为判断数据是否重复的依据。在这个例子中，数据会基于 id 和 code 两个字段去重。假设此时表内的测试数据如下：

那么在执行 optimize 强制触发合并后，会按照 id 和 code 分组，保留分组内的最后一条（观察 create_time 日期字段）：

optimize TABLE replace_table FINAL;

将其余重复的数据删除：

从执行的结果来看，ReplacingMergeTree 在去除重复数据时，确实是以 ORDERBY 排序键为基准的，而不是 PRIMARY KEY。因为在上面的例子中，ORDER BY 是 (id, code)，而 PRIMARY KEY 是 id，如果按照 id 值去除重复数据，则最终结果应该只剩下 A001、A002 和 A003 三行数据。

到目前为止，ReplacingMergeTree 看起来完美地解决了重复数据的问题。事实果真如此吗？现在尝试写入一批新数据：

insert into replace_table
values
('A001','C1','2020-07-02 12:01:01');

写入之后，执行 optimize 强制分区合并，并查询数据：

再次观察返回的数据，可以看到 A001:C1 依然出现了重复。这是怎么回事呢？这是因为 ReplacingMergeTree 是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。这就是上面说 ReplacingMergeTree 只是在一定程度上解决了重复数据问题的原因。

现在接着说明 ReplacingMergeTree 版本号的用法。以下面的语句为例：

CREATE TABLE replace_table_v (
    id String,
    code String,
    create_time DateTime
) ENGINE = ReplacingMergeTree(create_time)
PARTITION  BY toYYYYMM(create_time)
ORDER BY id ;

replace_table_v 基于 id 字段去重，并且使用 create_time 字段作为版本号，假设表内的数据如下所示：

那么在删除重复数据的时候，会保留同一组数据内 create_time 时间最长的那一行：

在知道了 ReplacingMergeTree 的使用方法后，现在简单梳理一下它的处理逻辑。
（1）使用 ORBER BY 排序键作为判断重复数据的唯一键。
（2）只有在合并分区的时候才会触发删除重复数据的逻辑。
（3）以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。
（4）在进行数据去重时，因为分区内的数据已经基于 ORBER BY 进行了排序，所以能够找到那些相邻的重复数据。
（5）数据去重策略有两种：
❑ 如果没有设置 ver 版本号，则保留同一组重复数据中的最后一行。
❑ 如果设置了 ver 版本号，则保留同一组重复数据中 ver 字段取值最大的那一行。

SummingMergeTree

假设有这样一种查询需求：终端用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的（GROUP BY 条件明确，且不会随意改变）。
对于这样的查询场景，在 ClickHouse 中如何解决呢？最直接的方案就是使用 MergeTree 存储数据，然后通过 GROUP BY 聚合查询，并利用 SUM 聚合函数汇总结果。这种方案存在两个问题。

❑ 存在额外的存储开销：终端用户不会查询任何明细数据，只关心汇总结果，所以不应该一直保存所有的明细数据。

❑ 存在额外的查询开销：终端用户只关心汇总结果，虽然 MergeTree 性能强大，但是每次查询都进行实时聚合计算也是一种性能消耗。

SummingMergeTree 就是为了应对这类查询场景而生的。顾名思义，它能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总合并成一行，这样既减少了数据行，又降低了后续汇总查询的开销。

在先前介绍 MergeTree 原理时曾提及，在 MergeTree 的每个数据分区内，数据会按照 ORDER BY 表达式排序。主键索引也会按照 PRIMARY KEY 表达式取值并排序。而 ORDER BY 可以指代主键，所以在一般情形下，只单独声明 ORDER BY 即可。此时，ORDER BY 与 PRIMARY KEY 定义相同，数据排序与主键索引相同。

如果需要同时定义 ORDER BY 与 PRIMARY KEY，通常只有一种可能，那便是明确希望 ORDER BY 与 PRIMARY KEY 不同。这种情况通常只会在使用 SummingMergeTree 或 AggregatingMergeTree 时才会出现。这是为何呢？这是因为 SummingMergeTree 与 AggregatingMergeTree 的聚合都是根据 ORDER BY 进行的。由此可以引出两点原因：主键与聚合的条件定义分离，为修改聚合条件留下空间。

现在用一个示例说明。假设一张 SummingMergeTree 数据表有 A、B、C、D、E、F 六个字段，如果需要按照 A、B、C、D 汇总，则有：

ORDER BY (A,B,C,D)

但是如此一来，此表的主键也被定义成了 A、B、C、D。而在业务层面，其实只需要对字段 A 进行查询过滤，应该只使用 A 字段创建主键。所以，一种更加优雅的定义形式应该是：

ORDER BY (A,B,C,D) PRIMARY KEY A

如果同时声明了 ORDER BY 与 PRIMARY KEY, MergeTree 会强制要求 PRIMARYKEY 列字段必须是 ORDER BY 的前缀。例如下面的定义是错误的：

ORDER BY(B,C) PRIMARY KEY A

PRIMARY KEY 必须是 ORDER BY 的前缀：

ORDER BY (B,C) PRIMARY KEY B

这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。

假设现在业务发生了细微的变化，需要减少字段，将先前的 A、B、C、D 改为按照 A、B 聚合汇总，则可以按如下方式修改排序键：

ALTER TABLE table_name MODIFY ORDER BY (A,B)

在修改 ORDER BY 时会有一些限制，只能在现有的基础上减少字段 。如果是新增排序字段，则只能添加通过 ALTER ADD COLUMN 新增的字段。但是 ALTER 是一种元数据的操作，修改成本很低，相比不能被修改的主键，这已经非常便利了。

现在开始正式介绍 SummingMergeTree 的使用方法。表引擎的声明方式如下所示：

ENGINE = SummingMergeTree((col1,col2,...))

其中，col1、col2 为 columns 参数值，这是一个选填参数，用于设置除主键外的其他数值类型字段，以指定被 SUM 汇总的列字段。如若不填写此参数，则会将所有非主键的数值类型字段进行 SUM 汇总。接来下用一组示例说明它的使用方法：

CREATE TABLE summing_table(
    id String,
    city String,
    v1 UInt32,
    v2 Float64,
    create_time DateTime
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id,city)
PRIMARY KEY id ;

注意，这里的 ORDER BY 是一项关键配置，SummingMergeTree 在进行数据汇总时，会根据 ORDER BY 表达式的取值进行聚合操作。假设此时表内的数据如下所示：

执行 optimize 强制进行触发和合并操作：

optimize TABLE summing_table FINAL

再次查询，表内数据会变成下面的样子：

至此能够看到，在第一个分区内，同为 A001:wuhan 的两条数据汇总成了一行。其中，v1 和 v2 被 SUM 汇总，不在汇总字段之列的 create_time 则选取了同组内第一行数据的取值。而不同分区之间，数据没有被汇总合并。

SummingMergeTree 也支持嵌套类型的字段，在使用嵌套类型字段时，需要被 SUM 汇总的字段名称必须以 Map 后缀结尾，例如：

CREATE TABLE summing_table_nested(
    id1 String,
    nestMap Nested(
        id UInt32,
        key UInt32,
        val UInt64
        ),
        create_time DateTime
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY id1 ;

在使用嵌套数据类型的时候，默认情况下，会以嵌套类型中第一个字段作为聚合条件 Key。假设表内的数据如下所示：

上述示例中数据会按照第一个字段 id 聚合，汇总后的数据会变成下面的样子：

数据汇总的逻辑示意如下所示：

在使用嵌套数据类型的时候，也支持使用复合 Key 作为数据聚合的条件。为了使用复合 Key，在嵌套类型的字段中，除第一个字段以外，任何名称是以 Key、Id 或 Type 为后缀结尾的字段，都将和第一个字段一起组成复合 Key。例如将上面的例子中小写 key 改为 Key：

上述例子中数据会以 id 和 Key 作为聚合条件。在知道了 SummingMergeTree 的使用方法后，现在简单梳理一下它的处理逻辑。

（1）用 ORBER BY 排序键作为聚合数据的条件 Key。
（2）只有在合并分区的时候才会触发汇总的逻辑。
（3）以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合 Key 相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。（4）如果在定义引擎时指定了 columns 汇总列（非主键的数值类型字段），则 SUM 汇总这些列字段；如果未指定，则聚合所有非主键的数值类型字段。
（5）在进行数据汇总时，因为分区内的数据已经基于 ORBER BY 排序，所以能够找到相邻且拥有相同聚合 Key 的数据。
（6）在汇总数据时，同一分区内，相同聚合 Key 的多行数据会合并成一行。其中，汇总字段会进行 SUM 计算；对于那些非汇总字段，则会使用第一行数据的取值。
（7）支持嵌套结构，但列字段名称必须以 Map 后缀结尾。嵌套类型中，默认以第一个字段作为聚合 Key。除第一个字段以外，任何名称以 Key、Id 或 Type 为后缀结尾的字段，都将和第一个字段一起组成复合 Key。

AggregatingMergeTree

有过数据仓库建设经验的读者一定知道“数据立方体”的概念，这是一个在数据仓库领域十分常见的模型。它通过以空间换时间的方法提升查询性能，将需要聚合的数据预先计算出来，并将结果保存起来。在后续进行聚合查询的时候，直接使用结果数据。