更新和删除 ClickHouse 数据

说明如何在 ClickHouse 中执行更新和删除操作

All quickstarts

数据仓储OSS

尽管 ClickHouse 主要面向高吞吐量的分析型工作负载，但在某些情况下也可以修改或删除现有数据。这些操作称为“变更”，通过 ALTER TABLE 命令执行。

如果你需要频繁执行更新，请考虑在 ClickHouse 中使用去重，这样就可以在不产生变更操作的情况下更新 和/或删除行。或者，也可以使用轻量级更新 或轻量级删除

更新数据

使用 ALTER TABLE...UPDATE 命令来更新表中的行：

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

<expression> 是满足 <filter_expr> 条件时该列的新值。<expression> 必须与该列的数据类型相同，或者能够使用 CAST 运算符转换为相同的数据类型。<filter_expr> 应为数据中的每一行返回一个 UInt8 值 (零或非零) 。多个 UPDATE <column> 语句可以合并到同一条 ALTER TABLE 命令中，并用逗号分隔。

示例：

1. 如下变更可通过字典查找，将 visitor_ids 更新为新的值：

   ALTER TABLE website.clicks
   UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
   WHERE visit_date < '2022-01-01'
   

2. 在一条命令中修改多个值，通常比执行多条命令更高效：

   ALTER TABLE website.clicks
   UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
   WHERE visit_date < '2022-01-01'
   

3. 对于分片表，可以使用 ON CLUSTER 执行变更：

   ALTER TABLE clicks ON CLUSTER main_cluster
   UPDATE click_count = click_count / 2
   WHERE visitor_id ILIKE '%robot%'
   

无法更新属于主键或排序键一部分的列。

删除数据

使用 ALTER TABLE 命令删除行：

ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>

<filter_expr> 应为每一行数据返回一个 UInt8 值。

示例

1. 删除某列值位于给定值数组中的所有记录：

   ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277)
   

2. 此查询会修改哪些数据？

   ALTER TABLE clicks ON CLUSTER main_cluster DELETE WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'
   

若要删除表中的所有数据，使用 TRUNCATE TABLE [<database].]<table> 命令效率更高。该命令也可通过 ON CLUSTER 执行。

有关更多详细信息，请参阅 DELETE 语句 文档页面。

轻量级删除

删除行的另一种方式是使用 DELETE FROM 命令，这称为轻量级删除。被删除的行会立即标记为已删除，并会在后续所有查询中自动被过滤掉，因此你无需等待 parts 合并，也无需使用 FINAL 关键字。数据清理会在后台异步进行。

DELETE FROM [db.]table [ON CLUSTER cluster] [WHERE expr]

例如，以下查询会删除 hits 表中 Title 列包含文本 hello 的所有行：

DELETE FROM hits WHERE Title LIKE '%hello%';

关于轻量级删除，有几点需要说明：

• 此功能仅适用于 MergeTree 表引擎家族。
• 默认情况下，轻量级删除为同步执行，会等待所有副本完成删除处理。其行为由 lightweight_deletes_sync 设置 控制。

All quickstarts