CollapsingMergeTree 表引擎

描述

CollapsingMergeTree 引擎继承自 MergeTree，并在合并过程中添加了折叠行（collapsing rows）的逻辑。CollapsingMergeTree 表引擎异步删除（折叠）具有相同排序键（ORDER BY）字段的行对，除非特殊字段 Sign 的值为 1 或 -1。没有相反值 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 修饰符。它将在返回结果之前合并数据。对于 CollapsingMergeTree，将返回每个键的最新状态行。

示例

使用示例

给定以下示例数据

┌──────────────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

让我们通过将数据插入到我们的表中来测试该方法。

但是，对于示例或小表来说，这是可以接受的

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

描述​

参数​

创建表​

折叠​

数据​

算法​

示例​

使用示例​

另一种方法的示例​

描述

参数

创建表

折叠

数据

算法

示例

使用示例

另一种方法的示例