Create a table in ApsaraDB for ClickHouse by using CREATE TABLE - ApsaraDB for ClickHouse

This topic describes how to create a table in ApsaraDB for ClickHouse.

Create a local table

Syntax:

CREATE TABLE [IF NOT EXISTS] [db.]local_table_name ON CLUSTER cluster
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = engine_name()
[PARTITION BY expr]
ORDER BY expr
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...];

Parameters:

Parameter	Description
`db`	The name of the database. The default value is the current database. This topic uses `default` as an example.
`local_table_name`	The name of the local table.
`ON CLUSTER cluster`	Creates a local table on each node. The value is fixed to `ON CLUSTER default`.
`name1,name2`	The column name.
`type1,type2`	The column type. Note For more information about the data types that ApsaraDB for ClickHouse supports, see Data types.
`ENGINE = engine_name()`	The table engine type. When you use this engine to create a table, configure the parameters as follows. `ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')`: a fixed configuration. You do not need to modify it. `ReplicatedMergeTree()`: equivalent to `ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')`. Important When you create tables in a Double-replica Edition cluster, make sure that the tables use Replicated table engines from the MergeTree family. If the tables use non-Replicated table engines, the data on the tables is not replicated across replicas. This can lead to data inconsistency. For more information about the table engine types that ApsaraDB for ClickHouse supports, see Table engines.
`ORDER BY expr`	The sort key. This parameter is required. The value can be a tuple of a column set or any expression.
`[DEFAULT\|MATERIALIZED\|ALIAS expr]`	The default expression. DEFAULT: A normal default expression. If a value is not specified for a field, a default value is automatically generated and used. MATERIALIZED: A materialized expression. ALIAS: An alias expression.
`GRANULARITY`	The index granularity parameter.
`[PARTITION BY expr]`	The partition key. Data is typically partitioned by date. You can also use other fields or field expressions.
`[PRIMARY KEY expr]`	The primary key. By default, the primary key is the same as the sort key. In most cases, you do not need to use the `PRIMARY KEY` clause to specify a primary key.
`[SAMPLE BY expr]`	The sampling expression. If you use a sampling expression, you must include this expression in the primary key.
`[SETTINGS name=value, ...]`	Additional parameters that affect performance. Note For more information about the parameters supported in `SETTINGS`, see SETTINGS configuration items.

Note

Only table engines in the MergeTree family support the following parameters: ORDER BY, GRANULARITY, PARTITION BY, PRIMARY KEY, SAMPLE BY, and [SETTINGS name=value, ...]. For more information, see CREATE TABLE.

Example:

CREATE TABLE local_table ON CLUSTER default
(
    Year UInt16,
    Quarter UInt8,
    Month UInt8,
    DayofMonth UInt8,
    DayOfWeek UInt8,
    FlightDate Date,
    FlightNum String,
    Div5WheelsOff String,
    Div5TailNum String
)ENGINE = MergeTree()
 PARTITION BY toYYYYMM(FlightDate)
 PRIMARY KEY (intHash32(FlightDate))
 ORDER BY (intHash32(FlightDate),FlightNum)
 SAMPLE BY intHash32(FlightDate)
SETTINGS index_granularity= 8192;

Create a distributed table

A distributed table is a collection of local tables. It provides a unified view that supports data writes and queries across multiple local tables. When you write data to a distributed table, the data is automatically distributed to each local table in the collection. When you query a distributed table, each local table is queried separately, and the results are aggregated and returned. You must create local tables before you can create a distributed table.

Note

Only ApsaraDB for ClickHouse Community-Compatible Edition supports creating distributed tables using the Distributed engine. ApsaraDB for ClickHouse Enterprise Edition does not use the concept of shards. In the Enterprise Edition, multiple nodes act as replicas. Therefore, you do not need to create distributed tables.

Syntax:

CREATE TABLE [db.]distributed_table_name ON CLUSTER default
 AS db.local_table_name ENGINE = Distributed(cluster, db, local_table_name [, sharding_key])

Parameters:

Parameter	Description
`db`	The name of the database. The default value is the current database. This topic uses `default` as an example.
`distributed_table_name`	The name of the distributed table.
`ON CLUSTER cluster`	Creates a table on each node. The value is fixed to `ON CLUSTER default`.
`local_table_name`	The name of the created local table.
`sharding_key`	The sharding expression. It determines the shard to which data is written. The `sharding_key` can be an expression, such as the `rand()` function, or a column, such as `user_id` of the Integer type.

Example:

CREATE TABLE distributed_table ON CLUSTER default
 AS default.local_table 
ENGINE = Distributed(default, default, local_table, rand());

Create a table by copying a table schema

You can copy the schema of a source table to create a new table with the same structure.

Syntax:

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

Parameters:

Parameter	Description
`db`	The name of the database. The default value is the current database. This topic uses `default` as an example.
`table_name1`	The source table whose schema you want to copy. This topic uses the created local table `local_table` as an example.
`table_name2`	The new table to be created.
`ON CLUSTER cluster`	Creates a table on each node. The value is fixed to `ON CLUSTER default`.
`[ENGINE = engine_name]`	The table engine type. If you do not specify a table engine, the new table uses the same engine as the source table by default. Note For more information about the table engine types that ApsaraDB for ClickHouse supports, see Table engines.

Example:

CREATE TABLE t2 ON CLUSTER default as default.local_table;

Create a table with the same structure as a query result set

You can create a table that has the same structure as the result set of a SELECT clause. The table uses a specified engine and is populated with the data from the result set.

Syntax:

CREATE TABLE [IF NOT EXISTS] [db.]s_table_name ON CLUSTER cluster ENGINE = engine_name() AS SELECT ...

Parameters:

Parameter	Description
`db`	The name of the database. The default value is the current database. This topic uses `default` as an example.
`s_table_name`	The table to be created from the `SELECT` statement.
`ON CLUSTER cluster`	Creates a table on each node. The value is fixed to `ON CLUSTER default`.
`ENGINE = engine_name()`	The table engine type. Note For more information about the table engine types that ApsaraDB for ClickHouse supports, see Table engines.
`SELECT ...`	The `SELECT` clause.

Example:

CREATE TABLE t3 ON CLUSTER default ENGINE =MergeTree() order by Year as select * from default.local_table;

References

For more information about how to create a table, see CREATE TABLE.
For more information about how to create a table by copying a table schema, see With a Schema Similar to Other Table.
For more information about creating a table using a SELECT statement, see From SELECT query.