Basic syntax

Elena Dvoryadkina

ADQM supports an SQL language so you can run different queries to operate data tables. This article describes syntax and provides examples of queries to create/modify/delete tables, view table information, insert and read data.

Create a MergeTree table

Syntax overview

To create a new table, use the CREATE TABLE query with a table engine specified via the ENGINE clause. See ClickHouse documentation for more details on the CREATE TABLE query.

Below is the basic syntax of the CREATE TABLE query for creating a table based on the MergeTree engine — the main table engine for working with big data in ADQM.

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
(   <column_name1> [<data_type1>] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS <expr1>] [CODEC(<compression_codec1>)] [TTL <expr1>],
    <column_name2> [<data_type2>] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS <expr2>] [CODEC(<compression_codec2>)] [TTL <expr2>],
    ...
    CONSTRAINT <constraint_name_1> CHECK <boolean_expr_1>,
    CONSTRAINT <constraint_name_2> CHECK <boolean_expr_2>,
    ...
    INDEX <index_name1> <expr1> TYPE <index_type1> GRANULARITY <granularity_value1>,
    INDEX <index_name2> <expr2> TYPE <index_type2> GRANULARITY <granularity_value2>)
ENGINE = MergeTree()
ORDER BY <expr>
[PARTITION BY <expr>]
[PRIMARY KEY <expr>]
[SAMPLE BY <expr>]
[TTL <expr>
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE <conditions>]
    [GROUP BY <key_expr> [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...];
Query parameters

 
In a CREATE TABLE query, you can specify a table structure explicitly. To do this, pass column descriptions, constraints, and secondary indexes as query parameters.

Column descriptions

MergeTree table columns are described as follows:

<column_name> [<data_type>] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS <expr>] [CODEC(<compression_codec>)] [TTL <expr>]

A column description includes:

Constraints

Constraints for column values are described as follows:

CONSTRAINT <constraint_name> CHECK <boolean_expr>

A constraint description inсludes:

  • constraint name;

  • boolean expression that data values to be inserted into the column should satisfy.

Data skipping indexes

For tables of the MergeTree family, you can set data skipping indexes that aggregate data into blocks according to the specified expressions. These aggregates allow skipping blocks that do not contain requested data when executing a SELECT query and reducing the amount of data to read from the disk.

Data skipping indexes are described in the following format:

INDEX <index_name> <expr> TYPE <index_type> GRANULARITY <granularity_value>

An index description contains:

  • index name;

  • data aggregation expression;

  • index type. Most commonly used indexes are:

    • minmax — stores the minimum and maximum values of the specified expression;

    • set(max_rows) — stores unique values (no more than max_rows) of the specified expression;

    • bloom_filter([false_positive]) — stores a Bloom filter for the specified columns. The optional false_positive parameter is the probability of receiving a false positive response from the filter. Possible values are (0, 1). A default value is 0.025.

  • granularity — the number of data granules in a block to be skipped.

You can also add and remove columns, constraints, and indexes after the table has been created. To do this, use ALTER TABLE queries (see the Modify a table section below).

Basic clauses

ON CLUSTER

By default, a table is created only on the server on which the query is run. To create a table on all servers of the cluster, use the ON CLUSTER clause with the specified cluster name

ENGINE

Table engine with parameters. Names of table engines are case-sensitive. You can obtain them from the system.table_engines system table

ORDER BY

Sorting key defined as one or more columns. ADQM uses the sorting key as a primary key if the primary key is not specified explicitly via the PRIMARY KEY clause

PARTITION BY

Partitioning key. A partitioning key can be a table column or any expression from table columns. For example, to specify partitioning by months, use the toYYYYMM(date_column) expression, where date_column is a column of the Date type. The partition key can also be a tuple of columns or expressions

PRIMARY KEY

Primary key. By default, a primary key is the same as a sorting key (ORDER BY), and it is not necessary to specify the PRIMARY KEY clause separately in most cases. If the primary key differs from the sorting key, then the expression tuple of the primary key must be a prefix of the sorting key expression tuple

SAMPLE BY

Expression for sampling

TTL

List of rules that define the storage time of data rows in the table and logic of data movement between disks and volumes

SETTINGS

Additional parameters that manage the behavior of a MergeTree table. You can use the system.merge_tree_settings system table to access the list of settings for tables of the MergeTree family and their values

In the CREATE TABLE query (as well as in other queries listed in this article), <table_name> is the name of a table and <db_name> is the name of a database that contains the table. If the database is not specified explicitly, the query applies to the table in the current database of the session. To set the current database, use the following query:

USE <db_name>;

Examples

Create a MergeTree table

 
The following query creates a MergeTree table with three columns that store numeric IDs, string values, and dates. It sets a tuple of the column with identifiers and the column with dates as the sorting key (the primary key will match the sorting key), and specifies the key for partitioning data by days.

CREATE TABLE table_mergeTree
(
    id Int32,
    value String,
    date Date
)
ENGINE = MergeTree
ORDER BY (id, date)
PARTITION BY toYYYYMMDD(date);

The MergeTree engine has no parameters, so you can specify it as ENGINE = MergeTree() or ENGINE = MergeTree.

Create a ReplacingMergeTree table

 
A ReplacingMergeTree table removes duplicate rows with the same sorting key value (ORDER BY, not PRIMARY KEY) when merging data parts.

The ReplacingMergeTree engine has one optional parameter (ver) — a column (of the UInt*, Date, or DateTime type) that stores versions of data records.

When merging data parts, ReplacingMergeTree leaves only one row from all the rows with the same sorting key:

  • The most recently inserted row, if the ver parameter is not specified.

    CREATE TABLE table_replacing_1
(
    id Int32,
    value String,
    date Date,
    ver Int32
)
ENGINE = ReplacingMergeTree()
ORDER BY id;

INSERT INTO table_replacing_1 VALUES(1, 'value1', '2022-01-01', 1), (1, 'value2', '2022-01-01', 2), (1, 'value3', '2022-01-01', -1);

SELECT * FROM table_replacing_1 FINAL;

    After deduplication, the last inserted row remains in the table:

    --id---value----------date---ver--
│  1 │ value3 │ 2022-01-01 │  -1 │
----------------------------------

  • A row with the maximum version, if the ver parameter is specified.

    CREATE TABLE table_replacing_2
(
    id Int32,
    value String,
    date Date,
    ver Int32
)
ENGINE = ReplacingMergeTree(ver)
ORDER BY id;

INSERT INTO table_replacing_2 VALUES(1, 'value1', '2022-01-01', 1), (1, 'value2', '2022-01-01', 2), (1, 'value3', '2022-01-01', -1);

SELECT * FROM table_replacing_2 FINAL;

    After deduplication, a row with the greatest value in the ver column remains in the table:

    --id---value----------date---ver--
│  1 │ value2 │ 2022-01-01 │   2 │
----------------------------------

Deduplication is performed at the partition level. If a table is partitioned, duplicates between partitions are not removed.

ReplacingMergeTree deduplicates data during the data merge which is performed in the background. So, you can get some data unprocessed if you run the SELECT query between merges. To initiate an unscheduled merge and get a table or partition without duplicates, you can use the FINAL modifier after the table name in the SELECT query, or you can pre-execute the OPTIMIZE query for the table or partition.

Create a SummingMergeTree table

 
The SummingMergeTree engine finds rows with the same sorting keys and replaces these rows with a single row that contains summed values in numeric columns. Values in columns of other types are chosen arbitrarily from any row.

The SummingMergeTree engine has one optional parameter (columns) — names of numeric columns whose values will be summed (these columns should not be in the sorting key).

  • If the columns parameter is not specified, ADQM sums data values in all columns of numeric types that are not parts of the sorting key.

    CREATE TABLE table_sum_1
(
    id UInt32,
    value1 Int32,
    value2 Int32,
    value3 Int32
)
ENGINE = SummingMergeTree()
ORDER BY id;

INSERT INTO table_sum_1 VALUES(1, 1, 1, 1), (1, 2, 2, 2), (1, 3, 3, 3);

SELECT * FROM table_sum_1;

    As a result, values in all numeric columns were summed up:

    --id---value1---value2---value3--
│  1 │      6 │      6 │      6 │
---------------------------------

  • If the columns parameter is specified, ADQM sums values in listed columns.

    CREATE TABLE table_sum_2
(
    id UInt32,
    value1 Int32,
    value2 Int32,
    value3 Int32
)
ENGINE = SummingMergeTree(value1, value2)
ORDER BY id;

INSERT INTO table_sum_2 VALUES(1, 1, 1, 1), (1, 2, 2, 2), (1, 3, 3, 3);

SELECT * FROM table_sum_2;

    As a result, values in the value1 and value2 columns were summed up, and a value of the value3 column was selected from an arbitrary row:

    --id---value1---value2---value3--
│  1 │      6 │      6 │      1 │
---------------------------------
Create an AggregatingMergeTree table

 
In AggregatingMergeTree tables, all rows with the same sorting key are replaced by a single row (within one data part) that stores a combination of aggregate function states. These tables can be used for incremental data aggregation, including for creation of aggregated materialized views.

The AggregatingMergeTree engine has no parameters. Below is an example of creating an aggregated materialized view.

  1. Create the table_sales table of the MergeTree type to store raw sales data:

    CREATE TABLE table_sales
(
    user_id Int64,
    store_id Int32,
    product_id Int32,
    date Date,
    sum Decimal(20, 2)
)
ENGINE = MergeTree
PARTITION BY date
ORDER BY (store_id, product_id, user_id);

  2. Create an AggregatingMergeTree materialized view that "follows" the table_sales table and stores intermediate states of aggregations:

    CREATE MATERIALIZED VIEW table_aggregating
(
    store_id Int32,
    product_id Int32,
    date Date,
    s AggregateFunction(sum, Decimal(20, 2)),
    c AggregateFunction(count, Decimal(20, 2))
)
ENGINE = AggregatingMergeTree
PARTITION BY date
ORDER BY (store_id, date) AS
SELECT
    store_id,
    product_id,
    date,
    sumState(sum) AS s,
    countState(sum) AS c
FROM table_sales
GROUP BY
    store_id,
    date,
    product_id;

    When inserting data into table_sales, the table_aggregating materialized view will automatically update aggregate states for all changed keys listed in GROUP BY.

  3. To get aggregated data, execute the SELECT …​ GROUP BY …​ query for the table_aggregating materialized view:

    SELECT
    store_id,
    product_id,
    date,
    sumMerge(s),
    countMerge(c)
FROM table_aggregating
GROUP BY
    store_id,
    date,
    product_id
LIMIT 1;
Create a CollapsingMergeTree table

 
The CollapsingMergeTree table engine allows quick writing constantly changing object states and removes old object states in the background. This can significantly reduce the amount of storage and increase the efficiency of SELECT queries.

CollapsingMergeTree asynchronously removes (collapses) pairs of rows that contain the same values in all columns of the sorting key (ORDER BY), but different values in the special sign column of the Int8 type. This column values specify a row type:

  • 1 — means that a row is a "state" row;

  • -1 — means that a row is a "cancel" row.

The sign column is passed to the CollapsingMergeTree engine as a parameter. Rows that do not have pairs remain in the table. For more information on the collapsing algorithm, see the CollapsingMergeTree article in the ClickHouse documentation.

An example below shows how the CollapsingMergeTree table engine processes data.

  1. Create a CollapsingMergeTree table and specify a column whose values indicate types of rows:

    CREATE TABLE table_collapsing
(
    id UInt32,
    pageViews UInt8,
    duration UInt8,
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY id;

  2. Use two INSERT queries to create two different data parts. In the second query, the first value to be inserted cancels the previous state of the object, and the second value defines the current state.

    INSERT INTO table_collapsing VALUES (1, 7, 100, 1);
INSERT INTO table_collapsing VALUES (1, 7, 100, -1), (1, 8, 150, 1);

  3. Use the SELECT query to make sure that rows corresponding to the obsolete state of the object have been removed from the table, and only the latest state of the object remains. Since ADQM merges inserted data parts at an unknown moment, aggregation is required to get completely "collapsed" data from the CollapsingMergeTree table. If you need to get data without aggregation (for example, to check the presence of rows whose last values satisfy some conditions), you can use the FINAL modifier for the FROM clause.

    SELECT * FROM table_collapsing FINAL;

    The output is:

    --id---pageViews---duration---sign--
│  1 │         8 │      150 │    1 │
------------------------------------
Create a VersionedCollapsingMergeTree table

 
The VersionedCollapsingMergeTree engine has the same purpose as CollapsingMergeTree, but uses a different algorithm for collapsing (deleting) rows. It allows inserting data in any order with multiple threads while CollapsingMergeTree requires strictly sequential data insertion.

The VersionedCollapsingMergeTree engine’s parameters are:

  • sign — name of a column (of the Int8 type) whose values indicate row types (1 means that a row is a "state" row; -1 indicates a "cancel" row that copies all fields of the corresponding "state" row except sign);

  • version — name of a column (of the UInt type) that contains versions of object states (each state of an object is identified with a separate number).

An example below shows how the VersionedCollapsingMergeTree table engine processes data.

  1. Create a VersionedCollapsingMergeTree table and specify columns whose values indicate a row type ("state" or "cancel") and object state version:

    CREATE TABLE table_versioned_collapsing
(
    id UInt32,
    pageViews UInt8,
    duration UInt8,
    sign Int8,
    version UInt8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY id;

  2. Use two INSERT queries to create two different data parts. In the second query, the first value to be inserted cancels the previous state of the object, and the second value defines the current state.

    INSERT INTO table_versioned_collapsing VALUES (1, 7, 100, 1, 1);
INSERT INTO table_versioned_collapsing VALUES (1, 7, 100, -1, 1), (1, 8, 150, 1, 2);

  3. Use the SELECT query to make sure that rows corresponding to the obsolete state of the object have been removed from the table, and only the latest state of the object remains. Since ADQM merges inserted data parts at an unknown moment, aggregation is required to get completely "collapsed" data from the CollapsingMergeTree table. If you need to get data without aggregation (for example, to check the presence of rows whose last values satisfy some conditions), you can use the FINAL modifier for the FROM clause.

    SELECT * FROM table_versioned_collapsing FINAL;

    The output is:

    --id---pageViews---duration---sign---version--
│  1 │         8 │      150 │    1 │       2 │
----------------------------------------------
Create replicated tables

 
To create replicated tables, use engines of the MergeTree family with the Replicated prefix.

Meta information related to the replication process is stored in ZooKeeper, so the Replicated* engines require the following parameters to build a unique path to each replicated table in ZooKeeper:

  • path to the table in ZooKeeper;

  • replica name in ZooKeeper (it identifies different replicas of the same table).

Each Replicated* engine also has the same optional parameters as the engine to which the given replicated version corresponds. For example, the ReplicatedSummingMergeTree engine, like the SummingMergeTree engine, has the optional columns parameter — a list of columns whose values should be summed up.

For an example of creating replicated tables with the ReplicatedMergeTree engine, see the Typical cluster article.

View the list of tables

You can view names of all tables that exist in the specified database in the following ways:

  • Use the SHOW TABLES query.

    SHOW TABLES FROM <database_name>;

  • Read values from the name column of the system.tables system table.

    SELECT name FROM system.tables WHERE database='<database_name>';

Get information on the specified table

Table structure

To obtain the description of table columns, use the DESCRIBE TABLE query:

DESCRIBE TABLE [<db_name.]<table_name>;

This query returns a list of table columns with their names and data types, as well as the following information on each column:

  • type of an expression for a default value (DEFAULT, MATERIALIZED, EPHEMERAL, or ALIAS);

  • expression for a default value;

  • comment;

  • compression codec;

  • TTL expression.

Example

 
Query:

CREATE TABLE test_table
(
    id Int32,
    description String DEFAULT 'unknown' COMMENT 'This column contains descriptions' CODEC(LZ4),
    date Date COMMENT 'This column contains event dates'
)
ENGINE = MergeTree
ORDER BY id;

DESCRIBE TABLE test_table;

Result:

--name----------type-----default_type---default_expression---comment-----------------------------codec_expression---ttl_expression--
│ id          │ Int32  │              │                    │                                   │                  │                │
│ description │ String │ DEFAULT      │ 'unknown'          │ This column contains descriptions │ LZ4              │                │
│ date        │ Date   │              │                    │ This column contains event dates  │                  │                │
------------------------------------------------------------------------------------------------------------------------------------

Table metadata

To view table metadata, use the system.tables system table. A query syntax:

SELECT * FROM system.tables WHERE name = '<table_name>' [FORMAT <format_name>];
Example

 
Query:

SELECT * FROM system.tables WHERE name = 'test_table' FORMAT Vertical;

Result:

Row 1:
──────
database:                      default
name:                          test_table
uuid:                          7239d59f-9144-4600-b47f-c9dd02330242
engine:                        MergeTree
is_temporary:                  0
data_paths:                    ['/var/lib/clickhouse/store/723/7239d59f-9144-4600-b47f-c9dd02330242/']
metadata_path:                 /var/lib/clickhouse/store/463/4632cdc5-46bc-4d4e-ad36-27b8d101e2e1/test_table.sql
metadata_modification_time:    2022-12-21 07:04:25
dependencies_database:         []
dependencies_table:            []
create_table_query:            CREATE TABLE default.test_table (`id` Int32, `v` String, `d` Date, `v2` String CODEC(ZSTD(10))) ENGINE = MergeTree PARTITION BY d ORDER BY id SETTINGS index_granularity = 8192 COMMENT 'This is a test table'
engine_full:                   MergeTree PARTITION BY d ORDER BY id SETTINGS index_granularity = 8192
as_select:
partition_key:                 d
sorting_key:                   id
primary_key:                   id
sampling_key:
storage_policy:                default
total_rows:                    1000000
total_bytes:                   56688997
lifetime_rows:                 ᴺᵁᴸᴸ
lifetime_bytes:                ᴺᵁᴸᴸ
comment:                       This is a test table
has_own_data:                  1
loading_dependencies_database: []
loading_dependencies_table:    []
loading_dependent_database:    []
loading_dependent_table:       []

Insert data into a table

To add data to a table, use the INSERT INTO query. After the VALUES clause, list column values (or data tuples, if you add more than one row) separated by commas:

INSERT INTO [<db_name>.]<table_name> [(<column_name1>, <column_name2>, <column_name3>)] VALUES (v11, v12, v13), (v21, v22, v23), ...;
Example

  1. Create a table with three columns:

    CREATE TABLE test_table
(
    a Int32,
    b String,
    c Int32
)
ENGINE = MergeTree
ORDER BY a;

  2. Insert a couple of rows with the specified values for all columns of the table:

    INSERT INTO test_table VALUES (1, 'a', 1), (2, 'b', 2);

    Result:

    --a---b---c--
│ 1 │ a │ 1 │
│ 2 │ b │ 2 │
-------------

  3. Insert two rows with data values specified for the a and b columns only:

    INSERT INTO test_table (a, b) VALUES (3, 'c'), (4, 'd');

    As a result, data values from the query are inserted into the a and b columns, and the c column is filled with zeros since it does not have a default value specified:

    --a---b---c--
│ 3 │ c │ 0 │
│ 4 │ d │ 0 │
-------------

The INSERT INTO query also allows you to copy data from one table to another one. To do this, use the combination of the INSERT INTO and SELECT clauses:

INSERT INTO [<db_name>.]<table_name> [(<column_name1>, <column_name2>, <column_name3>)] SELECT ... ;
Example

 
An example below copies the first three rows from the source_table table into the new_table table with the same structure. The source_table table contains the following data:

--a---b----c--
│ 1 │ a │  1 │
│ 1 │ a │  1 │
│ 2 │ b │  2 │
│ 3 │ c │  0 │
│ 6 │ e │ 12 │
│ 9 │ a │  9 │
--------------

  1. Create the new_table table with three columns:

    CREATE TABLE new_table
(
    d Int32,
    f String,
    g Int32
)
ENGINE = MergeTree
ORDER BY d;

  2. Insert three rows from the source_table table into the new table with the following query:

    INSERT INTO new_table SELECT * FROM source_table LIMIT 3;

    The query execution result:

    --d---f---g--
│ 1 │ a │ 1 │
│ 1 │ a │ 1 │
│ 2 │ b │ 2 │
-------------

Read data from a table

To retrieve data from a table, use the SELECT query. This query supports various modifiers and clauses that allow you to customize data selection. For example, you can filter/sort/group data that the query should return, limit the number of rows in the selection, set the output format, etc. For more information about all clauses available for SELECT queries, see the SELECT Query section of the ClickHouse documentation.

Select all data

 
To select all data from a table, use the asterisk symbol (*):

SELECT * FROM [<db_name.]<table_name>;

For example, the following query reads data from all columns of the test_table table:

SELECT * FROM test_table;

The output:

--a---b----c--
│ 1 │ a │  1 │
│ 1 │ a │  1 │
│ 2 │ b │  2 │
│ 3 │ c │  0 │
│ 6 │ e │ 12 │
│ 9 │ a │  9 │
--------------
Read data from the specified columns

 
To read data from individual columns, list column names after the SELECT clause:

SELECT <column_name1>[, ...] FROM [<db_name.]<table_name>;

For example, the following query reads data from the b and c columns of the test_table table:

SELECT b, c FROM test_table;

The output:

--b----c--
│ a │  1 │
│ a │  1 │
│ b │  2 │
│ c │  0 │
│ e │ 12 │
│ a │  9 │
----------
Select the specified number of rows

 
To read the specified number of data rows from a table, use the LIMIT clause in a SELECT query:

SELECT * FROM [<db_name>.]<table_name> LIMIT [n, ]m;

This query selects the first m rows from the table after skipping the first n rows.

For example, the following query reads the first three rows from the test_table table:

SELECT * FROM test_table LIMIT 3;

The output:

--a---b----c--
│ 1 │ a │  1 │
│ 1 │ a │  1 │
│ 2 │ b │  2 │
--------------
Read data that satisfy the specified condition

 
Use the WHERE clause to set a condition for filtering data to be read:

SELECT <column_name1>[, ...] FROM [<db_name.]<table_name> WHERE <filter_expr>;

For example, the following query returns values of the c column if they are greater than 1 but less than 10:

SELECT c FROM test_table WHERE (c > 1) AND (c < 10);

The output:

---c--
│  2 │
│  9 │
------
Sort the results

 
Use the ORDER BY clause to sort the results of a SELECT query:

SELECT <column_name1>[, ...] FROM [<db_name.]<table_name> ORDER BY <expr_list>;

For example, the following query sorts data by values of the c column in ascending order:

SELECT * FROM test_table ORDER BY c;

The output:

--a---b----c--
│ 3 │ c │  0 │
│ 1 │ a │  1 │
│ 1 │ a │  1 │
│ 2 │ b │  2 │
│ 9 │ a │  9 │
│ 6 │ e │ 12 │
--------------

Modify a table

Table engines of the MergeTree family, as well as Merge and Distributed engines support ALTER TABLE queries that allow changing table structure, settings or data. For example, below is syntax of a query for modifying table columns:

ALTER TABLE [<db_name>].<table_name> [ON CLUSTER <cluster_name>] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...;
Manipulate columns

 
The following clauses in ALTER TABLE queries allow you to perform various actions on table columns.

Clause Description Syntax

ADD COLUMN

Adds a new column to a table.

AFTER | FIRST clauses are used to indicate where to insert a new column — after the specified after_column_name column or at the beginning of the table. If none of these clauses is used, the column is added to the end of the table

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    ADD COLUMN [IF NOT EXISTS] <new_column_name> [<data_type>] [<default_expr>] [CODEC(<codec_name>)] [TTL <expr>] [AFTER <after_column_name> | FIRST];

DROP COLUMN

Deletes the specified column

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    DROP COLUMN [IF EXISTS] <column_name>;

RENAME COLUMN

Renames an existing column.

Columns specified in the table’s key expression (PRIMARY KEY or ORDER BY) cannot be renamed

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    RENAME COLUMN [IF EXISTS] <old_name> to <new_name>;

CLEAR COLUMN

Resets column values for the specified partition

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    CLEAR COLUMN [IF EXISTS] <column_name> IN PARTITION <partition_name>;

COMMENT COLUMN

Adds a comment to a column.

If a comment already exists, it is replaced with a new one, since a column can contain one comment only

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    COMMENT COLUMN [IF EXISTS] <column_name> 'Text comment';

MODIFY COLUMN

Changes column settings (data type, expression for default value, data compression codec, TTL, column position in a table)

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    MODIFY COLUMN [IF EXISTS] <column_name> [<data_type>] [<default_expr>] [CODEC(<codec_name>)] [TTL <expr>] [AFTER <after_column_name> | FIRST];
Manipulate key expressions

 
Tables of the MergeTree family (including replicated tables) support a query to change the sorting key:

ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>] MODIFY ORDER BY <new_expression>;
Manipulate data skipping indexes

 
You can use the following queries to add or remove indexes:

ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>] ADD INDEX <index_name> <expression> TYPE <index_type> GRANULARITY  <granularity_value> [FIRST|AFTER name];
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>] DROP INDEX <index_name>;

Only table engines of the MergeTree family support these operations.

Manipulate constraints

 
Use the following queries to add or delete constraints:

ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>] ADD CONSTRAINT <constraint_name> CHECK <expression>;
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>] DROP CONSTRAINT <constraint_name>;
Manipulate partitions and data parts

 
To work with partitions and data parts, you can use the following clauses in ALTER TABLE queries.

Clause Description Syntax

DETACH PARTITION|PART

Moves a partition/part to the detached directory (the server will not "see" the partition/part until the ATTACH PARTITION|PART query is executed).

The query is replicated — it moves data to the detached directory on all replicas

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    DETACH PARTITION|PART <partition_expr>;

DROP PARTITION|PART

Deletes a partition/part.

The query is replicated — it deletes data on all replicas

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    DROP PARTITION|PART <partition_expr>;

DROP DETACHED PARTITION|PART

Deletes the specified partition/part from the detached directory

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    DROP DETACHED PARTITION|PART <partition_expr>;

ATTACH PARTITION|PART

Adds a partition/part from the detached directory to the table.

This query is replicated

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    ATTACH PARTITION|PART <partition_expr>;

ATTACH PARTITION FROM

Copies a partition from one table to another one.

Both tables should have the same:

  • structures;

  • partitioning keys, sorting keys, and primary keys;

  • data storage policies (a disk where the partition is stored should be available for both tables).

 
ALTER TABLE <dest_table_name> [ON CLUSTER <cluster_name>]
    ATTACH PARTITION <partition_expr> FROM <source_table_name>;

REPLACE PARTITION FROM

Copies a partition from one table to another one and replaces existing partition in the destination table (data is not deleted from the source table).

Both tables should have the same:

  • structures;

  • partitioning keys, sorting keys, and primary keys;

  • data storage policies (a disk where the partition is stored should be available for both tables).

 
ALTER TABLE <dest_table_name> [ON CLUSTER <cluster_name>]
    REPLACE PARTITION <partition_expr> FROM <source_table_name>;

MOVE PARTITION TO TABLE

Moves a partition from one table to another one and deletes data from the source table.

Both tables should have the same:

  • structures;

  • partitioning keys, sorting keys, and primary keys;

  • data storage policies (a disk where the partition is stored should be available for both tables);

  • engine families (replicated or non-replicated).

 
ALTER TABLE <source_table_name> [ON CLUSTER <cluster_name>]
    MOVE PARTITION <partition_expr> TO TABLE <dest_table_name>;

CLEAR COLUMN IN PARTITION

Removes all values in a column for the specified partition.

If a default value is defined for a column, that default value will be set

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    CLEAR COLUMN <column_name> IN PARTITION <partition_expr>;

CLEAR INDEX IN PARTITION

Resets a secondary index in the specified partition

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    CLEAR INDEX <index_name> IN PARTITION <partition_expr>;

FREEZE PARTITION

Creates a backup of the specified partition.

If you do not use the PARTITION clause in the query, all partitions will be backed up.

The query is not replicated — the backup is created on a local server only.

The query copies only table data, not metadata. To back up table metadata, copy the /var/lib/clickhouse/metadata/database/table.sql file

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    FREEZE [PARTITION <partition_expr>] [WITH NAME 'backup_name'];

UNFREEZE PARTITION

Removes a backup of the specified partition from the disk.

If you omit the PARTITION clause, the query removes the backup of all partitions at once

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    UNFREEZE [PARTITION <part_expr>] WITH NAME 'backup_name';

FETCH PARTITION|PART

Downloads a partition/part from another server.

This query works for replicated tables only

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    FETCH PARTITION|PART <partition_expr> FROM 'path-in-zookeeper';

MOVE PARTITION|PART

Moves partition/part to another disk or volume.

The query is not replicated as different replicas can have different storage policy configurations

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    MOVE PARTITION|PART <partition_expr> TO DISK|VOLUME 'disk_name';

UPDATE IN PARTITION

Updates partition data that matches the specified condition

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    UPDATE <column_name1> = <expr1> [, ...] [IN PARTITION <partition_expr>] WHERE <filter_expr>;

DELETE IN PARTITION

Deletes partition data that matches the specified condition

 
ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    DELETE [IN PARTITION <partition_expr>] WHERE <filter_expr>;

Specify partition in a query

To specify a partition in an ALTER TABLE …​ PARTITION query, you can use:

  • Partition name in single quotes (quotes can be omitted if the partitioning expression is of the Int* type). For example, ALTER TABLE test_table DROP PARTITION '2022-12-26';.

  • A tuple of constants or expressions that have the same types as constants or expressions in a tuple of the table’s partitioning key. If the partitioning key consists of a single element, the expression should be wrapped in the tuple(…​) function. For example, ALTER TABLE test_table DROP PARTITION tuple(toYYYYMMDD(toDate('2022-12-26')));.

  • String identifier of the partition specified for the PARTITION ID expression in single quotes. For example, ALTER TABLE test_table DROP PARTITION ID '20221226';.

You can view names and identifiers of partitions in the partition and partition_id columns of the system.parts system table:

SELECT partition, partition_id FROM system.parts WHERE table = '<table_name>' and active = 1;

To set a name of a data part in the ATTACH PART and DROP DETACHED PART queries, use the value from the name column of the system.detached_parts system table in single quotes.

The Row update & deletion article describes how to update and delete data existing in a table.

Detach and attach a table

You can detach a table with the DETACH TABLE query:

DETACH TABLE [IF EXISTS] [<db_name.]<table_name> [ON CLUSTER <cluster_name>] [PERMANENTLY];

When this query is executed, the table becomes "invisible" to the server, but its data and metadata are not deleted. If the table was not detached permanently (the PERMANENTLY clause was not used in the query), the next time the server starts, it will read the table metadata and "see" the table again. If a table has been detached permanently, the server will not automatically re-enable it.

You can re-attach a table with the ATTACH query regardless of whether the table was detached permanently or not:

ATTACH TABLE [IF NOT EXISTS] [<db_name.]<table_name> [ON CLUSTER <cluster_name>];

Delete a table

To delete a table, use the DROP TABLE query:

DROP TABLE [IF EXISTS] [<db_name.]<table_name> [ON CLUSTER <cluster_name>];
Found a mistake? Seleсt text and press Ctrl+Enter to report it