CREATE TABLE: Creating a Table
This section describes the basic syntax and usage of the SQL statement for creating a ClickHouse 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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot