Create a table in ApsaraDB for ClickHouse by using DMS-ApsaraDB for ClickHouse-阿里云帮助中心

This topic describes how to create a table in ApsaraDB for ClickHouse using Data Management Service (DMS).

Prerequisites

You have completed the following steps in the Quick Start guide.

Procedure

On the SQL Console page of the DMS console, enter a CREATE TABLE statement to create a local table.

Note

Local tables store the actual data. Each ClickHouse node has its own local tables that manage and query the data stored on that node. Local tables are suitable for data partitioning on each node in a cluster.

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 current database is used by default.
`local_table_name`	The name of the local table.
`cluster`	The name of the cluster. The value is fixed to default.
`name1,name2`	The column names.
`type1,type2`	The column types.
`engine_name`	The table engine type. For more information, see Table engines. The value is MergeTree for single-replica edition clusters and ReplicatedMergeTree for master-replica clusters. Important When you create tables in a Double-replica Edition cluster, you must use Replicated table engines from the MergeTree family. If you use non-replicated table engines, data cannot be replicated between replicas, which may cause data inconsistency.
`PARTITION BY`	The partition key.
`ORDER BY`	The sort key.
`PRIMARY KEY`	You can specify the primary key.
`SAMPLE BY`	The sampling expression. If you use a sampling expression, the primary key must include this expression.
`SETTINGS`	Other parameters that affect performance.

Note

For more information about the parameters, see Create Table.

Example:

CREATE TABLE clickhouse_demo.ontime_local ON CLUSTER default
(
    `Year`                            UInt16,
    `Quarter`                         UInt8,
    `Month`                           UInt8,
    `DayofMonth`                      UInt8,
    `DayOfWeek`                       UInt8,
    `FlightDate`                      Date,
    `Reporting_Airline`               String,
    `DOT_ID_Reporting_Airline`        Int32,
    `IATA_CODE_Reporting_Airline`     String,
    `Tail_Number`                     String,
    `Flight_Number_Reporting_Airline` String,
    `OriginAirportID`                 Int32,
    `OriginAirportSeqID`              Int32,
    `OriginCityMarketID`              Int32,
    `Origin`                          FixedString(5),
    `OriginCityName`                  String,
    `OriginState`                     FixedString(2),
    `OriginStateFips`                 String,
    `OriginStateName`                 String,
    `OriginWac`                       Int32,
    `DestAirportID`                   Int32,
    `DestAirportSeqID`                Int32,
    `DestCityMarketID`                Int32,
    `Dest`                            FixedString(5),
    `DestCityName`                    String,
    `DestState`                       FixedString(2),
    `DestStateFips`                   String,
    `DestStateName`                   String,
    `DestWac`                         Int32,
    `CRSDepTime`                      Int32,
    `DepTime`                         Int32,
    `DepDelay`                        Int32,
    `DepDelayMinutes`                 Int32,
    `DepDel15`                        Int32,
    `DepartureDelayGroups`            String,
    `DepTimeBlk`                      String,
    `TaxiOut`                         Int32,
    `WheelsOff`                       Int32,
    `WheelsOn`                        Int32,
    `TaxiIn`                          Int32,
    `CRSArrTime`                      Int32,
    `ArrTime`                         Int32,
    `ArrDelay`                        Int32,
    `ArrDelayMinutes`                 Int32,
    `ArrDel15`                        Int32,
    `ArrivalDelayGroups`              Int32,
    `ArrTimeBlk`                      String,
    `Cancelled`                       UInt8,
    `CancellationCode`                FixedString(1),
    `Diverted`                        UInt8,
    `CRSElapsedTime`                  Int32,
    `ActualElapsedTime`               Int32,
    `AirTime`                         Nullable(Int32),
    `Flights`                         Int32,
    `Distance`                        Int32,
    `DistanceGroup`                   UInt8,
    `CarrierDelay`                    Int32,
    `WeatherDelay`                    Int32,
    `NASDelay`                        Int32,
    `SecurityDelay`                   Int32,
    `LateAircraftDelay`               Int32,
    `FirstDepTime`                    String,
    `TotalAddGTime`                   String,
    `LongestAddGTime`                 String,
    `DivAirportLandings`              String,
    `DivReachedDest`                  String,
    `DivActualElapsedTime`            String,
    `DivArrDelay`                     String,
    `DivDistance`                     String,
    `Div1Airport`                     String,
    `Div1AirportID`                   Int32,
    `Div1AirportSeqID`                Int32,
    `Div1WheelsOn`                    String,
    `Div1TotalGTime`                  String,
    `Div1LongestGTime`                String,
    `Div1WheelsOff`                   String,
    `Div1TailNum`                     String,
    `Div2Airport`                     String,
    `Div2AirportID`                   Int32,
    `Div2AirportSeqID`                Int32,
    `Div2WheelsOn`                    String,
    `Div2TotalGTime`                  String,
    `Div2LongestGTime`                String,
    `Div2WheelsOff`                   String,
    `Div2TailNum`                     String,
    `Div3Airport`                     String,
    `Div3AirportID`                   Int32,
    `Div3AirportSeqID`                Int32,
    `Div3WheelsOn`                    String,
    `Div3TotalGTime`                  String,
    `Div3LongestGTime`                String,
    `Div3WheelsOff`                   String,
    `Div3TailNum`                     String,
    `Div4Airport`                     String,
    `Div4AirportID`                   Int32,
    `Div4AirportSeqID`                Int32,
    `Div4WheelsOn`                    String,
    `Div4TotalGTime`                  String,
    `Div4LongestGTime`                String,
    `Div4WheelsOff`                   String,
    `Div4TailNum`                     String,
    `Div5Airport`                     String,
    `Div5AirportID`                   Int32,
    `Div5AirportSeqID`                Int32,
    `Div5WheelsOn`                    String,
    `Div5TotalGTime`                  String,
    `Div5LongestGTime`                String,
    `Div5WheelsOff`                   String,
    `Div5TailNum`                     String
)ENGINE = ReplicatedMergeTree()
 PARTITION BY toYYYYMM(FlightDate)
 PRIMARY KEY (intHash32(FlightDate))
 ORDER BY (intHash32(FlightDate))
 SAMPLE BY intHash32(FlightDate)
SETTINGS index_granularity= 8192 ;

Create a distributed table to facilitate data writes and queries.

Note

ApsaraDB for ClickHouse Enterprise Edition does not require you to create distributed tables. For details about the table engines supported by the Enterprise Edition, see ApsaraDB for ClickHouse Enterprise Edition Compatibility Guide.
Distributed tables are virtual tables that do not directly store any data. Instead, they act as a query layer that distributes queries to local tables on one or more nodes and aggregates the results. Distributed tables are suitable for distributed data queries and processing in a ClickHouse cluster environment.

Syntax:

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

Parameters:

Parameter	Description
`db`	The name of the database. The current database is used by default.
`d_table_name`	The name of the distributed table.
`cluster`	The name of the cluster. The value is fixed to default.
`local_table_name`	The name of the created local table.
`sharding_key`	The sharding expression.

Note

For more information about the parameters, see Create Table.

Example:

CREATE TABLE ontime_local_distributed ON CLUSTER default
 AS clickhouse_demo.ontime_local  
ENGINE = Distributed(default, clickhouse_demo, ontime_local, rand());

Next step

Import data