ADD COLUMN #

Add a new column with the specified name, type, encoding, and default expression to the table.
Syntax

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

• If the IF NOT EXISTS clause is included, the query will not return an error if the column already exists.
• If AFTER name_after (the name of another column) is specified, the column will be added after the specified column. To add the column to the beginning of the table, use the FIRST clause. Otherwise, the column will be added to the end of the table. For a series of actions, name_after can be the name of a column added in a previous action.

Example

-- 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 #

Syntax

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

• Order by column: You can modify its default_expr, comment, and codec.
• Partition by column: You can modify its comment.
• Normal column: You can modify its type, default_expr, comment, and codec.

Example

-- 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 #

Remove an attribute from the table: DEFAULT, ALIAS, MATERIALIZED, CODEC, COMMENT, TTL, SETTINGS.
Syntax

ALTER TABLE table_name MODIFY COLUMN column_name REMOVE property;

ExampleRemove TTL

ALTER TABLE table_with_ttl MODIFY COLUMN column_ttl REMOVE TTL;

DROP COLUMN #

Delete a column from the table. Partition by columns and order by columns cannot be deleted.
Syntax

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

• If the IF EXISTS clause is specified, the query will not return an error if the column does not exist.

Example

-- 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 #

Rename a column in the table. There are some important notes when using:

• You cannot rename a column to an existing column.
• You cannot rename a column to itself.
• The original column to be renamed must exist.

Syntax

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

• If the IF EXISTS clause is specified, the query will not return an error if the column does not exist.

Example

-- 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

COMMENT COLUMN #

Add a comment to a column
Syntax

COMMENT COLUMN [IF EXISTS] name 'Text comment'

Example

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

DETACH PARTITION|PART #

Move the partition or data part to the detached directory and forget it.
Syntax

ALTER TABLE table_name  DETACH PARTITION|PART partition_expr

Move all data of the specified partition to the detached directory. The server will treat the detached data partition as nonexistent. Before executing the ATTACH query, the server will not recognize the existence of this data.
Example

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

After the query is executed, you can perform any operation on the data in the detached directory—delete it from the file system, or simply keep it.

DROP PARTITION|PART #

Delete the partition or data part.

• Syntax

  ALTER TABLE table_name  DROP PARTITION|PART partition_expr
  

  Delete the specified partition from the table. This query will mark the partition as inactive and completely delete the data in about 10 minutes.

• Example 1: Delete Partition (Supports all tables. Directly deletes the directory at the underlying layer for good performance. Recommended for priority use.)

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

• Example 2: Delete Partition according to condition

  ALTER TABLE data_test_ckj.t00002 DROP PARTITION where `toDate(toStartOfDay(date1))` >'2024-10-06'; 
  

  toDate(toStartOfDay(date1)) is the syntax parameter after PARTITION BY, for example:

  CREATE TABLE test.partitionByClause
  (
  VisitDate Date,
  Hour UInt8,
  ClientID UUID
  )
  ENGINE = CnchMergeTree
  PARTITION BY toDate(toStartOfDay(date1))
  ORDER BY Hour;
  

  For more details, refer to the 设计分区键（PARTITION BY) section in the Create Table Statement Document.

DROP DETACHED PARTITION|PART #

Remove the specified Part or all PART of the specified Partition from the detached directory.
Syntax

ALTER TABLE table_name  DROP DETACHED PARTITION|PART partition_expr

ATTACH PARTITION|PART #

Add to Partition or Part from the detached directory to the table.
Syntax

ALTER TABLE table_name  ATTACH PARTITION|PART partition_expr

Add to Data from the detached directory to the table. Data can be Add to for the entire Partition or for an individual Part.
Example

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

ATTACH PARTITION FROM #

Copy Data partition from one table to another table and Add to.
Syntax

ALTER TABLE table2 ATTACH PARTITION partition_expr FROM table1

This Search copies the Data partition from table1 to table2.
Caution:

• Data will not be Delete from table1 or table2.
• table1 can be a temporary table.

To make the Search succeed, the following conditions must be satisfied:

• The two tables must have the same structure.
• The two tables must have the same Partition key, sort key, and primary key.
• The two tables must have the same index and projection.
• The two tables must have the same storage Strategy.

REPLACE PARTITION #

Copy Data partition from one table to another table and replace.
Syntax

ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1

This Search copies the Data partition from table1 to table2, and replaces the existing Partition in table2. This Actions is atomic.
Caution:

• Data will not be Delete from table1.
• table1 can be a temporary table.

To make the Search succeed, the following conditions must be satisfied:

• The two tables must have the same structure.
• The two tables must have the same Partition key, sort key, and primary key.
• The two tables must have the same index and projection.
• The two tables must have the same storage Strategy.

CLEAR INDEX IN PARTITION #

Reset the specified secondary index in the Partition.
Syntax

ALTER TABLE table_name CLEAR INDEX index_name IN PARTITION partition_expr

This Search is similar to CLEAR COLUMN, but it resets the index, not the Column Data.

FETCH PARTITION|PART #

Download Partition or Part from another server.
Syntax

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

Download Partition from another server. This Search only applies to replicated tables.
Example1: FETCH PARTITION

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

Example2: 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 #

Move a partition or data part to another disk or volume.
Syntax

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

Example

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 #

Update data in a partition by condition.
Syntax

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

Example

-- 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 #

Delete data in a partition by condition.
Syntax

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

Example

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

Non-unique key tables can use this query format to asynchronously update data. Unique key tables are not supported; for unique key tables, please use Update statement (UPDATE).
Syntax

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

Description
The ALTER TABLE prefix makes this syntax different from most other SQL-supporting systems. Its purpose is to indicate that, unlike similar queries in OLTP databases, this is a heavy operation and is not designed for frequent use.

Limitations:

1. Unique key tables are not supported; for unique key tables, please use Update statement (UPDATE).
2. filter_expr must be of UInt8 type. This query updates the value of the specified column to the value of the corresponding expression in the row, for those rows where filter_expr is non-zero. Use the CAST operator to map numerical values to the column's type. Updating columns used to calculate the primary key or partition key is not supported.
3. A query can contain several commands separated by commas.
4. The processing of this statement's query is asynchronous by default.

Example

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

Delete data from the table. Unique key tables are not supported. See "Delete statement".
Syntax

ALTER TABLE [tableIdentifier] DELETE WHERE filter_expr;

Description
The ALTER TABLE prefix makes this syntax different from most other SQL-supporting systems. Its purpose is to indicate that, unlike similar queries in OLTP databases, this is a heavy operation and is not designed for frequent use.

Limitations:

1. Unique key tables are not supported. For details, see "Delete From".
2. The type of filter_expr must be UInt8. This query deletes rows in the table where the expression evaluates to a non-zero value.
3. A query can contain multiple commands separated by commas.
4. Query processing is asynchronous by default.

Example

alter table test_common delete where id=101

ADD CONSTRAINT #

Add a constraint to a table
Syntax

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

• The IF EXISTS clause is specified. If the constraint does not exist, the query will not return an error.

Example

-- 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 #

Syntax

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

• The IF EXISTS clause is specified. If the constraint does not exist, the query will not return an error.

Example

-- 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

This is a set of actions to change table settings. You can change settings or reset them to the default values. A single query can change multiple settings at the same time. If the setting with the specified name does not exist, then the query raises an exception.
Syntax

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

Change settings #

Change table settings
Syntax

MODIFY SETTING setting_name=value [, ...]

Example

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 settings #

Reset table settings to default values. If the settings are in the default state, no action is taken.
Syntax

RESET SETTING setting_name [, ...]

Example

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;

The following actions can be used:

-- Add index description to table metadata
ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value [FIRST|AFTER name] 

-- Delete index description from table metadata and delete index file from disk
ALTER TABLE [db].name DROP INDEX name

-- Query to rebuild secondary index name in partition partition_name
ALTER TABLE [db.]table MATERIALIZE INDEX name IN PARTITION partition_name 

The first two commands are lightweight; they only change metadata or delete files.

MODIFY TTL #

Change table TTL. The columns used in the TTL clause must be in the partitioned columns.
Syntax

ALTER TABLE [tableIdentifier] MODIFY [ttlClause];

• The IF EXISTS clause is specified. If the column does not exist, the query does not return an error.

Example

-- 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

REMOVE TTL #

Remove the table's TTL
Syntax

ALTER TABLE [tableIdentifier] REMOVE TTL;

Example

-- 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

Add or change cluster properties.
Syntax

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