Basic syntax
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, ...];
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:
A column description includes:
|
Constraints |
Constraints for column values are described as follows:
A constraint description inсludes:
|
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 Data skipping indexes are described in the following format:
An index description contains:
|
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).
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 |
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 |
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 |
PRIMARY KEY |
Primary key. By default, a primary key is the same as a sorting key ( |
SAMPLE BY |
Expression for sampling |
TTL |
List of TTL (time-to-live) rules that define how long rows should be stored in a table, as well as how data should be processed after the specified time has passed (for example, old data can be automatically deleted, moved to other disks/volumes, recompressed with another codec, or aggregated/grouped — for more information, see Use TTL to manage data) |
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
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
.
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.
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
andvalue2
columns were summed up, and a value of thevalue3
column was selected from an arbitrary row:--id---value1---value2---value3-- │ 1 │ 6 │ 6 │ 1 │ ---------------------------------
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.
-
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);
-
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
, thetable_aggregating
materialized view will automatically update aggregate states for all changed keys listed inGROUP BY
. -
To get aggregated data, execute the
SELECT … GROUP BY …
query for thetable_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;
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.
-
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;
-
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);
-
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 theFINAL
modifier for theFROM
clause.SELECT * FROM table_collapsing FINAL;
The output is:
--id---pageViews---duration---sign-- │ 1 │ 8 │ 150 │ 1 │ ------------------------------------
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 exceptsign
); -
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.
-
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;
-
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);
-
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 theFINAL
modifier for theFROM
clause.SELECT * FROM table_versioned_collapsing FINAL;
The output is:
--id---pageViews---duration---sign---version-- │ 1 │ 8 │ 150 │ 1 │ 2 │ ----------------------------------------------
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
, orALIAS
); -
expression for a default value;
-
comment;
-
compression codec;
-
TTL expression.
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>];
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), ...;
-
Create a table with three columns:
CREATE TABLE test_table ( a Int32, b String, c Int32 ) ENGINE = MergeTree ORDER BY a;
-
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 │ -------------
-
Insert two rows with data values specified for the
a
andb
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
andb
columns, and thec
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 ... ;
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 │ --------------
-
Create the
new_table
table with three columns:CREATE TABLE new_table ( d Int32, f String, g Int32 ) ENGINE = MergeTree ORDER BY d;
-
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.
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 │ --------------
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 │ ----------
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 │ --------------
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 │ ------
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 ...;
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.
|
|
DROP COLUMN |
Deletes the specified column |
|
RENAME COLUMN |
Renames an existing column. Columns specified in the table’s key expression ( |
|
CLEAR COLUMN |
Resets column values for the specified partition |
|
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 |
|
MODIFY COLUMN |
Changes column settings (data type, expression for default value, data compression codec, TTL, column position in a table) |
|
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>;
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.
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>;
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 The query is replicated — it moves data to the detached directory on all replicas |
|
DROP PARTITION|PART |
Deletes a partition/part. The query is replicated — it deletes data on all replicas |
|
DROP DETACHED PARTITION|PART |
Deletes the specified partition/part from the detached directory |
|
ATTACH PARTITION|PART |
Adds a partition/part from the detached directory to the table. This query is replicated |
|
ATTACH PARTITION FROM |
Copies a partition from one table to another one. Both tables should have the same:
|
|
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:
|
|
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:
|
|
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 |
|
CLEAR INDEX IN PARTITION |
Resets a secondary index in the specified partition |
|
FREEZE PARTITION |
Creates a backup of the specified partition. If you do not use the 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 |
|
UNFREEZE PARTITION |
Removes a backup of the specified partition from the disk. If you omit the |
|
FETCH PARTITION|PART |
Downloads a partition/part from another server. This query works for replicated tables only |
|
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 |
|
UPDATE IN PARTITION |
Updates partition data that matches the specified condition |
|
DELETE IN PARTITION |
Deletes partition data that matches the specified condition |
|
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>];