如何在 ClickHouse 中更新数据

自 2016 年以来世界已经改变，ClickHouse 也是如此。请阅读关于此主题的更新后的帖子，其中解释了新的更新和删除功能。

目前 ClickHouse 中没有 UPDATE 或 DELETE 命令。这并不是因为我们有什么宗教信仰。ClickHouse 是一个以性能为导向的系统；数据修改在性能方面很难以最佳方式存储和处理。

但有时我们必须修改数据。有时数据应该实时更新。别担心，我们已经涵盖了这些情况。

MergeTree 引擎系列中的数据通过 partition_key 引擎参数进行分区。MergeTree 通过此分区键拆分所有数据。分区大小为一个月份。

这在许多方面都非常有用。尤其是在我们谈论数据修改时。

让我们看一个 Yandex.Metrica 服务器 mtlog02-01-1 的示例，该服务器存储了 2013 年的一些 Yandex.Metrica 数据。我们正在查看的表包含我们称为“hits”的用户事件。这是 hits 表的引擎描述

ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{layer}-{shard}/hits', -- zookeeper path
    '{replica}', -- settings in config describing replicas
    EventDate, -- partition key column
    intHash32(UserID), -- sampling key
    (CounterID, EventDate, intHash32(UserID), WatchID), -- index
    8192 -- index granularity
)

您可以看到分区键列是 EventDate。这意味着所有数据将使用此列按月拆分。

通过此 SQL，我们可以获取分区列表以及有关当前分区的一些统计信息

SELECT 
    partition, 
    count() as number_of_parts, 
    formatReadableSize(sum(bytes)) as sum_size 
FROM system.parts 
WHERE 
    active 
    AND database = 'merge' 
    AND table = 'hits' 
GROUP BY partition 
ORDER BY partition;

┌─partition─┬─number_of_parts─┬─sum_size───┐
│ 201306    │               1 │ 191.34 GiB │
│ 201307    │               4 │ 537.86 GiB │
│ 201308    │               6 │ 608.77 GiB │
│ 201309    │               5 │ 658.68 GiB │    
│ 201310    │               5 │ 768.74 GiB │
│ 201311    │               5 │ 654.61 GiB │
└───────────┴─────────────────┴────────────┘

有 6 个分区，每个分区中有几个部分。每个分区大约有 600 Gb 数据。分区严格来说是分区键的一段数据，在这里我们可以看到它是月份。部分是分区内的一段数据。基本上它是 LSMT 结构的节点之一，因此它们并不多，尤其是对于旧数据。如果它们太多，它们会合并并形成更大的部分。

有一组很好的操作可以处理分区

• DETACH PARTITION - 将分区移动到“detached”目录并忘记它。
• DROP PARTITION - 删除分区。
• ATTACH PART|PARTITION - 将新的部分或分区从“detached”目录添加到表中。
• FREEZE PARTITION - 创建分区的备份。
• FETCH PARTITION - 从另一台服务器下载分区。

我们可以在分区级别执行任何数据管理操作：移动、复制和删除。此外，还创建了特殊的 DETACH 和 ATTACH 操作来简化数据操作。DETACH 从表中分离分区，将所有数据移动到 detached 目录。数据仍然存在，您可以将其复制到任何地方，但分离的数据在请求级别不可见。ATTACH 则相反：从 detached 目录附加数据，使其变为可见。

这些 attach-detach 命令几乎在瞬间完成，因此您可以使您的更新对数据库客户端几乎透明。

以下是使用分区更新数据的计划

• 在另一个表中创建具有更新数据的修改分区
• 将此分区的数据复制到 detached 目录
• DROP PARTITION 在主表中
• ATTACH PARTITION 在主表中

分区交换对于低频率的大量数据更新特别有用。但是，当您需要实时更新大量数据时，它们不太方便。

在 Yandex.Metrica 中，我们有一个用户会话表。每一行都是网站上的一个会话：查看了一些页面，花费了一些时间，点击了一些横幅广告。此数据每秒更新一次：网站上的用户查看更多页面，点击更多按钮，并执行其他操作。网站所有者可以在 Yandex.Metrica 界面中实时看到这些操作。

那么我们是如何做到的呢？

我们更新数据不是通过更新该数据，而是添加更多关于已更改内容的数据。这通常称为 CRDT 方法，维基百科上有一篇关于该方法的文章。

它最初是为了解决事务中的冲突问题而创建的，但这个概念也允许更新数据。我们使用我们自己的数据模型来实现这种方法。我们称之为增量日志。

让我们看一个例子。

这里我们有一个会话信息，包含用户标识符 UserID、页面浏览量 PageViews、在网站上花费的时间（秒）Duration。还有一个 Sign 字段，我们稍后会描述它。

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

假设我们根据此数据计算一些指标。

• count() - 会话数
• sum(PageViews) - 所有用户查看的页面总数
• avg(Duration) - 平均会话时长，用户通常在网站上花费多长时间

假设现在我们对此进行了更新：用户又查看了一个页面，因此我们应该将 PageViews 从 5 更改为 6，并将 Duration 从 146 更改为 185。

我们再插入两行

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

第一行是删除行。它与我们已有的行完全相同，但 Sign 设置为 -1。第二行是更新后的行，所有数据都设置为新值。

之后，我们有三行数据

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

最重要的部分是修改后的指标计算。我们应该像这样更新我们的查询

 -- number of sessions
count() -> sum(Sign)
 -- total number of pages all users checked
sum(PageViews) -> sum(Sign * PageViews)
 -- average session duration, how long user usually spent on the website
avg(Duration) -> sum(Sign * Duration) / sum(Sign)

您可以看到它在此数据上按预期工作。删除行“隐藏”旧行，相同的值在聚合内部以 + 和 - 符号出现并相互抵消。

此外，它在更改分组键的情况下也能完全正常工作。如果我们想按 PageViews 对数据进行分组，则 PageView = 5 的所有数据都将为此行“隐藏”。

这种方法有一些限制

• 它仅适用于可以通过此 Sign 操作表示的指标。它涵盖了大多数情况，但无法计算最小值或最大值。对唯一值计算也有影响。但至少对于 Yandex.Metrica 的情况来说没问题，并且有很多不同的分析计算；
• 您需要以某种方式记住执行更新的外部系统中的旧值，以便您可以插入这些“删除”行；
• 一些其他影响；在 Google 论坛上有一个很好的回答。

ClickHouse 在 Collapsing 引擎系列中支持增量日志模型。

如果您使用 Collapsing 系列，“删除”行和旧的“已删除”行将在合并过程中折叠。合并是将数据合并到更大的块中的后台进程。这是一篇关于合并和 LSMT 结构的优秀文章。

在大多数情况下，“删除”行和“已删除”行将在几天内被删除。这里重要的是，您不会对数据大小产生任何显着的开销。在选择时仍然需要使用 Sign 字段。

此外，Collapsing 系列还提供了 FINAL 修饰符。使用 FINAL 可保证用户看到已折叠的数据，因此不需要使用 Sign 字段。FINAL 通常会导致巨大的性能下降，因为 ClickHouse 必须在 SELECT 执行期间按键对数据进行分组并删除行。但是，当您想检查查询或想以最终形式查看原始、未聚合的数据时，它很有用。

我们知道当前的功能集还不够。有些情况不符合限制。但我们有宏伟的计划，以下是我们正在准备的一些见解

• 按自定义键分区：当前分区方案仅绑定到月份。我们将取消此限制，并且可以按任何键创建分区。所有分区操作（如 FETCH PARTITION）都将可用。
• UPDATE 和 DELETE：更新和删除支持存在很多问题。性能下降、一致性保证、分布式查询等等。但我们相信，如果您需要在数据集中更新少量数据行，它不应该很痛苦。这将完成。