在 ClickHouse 中处理更新和删除_clickhouse数据update

本文字数：9491；估计阅读时间：24 分钟

作者：ClickHouse中国

Meetup活动：

首届ClickHouse官方Meetup活动正式开放报名，文末扫码报名！

作为世界上最快的实时分析数据库，许多 ClickHouse 的工作负载涉及大量的一次性写入数据，且并不经常修改数据（例如，由 IoT 设备生成的遥测事件，或电商网站产生的客户点击）。虽然这些数据通常是不可变的，但作为那些在分析过程中提供上下文的数据集（例如，基于设备或客户 ID 的查找表），可能就需要修改了。

在历史上，根据您不同的目标和性能需求，ClickHouse 提供了多种更新和删除数据的方法。本文的其余部分描述了每种方法及其权衡考虑，以及与轻量级删除相关的一些最新进展，它们解决了一些常见的挑战。我们推荐使用最佳实践，并在选择方法时，重点考虑那些重要的权衡因素。

在处理数据前，请确认“更新”是否是您解决问题的最佳方法。例如，对于不经常更改的数据，版本化数据可能是更好的选择。在存储效率和查询性能方面，ClickHouse 是排名第一的分析型数据库，因此在许多情况下，仅保存数据的多个版本，而不是更新原始数据，可能会更好。

轻量级删除

轻量级删除被公认为：从 ClickHouse 中删除数据的首选和最有效的方式。通过 DELETE FROM table 语法，用户可以指定一个条件，用来删除特定的行，如下所示：

DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2

默认情况下，此操作是异步的，除非将 mutations_sync 设置为1（参见下文）。执行删除时，ClickHouse 为每一行保存一个掩码，表示在 _row_exists 列中是否已删除它。随后的查询则排除了那些被删除的行，如下所示。

在内部，ClickHouse 将数据排序为多个部分（part），每个部分都包含列数据文件和索引。常规的合并周期负责组合（合并）和重写这些部分。这确保随着插入更多的数据，文件的数量并不会继续增长，也能保持快速的查询。这些合并考虑了轻量级删除，在新形成的部分中排除了标记为删除的行。

在 22.8 中发布的，截至写作时仍处于实验阶段，轻量级删除有望在接下来的某个版本中成为生产就绪功能。在此之前，使用轻量级删除需要设置 allow_experimental_lightweight_delete=true 的参数。

用户应该意识到，通过依赖正常的后台合并周期，最终行只会从磁盘上被删除。虽然从搜索结果中排除了这些行，但这些行会一直驻留在磁盘上，直到它们的部分（part）被合并。合并发生的时间是不确定的。这有几个含义：

• 空间节省不会像通过变异（mutations）发出的删除那样，立即的生效-请参见下文。如果空间节省至关重要，例如磁盘空间不足，请考虑使用变异。

• 由于无法保证删除，需要遵守合规性要求的用户可能希望使用变异来确保数据被删除掉。

轻量级删除操作的成本取决于 WHERE 子句中匹配的行数和当前部分（part）中数据的数量。当匹配的行数较少时，此操作将最为有效。用户还应该意识到，轻量级删除在 Wide 部分上表现最好，在这里，列数据文件是分开存储的，而 Compact 部分则使用单个文件用于所有列数据。前者允许将掩码 _row_exists 作为单独的文件存储，从而允许它独立于其他列进行写入。通常，在插入后会形成 Compact 的部分。一旦部分超过某个大小（例如，由于合并），就会使用 Wide 格式。对于大多数工作负载，这不应该是一个问题。

最后，请注意，轻量级删除使用到了，我们下面要描述的相同的变异（Mutation）队列和后台线程。我们建议您在（https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals）查看文档，以获取有关内部实现的更多详细信息。

变异 - Mutations

使用 Mutations 更新数据更新 

ClickHouse 表中的数据，使用 ALTER...UPDATE 语句是最简单的用法。

ALTER TABLE table
    UPDATE col1 = 'Hi' WHERE col2 = 2

此查询将根据给定的过滤条件更新表 table 中的 col1 。

与某些数据库不同，ClickHouse 的 ALTER UPDATE 语句默认是异步的。这意味着更新是在后台进行的，您将不会立即在表上看到更新的效果。这种更新表的过程称为 mutations。

这里需要注意的重要一点是，更新数据是一个重型查询，因为 ClickHouse 必须做很多工作，用来优化存储和处理结果。一个 mutation 操作强制所有包含那些要删除的行的数据部分被重写，当形成新部分时排除目标行。这可能会导致相当大的 I/O 和集群开销，所以使用时要小心，或者考虑下面讨论的其他方法。

使用 mutation 删除数据

像更新一样，删除也可以通过 mutation 进行，并为轻量级删除提供了一种替代方式。在大多数情况下，由于重写所有列的mutation成本，轻量级删除更适合于数据删除。更具体地说，与轻量级删除不同的是：所有列都将被重写，而不仅仅是一个 _row_exists 掩码列。

但是，考虑到轻量级删除的“最终从磁盘删除数据”的属性，用户可能更喜欢这种基于mutation的方法，以实现确保磁盘空间的节省。此外，当用户需要确保数据从磁盘中删除时，例如由于合规性原因，这种方法可能是更合适的。

ALTER TABLE table
    DELETE WHERE col2 = 3

此查询中， col2 值为 3 的所有行都会被删除。与其他mutation类似，删除也默认是异步的。可以使用上面描述的相同的 mutations_sync 参数设置来使其同步。

检查mutation进度

由于mutation是异步运行的，可以通过 system.mutations 表进行监控。这允许用户按其需要：检查表上特定mutation 的进度。

SELECT
    command,
    is_done
FROM system.mutations
WHERE table = 'tablename'
 
┌─command───────────────────────────────┬─is_done─┐
│ UPDATE col1 = 'Hi' WHERE col2 = 2     │       1 │
│ UPDATE col1 = 'All hi' WHERE col2 > 0 │       0 │
└───────────────────────────────────────┴─────────┘

如果特定mutation的 is_done 值为 0 ，则仍在执行。突变是为每个表部分执行的，其中mutation部分立即变得可用。

同步更新

对于需要同步更新的用户，可以将 mutations_sync 参数的值设置为 1 （或者如果我们还想等到所有副本都更新了，就设置为 2 ）：

SET mutations_sync = 1

现在，我们的更新查询将等待mutation 的完成：

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE col2 > 0
 
0 rows in set. Elapsed: 1.182 sec.

请注意，当 ClickHouse 等待后台的mutation完成时，这个查询花费了1秒钟。请注意，此参数也适用于轻量级删除。

更新整个表格

在某些情况下，用户需要更新整个列的值。最初，用户可能尝试使用没有 WHERE 子句的 ALTER TABLE 查询，来实现这一目标。但是，这失败了，如下所示：

ALTER TABLE table UPDATE col1 = 'bye';
 
Syntax error: failed at position 38 (end of query):
ALTER TABLE table UPDATE col1 = 'bye';

ClickHouse 不允许您更新整个表，因为更新是重型操作。强制 ClickHouse 接受此操作的一种方法是使用always-true的过滤器：

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE true

但是，更优的方法是创建一个新列，新值作为默认值，然后切换旧列和新列。例如：

ALTER TABLE table ADD COLUMN col1_new String DEFAULT 'global hi';
 
ALTER TABLE table
    RENAME COLUMN col1 TO col1_old,
    RENAME COLUMN col1_new TO col1,
    DROP COLUMN col1_old;

我们使用 col1_new 列的默认值，来指定我们要用作更新值的内容。这是安全的，而且效率更高，因为我们在这里跳过了重型的mutation操作。

使用 JOINs 更新和删除

有时，我们需要基于关系删除或更新行，因此我们必须联接表格。这在 ClickHouse 中最好使用 Join 表引擎和 joinGet 函数来实现。假设我们有两个表格 - 一个是所有的页面查看次数，另一个是所有的登录跟踪：

CREATE TABLE pageviews
(
    `user_id` UInt64,
    `time` DateTime,
    `session_id` UInt64
)
ENGINE = MergeTree
ORDER BY time;
 
CREATE TABLE logins
(
    `user_id` UInt64,
    `time` DateTime
)
ENGINE = MergeTree
ORDER BY time;

这两个表格的区别在于 logins 表格只存储每个会话的单一事件。假设在某个时刻，我们决定在 logins 表格中添加 session_id 列：

ALTER TABLE logins
    ADD COLUMN `session_id` UInt64

现在我们需要使用 user_id 和 time 的 JOIN，将 logins.session_id 列与 pageviews 表的相应值进行更新：

SELECT *
FROM logins AS l
JOIN pageviews AS p ON (p.user_id = l.user_id) AND (p.time = l.time)
 
┌─user_id─┬────────────────time─┬─p.user_id─┬──────────────p.time─┬─session_id─┐
│       2 │ 2023-01-09 12:23:16 │         2 │ 2023-01-09 12:23:16 │ 2752888102 │
│       1 │ 2023-01-09 13:23:16 │         1 │ 2023-01-09 13:23:16 │ 4135462640 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘

首先，我们需要创建并填充一个特殊的 Join 表：

CREATE TABLE pageviews_join
ENGINE = Join(ANY, LEFT, user_id, time) AS
SELECT *
FROM pageviews

此表格允许我们使用 joinGet 函数，在执行更新查询时基于 JOIN 获取值：

ALTER TABLE logins
    UPDATE session_id = joinGet('pageviews_join', 'session_id', user_id, time) WHERE session_id = 0

我们可以看到，logins 表格已经相应地使用 JOIN 更新了：

SELECT * FROM logins
 
┌─user_id─┬────────────────time─┬─session_id─┐
│       2 │ 2023-01-09 12:23:16 │ 2752888102 │
│       1 │ 2023-01-09 13:23:16 │ 4135462640 │
└─────────┴─────────────────────┴────────────┘

因为我们通过添加 session_id 列更改了 logins 表格，所以一旦完成更改，我们可以删除 pageviews_join 表（在删除之前，确保检查 system.mutations 表）：

DROP TABLE pageviews_join

同样的方法可以用于删除数据，使用轻量级或基于突变的删除方法。

高效删除大块数据

如果我们需要删除大量数据，用户可以分区一个表格，以便可以根据需要删除分区。这是一个轻量级的操作。假设我们有以下表格：

CREATE TABLE hits
(
    `project` String,
    `url` String,
    `time` DateTime,
    `hits` UInt32
)
ENGINE = MergeTree
PARTITION BY project
ORDER BY (project, path, time)

通过按 project 列分区此表，我们可以通过删除整个分区来删除具有特定 project 值的行。让我们删除 project = c 的所有内容：

ALTER TABLE hits
    DROP PARTITION 'c'

这里， c 是我们想要删除的 project 列的值：

在 system.parts 表中可以找到可用的分区列表：

SELECT partition
FROM system.parts
WHERE table = 'hits'
 
┌─partition─┐
│ c         │
│ a         │
│ b         │
└───────────┘

我们还可以使用 DETACH 和 ATTACH 语句在表格之间移动分区（例如，如果我们想要将数据移动到 trash 表格而不是删除它）。

在您的 DDL 中设置分区时，要注意避免场景的陷阱：按具有高基数的列，或表达式分区。这可能会导致创建许多部分（part），从而导致性能问题。

定期删除旧数据

在时间序列数据的情况下，我们可能想要定期删除过时的旧数据。ClickHouse为这个具体的用例提供了TTL功能。这需要配置一个表，并指定我们想要删除哪些数据，以及何时删除。假设我们想从 hits 表中删除一个月之前的数据：

ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH

这里我们要求ClickHouse删除所有 time 列值早于当前时间一个月的行。TTL也可以设置在列上，在一段时间后重置其值为默认值。通过按日期分区，四舍五入为适当的时间单位（例如，天），可以使此操作更为高效。当执行TTL规则时，ClickHouse将自动以最有效的方式删除数据。再次强调，表不应该按时间列的高基数（例如，毫秒粒度）进行分区，以避免高产生大量的部分（part）。对于大多数TTL操作，通常按天或月进行分区就足够了。

使用 CollapsingMergeTree 更新和删除

如果我们必须经常更新单个行，我们可以使用 CollapsingMergeTree 引擎高效地管理数据更新。

假设我们有一个文章统计表，用于跟踪每篇文章的阅读深度。我们希望用一个单独的行来显示每个用户读过的每篇文章有多深。这里的挑战是，我们必须在用户阅读文章时更新实际的阅读进度。让我们为我们的数据创建一个表：

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_start` DateTime,
    `read_end` DateTime,
    `sign` Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY (read_start, article_id, user_id)

特殊的 sign 列用于 CollapsingMergeTree，作为告诉ClickHouse我们想要更新特定行的方式。如果我们为 sign 列插入-1，则整行都将被删除。如果我们插入 sign = 1 的行，ClickHouse将保留该行。要更新的行基于在创建表时用 ORDER BY () DDL 语句使用的排序键来识别：

为了满足排序键上的去重条件，我们必须为 read_start、article_id、user_id 列插入相同的值来更新一行。例如，当用户开始阅读一篇文章时，我们可能会插入以下的行：

INSERT INTO article_reads
            VALUES(1, 12, 0, now(), now(), 1);

现在我们的表中有如下的一行数据：

SELECT *
FROM article_reads
 
┌─user_id─┬─article_id─┬─read_to─┬──────────read_start─┬────────────read_end─┬─sign─┐
│       1 │         12 │       0 │ 2023-01-06 15:20:32 │ 2023-01-06 15:20:32 │    1 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘

一分钟后，当用户阅读了文章的70%时，我们插入以下2行：

INSERT INTO article_reads
            VALUES(1, 12, 0, '2023-01-06 15:20:32', now(), -1),
                  (1, 12, 70, '2023-01-06 15:20:32', now(), 1);

带有 sign=-1 的第一行用于告诉ClickHouse它应该删除现有的行（基于 ORDER BY 元组的值 - read_start ,  article_id 和 user_id 列）。第二个插入的行（带有 sign=1 ）是带有 read_to 列的新行，该列设置为新值 70 。

由于数据更新是在后台发生的，最终结果的一致性，因此我们应该在 sign 列上进行过滤以获得正确的结果：

SELECT
    article_id,
    user_id,
    max(read_end),
    max(read_to)
FROM article_reads
WHERE sign = 1
GROUP BY
    user_id,
    article_id
 
┌─article_id─┬─user_id─┬───────max(read_end)─┬─max(read_to)─┐
│         12 │       1 │ 2023-01-06 15:21:59 │           70 │
└────────────┴─────────┴─────────────────────┴──────────────┘
 
1 row in set. Elapsed: 0.004 sec.

CollapsingMergreTree 引擎现在将在后台高效地删除已取消的行，所以我们不必手动删除它们。您可以在此处（https://www.google.com/url?q=https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/%23example-of-use&sa=D&source=docs&ust=1678440218544759&usg=AOvVaw3vOSZ1Rbl24kvzHT48kuyp）找到使用 CollapsingMergeTree 引擎的其他示例。

使用版本控制和ReplacingMergeTree进行Upsert

对于更复杂的情况，我们可能希望使用基于ReplacingMergeTree引擎的版本控制。这个引擎实现了在其他数据库管理系统中称为 UPSERT 的有效方式，通过使用一个特殊的版本列来跟踪哪些行应该被移除。如果存在具有相同排序键的多个行，则只保留版本最大的那个行，而其他行被移除：

对于我们先前关于文章阅读的例子，我们可以使用以下结构：

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_time` DateTime,
    `version` Int32
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (article_id, user_id)

请注意特殊的 version 数值列，它将被ReplacingMergeTree引擎用于标记行以供移除。让我们模拟一个用户随着时间从0%读到80%的文章：

INSERT INTO article_reads
           VALUES(1, 12, 0, '2023-01-06 15:20:32', 1),
                 (1, 12, 30, '2023-01-06 15:21:42', 2),
                 (1, 12, 45, '2023-01-06 15:22:13', 3),
                 (1, 12, 80, '2023-01-06 15:23:10', 4);

这里，随着阅读进度的跟踪，我们增加了 version 列的值。通过正常的合并周期，行的移除过程也在后台执行，所以我们需要在查询时基于最新的版本进行过滤：

SELECT *
FROM article_reads
WHERE (user_id = 1) AND (article_id = 12)
ORDER BY version DESC
LIMIT 1
 
┌─user_id─┬─article_id─┬─read_to─┬───────────read_time─┬─version─┐
│       1 │         12 │      80 │ 2023-01-06 15:23:10 │       5 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────┘

或者我们可以使用LIMIT 1 BY来获取带有最新版本的行列表：

SELECT
    user_id,
    article_id,
    read_to
FROM article_reads
ORDER BY version DESC
LIMIT 1 BY
    user_id,
    article_id
 
┌─user_id─┬─article_id─┬─read_to─┐
│       1 │         12 │      80 │
└─────────┴────────────┴─────────┘

同样，我们不需要关心旧版本的移除 - 这由ClickHouse在后台自动完成。

总结

在分析环境中更新和删除数据可能会很有挑战性，并且会显著影响数据库性能。为了解决这个问题，ClickHouse为不同的情况提供了多种强大的方式来有效地更新和删除数据：

• 通过 DELETE FROM 语法的轻量级删除来从ClickHouse中删除数据。这是删除数据的最有效方法，只要不需要立即节省磁盘空间，并且用户可以容忍已删除的数据“存在”在磁盘上。

• 在需要立即节省磁盘空间的情况下，通过 ALTER…DELETE 进行基于Mutation的删除。例如，合规性要求需要保证从磁盘上移除数据。

• 在不规则和不频繁的更改的情况下，使用 ALTER…UPDATE 进行基于Mutation的更新。

• 使用TTL根据日期/时间进行定期移除（过时的）数据，

• 使用CollapsingMergeTree频繁地更新或删除单个行。

• 使用ReplacingMergeTree实现基于版本控制的upsert（插入/更新）。

• 定期删除大块数据时删除分区。

• 创建新列（并删除旧列）也可能是更新整个表的更有效的方法。

Meetup 活动报名通知

好消息：ClickHouse Beijing User Group第1届 Meetup 已经开放报名了，人数限制在 50 人，请大家赶紧扫码报名。

本活动是由 ClickHouse 中国官方组织的线下技术交流活动。ClickHouse 的全球官方用户社区已经在 MeetUp网站上的 25 个城市组织了 meetup 活动，其中官方首届北京 Meetup 活动即将于 11 月 4 日举行。

• 沙龙时间：2023年11月4日

• 沙龙地点：北京市海淀区北四环西路56号辉煌时代大厦 3层WeWork

• 面向人群：ClickHouse 技术爱好者、开发者、运维、架构师等

联系我们

手机号：13910395701

邮箱：Tracy.Wang@clickhouse.com

满足您所有的在线分析列式数据库管理需求