处理ClickHouse中的更新和删除操作

![1.png](/storage/thumbnails/_signature/1NB128QJ4Q7F6U3KMO7HQLNO8G.png)

作为全球最快的实时分析数据库，许多ClickHouse工作负载涉及大量数据，这些数据仅写入一次，不经常修改（例如，由IOT设备生成的遥测事件或由电子商务网站生成的客户点击）。尽管这些数据通常是不可变的，但在分析期间提供上下文的关键数据集（例如，基于设备或客户ID的查找表）可能需要进行修改。

在ClickHouse中，有多种历史方法可用于更新和删除数据，具体取决于您的目标和性能要求。本文的其余部分将描述每种方法及其权衡，以及最近针对轻量级删除的一些发展，以解决一些常见挑战。我们建议最佳实践，并在考虑方法时强调一些重要的考虑因素。

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

### 总结
在分析环境中更新和删除数据可能具有挑战性，并可能严重影响数据库性能。为了解决这个问题，ClickHouse为不同情况提供了多种高效的更新和删除数据的强大方法：

- 通过DELETE FROM语法进行轻量级删除是从ClickHouse中删除数据的最高效方式，前提是不需要立即节省磁盘空间，而且用户可以容忍删除的数据“存在”在磁盘上。
- 在需要立即节省磁盘空间的情况下，可以使用ALTER…DELETE进行基于突变的删除。例如，合规性要求需要保证数据从磁盘上删除。
- 在出现不规则和不频繁更改的情况下，可以使用ALTER…UPDATE进行基于突变的更新。
- 使用TTL（Time To Live）根据日期/时间定期删除（过时的）数据。
- 使用CollapsingMergeTree来频繁更新或删除单独的行。
- 使用ReplacingMergeTree来实现基于版本控制的upsert（插入/更新）。
- 在定期删除大块数据时，通过删除分区来实现。
- 创建新列（并删除旧列）可能也是更新整个表格的更高效方式。

### 轻量级删除
轻量级删除是从ClickHouse中删除数据的首选和最有效方式。通过DELETE FROM table语法公开，用户可以指定条件来删除特定行，如下所示：
```sql
DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2
```
除非设置mutations_sync设置为1（请参见下文），否则此操作默认是异步的。执行删除操作时，ClickHouse为每行保存一个掩码，指示它是否已在_row_exists列中删除。随后的查询将排除这些已删除的行，如下所示

![2.png](/storage/thumbnails/_signature/3E8EHGTV1MG0VUNDPF9HTG8470.png)

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

![3.png](/storage/thumbnails/_signature/2S2LFNEDSRVDSHEAJCDTS09633.png)

轻量级删除功能在版本22.8中首次发布，截至撰写本文时仍处于实验阶段，但计划在接下来的某个版本中正式投入生产。在此之前，使用轻量级删除功能需要设置allow_experimental_lightweight_delete=true

用户应该明白，依赖正常的后台合并周期时，行将仅在某个时刻从磁盘中最终删除。尽管在搜索结果中被排除，但这些行将一直存在于磁盘上，直到它们所在的部分被合并。这一过程所需的时间是不确定的。这会产生一些影响：

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

轻量级删除操作的成本取决于WHERE子句中匹配行的数量以及当前数据部分的数量。此操作在匹配少量行时效率最高。用户还应该知道，轻量级删除在Wide部分上性能最佳，其中列数据文件分别存储，而在Compact部分上性能较差，其中一个文件用于存储所有列数据。前者允许将掩码_row_exists存储为单独的文件，从而允许独立写入，而不受其他列的影响。一般情况下，紧凑部分会在插入后形成。一旦部分超过一定大小（例如，由于合并），将使用宽格式。对于大多数工作负载，这不应该成为问题。

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

### Mutations / 突变

### 使用突变操作更新数据
在ClickHouse表中更新数据的最简单方法是使用ALTER...UPDATE语句。
```sql
ALTER TABLE table
    UPDATE col1 = 'Hi' WHERE col2 = 2
```

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

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

![4.png](/storage/thumbnails/_signature/2OAKC1D9GRA1PITQR62VN3P9TM.png)

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

### 使用突变操作删除数据
与更新操作一样，使用突变操作也可以进行删除操作，提供了对轻量级删除的替代方案。在大多数情况下，轻量级删除更适合数据删除，因为重新写入所有列的突变成本较高。具体来说，不同于轻量级删除，突变操作会重新写入所有列，而不仅仅是_row_exists掩码列。

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

```sql
ALTER TABLE table
    DELETE WHERE col2 = 3
```

在这个查询中，所有col2值为3的行都被删除。与其他突变操作类似，删除操作默认是异步的。可以使用上面描述的mutations_sync设置来使其同步进行。

### 检查突变操作进度

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

```sql
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，那么它仍在执行中。突变操作会针对每个表部分执行，其中已变异的部分会立即变得可用：

![5.png](/storage/thumbnails/_signature/2RJPGJ716I8QU79S9DEGLE1VIN.png)

### 同步更新
对于需要同步更新的用户，可以将mutations_sync参数设置为1（或2，如果还需要等待所有副本也完成更新）：
```sql
SET mutations_sync = 1
```
现在我们的更新查询将等待突变操作完成：
```sql
ALTER TABLE table
    UPDATE col1 = 'bye' WHERE col2 > 0

0 rows in set. Elapsed: 1.182 sec. 
```
请注意，这个查询完成需要1秒，因为ClickHouse等待后台突变操作完成。请注意，此参数也适用于轻量级删除。

### 更新整个表格
在某些情况下，用户可能需要更新整个列的值。最初，用户可能会尝试使用不带WHERE子句的ALTER TABLE查询来实现这一目标，然而这会失败，如下所示：

```sql
ALTER TABLE table UPDATE col1 = 'bye';

Syntax error: failed at position 38 (end of query):
ALTER TABLE table UPDATE col1 = 'bye';
```
ClickHouse不允许您更新整个表，因为更新操作开销较大。强制ClickHouse接受此操作的一种方法是使用一个始终为真的过滤条件：
```sql
ALTER TABLE table
    UPDATE col1 = 'bye' WHERE true
```

然而，一个更优化的方法是创建一个新列，将新值设置为默认值，然后切换旧列和新列。例如：
```sql
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列的默认值来指定我们要用作更新值的内容。这是安全的，而且效率更高，因为我们避免了繁重的突变操作。

### 使用JOIN进行更新和删除

有时，我们需要根据关联关系来删除或更新行，因此我们必须进行表格连接。在ClickHouse中，最好使用Join表格引擎和joinGet函数来实现这一点。假设我们有两个表格 - 一个包含所有页面浏览记录，另一个包含所有登录记录：
```sql
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列：
```sql
ALTER TABLE logins
    ADD COLUMN `session_id` UInt64
```
现在，我们需要使用user_id和time在pageviews表格上执行JOIN，将对应的值更新到logins.session_id列中：
```sql
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表格：
```sql
CREATE TABLE pageviews_join
ENGINE = Join(ANY, LEFT, user_id, time) AS
SELECT *
FROM pageviews
```
这个表格将允许我们在执行更新查询时使用joinGet函数来根据JOIN获取值：

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

我们可以看到，logins表格已根据JOIN进行了相应的更新：

```sql
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表格以确保）：

```sql
DROP TABLE pageviews_join
```

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

### 高效删除大块数据

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

```sql
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的数据：

```sql
ALTER TABLE hits
    DROP PARTITION 'c'
```

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

![6.png](/storage/thumbnails/_signature/67TN216ULVS7JK0G3TGNR845S.png)

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

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

┌─partition─┐
│ c         │
│ a         │
│ b         │
└───────────┘
```

我们还可以使用DETACH和ATTACH语句在表格之间移动分区（例如，如果我们希望将数据移动到垃圾表格而不是删除它）。在设置DDL中的分区时，要注意分区的常见陷阱，即按具有高基数的列或表达式进行分区。这可能会导致创建许多部分，从而导致性能问题。

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

```sql
ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH
```
在这里，我们要求ClickHouse删除所有时间列值比一个月前的当前时间更早的行。TTL也可以设置在列上，以在一段时间后将它们的值重置为默认值。通过按日期进行分区，舍入到适当的时间单位（例如，天），可以使此操作更加高效。当执行TTL规则时，ClickHouse将以最高效的方式自动删除数据。再次强调，不应该通过高基数的时间列（例如，毫秒级粒度）对表格进行分区，以避免高分区数量。通常，按天或月进行分区对于大多数TTL操作已经足够。

### 使用CollapsingMergeTree进行删除和更新操作
如果我们经常需要频繁更新单独的行，我们可以使用CollapsingMergeTree引擎来高效管理数据更新。

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

```sql
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)
```

对于CollapsingMergeTree，sign列被用作一种方法来告诉ClickHouse我们要更新特定的行。如果我们插入-1到sign列，整行数据将被删除。如果我们插入一行数据，其中sign = 1，ClickHouse将保留这一行。要更新的行根据在创建表时使用的ORDER BY () DDL语句中的排序键来标识：

![7.png](/storage/thumbnails/_signature/39UFTSPTRJAEG4C5DLN1FQL537.png)

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

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

现在我们在表格中有一行数据：

```sql
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%时，我们插入以下两行：
```sql
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列上进行筛选以获取正确的结果：

```sql
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进行更新操作

对于更复杂的情况，我们可能希望使用基于ReplacingMergeTree引擎的版本控制。该引擎通过使用特殊的版本列来实现其他DBMS中所称的UPSERT的高效方法，以跟踪应该删除哪些行。如果存在具有相同排序键的多行，存储中仅保留具有最大版本的行，而其他行将被删除：

![8.png](/storage/thumbnails/_signature/3OKC6TKKOBT65TM7G95NQFNOHF.png)

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

```sql
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)
```

请注意特殊的版本数字列，这将被ReplacingMergeTree引擎用来标记需要删除的行。让我们模拟用户随着时间从0阅读文章到80%：

```sql
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);
```

在这里，随着阅读进度的跟踪，我们逐渐增加版本列的值。行删除的过程也是通过正常的合并周期在后台执行的，因此我们需要在查询时基于最新的版本进行筛选：

```sql
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来获取具有最新版本的行的列表：

```sql
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在后台自动完成。

ref:

[https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse](https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse)

总结

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

通过DELETE FROM语法进行轻量级删除是从ClickHouse中删除数据的最高效方式，前提是不需要立即节省磁盘空间，而且用户可以容忍删除的数据“存在”在磁盘上。
在需要立即节省磁盘空间的情况下，可以使用ALTER…DELETE进行基于突变的删除。例如，合规性要求需要保证数据从磁盘上删除。
在出现不规则和不频繁更改的情况下，可以使用ALTER…UPDATE进行基于突变的更新。
使用TTL（Time To Live）根据日期/时间定期删除（过时的）数据。
使用CollapsingMergeTree来频繁更新或删除单独的行。
使用ReplacingMergeTree来实现基于版本控制的upsert（插入/更新）。
在定期删除大块数据时，通过删除分区来实现。
创建新列（并删除旧列）可能也是更新整个表格的更高效方式。

轻量级删除

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

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

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

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

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

Mutations / 突变

使用突变操作更新数据

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

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

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

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

使用突变操作删除数据

与更新操作一样，使用突变操作也可以进行删除操作，提供了对轻量级删除的替代方案。在大多数情况下，轻量级删除更适合数据删除，因为重新写入所有列的突变成本较高。具体来说，不同于轻量级删除，突变操作会重新写入所有列，而不仅仅是_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列的默认值来指定我们要用作更新值的内容。这是安全的，而且效率更高，因为我们避免了繁重的突变操作。

使用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;

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

ALTER TABLE logins
    ADD COLUMN `session_id` UInt64

现在，我们需要使用user_id和time在pageviews表格上执行JOIN，将对应的值更新到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         │
└───────────┘

定期删除旧数据

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

ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH

在这里，我们要求ClickHouse删除所有时间列值比一个月前的当前时间更早的行。TTL也可以设置在列上，以在一段时间后将它们的值重置为默认值。通过按日期进行分区，舍入到适当的时间单位（例如，天），可以使此操作更加高效。当执行TTL规则时，ClickHouse将以最高效的方式自动删除数据。再次强调，不应该通过高基数的时间列（例如，毫秒级粒度）对表格进行分区，以避免高分区数量。通常，按天或月进行分区对于大多数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)

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