Lindorm:CREATE TABLE

Applicable engines and versions

• The CREATE TABLE syntax is applicable to LindormTable and LindormTSDB.

• The CREATE TABLE syntax is supported by LindormTSDB whose version is 3.4.32 and later. For more information about how to view and upgrade the LindormTSDB version of the Lindorm instance, see Release notes of LindormTSDB and Upgrade the minor engine version of a Lindorm instance.

Syntax

create_table_statement ::=  CREATE TABLE [ IF NOT EXISTS ] table_identifier
                            '('
                                column_definition
                                ( ',' column_definition )*
                                 ',' PRIMARY KEY '(' primary_key ')' 
                            ')' 
                            [ PARTITION BY partition_definition ]
                            [ WITH  table_options ]
column_definition      ::=  column_identifier data_type [ NOT NULL ]
primary_key            ::=  column_identifier [ ',' column_identifier (ASC|DESC)]
partition_definition   ::=  HASH '(' column_identifier (',' column_identifier )* ')'
table_options          ::=  '(' option_definition (',' option_definition )*  ')'
option_definition      ::=  option_identifer '=' string_literal

Supported parameters

The supported parameters of the CREATE TABLE syntax are different in LindormTable and LindormTSDB. The following table describes the parameters supported by LindormTable and LindormTSDB.

Parameters

Table name (table_identifier)

The table name must meet the following requirements:

• The name can contain digits, letters, commas (,), hyphens (-), and underscores (_).

• The name cannot start with a period (.) or a hyphen (-).

• The name must be 1 to 255 characters in length.

Column definition (column_definition)

Primary key (primary_key)

The primary key of a table uniquely identifies the data in the table. A primary key consists of one or more columns. You must specify a primary key when you create a table.

When you use the CREATE TABLE syntax to create a table, take note of the items described in the following table.

Partitioning (partition_definition)

Partitioning is only supported by LindormTSDB. In a time series table, the partition key column specified by the PARTITION BY clause must be the VARCHAR column defined in the primary key.

When you create a table, you can use the PARTITION BY HASH(column1, column2, ..., columnN) clause to specify the columns based on which hash partitioning is performed on the table. Example: PARTITION BY HASH(c1, p1).

Table attributes (table_options)

This parameter is supported only by LindormTable. You can use the WITH keyword to add the attributes described in the following table to the table that you want to create.

Examples

Create a table

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
);

Verify the result

You can execute the DESCRIBE table sensor; statement to check whether the table is created.

Specify the TTL and compression algorithm

Create a wide table and set the data TTL of the table to 2,592,000 seconds (equivalent to 30 days) and the compression algorithm to ZSTD.

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
) WITH (COMPRESSION='ZSTD', TTL='2592000');

Verify the result

• You can execute the DESCRIBE table sensor; statement to check whether the table is created.

• Log on to the cluster management system. On the Overview page, click the name of the table that you want to check. In the Current table details section, click View table properties. On the page that appears, check the values of COMPRESSION and TTL. For more information about how to log on to the cluster management system, see Log on to the cluster management system.

Specify the interval at which the major compaction operation is performed

Create a wide table and set the interval at which the major compaction operation is performed to 864,000,000 ms (equivalent to 10 days).

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
) WITH (COMPACTION_MAJOR_PERIOD='864000000');

Verify the result

• You can execute the DESCRIBE table sensor; statement to check whether the table is created.

• Log on to the cluster management system. On the Overview page, click the name of the table that you want to check. In the Current table details section, click View table properties. On the page that appears, check the value of COMPACTION_MAJOR_PERIOD.

Enable dynamic columns

Create a wide table and set DYNAMIC_COLUMNS to TRUE.

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
) WITH (DYNAMIC_COLUMNS='TRUE');

Verify the result

• You can execute the DESCRIBE table sensor; statement to check whether the table is created.

• Log on to the cluster management system. On the Overview page, click the name of the table that you want to check. In the Current table details section, click View table properties. On the page that appears, check the value of DYNAMIC_COLUMNS.

Specify the boundary based on which hot and cold data separation is performed

Create a wide table and specify the boundary based on which hot and cold data separation is performed.

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
) WITH (CHS = '86400', CHS_L2 = 'storagetype=COLD');

Verify the result

• You can execute the DESCRIBE table sensor; statement to check whether the table is created.

• Log on to the cluster management system. On the Overview page, click the name of the table that you want to check. In the Current table details section, click View table properties. On the page that appears, check the values of CHS and CHS_L2.

Configure multiple attributes

Create a wide table and specify the compression algorithm, TTL, and the boundary based on which hot and cold data separation is performed.

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
) WITH (
    COMPRESSION='ZSTD', 
    CHS = '86400', 
    CHS_L2 = 'storagetype=COLD', 
    TTL = '2592000');

Verify the result

• You can execute the DESCRIBE table sensor; statement to check whether the table is created.

• Log on to the cluster management system. On the Overview page, click the name of the table that you want to check. In the Current table details section, click View table properties. On the page that appears, check the values of COMNPRESSION, CHS, CHS_L2, and TTL.

Configure partitioning

Create a wide table and pre-split the table into five regions whose start key is 1000 and end key is 9000.

CREATE TABLE sensor (
  p1 INTEGER NOT NULL, 
  c1 INTEGER, 
  c2 VARCHAR, 
  c3 VARCHAR,
  PRIMARY KEY(p1)
) WITH (NUMREGIONS='5', STARTKEY='1000', ENDKEY='9000');

Verify the result

• You can execute the DESCRIBE table sensor; statement to check whether the table is created.

• Log on to the cluster management system. On the Overview page, click the name of the table that you want to check. In the Shard information section, view the startKey and endKey values of each region.

Specify the start keys of multiple regions

Create a wide table and pre-define the start keys of five regions. In this case, the table contains six pre-split regions.

CREATE TABLE sensor (
  p1 INT NOT NULL,
  p2 INT NOT NULL,
  c1 VARCHAR,
  c2 BIGINT,
  PRIMARY KEY(p1, p2)
) WITH (SPLITKEYS = '100000,300000,500000,700000,900000');

Verify the result

• You can execute the DESCRIBE table sensor; statement to check whether the table is created.

• Log on to the cluster management system. On the Overview page, click the name of the table that you want to check. In the Shard information section, check the startKey and endKey values of each region.

Explicitly specify partition key column

In most queries, the instantaneous data of a single device is queried. Therefore, you can explicitly specify device_id as the partition key column.

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
) PARTITION BY HASH(device_id);

Verify the result

You can execute the DESCRIBE TABLE sensor; statement to check whether the table is created.