前面我们已经介绍过 ClickHouse 是列式存储数据库,并且是按照有序存储、且按照索引粒度建立稀疏索引,所以 ClickHouse 是不擅长做 update/delete 操作的,对于需要经常变化的数据,也不建议使用clickhouse。但是并不是说clickhouse就不能更新数据,clickhouse提供了一种基于alter语句的“突变”(mutations)操作来实现更新/删除操作。在使用mutations操作之前需要注意:
- mutations操作需要重置分区,是一种“很重”的操作,更适用于操作批量数据,最好是直接删除分区。如果是删除分区操作,应直接drop分区。
- clickhouse不支持事务,一旦语句被提交执行,就会立刻对现有数据产生影响,无法回滚。即使ClickHouse服务器重新启动,成功提交的mutations操作也将继续执行,但是如果mutations由于某种原因被卡住了,可以用 KILL MUTATION 语句取消它。
- mutations操作的执行是一个异步的后台过程,语句被提交之后就会立即返回,但并不表示数据已经被删除,会有一个后台进程去删除数据操作,具体数据什么时候删除,可以在系统表 system.mutations 查看,system.mutations 表记录了 mutations操作执行信息,is_done = 1表示删除完成。
- mutations操作是按照队列顺序执行的,虽然是异步的,但是在提交mutations操作之后插入的数据不会被影响。
- 已完成更新的条目不会立即删除,保留条目的数量由finished_mutations_to_keep存储引擎参数确定,超过数据量时旧的条目会被删除。
- 对于非副本表,所有ALTER查询都是同步执行的。对于副本表,查询只是向ZooKeeper添加适当操作的指令,操作本身会尽快执行,可以通过replication_alter_partitions_sync设置控制执行等待,如果为0表示不等待,如果为1表示只等待自己执行(默认,即一个),如果为2表示需要等待所有节点完成。另外还可以通过replication_wait_for_inactive_replica_timeout参数设置等待时间,0表示不等待,负整数表示无限制等待,正整数表示等待秒数。如果 replication_alter_partitions_sync = 2,某些副本ALTER操作超过 replication_wait_for_inactive_replica_timeout 时间,则会抛出 UNFINISHED 异常。
- ALTER TABLE … UPDATE|DELETE 语句默认都是异步执行的,但是可以通过 mutations_sync 参数控制,为0表示异步(默认),为1表示等待当前节点的所有mutations完成,为2表示等待所有副本节点执行完成。
1. UPDATE 语句
语法格式如下,和标准SQL语法相似:
ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>
Examples:
-- 使用字典查找更新
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';
-- 对于分片表,可以在CLUSTER上执行 mutations,旧版本不支持
ALTER TABLE clicks ON CLUSTER main_cluster UPDATE click_count = click_count / 2
WHERE visitor_id ILIKE '%robot%';
注意:
- 上面例子中的更新分片表在旧版本中是不支持的,只能更新本地表数据。如果没有做DDL语句同步的话,需要在分布式表的所有本地表对应的节点上分别执行 mutations 操作。
- 不能更新主键或者 ORDER BY 字段。
如果只对某个分区做UPDATE操作,除了在WHERE语句中指定条件外,还可以直接指定分区:
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id]
WHERE filter_expr;
-- Example
ALTER TABLE mt UPDATE x = x + 1 IN PARTITION 2 WHERE p = 2;
2. DELETE 语句
语法格式如下,和标准SQL语法相似:
ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>
Examples:
-- 删除记录
ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277);
-- 删除分片表数据
ALTER TABLE clicks ON CLUSTER main_cluster WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573';
注意:
- 旧版本分片表同样不支持DELETE操作。
- 如果是删除全表数据,应使用 TRUNCATE TABLE,效率会更高。
如果只对某个分区做DELETE操作,除了在WHERE语句中指定条件外,还可以直接指定分区:
ALTER TABLE [db.]table DELETE [IN PARTITION partition_id] WHERE filter_expr;
-- Example
ALTER TABLE mt DELETE IN PARTITION 2 WHERE p = 2;
如果删除整个分区数据,应该直接使用drop分区操作,参考第5节。
3. TRUNCATE 语句
TRUNCATE TABLE 适用于删除全表数据的情况,而且效率比DELETE更高。语法格式如下,高版本同样支持 ON CLUSTER 子句:
TRUNCATE TABLE [<database].]<table>
需要注意的是在一些低版本clickhouse中,直接对分片表使用TRUNCATE TABLE语句或许不会报错,但是数据并没有被删掉,依然需要对本地表执行操作。
4. DROP 语句
可以删除数据库、表、字典等。语法格式如下:
-- 删除数据库,先删除库中所有表,再删除数据库
DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster];
-- 删除表
DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster];
-- 删除字段
DROP DICTIONARY [IF EXISTS] [db.]name;
-- 删除视图,也可以通过 DROP TABLE 删除
DROP VIEW [IF EXISTS] [db.]name [ON CLUSTER cluster];
5. 列操作语句
语法格式如下,支持添加、删除、重命名字段,清楚列数据,添加注释等,可以通过逗号分隔执行多个列操作:
ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|RENAME|CLEAR|COMMENT|{MODIFY|ALTER}|MATERIALIZE COLUMN ...
5.1 新增列
语法格式如下:
ALTER TABLE [db].name [ON CLUSTER cluster] ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after | FIRST]
新增列默认是放在最后一列,AFTER 子句可以指定在某个列之后,FIRST 子句表示放在第一列。新增列数据默认都是对应类型的默认值(或者指定的默认值)。
Example:
ALTER TABLE alter_test ADD COLUMN Added1 UInt32 FIRST;
ALTER TABLE alter_test ADD COLUMN Added2 UInt32 AFTER NestedColumn;
5.2 删除列
语法格式如下:
ALTER TABLE [db].name [ON CLUSTER cluster] DROP COLUMN [IF EXISTS] name
如果列被物化视图引用,则不能被删除。
Example:
ALTER TABLE visits DROP COLUMN browser
5.3 重命名列
语法格式如下:
ALTER TABLE [db].name [ON CLUSTER cluster] RENAME COLUMN [IF EXISTS] name to new_name
不能对 ORDER BY or PRIMARY KEY 字段RENAME,否则报错。
Example:
ALTER TABLE visits RENAME COLUMN webBrowser TO browser
5.4 清除列数据
语法格式如下:
ALTER TABLE [db].name [ON CLUSTER cluster] CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name
清除指定分区上的指定列数据为默认值。
Example:
ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple();
ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902;
5.5 添加列注释
语法格式如下:
ALTER TABLE [db].name [ON CLUSTER cluster] COMMENT COLUMN [IF EXISTS] name 'Text comment'
每一列可以有一个注释。如果该列已有注释,则新注释将覆盖以前的注释。表字段注释可以通过 DESCRIBE TABLE 语句查询。
Example:
ALTER TABLE visits COMMENT COLUMN browser 'The table shows the browser used for accessing the site.'
5.6 修改列属性
语法格式如下:
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [codec] [TTL] [AFTER name_after | FIRST];
ALTER TABLE [db].name [ON CLUSTER cluster] ALTER COLUMN [IF EXISTS] name TYPE [type] [default_expr] [codec] [TTL] [AFTER name_after | FIRST];
修改列语句支持MODIFY / ALTER关键词,可以修改列类型、列顺序、TTL、压缩算法、默认值等。在更改类型时,将对值进行转换,就像对它们应用toType函数一样,需要花费较多时间。如果只更改默认表达式,则查询不会执行任何复杂的操作,而且几乎可以立即完成。
Example:
-- 更改字段类型
ALTER TABLE visits MODIFY COLUMN browser Array(String)
5.7 删除列属性
语法格式如下:
ALTER TABLE table_name MODIFY column_name REMOVE property;
可删除列默认值、压缩算法、注释、TTL等。
Example:
-- 删除TTL
ALTER TABLE table_with_ttl MODIFY COLUMN column_ttl REMOVE TTL;
6. 分区操作语句
支持卸载、删除、加载、拷贝、移动、备份等操作。
6.1 卸载分区
语法格式如下:
ALTER TABLE table_name DETACH PARTITION|PART partition_expr
卸载分区的意思是把分区数据移动到 detached 目录,相当于数据从表中被删除,但是没有完全删除,还可以通过 ATTACH 语句加载分区,恢复数据,类似于我们电脑的回收站机制。
Example:
ALTER TABLE mt DETACH PARTITION '2020-11-21';
ALTER TABLE mt DETACH PART 'all_2_2_0';
对于副本表,DETACH 语句会被同步到所有副本节点上执行,但是 DETACH 语句本身只能在leader节点上执行,可以通过 system.replicas 表查询leader副本节点,也可以对所有副本执行 DETACH 语句,非leader副本会抛出异常。
6.2 加载分区
语法格式如下:
ALTER TABLE table_name ATTACH PARTITION|PART partition_expr
从 detached 目录中加载分区或部分数据,可以是已卸载的分区数据或者是复合格式的数据文件。
Examples:
ALTER TABLE visits ATTACH PARTITION 201901;
ALTER TABLE visits ATTACH PART 201901_2_2_0;
对于副本表,该操作同样会同步到所有副本执行,如果当前副本 detached 目录没有复合条件的数据,而其他副本节点有,则当前副本会从其他副本下载数据,所以如果是外部数据,放置在任何一个副本节点上即可。
6.3 删除分区
语法格式如下:
ALTER TABLE table_name DROP PARTITION|PART partition_expr
删除分区数据并不会立即被删除,首先把分区标记为 inactive(非活跃),然后大约在10分钟内完全删除数据。在副本表上执行时,会删除所有副本分区。
Example:
ALTER TABLE mt DROP PARTITION '2020-11-21';
ALTER TABLE mt DROP PART 'all_4_4_0';
6.4 删除已卸载的分区
语法格式如下:
ALTER TABLE table_name DROP DETACHED PARTITION|PART partition_expr
6.5 复制分区
语法格式如下:
ALTER TABLE table2 ATTACH PARTITION partition_expr FROM table1
从表table1中复制分区到表table2,两个表必须具有相同的表结构和分区键。
6.6 替换分区
语法格式如下:
ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1
从表table1中复制分区到表table2,并替换table2中已存在的同名分区,两个表必须具有相同的表结构和分区键。
6.7 移动分区
移动分区有两种情况:从一个表移动到另一个表、从一个表移动到磁盘。
(1) 从一个表移动到另一个表
语法格式如下:
ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest
从表table_source中移动分区到表table_dest,table_source中的分区会被删除,两个表必须具有相同的表结构、分区键、家族表引擎(区分副本和非副本表)以及相同的存储策略。
(2) 从一个表移动到磁盘
ALTER TABLE table_name MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'
该操作不会同步到所有副本节点上,因为不同的副本可以有不同的存储策略。
6.8 备份分区
语法格式如下:
ALTER TABLE table_name FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name']
此查询创建指定分区的本地备份。如果省略PARTITION子句,查询将立即创建所有分区的备份。该查询不会自动复制到所有副本节点上,只会对当前节点做备份,且只备份数据,不备份元数据信息,如果需要备份元数据,需要拷贝 /var/lib/clickhouse/metadata/database/table.sql 文件。
6.9 删除备份分区
语法格式如下:
ALTER TABLE 'table_name' UNFREEZE [PARTITION 'part_expr'] WITH NAME 'backup_name'
从磁盘中移除指定名称的备份分区。如果省略PARTITION子句,查询将立即删除所有分区的备份。
6.10 清除分区索引
语法格式如下:
ALTER TABLE table_name CLEAR INDEX index_name IN PARTITION partition_expr
6.11 设置分区表达式
几乎所有的分区操作都需要指定 partition_expr(分区表达式),但是我们在建表的时候分区定义可能是五花八门,可以是数值型字段、时间类型字段,或者字段的一部分等等,怎么确定分区表达式呢?
- 通过 system.parts 表查询分区名称。
- 按照建表语句格式指定,例如建表按照月份分区,那么 partition_expr 可以为:tuple(toYYYYMM(toDate(‘2019-01-25’))) 。
- 通过分区ID指定,需要注意如果使用分区ID,语句中必须加ID关键字:ALTER TABLE visits DETACH PARTITION ID ‘201901’。
同样,OPTIMIZE 语句也支持指定分区:
OPTIMIZE TABLE table_not_partitioned PARTITION tuple() FINAL;
7. SETTING 操作语句
在前面介绍MergeTree表原理和建表语句的文章中,我们介绍了建表语句中的 SETTINGS 选项,可以指定一系列参数。同样,这些参数也是可以更改的(只适用于MergeTree表)。语法格式如下,可以同时修改多个设置参数:
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY|RESET SETTING ...
7.1 修改 SETTING
Example:
CREATE TABLE example_table (id UInt32, data String) ENGINE=MergeTree() ORDER BY id;
ALTER TABLE example_table MODIFY SETTING max_part_loading_threads=8, max_parts_in_total=50000;
7.2 清除 SETTING
恢复SETTING项到默认值:
CREATE TABLE example_table (id UInt32, data String) ENGINE=MergeTree() ORDER BY id
SETTINGS max_part_loading_threads=8;
ALTER TABLE example_table RESET SETTING max_part_loading_threads;
8. 跳数索引操作语句
clickhouse 除了主键中的稀疏索引以外,还支持二级索引(跳数索引),在前面介绍二级索引的文章中,我们介绍了二级索引的种类、原理,以及在建表语句中怎么创建二级索引。除了在建表语句中定义二级索引外,我们还可以在后期添加、删除二级索引。
语法格式如下:
-- 添加二级索引
ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value [FIRST|AFTER name];
-- 删除索引
ALTER TABLE [db].name DROP INDEX name;
-- 重建索引,一般用于新增索引对历史数据生效
ALTER TABLE [db.]table MATERIALIZE INDEX name [IN PARTITION partition_name];
Example:
-- 对表skip_table的my_value列建立名为vix的set二级索引
ALTER TABLE skip_table ADD INDEX vix my_value TYPE set(100) GRANULARITY 2;
需要注意新建立的二级索引只对新增数据有效,所以是一个轻量级操作,如果想要对历史数据也有效,需要执行:
ALTER TABLE skip_table MATERIALIZE INDEX vix;
该操作需要对历史数据重建索引,所以是一个 mutation 操作。
二级索引操作语句会自动同步到所有副本节点上。
9. 约束操作语句
建表指定约束:
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
...
CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
...
) ENGINE = engine
添加、删除约束:
ALTER TABLE [db].name ADD CONSTRAINT constraint_name CHECK expression;
ALTER TABLE [db].name DROP CONSTRAINT constraint_name;
约束操作语句对所有副本有效。添加大量的约束可能会对大型INSERT操作的性能产生影响。