ClickHouse(13)ClickHouse合并树MergeTree家族表引擎之CollapsingMergeTree解析

该引擎继承于MergeTree，并在数据块合并算法中添加了折叠行的逻辑。CollapsingMergeTree会异步的删除（折叠）这些除了特定列Sign有1和-1的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。因此，该引擎可以显著的降低存储量并提高SELECT查询效率。
简单来说就是，clickhouse会自动的合并有效和无效的数据，减少数据存储，并减少update所产生的性能消耗。具体的逻辑，下面介绍。

建表

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

sign — 类型列的名称：1是«状态»行，也就是最后的有效行，-1是«取消»行，也就是无效行。列数据类型 — Int8。

创建CollapsingMergeTree表时，需要与创建 MergeTree 表时相同的子句。

折叠

数据

考虑你需要为某个对象保存不断变化的数据的情景。似乎为一个对象保存一行记录并在其发生任何变化时更新记录是合乎逻辑的，但是更新操作对DBMS来说是昂贵且缓慢的，因为它需要重写存储中的数据。如果你需要快速的写入数据，则更新操作是不可接受的，但是你可以按下面的描述顺序地更新一个对象的变化。

在写入行的时候使用特定的列Sign。如果Sign=1则表示这一行是对象的状态，我们称之为«状态»行。如果Sign=-1则表示是对具有相同属性的状态行的取消，我们称之为«取消»行。

例如，我们想要计算用户在某个站点访问的页面页面数以及他们在那里停留的时间。在某个时候，我们将用户的活动状态写入下面这样的行。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

一段时间后，我们写入下面的两行来记录用户活动的变化。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

第一行取消了这个对象（用户）的状态。它需要复制被取消的状态行的所有除了Sign的属性。

第二行包含了当前的状态。因为我们只需要用户活动的最后状态，这些行可以在折叠对象的失效（老的）状态的时候被删除。CollapsingMergeTree会在合并数据片段的时候做这件事。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
└─────────────────────┴───────────┴──────────┴──────┘

这种方法的特殊属性

写入的程序应该记住对象的状态从而可以取消它。«取消»字符串应该是«状态»字符串的复制，除了相反的Sign。它增加了存储的初始数据的大小，但使得写入数据更快速。
由于写入的负载，列中长的增长阵列会降低引擎的效率。数据越简单，效率越高。
SELECT的结果很大程度取决于对象变更历史的一致性。在准备插入数据时要准确。在不一致的数据中会得到不可预料的结果，例如，像会话深度这种非负指标的负值。

算法

当ClickHouse合并数据片段时，每组具有相同主键的连续行被减少到不超过两行，一行Sign=1（«状态»行），另一行Sign=-1（«取消»行），换句话说，数据项被折叠了。

对每个结果的数据部分ClickHouse保存的算法：

如果«取消»和«状态»行数量相同，并且最后一行«状态»行，保留第一个«取消»和最后一个«状态»行。
如果«状态»行比«取消»行多一个或一个以上，保留最后一个«状态»行。
如果«取消»行比«状态»行多一个或一个以上，保留第一个«取消»行。
没有行，在其他所有情况下。合并会继续，但ClickHouse会把此情况视为逻辑错误并将其记录在服务日志中。这个错误会在相同的数据被插入超过一次时出现。

因此，折叠不应该改变统计数据的结果。变化逐渐地被折叠，因此最终几乎每个对象都只剩下了最后的状态。

Sign是必须的因为合并算法不保证所有有相同主键的行都会在同一个结果数据片段中，甚至是在同一台物理服务器上。ClickHouse用多线程来处理SELECT请求，所以它不能预测结果中行的顺序。如果要从CollapsingMergeTree表中获取完全«折叠»后的数据，则需要聚合。

要完成折叠，请使用GROUP BY子句和用于处理符号的聚合函数编写请求。例如，要计算数量，使用sum(Sign)而不是 count()。要计算某物的总和，使用sum(Sign * x)而不是sum(x)，并添加HAVING sum(Sign) > 0子句。

聚合体count,sum和avg可以用这种方式计算。如果一个对象至少有一个未被折叠的状态，则可以计算uniq聚合。min和 max聚合无法计算，因为CollaspingMergeTree不会保存折叠状态的值的历史记录。

如果你需要在不进行聚合的情况下获取数据（例如，要检查是否存在最新值与特定条件匹配的行），你可以在 FROM 从句中使用 FINAL 修饰符。这种方法显然是更低效的。

# 示例:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

# 建表:

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请求来创建两个不同的数据片段。如果我们使用一个请求插入数据，ClickHouse只会创建一个数据片段且不会执行任何合并操作。

#获取数据：

SELECT * FROM UAct

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    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 │
└─────────────────────┴───────────┴──────────┴──────┘

# 这种查询数据的方法是非常低效的。不要在大表中使用它。

资料分享

ClickHouse经典中文文档分享

ClickHouse(13)ClickHouse合并树MergeTree家族表引擎之CollapsingMergeTree解析

建表

折叠

数据

算法

资料分享

系列文章

clickhouse系列文章

张飞的猪

引用和评论

什么是AOP面向切面编程？怎么简单理解？

使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

MongoDB 8.0已全面可用

简道云搭载阿里云MongoDB数据库，帮助数以万计企业重构业务系统

利用MongoDB进行数据治理，防范构建生成式AI应用程序时的潜在安全风险

数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

使用 MongoDB 构建 AI：Patronus 如何自动进行大语言模型评估来增强对生成式 AI 的信心