修改表结构 (ALTER TABLE)

列操作（COLUMN）

添加列（ADD COLUMN）

添加一个具有指定名称、类型、编码和默认表达式的新列到表中。

语法

ALTER TABLE [tableIdentifier] ADD COLUMN [IF NOT EXISTS] [tableColumnDfnt] [AFTER name_after]

• 如果包含 IF NOT EXISTS 子句，则如果列已经存在，查询不会返回错误。
• 如果指定了 AFTER name_after（另一个列的名称），则该列将添加在指定列之后。如果希望将列添加到表的开头，请使用 FIRST 子句。否则，列将被添加到表的末尾。对于一系列操作，name_after 可以是先前操作中添加的列的名称。

示例

-- Step 1: create a table 
CREATE TABLE db_name.table_name
(
    `order_by_column ` String
)
ENGINE = `CnchMergeTree`
ORDER BY (`order_by_column `)

-- Step 2: add column
ALTER TABLE db_name.table_name ADD COLUMN IF NOT EXISTS column_name String COMMENT 'column comment here' AFTER order_by_column

修改列（MODIFY COLUMN）

语法

ALTER TABLE [tableIdentifier] MODIFY COLUMN [IF EXISTS] [tableColumnDfnt] 

• 排序列（Order by column）：可以修改其 default_expr、注释（comment）、编码方式（codec）。
• 分区列（Partition by column）：可以修改其注释（comment）。
• 普通列（Normal column）：可以修改其类型（type）、default_expr、注释（comment）、编码方式（codec）。

示例

-- Step 1: create a table 
CREATE TABLE db_name.table_name
(
    `order_by_column ` String,
    `normal_column` Int64 DEFAULT 0
)
ENGINE = `CnchMergeTree`
ORDER BY (`order_by_column `)

-- Step 2: modify column normal_column_name & update its data type, comment and default value
ALTER TABLE db_name.table_name MODIFY COLUMN IF EXISTS normal_column String DEFAULT 'new_default_value' COMMENT 'new comment' 

MODIFY COLUMN REMOVE

移除一个表的属性: **`DEFAULT`**, **`ALIAS`**, **`MATERIALIZED`**, **`CODEC`**, **`COMMENT`**, **`TTL`**, **`SETTINGS`**.

语法

ALTER TABLE table_name MODIFY COLUMN column_name REMOVE property;

示例Remove TTL

ALTER TABLE table_with_ttl MODIFY COLUMN column_ttl REMOVE TTL;

MODIFY COLUMN MODIFY SETTING

修改一个表的参数设置

语法

ALTER TABLE table_name MODIFY COLUMN column_name MODIFY SETTING name=value,...;

示例
修改字段 max_compress_block_size 到 1MB:

ALTER TABLE table_name MODIFY COLUMN column_name MODIFY SETTING max_compress_block_size = 1048576;

MODIFY COLUMN RESET SETTING

重置列参数设置，同时移除表 CREATE 语句列表达式的参数设置声明。

语法

ALTER TABLE table_name MODIFY COLUMN column_name RESET SETTING name,...;

示例
重设置表参数 max_compress_block_size 为 default 值:

ALTER TABLE table_name MODIFY COLUMN column_name RESET SETTING max_compress_block_size;

删除列（DROP COLUMN）

删除表中的一列。Partition by columns 和 order by columns 不允许删除。

语法

ALTER TABLE [tableIdentifier] DROP COLUMN [IF EXISTS] column_name;

• 指定了IF EXISTS子句，如果列不存在，查询将不会返回错误。

示例

-- Step 1: create a table 
CREATE TABLE db_name.table_name
(
    `order_by_column` String,
    `normal_column` Int64
)
ENGINE = `CnchMergeTree`
ORDER BY (`order_by_column`)

-- Step 2: drop the normal column
ALTER TABLE db_name.table_name DROP COLUMN IF EXISTS normal_column

重命名列（RENAME COLUMN）

重命名表中的列。使用时有一些注意事项：

• 不能将列重命名为已存在的列。
• 不能将列重命名为自身。
• 重命名的原始列必须存在。

语法

ALTER TABLE [tableIdentifier] RENAME COLUMN [IF EXISTS] column_name_from TO column_name_to;

• 指定了IF EXISTS子句，如果列不存在，查询将不会返回错误。

示例

-- Step 1: create a table 
CREATE TABLE db_name.table_name
(
    `order_by_column` String,
    `old_column_name` Int64
)
ENGINE = `CnchMergeTree`
ORDER BY (`old_column_name`)

-- Step 2: rename column
ALTER TABLE db_name.table_name RENAME COLUMN old_column_name TO new_column_name

清除列（CLEAR COLUMN）

重置指定分区的列中的所有数据

语法

CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name

示例

ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple()

注释列（COMMENT COLUMN）

注释一个列

语法

COMMENT COLUMN [IF EXISTS] name 'Text comment'

示例

ALTER TABLE visits COMMENT COLUMN browser 'This column shows the browser used for accessing the site.'

分区操作（PARTITION）

DETACH PARTITION|PART

将分区或数据部分移动到分离目录并忘记它。

语法

ALTER TABLE table_name  DETACH PARTITION|PART partition_expr

将指定分区的所有数据移动到分离目录。服务器会将该分离的数据分区视为不存在。在执行 ATTACH 查询之前，服务器不会知道这些数据的存在。

示例

ALTER TABLE mt DETACH PARTITION '2020-11-21';
ALTER TABLE mt DETACH PART 'all_2_2_0';

查询执行后，您可以对分离目录中的数据进行任何操作——将其从文件系统中删除，或仅将其保留。

DROP PARTITION|PART

删除分区或数据部分。

语法

ALTER TABLE table_name  DROP PARTITION|PART partition_expr

从表中删除指定的分区。此查询将分区标记为非活动状态，并在大约10分钟内完全删除数据。

示例

ALTER TABLE mt DROP PARTITION '2020-11-21';
ALTER TABLE mt DROP PART 'all_4_4_0';

DROP DETACHED PARTITION|PART

从分离目录中移除指定的部分或指定分区的所有PART

语法

ALTER TABLE table_name  DROP DETACHED PARTITION|PART partition_expr

FORGET PARTITION

如果分区为空，则删除分区元数据。

语法

ALTER TABLE table_name FORGET PARTITION partition_expr

如果分区不为空或未知，查询将失败。请确保仅对将不再使用的分区执行此操作。

示例

ALTER TABLE mt FORGET PARTITION '20201121';

ATTACH PARTITION|PART

将分区或数据部分从分离目录添加到表中。

语法

ALTER TABLE table_name  ATTACH PARTITION|PART partition_expr

将数据从分离目录添加到表中。可以为整个分区或单独的PART添加数据。

示例

ALTER TABLE visits ATTACH PARTITION 201901;
ALTER TABLE visits ATTACH PART 201901_2_2_0;

ATTACH PARTITION FROM

将数据分区从一个表复制到另一个表并添加。

语法

ALTER TABLE table2 ATTACH PARTITION partition_expr FROM table1

此查询将数据分区从 table1 复制到 table2。
注意：

• 数据不会从 table1 或 table2 中删除。

• table1 可以是一个临时表。

  为了使查询成功运行，必须满足以下条件：

• 两个表必须具有相同的结构。

• 两个表必须具有相同的分区键、排序键和主键。

• 两个表必须具有相同的索引和投影。

• 两个表必须具有相同的存储策略。

REPLACE PARTITION

将数据分区从一个表复制到另一个表并替换。

语法

ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1

此查询将数据分区从 table1 复制到 table2，并替换 table2 中现有的分区。该操作是原子的。
注意：

• 数据不会从 table1 中删除。

• table1 可以是一个临时表。

  为了使查询成功运行，必须满足以下条件：

• 两个表必须具有相同的结构。

• 两个表必须具有相同的分区键、排序键和主键。

• 两个表必须具有相同的索引和投影。

• 两个表必须具有相同的存储策略。

MOVE PARTITION TO TABLE

将数据分区从一个表移动到另一个表。

语法

ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest

此查询将数据分区从 table_source 移动到 table_dest，并从 table_source 中删除数据。
为了使查询成功运行，必须满足以下条件：

• 两个表必须具有相同的结构。
• 两个表必须具有相同的分区键、排序键和主键。
• 两个表必须具有相同的索引和投影。
• 两个表必须具有相同的存储策略。
• 两个表必须属于相同的引擎家族（复制或非复制）。

CLEAR COLUMN IN PARTITION

重置分区中指定列的值。

语法

ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr

重置分区中指定列的所有值。如果在创建表时确定了 DEFAULT 子句，此查询会将列值设置为指定的默认值。

示例

ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902

CLEAR INDEX IN PARTITION

重置分区中指定的二级索引。

语法

ALTER TABLE table_name CLEAR INDEX index_name IN PARTITION partition_expr

此查询与 CLEAR COLUMN 类似，但它重置的是索引，而不是列数据。

FREEZE PARTITION

创建分区的备份。

语法

ALTER TABLE table_name FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name']

此查询创建指定分区的本地备份。如果省略 PARTITION 子句，查询会一次性创建所有分区的备份。

UNFREEZE PARTITION

移除分区的备份。

语法

ALTER TABLE table_name UNFREEZE [PARTITION 'part_expr'] WITH NAME 'backup_name'

此查询创建指定分区的本地备份。如果省略 PARTITION 子句，查询将一次性备份所有分区。
移除磁盘上具有指定名称的冻结分区。如果省略 PARTITION 子句，查询将一次性移除所有分区的备份。

FETCH PARTITION|PART

从另一台服务器下载分区或数据部分。

语法

ALTER TABLE table_name FETCH PARTITION|PART partition_expr FROM 'path-in-zookeeper'

从另一台服务器下载分区。此查询仅适用于复制表。

示例1：FETCH PARTITION

ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits';
ALTER TABLE users ATTACH PARTITION 201902;

示例2：FETCH PART

ALTER TABLE users FETCH PART 201901_2_2_0 FROM '/clickhouse/tables/01-01/visits';
ALTER TABLE users ATTACH PART 201901_2_2_0;

MOVE PARTITION|PART

将分区或数据部分移动到另一个磁盘或卷。

语法

ALTER TABLE table_name MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'

示例

ALTER TABLE hits MOVE PART '20190301_14343_16206_438' TO VOLUME 'slow'
ALTER TABLE hits MOVE PARTITION '2019-09-01' TO DISK 'fast_ssd'

UPDATE IN PARTITION

按条件更新分区内的数据。

语法

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] [IN PARTITION partition_expr] WHERE filter_expr

示例

-- using partition name
ALTER TABLE mt UPDATE x = x + 1 IN PARTITION 2 WHERE p = 2;

-- using partition id
ALTER TABLE mt UPDATE x = x + 1 IN PARTITION ID '2' WHERE p = 2;

DELETE IN PARTITION

按条件删除分区内的数据。

语法

ALTER TABLE [db.]table DELETE [IN PARTITION partition_expr] WHERE filter_expr

示例

-- using partition name
ALTER TABLE mt DELETE IN PARTITION 2 WHERE p = 2;

-- using partition id
ALTER TABLE mt DELETE IN PARTITION ID '2' WHERE p = 2;

更新操作（UPDATE）

可以使用此基本查询格式更新数据。不支持唯一表。请参阅“更新语句”。

语法

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr;

说明
ALTER TABLE 的前缀使这个语法与其他大多数支持SQL的系统不同。它的目的是表明，与OLTP数据库中的类似查询不同，这是一个繁重的操作，不是为频繁使用而设计。

**使用限制：**

1. 不支持 Unique 表，详情参见 "UPDATE ... SET"。
2. filter_expr必须是UInt8类型。这个查询将指定列的值更新为行中相应表达式的值，对于这些行，filter_expr取值为非零。使用CAST操作符将数值映射到列的类型上。不支持更新用于计算主键或分区键的列。
3. 一个查询可以包含几个由逗号分隔的命令。
4. 此语句的查询处理默认是异步操作。

示例

alter table table_alter update bool=1 where uint8=21;

删除操作（DELETE）

从表中删除数据。不支持唯一表。请参阅“删除语句”。

语法

ALTER TABLE [tableIdentifier] DELETE WHERE filter_expr;

说明
ALTER TABLE前缀使得这个语法不同于大多数其他支持SQL的系统。它的目的是表示，与OLTP数据库中的类似查询不同，这是一个不为经常使用而设计的繁重操作。

**使用限制：**

1. 不支持 Unique 表，详情参见 "Delete From"。
2. filter_expr 的类型必须是UInt8。该查询删除表中该表达式接受非零值的行。
3. 一个查询可以包含多个用逗号分隔的命令。
4. 查询处理默认是异步的。

示例

alter table test_common delete where id=101

约束操作（CONSTRAINT）

添加约束（ADD CONSTRAINT）

添加一个表的约束

语法

ALTER TABLE [tableIdentifier] ADD CONSTRAINT [IF NOT EXISTS] constraint_name CHECK columnExpr;

• 指定了IF EXISTS子句，如果约束不存在，查询将不会返回错误。

示例

-- Step 1: create a table
CREATE TABLE `example_table`
(
    `order_by_column` String
)
ENGINE = `CnchMergeTree`
ORDER BY (`order_by_column`)

-- Step 2: add constraint check_str
ALTER TABLE example_table ADD CONSTRAINT IF NOT EXISTS check_str CHECK order_by_column != 'forbidden_string'

删除约束（DROP CONSTRAINT）

语法

ALTER TABLE [tableIdentifier] DROP CONSTRAINT [IF EXISTS] constraint_name;

• 指定了IF EXISTS子句，如果约束不存在，查询将不会返回错误。

示例

-- Step 1: create a table with the constraint check_str
CREATE TABLE `example_table`
(
    `order_by_column` String,
    CONSTRAINT check_str CHECK order_by_column != 'forbidden_string'
)
ENGINE = `CnchMergeTree`
ORDER BY (`order_by_column`)

-- Step 2: drop constraint check_str
ALTER TABLE example_table DROP CONSTRAINT IF EXISTS check_str

表设置操作（SETTINGS）

这是一组更改表设置的操作。你可以修改设置或将其重置为默认值。单个查询可以同时更改多个设置。 如果指定名称的设置不存在，则查询会引发异常。

语法

ALTER TABLE [db].name MODIFY|RESET SETTING ...

修改设置

更改表设置

语法

MODIFY SETTING setting_name=value [, ...]

示例

CREATE TABLE example_table (id UInt32, data String) ENGINE=CnchMergeTree() ORDER BY id;
ALTER TABLE example_table MODIFY SETTING max_part_loading_threads=8, max_parts_in_total=50000;

重置设置

重置表设置为默认值。如果设置处于默认状态，则不采取任何操作。

语法

RESET SETTING setting_name [, ...]

示例

CREATE TABLE example_table (id UInt32, data String) ENGINE=CnchMergeTree() ORDER BY id
    SETTINGS max_part_loading_threads=8;
ALTER TABLE example_table RESET SETTING max_part_loading_threads;

索引操作（INDEX）

可以使用以下操作：

--向表元数据添加索引描述
ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value [FIRST|AFTER name] 

--从表元数据中删除索引描述并从磁盘中删除索引文件
ALTER TABLE [db].name DROP INDEX name

--查询在分区partition_name中重建二级索引name
ALTER TABLE [db.]table MATERIALIZE INDEX name IN PARTITION partition_name 

前两个命令是轻量级的，它们只更改元数据或删除文件。

TTL操作（TTL）

修改TTL（MODIFY TTL）

更改表 TTL。TTL 子句中使用的列必须位于按列分区中。

语法

ALTER TABLE [tableIdentifier] MODIFY [ttlClause];

• 指定了IF EXISTS子句，如果列不存在，查询将不会返回错误。

示例

-- Step 1: create a table with table TTL to be 1 day
CREATE TABLE `example_table`
(
    `d` DateTime
)
ENGINE = `CnchMergeTree`
PARTITION BY d
ORDER BY `d`
TTL d + INTERVAL 1 DAY

-- Step 2: modify table ttl & change the TTL to 2 month
ALTER TABLE example_table
    MODIFY TTL d + INTERVAL 2 MONTH

移除TTL（REMOVE TTL）

移除表的TTL

语法

ALTER TABLE [tableIdentifier] REMOVE TTL;

示例

-- Step 1: create a table with table TTL to be 1 day
CREATE TABLE `example_table`
(
    `d` DateTime
)
ENGINE = `CnchMergeTree`
PARTITION BY d
ORDER BY d
TTL d + INTERVAL 1 DAY

-- Step 2: remove table ttl
ALTER TABLE example_table
    REMOVE TTL

CLUSTER操作（MODIFY CLUSTER）

添加或更改集群属性。

语法

ALTER TABLE [tableIdentifier] MODIFY CLUSTER BY (column, expression, ...) INTO 64 BUCKETS