Help Center/CloudTable Service/User Guide/Using ClickHouse/Common SQL Commands for Storage-Compute Coupled ClickHouse/CREATE TABLE
Updated on 2025-12-29 GMT+08:00
View PDF

CREATE TABLE

This section describes how to create a table.

Creating a Local Table

CREATE TABLE [IF NOT EXISTS] [database_name.]table_name [ON CLUSTER default_cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2[type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = engine_name()
[PARTITION BY expr_list]
[ORDER BY expr_list]
Table 1 Parameters

Parameter

Description

database_name

Database name. The default value is the name of the selected database.

table_name

Name of the local table.

ON CLUSTER default_cluster

This parameter specifies that a local table is created on each node. The parameter format is fixed at ON CLUSTERdefault_cluster.

name1,name2

Column names.

ENGINE =engine_name()

Table engine type.

When creating a table in a dual-replica cluster, you must use the Replicated* engine that supports data replication in the MergeTree series. Otherwise, data is not replicated between replicas, resulting in inconsistent data query results. When using this engine to create a table, set the parameters as follows:

  • ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}') (The parameters are fixed and do not need to be modified.)
  • ReplicatedMergeTree(), which is equivalent to ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}').

ORDER BY expr_list

Sorting key, which is mandatory. It can be a tuple of a set of columns or any expression.

[PARTITION BY expr_list]

Partition key. The table is usually partitioned by date. You can also use other fields or field expressions.

Example:

  • Create a database. For details, see CREATE DATABASE.
  • Use the database. 
    use demo;
  • Create a table named demo.test. 
    CREATE TABLE demo.test ON CLUSTER default_cluster(`EventDate` DateTime, `id` UInt64)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}') PARTITION BY toYYYYMM(EventDate) ORDER BY id;

Creating a Table by Copying the Structure of an Existing Table

You can use the following syntax to create a table by copying the structure of a source table:

CREATE TABLE [IF NOT EXISTS] [db.]table_name2 ON CLUSTER default_cluster
 AS [db.]table_name1 [ENGINE = engine_name];
Table 2 Parameters

Parameter

Description

db

Database name. The default value is the name of the selected database.

table_name1

Name of the source table from which the structure is copied.

table_name2

Name of the table that you want to create.

ON CLUSTER default_cluster

This parameter specifies that a table is created on each node. The parameter format is fixed at ON CLUSTERdefault_cluster.

[ENGINE =engine_name]

Table engine type. If you do not specify a table engine when you create a table, the table engine of the source table is used by default.

Example:

  • Create a database. 
    create database demo;
  • Use the database. 
    use demo;
  • Create a data table. 
    create table demo_t(uid Int32,name String,age UInt32,gender String)engine = TinyLog;
  • Copy the table structure. 
    create table demo_t2 as demo_t;
  • View the table structure.

Creating a Table by Specifying a SELECT Clause in a CREATE TABLE Statement

You can use a specified table engine to create a table that has the same structure as the query result of the SELECT clause. The query result of the SELECT clause is populated to the table.

CREATE TABLE [IF NOT EXISTS] [database_name.]table_name ENGINE = engine_name AS SELECT ...
Table 3 Parameters

Parameter

Description

database_name

Database name. The default value is the name of the selected database.

table_name

Table created using the SELECT statement.

ENGINE =engine_name()

Table engine type.

SELECT ...

SELECT clause.

Example:

  • Create a table. 
    CREATE TABLE default.demo1 ON CLUSTER default_cluster( `EventDate` DateTime, `id` UInt64)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/demo1', '{replica}') PARTITION BY toYYYYMM(EventDate) ORDER BY id;
  • Create a table using the SELECT statement. 
    create table t3 ON CLUSTER default_cluster ENGINE =MergeTree() order by EventDate as select * from default.demo1;
  • Query the structures of tables demo1 and t3. 
    desc demo1;

    The query result shows that the structures of the two tables are the same.

    cloudtable-wlr-click-20230730-06-server-1-1 :) desc demo1;
DESCRIBE TABLE demo1
Query id: 712f6b91-668d-4f70-b160-aac8e52f63a4
┌─name──────┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ EventDate │ DateTime │              │                    │         │                  │                │
│ id        │ UInt64   │              │                    │         │                  │                │
└───────────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
2 rows in set. Elapsed: 0.001 sec.
cloudtable-wlr-click-20230730-06-server-1-1 :) desc t3;
DESCRIBE TABLE t3
Query id: 11b67532-26f0-49c5-b36d-439d45c279bf
┌─name──────┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ EventDate │ DateTime │              │                    │         │                  │                │
│ id        │ UInt64   │              │                    │         │                  │                │
└───────────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
2 rows in set. Elapsed: 0.001 sec.