CREATE TABLE: Creating a Table

Basic Syntax

• Method 1: Creating a table named table_name in the specified database_name database.

  If the table creation statement does not contain database_name, the name of the database selected during client login is used by default.

  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]

  

  You are advised to use PARTITION BY to create table partitions when creating a ClickHouse table. The ClickHouse data migration tool works on table partitions. If you do not use PARTITION BY to create table partitions in table creation, the table data cannot be migrated on the UI in Migrating Data Between ClickHouseServer Nodes in a Cluster.

• Method 2: Creating a table with the same structure as database_name2.table_name2 and specifying a different table engine for the table

  If no table engine is specified, the created table uses the same table engine as database_name2.table_name2.

  CREATE TABLE [IF NOT EXISTS] [database_name.]table_name AS [database_name2.]table_name2 [ENGINE = engine_name]

• Method 3: Using the specified engine to create a table with the same structure as the result of the SELECT clause and filling it with the result of the SELECT clause

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

Example

-- Create a table named test in the default database and default_cluster cluster.
CREATE TABLE default.test ON CLUSTER default_cluster
(
    `EventDate` DateTime, 
    `id` UInt64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}')
PARTITION BY toYYYYMM(EventDate)
ORDER BY id