CREATE TABLE

Table name (table_identifier)

The table name must meet the following requirements:

• 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 uniquely identifies data in a table and 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, use the KEY or INDEX keyword to specify the index.

Partition (partition_definition)

Partitions are supported only by LindormTSDB. In a time series table, the column used in the PARTITION BY clause must be a VARCHAR primary key column.

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)

Only LindormTable supports table attributes. 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)
);

Verification

Run theDESCRIBE table sensor; statement to verify that the table was created.

Create a table with an index

This example shows how to create a secondary index while creating 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)
);

Verification

RunDESCRIBE table sensor; to verify that the table was created, and runSHOW INDEX FROM sensor; to verify that the secondary index was created.

Specify TTL and compression algorithm

This example creates a wide table, sets the TTL to 30 days (2,592,000 seconds), and specifies ZSTD as the compression algorithm.

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

Verification

• Run theDESCRIBE table sensor; statement to verify that the table was created.

• Go to the Overview page in the cluster management system and click the name of the target table. In the current table details area, click view table properties to check the COMPRESSION and TTL parameters.

Specify the major compaction interval

This example creates a wide table and sets the major compaction interval 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');

Verification

• Run theDESCRIBE table sensor; statement to verify that the table was created.

• Go to the Overview page in the cluster management system and click the name of the target table. In the current table details area, click view table properties to check the value of the COMPACTION_MAJOR_PERIOD parameter.

Enable dynamic columns

To enable writing data to dynamic columns when creating a wide table, set DYNAMIC_COLUMNS toTRUE.

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

Verification

• Run theDESCRIBE table sensor; statement to verify that the table was created.

• Go to the Overview page in the cluster management system and click the name of the target table. In the current table details area, click view table properties to check the value of the DYNAMIC_COLUMNS parameter.

Specify the hot and cold data boundary

This example creates a wide table and sets a hot-cold data boundary for data archival.

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

Verification

• Run theDESCRIBE table sensor; statement to verify that the table was created.

• Go to the Overview page in the cluster management system and click the name of the target table. In the current table details area, click view table properties to check the CHS and CHS_L2 parameters.

Set multiple attributes at once

This example creates a wide table with compression, TTL, and a hot-cold data 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 (
    COMPRESSION='ZSTD', 
    CHS = '86400', 
    CHS_L2 = 'storagetype=COLD', 
    TTL = '2592000');

Verification

• Run theDESCRIBE table sensor; statement to verify that the table was created.

• Go to the Overview page in the cluster management system and click the name of the target table. In the current table details area, click view table properties to check the COMPRESSION, CHS, CHS_L2, and TTL parameters.

Configure partitions

This example creates a wide table pre-split into five partitions, with start key 1000 and end key 9000.

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

Verification

• Run theDESCRIBE table sensor; statement to verify that the table was created.

• Go to the Overview page in the cluster management system and click the name of the target table. In the Shard information area, check the startKey and endKey parameters for each partition.

Set start keys for multiple partitions

Code examples

• Specify partitions by numeric keys (based on your data distribution)

  This example creates a wide table with pre-defined start keys for five partitions, resulting in a total of six pre-split partitions.

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

• Specify partitions by string keys (based on your data distribution)

  CREATE TABLE your_table (
      address VARCHAR NOT NULL,
      col1 VARCHAR,
      PRIMARY KEY (address)
  ) WITH (
      SPLITKEYS = '0x01,0x02,0x03,0x04,0x05,0x06,0x07,0x08,0x09,0x0a,0x0b,0x0c,0x0d,0x0e,0x0f,0x10,0x11,0x12,0x13,0x14,0x15,0x16,0x17,0x18,0x19,0x1a,0x1b,0x1c,0x1d,0x1e,0x1f,0x20,0x21,0x22,0x23,0x24,0x25,0x26,0x27,0x28,0x29,0x2a,0x2b,0x2c,0x2d,0x2e,0x2f,0x30,0x31,0x32,0x33,0x34,0x35,0x36,0x37,0x38,0x39,0x3a,0x3b,0x3c,0x3d,0x3e,0x3f,0x40,0x41,0x42,0x43,0x44,0x45,0x46,0x47,0x48,0x49,0x4a,0x4b,0x4c,0x4d,0x4e,0x4f,0x50,0x51,0x52,0x53,0x54,0x55,0x56,0x57,0x58,0x59,0x5a,0x5b,0x5c,0x5d,0x5e,0x5f,0x60,0x61,0x62,0x63,0x64,0x65,0x66,0x67,0x68,0x69,0x6a,0x6b,0x6c,0x6d,0x6e,0x6f,0x70,0x71,0x72,0x73,0x74,0x75,0x76,0x77,0x78,0x79,0x7a,0x7b,0x7c,0x7d,0x7e,0x7f,0x80,0x81,0x82,0x83,0x84,0x85,0x86,0x87,0x88,0x89,0x8a,0x8b,0x8c,0x8d,0x8e,0x8f,0x90,0x91,0x92,0x93,0x94,0x95,0x96,0x97,0x98,0x99,0x9a,0x9b,0x9c,0x9d,0x9e,0x9f,0xa0,0xa1,0xa2,0xa3,0xa4,0xa5,0xa6,0xa7,0xa8,0xa9,0xaa,0xab,0xac,0xad,0xae,0xaf,0xb0,0xb1,0xb2,0xb3,0xb4,0xb5,0xb6,0xb7,0xb8,0xb9,0xba,0xbb,0xbc,0xbd,0xbe,0xbf,0xc0,0xc1,0xc2,0xc3,0xc4,0xc5,0xc6,0xc7,0xc8,0xc9,0xca,0xcb,0xcc,0xcd,0xce,0xcf,0xd0,0xd1,0xd2,0xd3,0xd4,0xd5,0xd6,0xd7,0xd8,0xd9,0xda,0xdb,0xdc,0xdd,0xde,0xdf,0xe0,0xe1,0xe2,0xe3,0xe4,0xe5,0xe6,0xe7,0xe8,0xe9,0xea,0xeb,0xec,0xed,0xee,0xef,0xf0,0xf1,0xf2,0xf3,0xf4,0xf5,0xf6,0xf7,0xf8,0xf9,0xfa,0xfb,0xfc,0xfd,0xfe,0xff'
  );

• Specify a split algorithm

  CREATE TABLE your_table2 (
      address VARCHAR NOT NULL,
      col1 VARCHAR,
      PRIMARY KEY (address)
  ) WITH (
      NUMREGIONS = 256,
      SPLITALGO = 'HexStringSplit'
  );

Verification

• Run theDESCRIBE table sensor; statement to verify that the table was created.

• Go to the Overview page in the cluster management system and click the name of the target table. In the Shard information area, check the startKey and endKey parameters for each partition.

Explicitly specify a partition key column

Because most queries retrieve instantaneous data for a single device, 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);

Verification

Run theDESCRIBE TABLE sensor; statement to verify that the table was created.

Create a geospatial table

• Create a table that supports the generic Geometry spatial data type, allowing you to write any type of spatial data.

  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 for the Point data type. Only point data can be written to this 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 for the Polygon data type. Only polygon data can be written to this 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 for the LineString data type. Only line data can be written to this 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 for the MULTIPOINT data type. Only multipoint data can be written to this 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 for the MULTILINESTRING data type. Only multilinestring data can be written to this 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 for the MULTIPOLYGON data type. Only multipolygon data can be written to this 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 for the GEOMETRYCOLLECTION data type. Only geometry collection data can be written to this 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 with multiple columns of the Geometry type.

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