全部产品
Search
文档中心

云原生多模数据库 Lindorm:CREATE TABLE

更新时间:Sep 10, 2024

CREATE TABLE语句用于在Lindorm宽表引擎或时序引擎中创建表,支持多种数据类型,例如VARCHAR、TIMESTAMP、BIGINT等;支持多种表属性,例如数据有效期TTL、压缩算法COMPRESSION等。您可以灵活搭配各种表属性和数据类型,设计出更贴合业务场景的表。

引擎与版本

  • CREATE TABLE适用于宽表引擎和时序引擎。

  • 时序引擎自3.4.32版本开始支持标准CREATE TABLE语法。如何查看或升级时序引擎版本,请参见时序引擎版本说明升级小版本

语法

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)

列定义(column_definition)

主键(primary_key)

分区(partition_definition)

✖️

表属性(table_options)

✖️

使用说明

表名(table_identifier)

关于表名的设置,您需要注意以下内容:

  • 可包含数字、大写英文字符、小写英文字符、半角句号(.)、中划线(-)和下划线(_)。

  • 表名不能以半角句号(.)或中划线(-)开头。

  • 表名的长度为1~255字符。

列定义(column_definition

语法要素

是否必填

使用说明

列名(column_identifier)

  • 可包含数字、大写英文字符、小写英文字符、半角句号(.)、中划线(-)和下划线(_)。

  • 不允许使用系统保留关键字作为列名。

  • 长度不能超过255字节。

数据类型(data_type)

支持的数据类型,请参见数据类型

重要

创建时序表时,如果需要使用时间戳(TIMESTAMP)数据类型,请注意以下几点:

  • 只能指定一列为TIMESTAMP类型。

  • 时序引擎对于时间戳的编码和解析,统一按照北京时间(GMT+8)来进行。

  • 在一些较旧的系统中,其Unix时间戳仍然是按照32位进行处理。这类时间戳写入Lindorm时序引擎前建议乘以1000。如果不加转换地将这些时间戳直接写入时序引擎,将会引起语义解释上的偏差。例如,时间戳为1641009600,在较旧系统中会被解释为2022-01-01 12:00:00,但在Lindorm时序引擎中则会被解释为1970-01-20 07:50:09(GMT+8) 

NULL约束

列的值是否允许为NULL。

重要

目前Lindorm SQL不会进行NULL约束的校验,该校验工作是由存储引擎执行的。

NULL约束的校验方式取决于存储引擎的校验规则,不同存储引擎的校验规则不同,因此在使用过程中可能会出现已指定NOT NULL但仍可写入NULL的情况。

为保证数据能够正常写入,请遵循以下统一要求:

  • 主键列不允许为空,即在建表时,主键列必须指定NOT NULL。

  • 对于非主键列,不建议在非主键列的定义中指定NOT NULL。

主键(primary_key)

重要

Lindorm单机版实例不支持PRIMARY KEY。

主键是表中数据的唯一标识,由一列或多列组成,建表时必须指定主键PRIMARY KEY。

在使用CREATE TABLE语句建表时,需要注意以下内容:

引擎类型

主键使用说明

宽表引擎

  • 单个主键列的最大长度为2 KB。

  • 所有主键列的长度之和不能超过30 KB。

  • 单个非主键列的最大长度不能超过2 MB。

时序引擎

  • 时序表的主键列中数据类型为VARCHAR的列又被称作标签(TAG)列,数据类型为TIMESTAMP的列又被称为时间戳列

  • 主键列的数据类型必须是VARCHAR或TIMESTAMP类型。

  • 主键列可以包含一个或多个VARCHAR类型的列,但只能包含一个TIMESTAMP类型的列。

  • 在时序表中建议选择数据源的唯一标识作为PRIMARY KEY,例如物联网场景中的设备ID,车联网场景中的车辆唯一标识,监控场景中的应用ID或ip:port等。更多内容,请参见PRIMARY KEY设计的最佳实践

分区(partition_definition

时序引擎支持分区。在时序表中,用作PARTITION BY的列必须是主键定义中的VARCHAR列

建表时,您可以通过PARTITION BY HASH(column1, column2, ..., columnN)语句,显示指定一个或多个列为表进行Hash分区。例如:PARTITION BY HASH(c1, p1)

表属性(table_options)

宽表引擎支持表属性(table_options)。您可以通过WITH关键字添加以下表属性:

选项

类型

描述

COMPRESSION

STRING

表的压缩算法。可选的压缩算法分别是:

  • SNAPPY

  • ZSTD

  • LZ4

说明

宽表引擎2.3.4版本前默认未指定压缩算法,宽表引擎2.3.4及以后的版本默认压缩算法为ZSTD。

TTL

INT

数据有效期,单位为秒(s)。

说明
  • 默认TTL选项为空,即数据不会过期。

  • 您可以通过在建表语句中添加TTL=<指定时间>为表设置数据有效期,也可以通过将TTL修改为空字符串来取消数据有效期。具体用法可参见示例

COMPACTION_MAJOR_PERIOD

LONG

系统执行major compaction的周期 ,单位为毫秒(ms)。具体用法可参见指定Major Compaction周期

说明

默认值:Math.Min(TTL,1728000000ms)。如果不设置TTL,系统默认该参数值为20天(20*24*60*60*1000ms=1728000000ms)。

MUTABILITY

STRING

索引相关,表示对主表的写入模式进行分类。默认值为MUTABLE_LATEST。

所有取值如下:

  • IMMUTABLE

  • IMMUTABLE_ROWS

  • MUTABLE_LATEST

  • MUTABLE_ALL

取值的详细说明,请参见高性能原生二级索引

重要

创建索引表后,MUTABILITY参数的值不支持修改。

CONSISTENCY

STRING

表的一致性属性。对于多可用区实例,该参数表示主备数据的一致性。包括以下两种级别:

  • eventual:最终一致,默认值。

  • strong:强一致。

重要

对于多可用区实例,如果数据存在先读后写,例如increase、append、索引更新,则需要指定主表CONSISTENCY参数的值为strong,保证主备数据一致。

NUMREGIONS

INT

预先设置建表时的Region数。

CHS

INT

冷热分界线,单位为秒。

说明
  • 设置冷热分界线需要开通冷存储功能,功能介绍和开通方式参考冷热分离介绍

  • 设置冷热分界线选项时,必须同时设置CHS_L2='storagetype=COLD'

STARTKEY和ENDKEY

与PRIMARY KEY中第一个列的数据类型相同

预先设置建表的Region分区起止Key。

说明
  • 如果指定STARTKEY和ENDKEY,必须与NUMREGIONS同时指定。如果在未指定NUMREGIONS的情况下指定STARTKEY和ENDKEY将不会产生实际效果。

  • STARTKEY和ENDKEY中指定的字符串常量值将会隐式转换为PRIMARY KEY中的第一个列的类型用作分区的起止Key,且当前仅支持对以下类型指定STARTKEY和ENDKEY。

    • SMALLINT

    • INTEGER

    • BIGINT

    • CHAR

    • VARCHAR

    • FLOAT

    • DOUBLE

SPLITKEYS

与PRIMARY KEY中第一个列的数据类型相同

预先设置表的全部预分区的起始Key。

说明
  • SPLITKEYS从宽表引擎2.5.4版本开始支持。

  • 指定字符串形式的SPLITKEYS值中通过半角逗号(,)区分各个预分区的起始Key。置于成对双引号中的逗号将会被视作普通字符。

  • SPLITKEYS无法与NUMREGIONS、STARTKEY以及ENDKEY一同使用。

  • 与STARTKEY和ENDKEY一样,指定的字符串常量值将会隐式转换为PRIMARY KEY中的第一个列的类型用作分区的起始Key。仅支持以下数据类型。

    • SMALLINT

    • INTEGER

    • BIGINT

    • CHAR

    • VARCHAR

    • FLOAT

    • DOUBLE

SPLITALGO

STRING

定义预分区的分裂算法。目前仅支持以下分裂算法:

  • HexStringSplit:使用十六进制格式的字节对主键进行切分。

  • UniformSplit:使用原始的Byte值进行平均切分。

DYNAMIC_COLUMNS

STRING

是否开启动态列。取值:

  • True:是。

  • False:否,默认值。

说明

动态列仅支持Varbinary类型。关于动态列的介绍,请参见动态列

VERSIONS

STRING

列值保留的版本数。取值为大于等于1的整数。默认值为1,表示保留一个版本。Lindorm支持列值保留多个版本,多版本管理的详细说明,请参见多版本数据管理

重要

VERSIONS参数的值过大可能会影响数据的查询和存储性能,请尽量避免设置过大的值。建议将VERSIONS的值设置1。

BLOB_BUCKET_NAME

STRING

为包含BLOB列的表创建BUCKET。取值为自定义的BUCKET名称。

BUCKET名称需遵循以下规则:

  • 只能包含小写字母、数字、半角句号(.)、中划线(-)。

  • 长度为3~63个字符。

  • 禁止以中划线(-)作为BUCKET名称的开头或结尾。

  • 名称中不允许包含连续的半角句号(.)。

说明
  • BLOB_BUCKET_NAME从宽表2.6.4版本开始支持。

  • 如果建表时未设置BLOB列,但设置了BLOB_BUCKET_NAME属性,则系统不会触发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;查看表是否已创建成功。

  • 在集群管理系统的概览页面,单击目标数据库下的目标表名。在当前详情表格区域,单击查看表属性,查看COMPRESSIONTTL参数的值。如何进入集群管理系统,请参见登录集群管理系统

指定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;查看表是否已创建成功。

  • 在集群管理系统的概览页面,单击目标数据库下的目标表名。在当前详情表格区域,单击查看表属性,查看CHSCHS_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;查看表是否已创建成功。

  • 在集群管理系统的概览页面,单击目标数据库下的目标表名。在当前详情表格区域,单击查看表属性,查看COMPRESSIONCHSCHS_L2TTL参数的值。

设置分区

创建宽表时预定义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;查看表是否已创建成功。

  • 在集群管理系统的概览页面,单击目标数据库下的目标表名。在分片详情区域,查看各分片的startKeyendKey参数的值。

设置多个分区的起始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;查看表是否已创建成功。

  • 在集群管理系统的概览页面,单击目标数据库下的目标表名。在分片详情区域,查看各分片的startKeyendKey参数的值。

显式指定分区列

由于大部分查询场景会查询单个设备的瞬时数据,因此可以显式指定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;语句,验证建表结果。