CREATE TABLE - Lindorm - Alibaba Cloud Documentation Center

This topic describes the syntax used to create a table that supports geometry data types.

Syntax

create_table_statement ::=  CREATE TABLE [ IF NOT EXISTS ] table_name
                            '('
                                column_definition
                                ( ',' column_definition )*
                                 ',' [constraint pk]  PRIMARY KEY '(' primary_key ')' 
                            ')' WITH [ table_options ]
column_definition      ::=  column_name lql_type 
primary_key            ::=  primary_item [ ',' primary_item ]
primary_item           ::=  column_name (ASC|DESC)
                            | [z-order] '(' [ column_name ( ',' column_name )* ] ')'

Note

This syntax supports IF NOT EXISTS. You can specify one or more columns as the primary key.

Parameters

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)

Option	Required	Description
Column name (column_identifier)	Yes	The name can contain digits, letters, commas (,), hyphens (-), and underscores (_). The column name cannot contain system reserved keywords. The name cannot exceed 255 characters in length.
Data types (data type)	Yes	For more information about data types that are supported by this syntax, see Data types. Important If you want to specify the TIMESTAMP type when you create a time series data, take note of the following items: You can specify only one column of the TIMESTAMP data type in a time series table. LindormTSDB encodes and parses timestamps based on Beijing Time (GMT+8). The UNIX timestamps in outdated systems are still parsed to 32-bit values. We recommend that you multiply these timestamps by 1000 before you insert the timestamps to LindormTSDB. If you insert the timestamps to the LindormTSDB without converting the timestamps, LindormTSDB cannot parse the timestamps as expected. For example, the `1641009600` timestamp is parsed as `2022-01-01 12:00:00` in an outdated system. LindormTSDB parses the timestamp as `1970-01-20 07:50:09(GMT+8)`.
NULL constraint	No	Specifies whether the value of this option can be NULL. Important Lindorm SQL does not verify the NULL values of options. These values are verified by the engine. The verification method of NULL values depends on the verification rules of the engine. The verification rules vary with the engine. Therefore, NULL values may be written to the table even if you set this option to NOT NULL. To ensure that data can be written to the table, take note of the following requirements: Specify NOT NULL in the definition of all primary key columns. We recommend that you do not specify NOT NULL in the definition of non-primary key columns.

Primary key (primary_key)

Important

Standalone Lindorm instances do not support primary keys.

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.

Engine	Primary key usage
LindormTable	A single primary key column can be up to 2 KB in length. The total length of all primary key columns cannot exceed 30 KB. A column that is not included in the primary key can be up to 2 MB in length.
LindormTSDB	In the primary key columns of a time series table, columns of the VARCHAR data type are called the TAG columns. The column of the TIMESTAMP data type column is called the timestamp column. Primary key can contain columns only of the VARCHAR or TIMESTAMP type. A primary key can contain one or more columns of the VARCHAR type but only one column of the TIMESTAMP type. We recommend that you use values that can uniquely identify the data in a time series table as the primary key of the table. For example, you can use device IDs as the primary key in IoT scenarios, vehicle VINs as the primary key in IoV scenarios, and application IDs or `ip:port` strings as the primary key in monitoring scenarios. For more information, see the Best practice for designing primary keys section of the "Design a time series table" topic.

Partitioning (partition_definition)

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

Attribute	Type	Description
COMPRESSION	STRING	The algorithm used to compress the data in the table. Valid values: SNAPPY ZSTD LZ4 Note In LindormTable whose version is earlier than 2.3.4, no compression algorithm is used by default. In LindormTable 2.3.4 and later, the ZSTD algorithm is used by default.
TTL	INT	The Time-to-Live (TTL) value of data in the table. Unit: second. Note By default, the value of this attribute is null, which indicates that data in the table does not expire. You can configure this attribute in the `TTL=<Specified time>` format to specify a TTL for the table. You can also set this attribute to a null string to cancel the configured TTL. For more information, see the Examples section of the "ALTER TABLE" topic.
COMPACTION_MAJOR_PERIOD	LONG	The interval at which the `major compaction` operation is performed. Unit: milliseconds. For more information, see the Specify the interval at which the major compaction operation is performed section of the "ALTER TABLE" topic. Note The default interval is calculated by using the following formula: Math.Min(TTL,1728000000ms). If you do not specify a TTL, the value of this attribute is set to 1728000000 ms (equivalent to 20 days) by default.
MUTABILITY	STRING	An attribute related to indexes. This option specifies the mutability of the base table. Default value: MUTABLE_LATEST. Valid values: IMMUTABLE IMMUTABLE_ROWS MUTABLE_LATEST MUTABLE_ALL For more information about the values, see Secondary indexes. Important The value of the MUTABILITY attribute cannot be modified after the index table is created.
CONSISTENCY	STRING	The consistency level of the table. Default value: eventual. For a cross-zone instance, this parameter specifies the data consistency between the primary table and secondary table. Valid values: eventual: eventually consistent strong: strongly consistent Important For a cross-zone instance, if data is read and then written in increase, append, and index update operations, you must set the CONSISTENCY parameter of the primary table to `strong` to ensure data consistency between the primary and secondary tables.
NUMREGIONS	INT	The number of data regions in the table that you want to create.
CHS	INT	The time period after which data is stored by using cold storage. Unit: second. Note To configure this attribute, you must enable cold storage for the instance. For more information about cold storage, see Overview. When you configure this attribute, you must also set the CHS_L2 attribute to `storagetype=COLD`.
STARTKEY and ENDKEY	Same data type as that of the first column in the primary key specified by PRIMARY KEY	The start key and end key of each region. Note The STARTKEY and ENDKEY attributes must be specified together with the NUMREGIONS attribute. If the NUMREGIONS attribute is not specified, the STARTKEY and ENDKEY attributes do not take effect. If you specify string values for the STARTKEY and ENDKEY attributes, the strings are implicitly converted to values with the same data types as the first column of the primary key. Only the following data types are supported for the STARTKEY and ENDKEY attributes: SMALLINT INTEGER BIGINT CHAR VARCHAR FLOAT DOUBLE
SPLITKEYS	Same data type as that of the first column in the primary key specified by PRIMARY KEY	The start keys of all pre-split regions. Note This attribute is supported in LindormTable 2.5.4 and later versions. The value of this attribute is a series of strings that specify the start key of each pre-split region. The strings are separated by commas (,). However, commas (,) that are included in pairs of double quotation marks are identified as normal characters rather than the separators of the start keys. This attribute cannot be specified together with the NUMREGIONS, STARTKEY, and ENDKEY attributes. If you specify string values for SPLITKEYS, the strings are implicitly converted to values with the same data types as the first column of the primary key. Only the following data types are supported for the SPLITKEYS attribute: SMALLINT INTEGER BIGINT CHAR VARCHAR FLOAT DOUBLE
SPLITALGO	STRING	The splitting algorithm used for pre-partitioning. The following two algorithms are supported: HexStringSplit: splits the primary key based on hexadecimal bytes. UniformSplit: evenly splits the primary key based on the original bytes in the primary key.
DYNAMIC_COLUMNS	STRING	Specifies whether to enable dynamic columns. Default value: False. Valid values: True: Enable dynamic columns for the table. False: Disable dynamic columns for the table. Note Dynamic columns support only the VARBINARY data type. For more information about dynamic columns, see Dynamic columns.
VERSIONS	STRING	The number of versions that are retained for column data. The value of this parameter is an integer greater than or equal to 1. The default value of this parameter is 1, which indicates that only one version is retained for column data. Lindorm allows you to retain multiple versions of column data. For more information, see Use hints to implement data versioning. Important If you set VERSIONS to a large value, the query and storage performance of the table may be degraded. We recommend that you do not set this attribute to a large value. Recommended value: 1.
BLOB_BUCKET_NAME	STRING	The name of the OSS bucket that is created for tables that contain BLOB columns. The bucket name must meet the following requirements: The name can contain only lowercase letters, digits, periods (.), and hyphens (-). The name must be 3 to 63 characters in length. The name cannot start or end with a hyphen (-). The name cannot contain consecutive periods (.). Note This attribute is supported by LindormTable 2.6.4 and later. If you do not specify a BLOB column when you create a table but configure the BLOB_BUCKET_NAME attribute, Lindorm does not check the validity of the specified bucket name.

Note

LindormTable whose version is earlier than 2.2.16 does not support the WITH keyword. In this case, you must enclose the attribute name with a pair of quotation marks ('). The attribute value can be configured based on the data type of the attribute. If the attribute value is a string, you must enclose the string with a pair of quotation marks ('). Example: CREATE TABLE IF NOT EXISTS t1(c1 varchar, c2 bigint, c3 int, c4 int, PRIMARY KEY(c1,c2)) 'CONSISTENCY'='strong';.

Examples

You can execute the following statement to create a table that supports all geometry data types:

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

You can execute the following statement to write different types of geometry data to the table:

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

You can execute the following statement to create a table that supports only point objects:
```
CREATE TABLE pts(gid INT, g GEOMETRY(POINT), PRIMARY KEY(gid);
```
You can execute the following statement to write point objects to the table:
```
UPSERT INTO pts(gid, g) VALUES(0, ST_MakePoint(0,0)),(1, ST_MakePoint(1,1));
```
You can execute the following statement to create a table that supports only polygon objects:
```
CREATE TABLE polys(gid INT, g GEOMETRY(POLYGON), PRIMARY KEY(gid));
```
You can execute the following statement to write a polygon object to the table:
```
UPSERT INTO polys(gid,g) VALUES(0,ST_GeomFromText('POLYGON((2 2, 2 8, 8 8, 8 2, 2 2))'));
```
You can execute the following statement to create a table that supports only LineString objects:
```
CREATE TABLE lines(gid INT, g GEOMETRY(LINESTRING), PRIMARY KEY(gid));
```
You can execute the following statement to write a LineString object to the table:
```
UPSERT INTO lines(gid,g) VALUES(0, ST_GeomFromText('LINESTRING(-12.2 4.3, -10.2 4.3)'));
```
You can execute the following statement to create a table that supports only MultiPoint objects:
```
CREATE TABLE multipoints(gid INT, g GEOMETRY(MULTIPOINT), PRIMARY KEY(gid));
```
You can execute the following statement to write a MultiPoint object to the table:
```
UPSERT INTO multipoints(gid,g) VALUES(0, ST_GeomFromText('MULTIPOINT (10 40, 40 30, 20 20, 30 10)'));
```

You can execute the following statement to create a table that supports only MultiLineString objects:

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

You can execute the following statement to write a MultiLineString object to the table:

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

You can execute the following statement to create a table that supports only MultiPolygon objects:

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

You can execute the following statement to write a MultiPolygon object to the table:

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

You can execute the following statement to create a table that supports only GeometryCollection objects:

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

You can execute the following statement to write a GeometryCollection object to the table:

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

You can execute the following statement to create a table that contains multiple columns of different geometry data:
```
CREATE TABLE mix(gid INT, pt GEOMETRY(POINT), ply GEOMETRY(POLYGON), PRIMARY KEY(gid));
```