CREATE TABLE
Contents
CREATE TABLE#
CREATE TABLE
语句用于创建一张表。同一个数据库下,表名必须是唯一的,在同一个数据库下,重复创建同名表,会发生错误。
Syntax#
CreateTableStmt ::=
'CREATE' 'TABLE' IfNotExists TableName ( TableElementList CreateTableSelectOpt | LikeTableWithOrWithoutParen ) OnCommitOpt
IfNotExists ::=
('IF' 'NOT' 'EXISTS')?
TableName ::=
Identifier ('.' Identifier)?
TableElementList ::=
TableElement ( ',' TableElement )*
TableElement ::=
ColumnDef | ColumnIndex
建表语句中需要定义TableElementList
,即TableElement
列表。TableElement
分为列描述ColumnDef
和列索引ColumnIndex
。OpenMLDB要求TableElement
列表中至少包含一个ColumnDef
。
或者基于 LIKE
语法建表,目前支持基于 Hive 和 Parquet 格式。
CreateTableStmt ::=
'CREATE' 'TABLE' TableName LIKE LikeType PATH
TableName ::=
Identifier ('.' Identifier)?
LikeType ::=
'HIVE' | 'PARQUET'
PATH ::=
string_literal
基于 Hive 建表的详情可查看文档 Hive 数据源支持。
列描述ColumnDef(必要)#
ColumnDef ::=
ColumnName ( ColumnType ) [ColumnOptionList]
ColumnName ::=
Identifier ( '.' Identifier ( '.' Identifier )? )?
ColumnType ::=
'INT' | 'INT32'
|'SMALLINT' | 'INT16'
|'BIGINT' | 'INT64'
|'FLOAT'
|'DOUBLE'
|'TIMESTAMP'
|'DATE'
|'BOOL'
|'STRING' | 'VARCHAR'
ColumnOptionList ::=
ColumnOption*
ColumnOption ::=
['DEFAULT' DefaultValueExpr ] ['NOT' 'NULL']
DefaultValueExpr ::=
int_literal | float_literal | double_literal | string_literal
一张表中包含一个或多个列。每一列的列描述ColumnDef
描述了列名、列类型以及列约束配置。
列名:列在表中的名字。同一张表内的列名必须是唯一的。
列类型:列的类型。关于OpenMLDB支持的数据类型,详见数据类型。
列约束配置:
NOT NULL
: 该列的取值不允许为空。DEFAULT
: 设置该列的默认值。NOT NULL
的属性推荐同时配置DEFAULT
默认值,在插入数据时,若没有定义该列的值,会插入默认值。若设置了NOT NULL
属性但没有配置DEFAULT
值,插入语句中未定义该列值时,OpenMLDB会抛出错误。
Example#
示例1:创建一张表
将当前数据库设为db1
,在当前数据库中创建一张表t1
,包含列col0
,列类型为STRING
CREATE DATABASE db1;
-- SUCCEED
USE db1;
-- SUCCEED: Database changed
CREATE TABLE t1(col0 STRING);
-- SUCCEED
假如当前会话不在数据库db1
下,但是仍要在db1
中创建一张表t2
,包含列col0
,列类型为STRING;列col1
,列类型为int。
CREATE TABLE db1.t2 (col0 STRING, col1 int);
-- SUCCEED
切换到数据库db1
,查看表t2
的详细信息。
USE db1;
-- SUCCEED: Database changed
desc t2;
--- ------- --------- ------ ---------
# Field Type Null Default
--- ------- --------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
--- ------- --------- ------ ---------
--- -------------------- ------ ---- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---- ------ ---------------
1 INDEX_0_1639524201 col0 - 0min kAbsoluteTime
--- -------------------- ------ ---- ------ ---------------
--------------
storage_mode
--------------
Memory
--------------
示例2:在同一个数据库下重复创建同名表
CREATE TABLE t1 (col0 STRING NOT NULL, col1 int);
-- SUCCEED
CREATE TABLE t1 (col0 STRING NOT NULL, col1 int);
-- Error: table already exists
CREATE TABLE t1 (col0 STRING NOT NULL, col1 string);
-- Error: table already exists
示例3:创建一张表,配置列不允许为空(NOT NULL)
USE db1;
-- SUCCEED: Database changed
CREATE TABLE t3 (col0 STRING NOT NULL, col1 int);
-- SUCCEED
查看该表的详细信息
desc t3;
--- ------- --------- ------ ---------
# Field Type Null Default
--- ------- --------- ------ ---------
1 col0 Varchar NO
2 col1 Int YES
--- ------- --------- ------ ---------
--- -------------------- ------ ---- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---- ------ ---------------
1 INDEX_0_1657327434 col0 - 0min kAbsoluteTime
--- -------------------- ------ ---- ------ ---------------
--------------
storage_mode
--------------
Memory
--------------
示例4:创建一张表,设置列默认值
USE db1;
--SUCCEED: Database changed
CREATE TABLE t4 (col0 STRING DEFAULT "NA", col1 int);
-- SUCCEED
desc t4;
--- ------- --------- ------ ---------
# Field Type Null Default
--- ------- --------- ------ ---------
1 col0 Varchar YES NA
2 col1 Int YES
--- ------- --------- ------ ---------
--- -------------------- ------ ---- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---- ------ ---------------
1 INDEX_0_1657327593 col0 - 0min kAbsoluteTime
--- -------------------- ------ ---- ------ ---------------
--------------
storage_mode
--------------
Memory
--------------
示例5:基于 Hive 表创建新表
首先配置OpenMLDB支持Hive,然后使用以下语句。
CREATE TABLE db1.t1 LIKE HIVE 'hive://hive_db.t1';
-- SUCCEED
注意目前创建的表名必须包括DB名。
示例6:基于 Parquet 文件创建新表
CREATE TABLE db1.t1 LIKE PARQUET 'file://t1.parquet';
-- SUCCEED
注意目前创建的表名必须包括DB名。
列索引ColumnIndex(可选)#
ColumnIndex ::=
'INDEX' <OptionalIndexName> '(' IndexOptionList ')'
IndexOptionList ::=
IndexOption ( ',' IndexOption )*
IndexOption ::=
IndexOptionName '=' expr
索引可以被数据库搜索引擎用来加速数据的检索。 简单说来,索引就是指向表中数据的指针。OpenMLDB 支持的索引配置项(IndexOptionName
)有索引KEY
,索引时间列TS
, 最大存活时间/条数TTL
和淘汰规则TTL_TYPE
。其中KEY
是必须配置的,其他配置项都为可选项。下表介绍了各索引配置项的含义、支持的表达式(expr
)以及用法示例:
配置项 |
描述 |
expr |
用法示例 |
---|---|---|---|
|
索引列(必选)。OpenMLDB支持单列索引,也支持联合索引。当 |
支持单列索引: |
单列索引: |
|
索引时间列(可选)。同一个索引上的数据将按照时间索引列排序。当不显式配置 |
|
|
|
淘汰规则(可选)。包括四种类型,当不显式配置 |
支持的expr如下: |
具体用法可以参考下文“TTL和TTL_TYPE的配置细则” |
|
最大存活时间/条数(可选)。依赖于 |
支持数值: |
具体用法可以参考下文“TTL和TTL_TYPE的配置细则” |
TTL和TTL_TYPE的配置细则:
TTL_TYPE |
TTL |
描述 |
用法示例 |
---|---|---|---|
|
TTL的值代表过期时间。配置值为时间段如 |
当记录过期时,会被淘汰。 |
|
|
TTL的值代表最大存活条数。即同一个索引下面,最大允许存在的数据条数。最大可以配置1000条 |
记录超过最大条数时,会被淘汰。 |
|
|
配置过期时间和最大存活条数。配置值是一个2元组,形如 |
当且仅当记录过期或记录超过最大条数时,才会淘汰。 |
|
|
配置过期时间和最大存活条数。配置值是一个2元组,形如 |
当记录过期且记录超过最大条数时,记录会被淘汰。 |
|
Example#
示例1:创建一张带单列索引的表
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1));
-- SUCCEED
desc t1;
--- ---------- ----------- ------ ---------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ------ ---- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---- ------ ---------------
1 INDEX_0_1639524520 col1 - 0min kAbsoluteTime
--- -------------------- ------ ---- ------ ---------------
示例2:创建一张带联合列索引的表
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=(col0, col1)));
-- SUCCEED
desc t1;
--- ---------- ----------- ------ ---------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ----------- ---- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ----------- ---- ------ ---------------
1 INDEX_0_1639524576 col0|col1 - 0min kAbsoluteTime
--- -------------------- ----------- ---- ------ ---------------
示例3:创建一张带单列索引+时间列的表
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time));
-- SUCCEED
desc t1;
--- ---------- ----------- ------ ---------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ------ ---------- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---------- ------ ---------------
1 INDEX_0_1639524645 col0 std_time 0min kAbsoluteTime
--- -------------------- ------ ---------- ------ ---------------
示例4:创建一张带单列索引+时间列的TTL type为abusolute表,并配置ttl为30天
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=absolute, TTL=30d));
-- SUCCEED
desc t1;
--- ---------- ----------- ------ ---------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ------ ---------- ---------- ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---------- ---------- ---------------
1 INDEX_0_1639524729 col1 std_time 43200min kAbsoluteTime
--- -------------------- ------ ---------- ---------- ---------------
示例5:创建一张带单列索引+时间列的TTL type为latest表,并配置ttl为1
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=latest, TTL=1));
-- SUCCEED
desc t1;
--- ---------- ----------- ------ ---------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ------ ---------- ----- -------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---------- ----- -------------
1 INDEX_0_1639524802 col1 std_time 1 kLatestTime
--- -------------------- ------ ---------- ----- -------------
示例6:创建一张带单列索引+时间列的TTL type为absANDlat表,并配置过期时间为30天,最大留存条数为10条
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=absandlat, TTL=(30d,10)));
-- SUCCEED
desc t1;
--- ---------- ----------- ------ ---------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ------ ---------- -------------- ------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---------- -------------- ------------
1 INDEX_0_1639525038 col1 std_time 43200min&&10 kAbsAndLat
--- -------------------- ------ ---------- -------------- ------------
示例7:创建一张带单列索引+时间列的TTL type为absORlat表,并配置过期时间为30天,最大留存条数为10条
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=absorlat, TTL=(30d,10)));
--SUCCEED
desc t1;
--- ---------- ----------- ------ ---------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ------ ---------- -------------- -----------
# name keys ts ttl ttl_type
--- -------------------- ------ ---------- -------------- -----------
1 INDEX_0_1639525079 col1 std_time 43200min||10 kAbsOrLat
--- -------------------- ------ ---------- -------------- -----------
示例8:创建一张多索引的表
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col0, TS=std_time), INDEX(KEY=col1, TS=std_time));
--SUCCEED
desc t1;
--- ---------- ----------- ------ ---------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ------ ---------- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---------- ------ ---------------
1 INDEX_0_1648692457 col0 std_time 0min kAbsoluteTime
2 INDEX_1_1648692457 col1 std_time 0min kAbsoluteTime
--- -------------------- ------ ---------- ------ ---------------
表属性TableOptions(可选)#
TableOptions
::= 'OPTIONS' '(' TableOptionItem (',' TableOptionItem)* ')'
TableOptionItem
::= PartitionNumOption
| ReplicaNumOption
| DistributeOption
| StorageModeOption
PartitionNumOption
::= 'PARTITIONNUM' '=' int_literal
ReplicaNumOption
::= 'REPLICANUM' '=' int_literal
DistributeOption
::= 'DISTRIBUTION' '=' DistributionList
DistributionList
::= DistributionItem (',' DistributionItem)*
DistributionItem
::= '(' LeaderEndpoint ',' FollowerEndpointList ')'
LeaderEndpoint
::= Endpoint
FollowerEndpointList
::= '[' Endpoint (',' Endpoint)* ']'
Endpoint
::= string_literals
StorageModeOption
::= 'STORAGE_MODE' '=' StorageMode
StorageMode
::= 'Memory'
| 'HDD'
| 'SSD'
配置项 |
描述 |
用法示例 |
---|---|---|
|
配置表的分区数。OpenMLDB将表分为不同的分区块来存储。分区是OpenMLDB的存储、副本、以及故障恢复相关操作的基本单元。不显式配置时, |
|
|
配置表的副本数。请注意,副本数只有在集群版中才可以配置。 |
|
|
配置分布式的节点endpoint。一般包含一个Leader节点和若干Follower节点。 |
|
|
表的存储模式,支持的模式有 |
|
磁盘表与内存表区别#
磁盘表对应
STORAGE_MODE
的取值为HDD
或SSD
。内存表对应的STORAGE_MODE
取值为Memory
。目前磁盘表不支持GC操作
磁盘表插入数据,同一个索引下如果(
key
,ts
)相同,会覆盖旧的数据;内存表则会插入一条新的数据磁盘表不支持
addindex
和deleteindex
操作,所以创建磁盘表的时候需要定义好所有需要的索引 (deploy
命令会自动添加需要的索引,所以对于磁盘表,如果创建的时候缺失对应的索引,则deploy
会失败)
Example#
创建一张表,配置分片数为8,副本数为3,存储模式为HDD
USE db1;
--SUCCEED: Database changed
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time)) OPTIONS(partitionnum=8, replicanum=3, storage_mode='HDD');
--SUCCEED
DESC t1;
--- ---------- ----------- ------ ----------
# Field Type Null Default
--- ---------- ----------- ------ ---------
1 col0 Varchar YES
2 col1 Int YES
3 std_time Timestamp YES
--- ---------- ----------- ------ ---------
--- -------------------- ------ ---------- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---------- ------ ---------------
1 INDEX_0_1651143735 col1 std_time 0min kAbsoluteTime
--- -------------------- ------ ---------- ------ ---------------
--------------
storage_mode
--------------
HDD
--------------
创建一张表,指定分片的分布状态
create table t1 (col0 string, col1 int) options (DISTRIBUTION=[('127.0.0.1:30921', ['127.0.0.1:30922', '127.0.0.1:30923']), ('127.0.0.1:30922', ['127.0.0.1:30921', '127.0.0.1:30923'])]);
--SUCCEED