博客 / 工程

ClickHouse 中处理更新和删除

Denys Golotiuk

3 月 10, 2023 - 18 分钟阅读

ClickHouse Delete Statement.png

作为世界上最快的实时分析数据库，许多 ClickHouse 工作负载涉及大量数据，这些数据写入一次后不会频繁修改（例如，IOT 设备生成的遥测事件或电子商务网站生成的客户点击）。虽然这些通常是不可变的，但在分析期间提供上下文的关键附加数据集（例如，包含基于设备或客户 ID 的信息的查找表）可能需要修改。

从历史上看，根据您的目标和性能要求，已经有多种方法可以在 ClickHouse 中更新和删除数据。本文的其余部分描述了每种方法及其权衡，以及轻量级删除的一些最新进展，这些进展解决了一些常见的挑战。我们推荐最佳实践，并在考虑方法时强调一些重要的注意事项。

在继续之前，请确定更新是否是解决问题的最佳方法。例如，对于不经常更改的数据，对数据进行版本控制可能是更好的选择。ClickHouse 是存储效率和查询性能方面排名第一的分析数据库，因此在许多情况下，仅保存数据的多个版本而不是更新可能效果更好。

轻量级删除代表了从 ClickHouse 中删除数据的首选和最有效方式。通过 DELETE FROM 表语法公开，用户可以指定条件来删除特定行，如下所示

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

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

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

lightweight-delete-merge(1).png

轻量级删除在 22.8 版本中发布，并且在撰写本文时仍处于实验阶段，它有望在接下来的版本之一中投入生产使用。在此之前，使用轻量级删除需要设置 allow_experimental_lightweight_delete=true。

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

空间节省不会像通过突变发出删除那样立即生效 - 请参阅下文。如果空间节省至关重要，例如磁盘空间不足，请考虑使用突变。
由于无法保证删除，因此有合规性要求的用户可能希望使用突变来确保数据被删除。

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

最后，请注意，轻量级删除使用我们在下面描述的相同突变队列和后台线程。我们建议查阅此处的文档，以获取有关内部实现的更多详细信息。

更新 ClickHouse 表中数据的最简单方法是使用 ALTER…UPDATE 语句。

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

此查询将使用给定的过滤器更新 table 表上的 col1。

与某些数据库不同，ClickHouse 的 ALTER UPDATE 语句默认是异步的。这意味着更新在后台发生，您不会立即在表上获得效果。这种更新表的过程称为突变。

这里的一个重要提示 是，更新数据是一个繁重的查询，因为 ClickHouse 必须做大量工作来优化存储和处理。突变操作强制删除包含这些行的所有数据部分，并在形成新部分时排除目标行。这可能会导致相当大的 I/O 和集群开销，因此请谨慎使用此功能，或考虑下面讨论的替代方案。

与更新一样，删除也可以通过突变实现，并提供轻量级删除的替代方案。在大多数情况下，由于重写所有列的突变成本，轻量级删除更适合数据删除。更具体地说，与轻量级删除不同，所有列都被重写，而不仅仅是 _row_exists 掩码列。

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

ALTER TABLE table
    DELETE WHERE col2 = 3

在此查询中，所有 col2 值为 3 的行都将被删除。与其他突变类似，默认情况下删除也是异步的。可以使用上面描述的相同 mutations_sync 设置使其同步。

由于突变是异步运行的，因此可以通过 system.mutations 表执行监控。这允许用户检查表上特定突变的进度。

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

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

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

SET mutations_sync = 1

现在我们的更新查询将等待突变完成

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE col2 > 0

0 rows in set. Elapsed: 1.182 sec.

请注意，此查询花费了 1 秒钟才完成，而 ClickHouse 等待后台突变完成。请注意，此参数也适用于轻量级删除。

在某些情况下，用户需要更新整个列的值。最初，用户可能会尝试通过使用不带 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 接受此操作的一种方法是使用始终为真的过滤器

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 列的默认值来指定我们想要用作更新的值。这是安全且更高效的，因为我们在此处跳过了繁重的突变操作。

有时，我们需要根据关系删除或更新行；因此，我们必须连接表。这最好在 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 表每个会话仅存储一个事件。假设在某个时间点，我们决定将 session_id 列添加到 logins 表

ALTER TABLE logins
    ADD COLUMN `session_id` UInt64

我们现在需要使用 user_id 和 time 上的 JOIN，使用 pageviews 表中的相应值更新 logins.session_id 列

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

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

在 DDL 中设置分区时，请注意按高基数列或表达式进行分区的常见陷阱。这可能会导致创建许多部分，从而导致性能问题。

在时间序列数据的情况下，我们可能希望定期删除过时的数据。ClickHouse 具有 TTL 功能，专门用于此用例。这需要配置表并指定我们想要删除哪些数据以及何时删除。假设我们想从 hits 表中删除一个月前的旧数据

ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH

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

如果我们必须频繁更新单个行，我们可以使用 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 引擎现在将负责在后台有效地从存储中删除已取消的行，因此我们不必手动删除它们。您可以在此处找到更多使用 CollapsingMergeTree 引擎的示例。

对于更复杂的情况，我们可能希望使用基于 ReplacingMergeTree 引擎的版本控制。此引擎实现了一种有效的方法来执行在其他 DBMS 中称为 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 进行基于突变的删除。例如，合规性要求需要保证从磁盘中删除数据。
在不规则和非频繁更改的情况下，使用 ALTER…UPDATE 进行基于突变的更新
使用 TTL 定期删除基于日期/时间（过时）数据，
使用 CollapsingMergeTree 频繁更新或删除单个行。
使用 ReplacingMergeTree 实现基于版本控制的 upsert（插入/更新）。
删除分区在定期删除大型数据块时。
创建新列（并删除旧列）也可能是更新整个表的更有效方法。

分享这篇文章

订阅我们的新闻通讯

随时了解功能发布、产品路线图、支持和云产品！

正在加载表单...

博客 / 工程

ClickHouse 中处理更新和删除

轻量级删除

突变

使用突变更新数据

使用突变删除数据

检查突变进度

同步更新

更新整个表

使用 JOIN 更新和删除

高效删除大型数据块

定期删除旧数据

使用 CollapsingMergeTree 删除和更新

使用版本控制和 ReplacingMergeTree 进行 Upsert

总结

订阅我们的新闻通讯

最近的文章