Lindorm:CREATE TABLE

Table name (table_identifier)

Note the following when you set a table name:

• The name can contain digits, uppercase letters, lowercase letters, periods (.), 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)

A 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)

Partitions are supported only by LindormTSDB. In a time series table, the columns used for PARTITION BY must be VARCHAR columns 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 to hash-partition the table. For example: PARTITION BY HASH(c1, p1).

Table properties (table_options)

Only the wide table engine supports table properties (table_options). You can use the WITH keyword to add the following table properties:

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)
);

Verifying the results

You can execute DESCRIBE table sensor; to check whether the table has been created successfully.

Create an index when creating 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)
);

Result Verification

You can execute DESCRIBE table sensor; to check if the table was created successfully, and execute SHOW INDEX FROM sensor; to check if the secondary index was created successfully.

Specify the TTL and compression algorithm

When you create a wide table, set the TTL to 30 days (2592000 seconds) 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 results

• You can execute DESCRIBE table sensor; to check whether the table has been created successfully.

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

Specify the Major Compaction period

When you create a wide table, 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

• You can execute DESCRIBE table sensor; to check whether the table has been created successfully.

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

Enable the dynamic column feature

After you set the DYNAMIC_COLUMNS parameter to TRUE when you create a wide table, you can write 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

• You can execute DESCRIBE table sensor; to check whether the table was created successfully.

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

Specify the boundary for hot and cold data

When you create a wide table, set the boundary for hot and cold data. 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');

Verifying the result

• You can execute DESCRIBE table sensor; to check whether the table was successfully created.

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

Set multiple properties at the same time

When you create a wide table, specify the compression algorithm, TTL, and the boundary for hot and cold data.

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');

Verifying the results

• You can execute DESCRIBE table sensor; to check whether the table has been created successfully.

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

Set partitions

When you create a wide table, you can predefine five partitions. The start key for these 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');

Verifying the result

• You can execute DESCRIBE table sensor; to check whether the table has been created successfully.

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

Set the start keys for multiple partitions

When you create a wide table, you can pre-define five start keys. The table is then created with six 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');

Verifying the results

• You can execute DESCRIBE table sensor; to verify that the table was created successfully.

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

Explicitly specify partition key columns

Most query scenarios involve querying the instantaneous data of a single device. For these scenarios, 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);

Verifying the result

You can execute the DESCRIBE TABLE sensor; statement to verify the table creation result.

Create a spatio-temporal data table

• Create a table that supports any spatial data type (Geometry). You can write data of any spatial data type to the table.

  CREATE TABLE geoms(gid INT, g GEOMETRY, PRIMARY KEY(gid));

  Write data of any spatial data type.

  UPSERT INTO geoms(gid, g) VALUES(0,ST_GeomFromText('POINT(-10.1 3.3)')),(1,ST_GeomFromText('LINESTRING(-12.2 4.3, -10.2 4.3)')),(2,ST_GeomFromText('POLYGON((2 2, 2 8, 8 8, 8 2, 2 2))'));

• Create a table that supports the Point type. You can only write point data to the table.

  CREATE TABLE pts(gid INT, g GEOMETRY(POINT), PRIMARY KEY(gid));

  Write point data.

  UPSERT INTO pts(gid, g) VALUES(0, ST_MakePoint(0,0)),(1, ST_MakePoint(1,1));

• Create a table that supports the Polygon type. You can only write polygon data to the table.

  CREATE TABLE polys(gid INT, g GEOMETRY(POLYGON), PRIMARY KEY(gid));

  Write polygon data.

  UPSERT INTO polys(gid,g) VALUES(0,ST_GeomFromText('POLYGON((2 2, 2 8, 8 8, 8 2, 2 2))'));

• Create a table that supports the LineString type. You can only write line data to the table.

  CREATE TABLE lines(gid INT, g GEOMETRY(LINESTRING), PRIMARY KEY(gid));

  Write line data.

  UPSERT INTO lines(gid,g) VALUES(0, ST_GeomFromText('LINESTRING(-12.2 4.3, -10.2 4.3)'));

• Create a table that supports the MULTIPOINT type. You can only write multipoint data to the table.

  CREATE TABLE multipoints(gid INT, g GEOMETRY(MULTIPOINT), PRIMARY KEY(gid));

  Write multipoint data.

  UPSERT INTO multipoints(gid,g) VALUES(0, ST_GeomFromText('MULTIPOINT (10 40, 40 30, 20 20, 30 10)'));

• Create a table that supports the MULTILINESTRING type. You can only write multilinestring data to the table.

  CREATE TABLE multilines(gid INT, g GEOMETRY(MULTILINESTRING), PRIMARY KEY(gid));

  Write multilinestring data.

  UPSERT INTO multilines(gid,g) VALUES(0, ST_GeomFromText('MULTILINESTRING ((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10))'));

• Create a table that supports the MULTIPOLYGON type. You can only write multipolygon data to the table.

  CREATE TABLE multipolys(gid INT, g GEOMETRY(MULTIPOLYGON), PRIMARY KEY(gid));

  Write multipolygon data.

  UPSERT INTO multipolys(gid,g) VALUES(0, ST_GeomFromText('MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),((15 5, 40 10, 10 20, 5 10, 15 5)))'));

• Create a table that supports the GEOMETRYCOLLECTION type. You can only write geometry collection data to the table.

  CREATE TABLE collections(gid INT, g GEOMETRY(GEOMETRYCOLLECTION), PRIMARY KEY(gid));

  Write geometry collection data.

  UPSERT INTO collections(gid,g) VALUES(0, ST_GeomFromText('GEOMETRYCOLLECTION (POINT (40 10), LINESTRING (10 10, 20 20, 10 40), POLYGON ((40 40, 20 45, 45 30, 40 40)))'));

• Create a table that consists of columns of multiple spatial data types (Geometry).

  CREATE TABLE mix(gid INT, pt GEOMETRY(POINT), ply GEOMETRY(POLYGON), PRIMARY KEY(gid));