Help Center/ CloudTable Service/ Developer Guide/ ClickHouse Application Development Guide/ SQL Syntax/ CREATE TABLE
Updated on 2024-04-29 GMT+08:00
View PDF
Share

CREATE TABLE

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