On this page

Creating a Local Table
Creating a Table by Copying the Structure of an Existing Table
Creating a Table by Specifying a SELECT Clause in a CREATE TABLE Statement

Show all

Help Center/ CloudTable Service/ Developer Guide/ ClickHouse Application Development Guide/ SQL Syntax/ CREATE TABLE

CREATE TABLE

Updated on 2024-04-29 GMT+08:00

View PDF

This section describes how to create a table in ClickHouse.

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** Parameter description
Parameter	Description
database_name	Name of the database. The default value is 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 ON CLUSTER ClickHouse cluster name.
name1,name2	Column name.
ENGINE = engine_name()	Table engine type. When you create a table in a cluster of the double-replica edition, you must use the Replicated * engine that supports data replication among the engines in the MergeTree family. If you do not use the Replicated * engine, data cannot be replicated between replicas and inconsistent data query results are returned. When you use this engine to create a table, use one of the following methods to configure the parameters: ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}'). The characters in the parameters cannot be changed. ReplicatedMergeTree(), which is equivalent to ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}').
ORDER BY expr_list	Sort key. This parameter is required. The value can be a Tuple of a set of columns or an expression.
[PARTITION BY expr_list]	Partition key. In most cases, data is partitioned by date. You can specify another field or field expression as the partition key.

Examples:

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

Run the following statements to create a table by copying the schema of an existing table so that the table has the same structure as the source table.

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

**Table 2** Parameter description
Parameter	Description
db	Name of the database. The default value is 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 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.

Examples:

Create a database.
```
create database demo;
```
Use the database.
```
use demo;
```

Create a 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;
```
Basic Syntax

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 schema 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** Parameter description
Parameter	Description
database_name	Name of the database. The default value is the selected database.
table_name	Table created using the SELECT statement.
ENGINE = engine_name()	Table engine type.
SELECT ...	SELECT clause.

Examples:

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;

Run the SELECT statement to create a table.

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: SQL Syntax

Previous topic: CREATE DATABASE

Next topic: DESC: Querying a Table Structure

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot