更新和删除 ClickHouse 数据
尽管 ClickHouse 侧重于高容量分析工作负载,但在某些情况下仍然可以修改或删除现有数据。这些操作称为“变异”,并使用 ALTER TABLE
命令执行。您还可以使用 ClickHouse 的轻量级删除功能 DELETE
删除一行。
如果您需要执行频繁更新,请考虑在 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
命令中。
示例:
这样的变异允许使用字典查找更新替换
visitor_ids
。ALTER TABLE website.clicks
UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
WHERE visit_date < '2022-01-01'在一个命令中修改多个值可能比多个命令更有效率。
ALTER TABLE website.clicks
UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
WHERE visit_date < '2022-01-01'对于分片表,变异可以在
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 值。
示例
删除任何列在值数组中的记录。
ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277)
此查询更改了什么?
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
命令,称为**轻量级删除**。已删除的行会立即被标记为已删除,并且会自动从所有后续查询中过滤掉,因此您无需等待部分合并或使用 FINAL
关键字。数据的清理会在后台异步进行。
DELETE FROM [db.]table [ON CLUSTER cluster] [WHERE expr]
例如,以下查询删除 hits
表中 Title
列包含文本 hello
的所有行
DELETE FROM hits WHERE Title LIKE '%hello%';
关于轻量级删除的一些说明
- 此功能仅适用于
MergeTree
表引擎系列。 - 轻量级删除默认情况下是异步的。将
mutations_sync
设置为 1 以等待一个副本处理语句,并将mutations_sync
设置为 2 以等待所有副本。