CREATE TABLE语句用于在Lindorm宽表引擎或时序引擎中创建表,支持多种数据类型,例如VARCHAR、TIMESTAMP、BIGINT等;支持多种表属性,例如数据有效期TTL、压缩算法COMPRESSION等。您可以灵活搭配各种表属性和数据类型,设计出更贴合业务场景的表。
引擎与版本
语法
create_table_statement ::= CREATE TABLE [ IF NOT EXISTS ] table_identifier
'('
column_definition
( ',' column_definition )*
',' PRIMARY KEY '(' primary_key ')'
')'
[ PARTITION BY partition_definition ]
[ WITH table_options ]
column_definition ::= column_identifier data_type [ NOT NULL ]
primary_key ::= column_identifier [ ',' column_identifier (ASC|DESC)]
partition_definition ::= HASH '(' column_identifier (',' column_identifier )* ')'
table_options ::= '(' option_definition (',' option_definition )* ')'
option_definition ::= option_identifer '=' string_literal
差异说明
Lindorm宽表引擎和时序引擎的CREATE TABLE语法差异较大。差异对比请参见下表。
语法要素 | 宽表引擎 | 时序引擎 |
〇 | 〇 | |
〇 | 〇 | |
〇 | 〇 | |
✖️ | 〇 | |
〇 | ✖️ |
使用说明
表名(table_identifier)
关于表名的设置,您需要注意以下内容:
可包含数字、大写英文字符、小写英文字符、半角句号(.)、中划线(-)和下划线(_)。
表名不能以半角句号(.)或中划线(-)开头。
表名的长度为1~255字符。
列定义(column_definition)
语法要素 | 是否必填 | 使用说明 |
列名(column_identifier) | 是 |
|
数据类型(data_type) | 是 | 支持的数据类型,请参见数据类型。 重要 创建时序表时,如果需要使用时间戳(TIMESTAMP)数据类型,请注意以下几点:
|
NULL约束 | 否 | 列的值是否允许为NULL。 重要 目前Lindorm SQL不会进行NULL约束的校验,该校验工作是由存储引擎执行的。 NULL约束的校验方式取决于存储引擎的校验规则,不同存储引擎的校验规则不同,因此在使用过程中可能会出现已指定NOT NULL但仍可写入NULL的情况。 为保证数据能够正常写入,请遵循以下统一要求:
|
主键(primary_key)
Lindorm单机版实例不支持PRIMARY KEY。
主键是表中数据的唯一标识,由一列或多列组成,建表时必须指定主键PRIMARY KEY。
在使用CREATE TABLE
语句建表时,需要注意以下内容:
引擎类型 | 主键使用说明 |
宽表引擎 |
|
时序引擎 |
|
分区(partition_definition)
仅时序引擎支持分区。在时序表中,用作PARTITION BY的列必须是主键定义中的VARCHAR列。
建表时,您可以通过PARTITION BY HASH(column1, column2, ..., columnN)
语句,显示指定一个或多个列为表进行Hash分区。例如:PARTITION BY HASH(c1, p1)
。
表属性(table_options)
仅宽表引擎支持表属性(table_options)。您可以通过WITH
关键字添加以下表属性:
选项 | 类型 | 描述 |
COMPRESSION | STRING | 表的压缩算法。可选的压缩算法分别是:
说明 宽表引擎2.3.4版本前默认未指定压缩算法,宽表引擎2.3.4及以后的版本默认压缩算法为ZSTD。 |
TTL | INT | 数据有效期,单位为秒(s)。 说明
|
COMPACTION_MAJOR_PERIOD | LONG | 系统执行 说明 默认值:Math.Min(TTL,1728000000ms)。如果不设置TTL,系统默认该参数值为20天(20*24*60*60*1000ms=1728000000ms)。 |
MUTABILITY | STRING | 索引相关,表示对主表的写入模式进行分类。默认值为MUTABLE_LATEST。 所有取值如下:
取值的详细说明,请参见高性能原生二级索引。 重要 创建索引表后,MUTABILITY参数的值不支持修改。 |
CONSISTENCY | STRING | 表的一致性属性。对于多可用区实例,该参数表示主备数据的一致性。包括以下两种级别:
重要 对于多可用区实例,如果数据存在先读后写,例如increase、append、索引更新,则需要指定主表CONSISTENCY参数的值为 |
NUMREGIONS | INT | 预先设置建表时的Region数。 |
CHS | INT | 冷热分界线,单位为秒。 说明
|
STARTKEY和ENDKEY | 与PRIMARY KEY中第一个列的数据类型相同 | 预先设置建表的Region分区起止Key。 说明
|
SPLITKEYS | 与PRIMARY KEY中第一个列的数据类型相同 | 预先设置表的全部预分区的起始Key。 说明
|
SPLITALGO | STRING | 定义预分区的分裂算法。目前仅支持以下分裂算法:
|
DYNAMIC_COLUMNS | STRING | 是否开启动态列。取值:
说明 动态列仅支持Varbinary类型。关于动态列的介绍,请参见动态列。 |
VERSIONS | STRING | 列值保留的版本数。取值为大于等于1的整数。默认值为1,表示保留一个版本。Lindorm支持列值保留多个版本,多版本管理的详细说明,请参见多版本数据管理。 重要 VERSIONS参数的值过大可能会影响数据的查询和存储性能,请尽量避免设置过大的值。建议将VERSIONS的值设置1。 |
BLOB_BUCKET_NAME | STRING | 为包含BLOB列的表创建BUCKET。取值为自定义的BUCKET名称。 BUCKET名称需遵循以下规则:
说明
|
2.2.16版本前的宽表引擎在设置表属性时不支持WITH
关键字,需在表属性关键字前后添加半角单引号('),属性值可以基于类型进行设置。如果属性值的类型是字符串(STRING)则需在字符串前后添加半角单引号('),例如CREATE TABLE IF NOT EXISTS t1(c1 varchar, c2 bigint, c3 int, c4 int, PRIMARY KEY(c1,c2)) 'CONSISTENCY'='strong';
。
示例
创建表
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)
);
结果验证
您可以执行DESCRIBE table sensor;
查看表是否已创建成功。
指定数据有效期和压缩算法
创建宽表时指定数据有效期为30天(2592000秒),压缩算法为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');
结果验证
您可以执行
DESCRIBE table sensor;
查看表是否已创建成功。在集群管理系统的概览页面,单击目标数据库下的目标表名。在当前详情表格区域,单击查看表属性,查看COMPRESSION和TTL参数的值。如何进入集群管理系统,请参见登录集群管理系统。
指定Major Compaction周期
创建宽表时指定Major Compaction的周期为10天(864,000,000毫秒)。
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');
结果验证
您可以执行
DESCRIBE table sensor;
查看表是否已创建成功。在集群管理系统的概览页面,单击目标数据库下的目标表名。在当前详情表格区域,单击查看表属性,查看COMPACTION_MAJOR_PERIOD参数的值。
开启动态列功能
创建宽表时将DYNAMIC_COLUMNS参数的值设置为TRUE
后,支持在表中写入动态列数据。
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');
结果验证
您可以执行
DESCRIBE table sensor;
查看表是否已创建成功。在集群管理系统的概览页面,单击目标数据库下的目标表名。在当前详情表格区域,单击查看表属性,查看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 (CHS = '86400', CHS_L2 = 'storagetype=COLD');
结果验证
您可以执行
DESCRIBE table sensor;
查看表是否已创建成功。在集群管理系统的概览页面,单击目标数据库下的目标表名。在当前详情表格区域,单击查看表属性,查看CHS和CHS_L2参数的值。
同时设置多个属性
创建宽表时指定表的压缩算法、数据有效期以及冷热分界线。
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');
结果验证
您可以执行
DESCRIBE table sensor;
查看表是否已创建成功。在集群管理系统的概览页面,单击目标数据库下的目标表名。在当前详情表格区域,单击查看表属性,查看COMPRESSION、CHS、CHS_L2和TTL参数的值。
设置分区
创建宽表时预定义5个分区,且5个分区的起始Key为1000,终止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');
结果验证
您可以执行
DESCRIBE table sensor;
查看表是否已创建成功。在集群管理系统的概览页面,单击目标数据库下的目标表名。在分片详情区域,查看各分片的startKey和endKey参数的值。
设置多个分区的起始Key
创建宽表时预定义5个分区的起始Key,表创建好后默认包含6个预分区。
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');
结果验证
您可以执行
DESCRIBE table sensor;
查看表是否已创建成功。在集群管理系统的概览页面,单击目标数据库下的目标表名。在分片详情区域,查看各分片的startKey和endKey参数的值。
显式指定分区列
由于大部分查询场景会查询单个设备的瞬时数据,因此可以显式指定device_id作为数据分区的分区列。
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);
结果验证
您可以执行DESCRIBE TABLE sensor;
语句,验证建表结果。