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 ClickHouse cluster name]
(
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 ClickHouse cluster name	This parameter specifies that a local table is created on each node. The parameter format is fixed at ON CLUSTER ClickHouse cluster name.
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 ClickHouse cluster name
 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 ClickHouse cluster name	This parameter specifies that a table is created on each node. The parameter format is fixed at ON CLUSTER ClickHouse cluster name.
[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.

Parent topic: Common SQL Commands for Storage-Compute Coupled ClickHouse

Previous topic: CREATE DATABASE

Next topic: DESC|DESCRIBE TABLE