Lindorm:CREATE TABLE

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.

Index expression (KEY|INDEX)

To create an index table by using the CREATE TABLE statement, you can use the KEY or INDEX keyword to specify the index that you want to create.

When you use the index expression, take note of the following items: If you do not explicitly specify the index name, the system generates an index name. Format: <table_name>_idx_${auto-increment number}. Secondary indexes and search indexes can be created only by using the index expression. For more information about index types, see the CREATE INDEX section of the "CREATE INDEX" topic. If you create a secondary index and do not specify the INCLUDE expression, the created index contains all columns. This is equivalent to setting the index attribute INDEX_COVERED_TYPE to COVERED_ALL_COLUMNS_IN_SCHEMA. If the created table is a dynamic table, it is equivalent to setting the index attribute INDEX_COVERED_TYPE to COVERED_DYNAMIC_COLUMNS.

If you specify the KEY or INDEX clause but do not explicitly specify the MUTABILITY and CONSISTENCY attributes, the table has two table attributes: CONSISTENCY ='strong' and MUTABILITY='MUTABLE_LATEST'.

If the index expression is included in the CREATE TABLE statement, the system first creates the table and then creates the index. If an exception occurs while creating the index, the created table and index may remain in the database and will not be automatically cleared or deleted. You can view the table and index by executing a statement such as SHOW or DESCRIBE, but you may not be able to write data to or query the table or index. We recommend that you clear and then recreate the table and index.

Partitioning (partition_definition)

Partitioning is supported only 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.

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.

Create an index when you create a table

Create a secondary index when you create a table.

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

Verify the result

You can execute the DESCRIBE table sensor; statement to check whether the table is created and execute the SHOW INDEX FROM sensor; statement to check whether the secondary index 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 COMPRESSION, 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 a 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.