如何在 ClickHouse 中更新数据

自 2016 年以来，世界已经发生了变化，ClickHouse 也一样。请阅读关于此主题的更新后的帖子，它解释了新的更新和删除功能。

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

但有时我们必须修改数据。有时数据需要实时更新。不用担心，我们已经涵盖了这些情况。

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

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

让我们看一个 Yandex.Metrica 服务器 mtlog02-01-1 上的例子，它存储 2013 年的一些 Yandex.Metrica 数据。我们正在查看的表包含我们称之为“点击”的用户事件。这是 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 - 将分区移动到“分离”目录并将其遗忘。
• DROP PARTITION - 删除分区。
• ATTACH PART|PARTITION -- 从“分离”目录向表中添加新部分或分区。
• FREEZE PARTITION - 创建分区的备份。
• FETCH PARTITION - 从另一台服务器下载分区。

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

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

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

• 在另一个表上创建包含更新数据的修改后的分区
• 将此分区的 数据复制到分离目录
• 在主表中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。第二个是更新后的行，所有数据都设置为新值。

之后，我们有 3 行数据

┌──────────────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 操作表示的指标。它涵盖了大多数情况，但无法计算最小值或最大值。它还会影响 uniq 计算。但这对于 Yandex.Metrica 的情况来说至少是没问题的，而且有很多不同的分析计算；
• 您需要在进行更新的外部系统中以某种方式记住旧值，以便您可以插入这些“删除”行；
• 一些其他影响；有一个很棒的答案在 Google Groups 上。

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

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

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

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

我们知道现有的功能集还不够完善。有些用例不符合目前的限制。但我们有宏伟的计划，以下是一些我们正在准备的见解。

• 按自定义键分区：当前的分区方案仅限于按月进行。我们将消除此限制，用户可以按任何键创建分区。所有分区操作（例如 FETCH PARTITION）都将可用。
• UPDATE 和 DELETE：更新和删除支持存在很多问题。性能下降、一致性保证、分布式查询等等。但我们相信，如果你需要更新数据集中的一小部分行，这个过程不应该很痛苦。我们会做到这一点。