Create Table

Create Local Table

Syntax:


CREATE TABLE [IF NOT EXISTS] [db.]local_table_name ON CLUSTER ck_cluster
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = engine_name()
[PARTITION BY expr]
ORDER BY expr
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...];

Parameter Description:

Parameter	Description
`db`	The name of the database, default is the currently selected database. In this document, ck_test is used as an example.
`local_table_name`	Local table name.
`ON CLUSTER ck_test`	Create a local table on every node, fixed as `ON CLUSTER ck_test`.
`name1,name2`	Column names.
`type1,type2`	Column types. For types supported by UClickHouse, please refer to Data Types.
`ENGINE = engine_name()`	Table engine type. For dual-replica clusters, you should use the Replicated* engines of the MergeTree series that support data replication, otherwise data will not be replicated between replicas, resulting in inconsistent query results. When using this engine to create a table, the parameter should be provided as follows. ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}','{replica}'), characters in the parameter should not be modified. ReplicatedMergeTree(), which is equivalent to ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}'). For table engine types supported by ClickHouse, please refer to Table Engine.
`ORDER BY expr`	Sorting key, required, can be a tuple of columns or any expression.
`[DEFAULT\|MATERIALIZED\|ALIAS expr]`	Default expression. DEFAULT: Common default expression. Generates and fills in default values when the field is omitted. MATERIALIZED: Materialized expression. ALIAS: Alias expression.
`GRANULARITY`	Index granularity parameter.
`[PARTITION BY expr]`	Partition key. Usually partitioned by date, but can use other fields or expressions.
`[PRIMARY KEY expr]`	Primary key, by default the same as the sorting key. In most cases, you don't need to use the `PRIMARY KEY` clause to specify the primary key.
`[SAMPLE BY expr]`	Sampling expression. If you want to use a sampling expression, it must be included in the primary key.
`[SETTINGS name=value, ...]`	Additional parameters affecting performance. Tip For parameters supported in `SETTINGS`, please refer to SETTINGS Configuration .

The parameters ORDER BY, GRANULARITY, PARTITION BY, PRIMARY KEY, SAMPLE BY and [SETTINGS name=value, ...] are only supported by the MergeTree family of table engines. For more parameter explanations, refer to CREATE TABLE .

Example:


CREATE TABLE lineorder_local on cluster ck_cluster
(
    LO_ORDERKEY             UInt32,
    LO_LINENUMBER           UInt8,
    LO_CUSTKEY              UInt32,
    LO_PARTKEY              UInt32,
    LO_SUPPKEY              UInt32,
    LO_ORDERDATE            Date,
    LO_ORDERPRIORITY        LowCardinality(String),
    LO_SHIPPRIORITY         UInt8,
    LO_QUANTITY             UInt8,
    LO_EXTENDEDPRICE        UInt32,
    LO_ORDTOTALPRICE        UInt32,
    LO_DISCOUNT             UInt8,
    LO_REVENUE              UInt32,
    LO_SUPPLYCOST           UInt32,
    LO_TAX                  UInt8,
    LO_COMMITDATE           Date,
    LO_SHIPMODE             LowCardinality(String)
)
ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

Create Distributed Table

A distributed table is a collection of local tables that abstracts multiple local tables into a unified table, providing write and query capabilities. When data is written to a distributed table, it is automatically distributed to multiple local tables in the collection. When querying the distributed table, each local table in the collection is queried separately, and the final result is aggregated and returned. You need to create a local table first and then create a distributed table.

Basic Syntax:


CREATE TABLE [db.]distributed_table_name ON CLUSTER ck_cluster
 AS db.local_table_name ENGINE = Distributed(cluster, db, local_table_name [, sharding_key])

Parameter Description:

Parameter	Description
`db`	The name of the database, default is the currently selected database. In this document, ck_cluster is used as an example.
`distributed_table_name`	Distributed table name.
`ON CLUSTER ck_cluster`	Create a table on every node, fixed as `ON CLUSTER ck_cluster`.
`local_table_name`	Already created local table name.
`sharding_key`	Sharding expression, used to decide into which shard the data will be written. `sharding_key` can be an expression, such as the function `rand()`, or a column, such as `user_id` (Integer type).

Example:


CREATE TABLE lineorder_distribute  AS lineorder on cluster ck_cluster
ENGINE = Distributed(
    ck_cluster,
    ch_test,
    lineorder_local,
    rand()
  );

Create Table by Copying Table Structure

Basic Syntax:


CREATE TABLE [IF NOT EXISTS] [db.]table_name2 ON CLUSTER ck_cluster AS [db.]table_name1 [ENGINE = engine_name];

Parameter Description:

Parameter	Description
`db`	The name of the database, default is the currently selected database. In this document, ck_cluster is used as an example.
`table_name1`	Source table whose structure is copied. In this document, an already created local table named local_table is used as an example.
`table_name2`	Newly created table.
`ON CLUSTER ck_cluster`	Create a table on every node, fixed as `ON CLUSTER ck_cluster`.
`[ENGINE = engine_name]`	Table engine type. If not specified, the engine is the same as the copied table. Note For table engine types supported by UClickHouse, please refer to Table Engine .

Example:


create table lineorder ON CLUSTER ck_cluster as ck_test.lineorder_local;

Create Table Using SELECT Statement

Basic Syntax:


CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER ck_cluster ENGINE = engine_name() AS SELECT ...

Parameter Description:

Parameter	Description
`db`	The name of the database, default is the currently selected database. In this document, ck_cluster is used as an example.
`table_name`	Table created using SELECT statement.
`ON CLUSTER ck_cluster`	Create a table on every node, fixed as `ON CLUSTER ck_cluster`.
`ENGINE = engine_name()`	Table engine type. For table engine types supported by UClickHouse, please refer to Table Engine
`SELECT ...`	`SELECT` clause.

Example:


create table lineorder ON CLUSTER ck_cluster ENGINE =MergeTree() order by Year as select * from ck_cluster.local_table;

Official Documentation Reference

For more information on creating tables, please see CREATE TABLE .
For more information on creating tables by copying table structure, please see With a Schema Similar to Other Table .
For more information on creating tables using the SELECT statement, please see From SELECT query .

Create Table

Create Local Table

Create Distributed Table

Create Table by Copying Table Structure

Create Table Using SELECT Statement

Official Documentation Reference