Lindorm:CREATE TABLE

Table name

• Allowed characters: digits, uppercase letters, lowercase letters, periods (.), hyphens (-), and underscores (_).

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

• Length: 1 to 255 characters.

Column definition

TIMESTAMP type (LindormTSDB only):

• Only one column per table can be of the TIMESTAMP type.

• LindormTSDB encodes and parses timestamps based on UTC+8.

• Legacy 32-bit Unix timestamps must be multiplied by 1000 before writing to LindormTSDB. Writing a 32-bit Unix timestamp directly causes a semantic interpretation error. For example, 1641009600 represents 2022-01-01 12:00:00 in legacy systems, but is interpreted as 1970-01-20 07:50:09 (UTC+8) in LindormTSDB.

NULL constraint behavior:

Primary key

A primary key uniquely identifies rows in a table. It consists of one or more columns and must be specified when creating a table.

LindormTable primary key limits:

LindormTSDB primary key structure:

In a time series table, primary key columns serve two distinct roles:

• Tag column: A primary key column of the VARCHAR type. Identifies the data source — for example, device ID in Internet of Things (IoT) scenarios, vehicle identifier in Internet of Vehicles (IoV) scenarios, or application ID or ip:port in monitoring scenarios.

• Timestamp column: A primary key column of the TIMESTAMP type. Only one timestamp column is allowed per table.

The primary key can contain one or more VARCHAR (tag) columns, but only one TIMESTAMP column. All primary key columns must be VARCHAR or TIMESTAMP type.

For guidance on designing primary keys for time series tables, see Best practices for PRIMARY KEY design.

Index expression (KEY|INDEX)

Use the KEY or INDEX keyword in a CREATE TABLE statement to create an index at table creation time. You can create secondary indexes and search indexes this way.

Index naming: If no index name is specified, the system generates one in the format table_name_idx_${auto_increment_id}.

Secondary index redundancy (when INCLUDE is not specified):

Default table properties when using KEY/INDEX (if MUTABILITY and CONSISTENCY are not specified):

• CONSISTENCY = 'strong'

• MUTABILITY = 'MUTABLE_LATEST'

Residual state on index creation failure: If an exception occurs during index creation, the table and index may remain in a residual state and are not automatically cleared. They remain visible via SHOW and DESCRIBE statements, but data writes and queries may fail. Manually drop the residual table and index, then recreate them.

Partition

Partitions are supported only in LindormTSDB. The columns used in PARTITION BY must be VARCHAR columns in the primary key.

Syntax: PARTITION BY HASH(column1, column2, ..., columnN)

Table properties

Only LindormTable supports table properties via the WITH keyword.

In LindormTable versions earlier than 2.2.16, the WITH keyword is not supported. Enclose property keywords in single quotes instead. For string values, also enclose the value in single quotes. Example: CREATE TABLE IF NOT EXISTS t1(c1 varchar, c2 bigint, c3 int, c4 int, PRIMARY KEY(c1,c2)) 'CONSISTENCY'='strong';

Create a basic 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)
);

Run DESCRIBE table sensor; to verify the table was created.

Create a table with a secondary index

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

To verify:

• Run DESCRIBE table sensor; to check the table.

• Run SHOW INDEX FROM sensor; to check the secondary index.

Set TTL and compression algorithm

Set TTL to 30 days (2,592,000 seconds) and compression 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');

To verify:

• Run DESCRIBE table sensor; to check the table.

• In the cluster management system, navigate to the Overview page, click the table name, and click View Table Properties in the Current Details Table area to confirm the COMPRESSION and TTL values.

Set the major compaction period

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

To verify: Run DESCRIBE table sensor;, or click View Table Properties in the Current Details Table area of the cluster management system.

Enable dynamic columns

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

To verify: Run DESCRIBE table sensor;, or click View Table Properties in the Current Details Table area of the cluster management system.

Set hot/cold data boundary

Archive data older than 86,400 seconds (1 day) to cold storage.

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

CHS requires cold storage to be enabled and must be used together with CHS_L2='storagetype=COLD'.

To verify: Run DESCRIBE table sensor;, or click View Table Properties in the Current Details Table area of the cluster management system to confirm the CHS and CHS_L2 values.

Combine multiple properties

Set compression, TTL, and hot/cold data boundary together.

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

To verify: Run DESCRIBE table sensor;, or click View Table Properties in the Current Details Table area of the cluster management system to confirm the COMPRESSION, CHS, CHS_L2, and TTL values.

Pre-create partitions with start and end keys

Pre-create five partitions with start key 1000 and end key 9000. STARTKEY and ENDKEY require NUMREGIONS.

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

To verify: Run DESCRIBE table sensor;, or navigate to the Shard Details area in the cluster management system to view the startKey and endKey values for each shard.

Pre-create partitions with explicit split keys

Specify five split keys to pre-create six partitions. SPLITKEYS cannot be used with NUMREGIONS, STARTKEY, or ENDKEY.

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

To verify: Run DESCRIBE table sensor;, or view the startKey and endKey values in the Shard Details area of the cluster management system.

Specify a partition key column

For queries that target a single device's instantaneous data, specify device_id as the partition key to control data distribution.

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

Run DESCRIBE TABLE sensor; to verify the table creation result.

Create a spatio-temporal data table

Any spatial type (GEOMETRY) — accepts any spatial data type:

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

Write data of any spatial 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))'));

Point type only:

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

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

Polygon type only:

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

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

LineString type only:

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

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

MULTIPOINT type only:

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

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

MULTILINESTRING type only:

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

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

MULTIPOLYGON type only:

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

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

GEOMETRYCOLLECTION type only:

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

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

Multiple spatial columns:

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