在 ClickHouse 中处理更新和删除以及 Upserts _临沂市新商网络技术有限公司

在 ClickHouse 中处理更新和删除以及 Upserts

发布时间：2023-09-26

作为世界上最快的实时分析数据库，ClickHouse 的许多工作负载都涉及大量的数据，这些数据只写一次，不经常修改(例如，物联网设备产生的遥测事件或电子商务网站产生的客户点击)。虽然这些通常是不可变的，但在分析期间提供上下文的其他关键数据集(例如，基于设备或客户 ID 的信息查找表)可能需要修改。

根据你的目标和性能要求，ClickHouse 有多种更新和删除数据的方法。本文的其余部分将描述每种方法及其优缺点，以及解决一些常见挑战的轻量级删除的一些最新进展。我们推荐最佳实践，并强调在考虑一种方法时需要考虑的一些重要事项。

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

轻量级删除

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

https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals

DELETE FROM table WHERE col1 = 'Hi' AND col2 = 21.

默认情况下，该操作是异步的，除非 mutations_sync 设置为 1(见下文)。执行删除时，ClickHouse 会为每一行保存一个掩码，在 _row_exists 列中表示是否被删除。随后的查询依次排除这些已删除的行，如下所示。

图片

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

图片

它于 22.8 发布，在撰写本文时仍处于试验阶段，轻量级删除将在下一个版本中成为生产就绪。在此之前，使用轻量级删除需要设置allow_experimental_lightweight_delete=true。

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

节省的空间不会像通过突变发出删除那样直接——见下文。如果节省空间非常重要，例如磁盘空间不足，请考虑使用突变。
由于不能保证删除，有遵从性需求的用户可能希望使用突变来确保删除数据。

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

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-data-storage

最后，请注意，轻量级删除使用与我们下面描述的相同的突变队列和后台线程。关于内部实现的更多细节，我们推荐使用这里的文档。

https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals

突变

使用突变更新数据

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

https://clickhouse.com/docs/en/sql-reference/statements/alter/update

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

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

与一些数据库不同，ClickHouse 的 ALTER UPDATE 语句默认情况下是异步的。这意味着更新发生在后台，您不会立即对表产生影响。这个更新表的过程称为突变。

https://clickhouse.com/docs/en/sql-reference/statements/alter/#mutations

图片

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

使用突变删除数据

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

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

ALTER TABLE table
    DELETE WHERE col2 = 31.
2.

在此查询中，删除 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 │
└───────────────────────────────────────┴─────────┘1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

如果 is_done 的值对于特定的突变是 0，那么它仍然在执行。对每个表部分执行突变，突变后的部分立即可用：

图片

同步更新

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

https://clickhouse.com/docs/en/operations/settings/settings/#mutations_sync

SET mutations_sync = 11.

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

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

0 rows in set. Elapsed: 1.182 sec.1.
2.
3.
4.

注意，当 ClickHouse 等待后台突变完成时，这个查询花了 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';1.
2.
3.
4.

ClickHouse 不会让你更新整个表，因为更新是繁重的。强迫 ClickHouse 接受此操作的一种方法是使用始终为真过滤器:

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE true1.
2.

然而，更优的方法是创建一个新列，将新值作为默认值，然后在新旧列之间切换。例如：

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;1.
2.
3.
4.
5.
6.

我们使用 col1_new 列的默认值来指定要使用的更新值。这是安全且高效得多的，因为我们跳过了这里的重突变操作。

使用 JOIN 进行更新和删除

有时，我们需要根据关系删除或更新行; 因此，我们必须 join 表。在 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;1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

这两个表之间的区别在于，logins 表每个会话只存储一个事件。假设在某个时间点，我们决定将 session_id 列添加到 logins 表中:

ALTER TABLE logins
    ADD COLUMN `session_id` UInt641.
2.

我们现在需要使用 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 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘1.
2.
3.
4.
5.
6.
7.
8.

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

CREATE TABLE pageviews_join
ENGINE = Join(ANY, LEFT, user_id, time) AS
SELECT *
FROM pageviews1.
2.
3.
4.

该表将允许我们在执行更新查询时使用 joinGet 函数来基于 JOIN 获取值:

ALTER TABLE logins
    UPDATE session_id = joinGet('pageviews_join', 'session_id', user_id, time) WHERE session_id = 01.
2.

我们可以看到，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 │
└─────────┴─────────────────────┴────────────┘1.
2.
3.
4.
5.
6.

因为我们已经通过添加 session_id 列更改了 logins 表，所以我们可以在更改完成后 DROP pageviews_join 表(删除之前请检查 system.mutations 表以确定):

DROP TABLE pageviews_join1.

同样的方法也可以用于通过轻量级或基于突变的删除来删除数据。

高效删除大块数据块

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

CREATE TABLE hits
(
    `project` String,
    `url` String,
    `time` DateTime,
    `hits` UInt32
)
ENGINE = MergeTree
PARTITION BY project
ORDER BY (project, path, time)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

通过 project 列对该表进行分区，我们可以通过删除整个分区来删除具有特定 project 值的行。让我们删除所有带有 project = c 的内容:

ALTER TABLE hits
    DROP PARTITION 'c'1.
2.

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

图片

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

SELECT partition
FROM system.parts
WHERE table = 'hits'

┌─partition─┐
│ c         │
│ a         │
│ b         │
└───────────┘1.
2.
3.
4.
5.
6.
7.
8.
9.

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

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

定期删除旧数据

对于时间序列数据，我们可能希望定期删除过时的数据。ClickHouse 对于这个确切的用例具有 TTL 特性。这需要配置一个表，并指定我们希望删除哪些数据以及何时删除。假设我们想从 hits 表中删除超过一个月的数据:

https://docs.google.com/document/d/1CAmuBror9fGLZwFCiBUH5rt89LKq-ylA1Lpa6-FrJns/edit
https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl

ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH1.
2.

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

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-column-ttl

使用 CollapsingMergeTree 删除和更新

如果我们必须频繁地更新单个行，我们可以使用 CollapsingMergeTree 引擎来有效地管理数据更新。

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

特殊的 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);1.
2.

现在我们在表中有了一行:

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 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘1.
2.
3.
4.
5.
6.

一分钟后，当用户达到文章的 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);1.
2.
3.

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

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

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/#table_engine-collapsingmergetree-collapsing-algorithm

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.1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

现在，CollapsingMergreTree 引擎会在后台有效地从存储中删除被取消的行，所以我们不必手动删除它们。你可以在这里找到更多使用 CollapsingMergeTree 引擎的例子。

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

使用版本控制和 ReplacingMergeTree 的 Upserts

对于更复杂的情况，我们可能希望使用基于 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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

请注意特殊的 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);1.
2.
3.
4.
5.

这里，我们在跟踪读取进度时增加 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 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────┘1.
2.
3.
4.
5.
6.
7.
8.
9.

或者我们可以使用 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 │
└─────────┴────────────┴─────────┘1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

再次，我们不必关心旧版本的删除-这是由 ClickHouse 在后台自动完成。

总结

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

轻量删除通过 DELETE FROM 语法删除数据从 ClickHouse。这是最有效的删除数据的方法，前提是不需要立即节省磁盘空间，并且用户可以容忍删除的数据“存在”在磁盘上。
在需要立即节省磁盘空间的情况下，通过 ALTER…DELETE 进行基于突变的删除。例如，遵从性需求需要保证从磁盘中删除数据。
在不规律和不频繁的变化情况下，使用 ALTER…UPDATE 进行基于突变的更新
使用 TTLs 定期删除基于日期/时间的(过时的)数据;

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl

使用 CollapsingMergeTree 频繁地更新或删除单个行。
https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/
使用 ReplacingMergeTree 实现基于版本控制的 upsert(插入/更新)。
https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree/
定期删除大数据块时删除分区。
https://clickhouse.com/docs/en/sql-reference/statements/alter/partition/#drop-partitionpart
创建新列(并删除旧列)可能也是更新整个表的更有效的方法。

Refs

https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse

软件产品

人工智能

物联网

服务器运维服务

数据可视化

电子政务