Lindorm:CREATE TABLE

Table name (table_identifier)

Note the following requirements for table names:

• Can contain digits, uppercase letters, lowercase letters, periods (.), hyphens (-), and underscores (_).

• Cannot start with a period (.) or a hyphen (-).

• Must be 1 to 255 characters in length.

Column definition (column_definition)

Primary key (primary_key)

The primary key is the unique identifier for data in a table. It consists of one or more columns. You must specify a primary key (PRIMARY KEY) when you create a table.

When you use the CREATE TABLE statement to create a table, note the following:

Index expression (KEY|INDEX)

When you use the CREATE TABLE statement to create an index table, you can use the KEY or INDEX keyword to specify the index to create.

Partition (partition_definition)

Partitioning is supported only by LindormTSDB. In a time series table, the column used for PARTITION BY must be a VARCHAR column in the primary key definition.

When you create a table, you can use the PARTITION BY HASH(column1, column2, ..., columnN) statement to explicitly specify one or more columns for hash partitioning. For example: PARTITION BY HASH(c1, p1).

Table attributes (table_options)

Table attributes (table_options) are supported only by LindormTable. You can use the WITH keyword to add the following table attributes:

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

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

Execute the DESCRIBE table sensor; statement to check whether the table is created, and then execute SHOW INDEX FROM sensor; to check whether the secondary index is created.

Specify the data TTL and compression algorithm

Create a wide table, set the data TTL to 30 days (2,592,000 seconds), and set 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

• Execute the DESCRIBE table sensor; statement to check whether the table is created.

• On the Overview page of the cluster management system, click the name of the destination table in the destination database. In the Current Table Details area, click View Table Properties to view the values of the COMPRESSION and TTL parameters.

Specify the major compaction period

Create a wide table and set the major compaction period to 10 days (864,000,000 milliseconds).

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

• Execute the DESCRIBE table sensor; statement to check whether the table is created.

• On the Overview page of the cluster management system, click the name of the destination table in the destination database. In the Current Table Details area, click View Table Properties to view the value of the COMPACTION_MAJOR_PERIOD parameter.

Enable dynamic columns

Create a wide table and set the DYNAMIC_COLUMNS parameter to TRUE to support writing dynamic column data to the 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)
) WITH (DYNAMIC_COLUMNS='TRUE');

Verify the result

• Execute the DESCRIBE table sensor; statement to check whether the table is created.

• On the Overview page of the cluster management system, click the name of the destination table in the destination database. In the Current Table Details area, click View Table Properties to view the value of the DYNAMIC_COLUMNS parameter.

Specify the boundary for hot and cold data separation

Create a wide table and set the boundary for hot and cold data separation. The system archives data based on this boundary.

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

• Execute the DESCRIBE table sensor; statement to check whether the table is created.

• On the Overview page of the cluster management system, click the name of the destination table in the destination database. In the Current Table Details area, click View Table Properties to view the values of the CHS and CHS_L2 parameters.

Set multiple attributes at the same time

Create a wide table and specify the compression algorithm, data TTL, and the boundary for hot and cold data separation.

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

• Execute the DESCRIBE table sensor; statement to check whether the table is created.

• On the Overview page of the cluster management system, click the name of the destination table in the destination database. In the Current Table Details area, click View Table Properties to view the values of the COMPRESSION, CHS, CHS_L2, and TTL parameters.

Set partitions

Create a wide table with five predefined partitions. The start key of the partitions is 1000 and the 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

• Execute the DESCRIBE table sensor; statement to check whether the table is created.

• On the Overview page of the cluster management system, click the name of the destination table in the destination database. In the Shard Details area, you can view the values of the startKey and endKey parameters for each shard.

Set the start keys of multiple partitions

Create a wide table and predefine the start keys for five partitions. After the table is created, it contains six pre-split partitions by default.

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

• Execute the DESCRIBE table sensor; statement to check whether the table is created.

• On the Overview page of the cluster management system, click the name of the destination table in the destination database. In the Shard Details area, you can view the values of the startKey and endKey parameters for each shard.

Explicitly specify a partition key column

Most query scenarios involve querying the instantaneous data of a single device. Therefore, you can explicitly specify device_id as the partition key column for data partitioning.

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

Execute the DESCRIBE TABLE sensor; statement to verify the result.