CollapsingMergeTree

描述

CollapsingMergeTree 引擎继承自 MergeTree，并在合并过程中添加了折叠行的逻辑。如果排序键 (ORDER BY) 中的所有字段都等效，除了特殊字段 Sign，它可以是 1 或 -1，则 CollapsingMergeTree 表引擎会异步删除（折叠）成对的行。没有成对相反值的 Sign 的行将被保留。

有关更多详细信息，请参阅文档的折叠部分。

注意

此引擎可以显著减少存储量，从而提高 SELECT 查询的效率。

参数

此表引擎的所有参数，除了 Sign 参数外，都与 MergeTree 中的含义相同。

Sign — 指定列的名称，该列表示行的类型，其中 1 是“状态”行，-1 是“取消”行。类型：Int8。

创建表

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

创建表的已弃用方法

注意

不建议在新项目中使用以下方法。如果可能，我们建议更新旧项目以使用新方法。

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

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 除外。上面的第二行包含当前状态。

由于我们只需要用户活动的最后状态，因此可以删除原始“状态”行和我们插入的“取消”行，如下所示，折叠对象的无效（旧）状态

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │ -- old "state" row can be deleted
│ 4324182021466249494 │         5 │      146 │   -1 │ -- "cancel" row can be deleted
│ 4324182021466249494 │         6 │      185 │    1 │ -- new "state" row remains
└─────────────────────┴───────────┴──────────┴──────┘

当数据部件合并发生时，CollapsingMergeTree 会精确地执行此折叠行为。

注意

每个更改需要两行的原因在算法段落中进一步讨论。

这种方法的特点

写入数据的程序应记住对象的状态，以便能够取消它。“取消”行应包含“状态”行的排序键字段的副本和相反的 Sign。这会增加初始存储大小，但允许我们快速写入数据。
列中不断增长的长数组会降低引擎的效率，因为写入负载增加。数据越简单，效率越高。
SELECT 结果在很大程度上取决于对象更改历史记录的一致性。准备要插入的数据时要准确。如果数据不一致，您可能会得到不可预测的结果。例如，非负指标（如会话深度）的负值。

算法

当 ClickHouse 合并数据部件时，每个具有相同排序键 (ORDER BY) 的连续行组最多减少到两行，即 Sign = 1 的“状态”行和 Sign = -1 的“取消”行。换句话说，在 ClickHouse 中，条目会折叠。

对于每个生成的数据部件，ClickHouse 会保存


1.	第一个“取消”行和最后一个“状态”行，如果“状态”行和“取消”行的数量匹配并且最后一行是“状态”行。
2.	最后一个“状态”行，如果“状态”行多于“取消”行。
3.	第一个“取消”行，如果“取消”行多于“状态”行。
4.	在所有其他情况下，不保存任何行。

此外，当“状态”行比“取消”行多至少两行，或者“取消”行比“状态”行多至少两行时，合并继续。但是，ClickHouse 将这种情况视为逻辑错误，并将其记录在服务器日志中。如果同一数据多次插入，则可能会发生此错误。因此，折叠不应更改计算统计信息的结果。更改会逐渐折叠，以便最终只留下几乎每个对象的最后状态。

Sign 列是必需的，因为合并算法不保证具有相同排序键的所有行都将位于同一生成的数据部件中，甚至位于同一物理服务器上。 ClickHouse 使用多个线程处理 SELECT 查询，并且无法预测结果中行的顺序。

如果需要从 CollapsingMergeTree 表中获取完全“折叠”的数据，则需要聚合。要完成折叠，请编写带有 GROUP BY 子句和考虑符号的聚合函数的查询。例如，要计算数量，请使用 sum(Sign) 而不是 count()。要计算某项的总和，请使用 sum(Sign * x) 以及 HAVING sum(Sign) > 0，而不是示例中的 sum(x)。

聚合 count、sum 和 avg 可以通过这种方式计算。如果对象具有至少一个非折叠状态，则可以计算聚合 uniq。无法计算聚合 min 和 max，因为 CollapsingMergeTree 不保存折叠状态的历史记录。

注意

如果需要提取没有聚合的数据（例如，检查最新值与某些条件匹配的行是否存在），则可以对 FROM 子句使用 FINAL 修饰符。

这种方法效率明显较低。

示例

使用示例

给定以下示例数据

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

让我们使用 CollapsingMergeTree 创建一个表 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 查询来创建两个不同的数据部件。

注意

如果我们使用单个查询插入数据，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 在后台以我们无法预测的未知时刻合并数据部件。

因此，我们需要一个聚合，我们使用 sum 聚合函数和 HAVING 子句执行聚合

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 │
└─────────────────────┴───────────┴──────────┴──────┘

注意

这种选择数据的方式效率较低，不建议用于扫描大量数据（数百万行）。

另一种方法的示例

这种方法的想法是，合并仅考虑键字段。因此，在“取消”行中，我们可以指定负值，这些负值在求和时均衡行的先前版本，而无需使用 Sign 列。

对于此示例，我们将使用以下示例数据

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

对于这种方法，有必要更改 PageViews 和 Duration 的数据类型以存储负值。因此，当使用 collapsingMergeTree 创建表 UAct 时，我们将这些列的值从 UInt8 更改为 Int16

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews Int16,
    Duration Int16,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

让我们通过将数据插入到我们的表中来测试该方法。回想一下，不建议在生产案例中使用 FINAL 关键字。但是，对于示例或小型表，它是可以接受的

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─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

SELECT
    UserID,
    sum(PageViews) AS PageViews,
    sum(Duration) AS Duration
FROM UAct
GROUP BY UserID

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

SELECT COUNT() FROM UAct

┌─count()─┐
│       3 │
└─────────┘

OPTIMIZE TABLE UAct FINAL;

SELECT * FROM UAct

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

描述​

参数​

创建表​

折叠​

数据​

算法​

示例​

使用示例​

另一种方法的示例​

描述

参数

创建表

折叠

数据

算法

示例

使用示例

另一种方法的示例