CREATE TABLE#

The CREATE TABLE statement is used to create a table. The table name must be unique in one database.

Syntax#

CreateTableStmt ::=
    'CREATE' 'TABLE' IfNotExists TableName ( 
      TableElementList CreateTableSelectOpt | LikeTableWithOrWithoutParen ) OnCommitOpt
IfNotExists ::=
    ('IF' 'NOT' 'EXISTS')?
TableName ::=
    Identifier ('.' Identifier)?
    
TableElementList ::=
    TableElement ( ',' TableElement )*
TableElement ::=
    ColumnDef | ColumnIndex

The TableElementList needs to be defined in the CREATE TABLE statement. TableElementList consists of ColumnDef (column definition) and ColumnIndex. OpenMLDB requires at least one ColumnDef in the TableElementList.

Or use Hive tables and Parquet files to create new tables.

CreateTableStmt ::=
    'CREATE' 'TABLE' TableName LIKE LikeType PATH

TableName ::=
    Identifier ('.' Identifier)?

LikeType ::=
    'HIVE' | 'PARQUET'

PATH ::=
    string_literal

Here is the known issues of creating tables with Hive.

  • May get timeout for the default CLI config and need to show tables to check result.

  • The column constraints of Hive tables such as NOT NULL will not copy to new tables.

ColumnDef (required)#

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

A table contains one or more columns. The column description ColumnDef for each column describes the column name, column type, and options.

  • ColumnName: The name of the column in the table. Column names within the same table must be unique.

  • ColumnType: The data type of the column. To learn about the data types supported by OpenMLDB, please refer to Data Types.

  • ColumnOptionList:

    • NOT NULL: The column does not allow null values.

    • DEFAULT: The default value of this column. It is recommended to configure the default value if NOT NULL is configured. In this case, when inserting data, if the value of the column is not defined, the default value will be inserted. If the NOT NULL attribute is configured but the DEFAULT value is not configured, OpenMLDB will throw an error when the change column value is not defined in the INSERT statement.

Example#

Example 1: Create a Table

The following SQL commands set the current database to db1 and create a table t1 in the current database, including the column named col0. The data type of col0 is STRING.

CREATE DATABASE db1;
-- SUCCEED
USE db1;
-- SUCCEED: Database changed
CREATE TABLE t1(col0 STRING);
-- SUCCEED

The following SQL command shows how to create a table in a database which is not the database currently used.

CREATE TABLE db1.t2 (col0 STRING, col1 int);
-- SUCCEED

Switch to database db1 to see the details of the table just created.

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
 --------------

Example 2: Create a Duplicate Table

The following SQL command creates a table, whose name is the same as an existing table of this database.

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

Example 3: Create a Table with NOT NULL on Certain Columns

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
 --------------

Example 4: Create a Table with Default Value

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
 --------------

Example 5: Create a Table from the Hive table

At first configure OpenMLDB to support Hive, then create table with the following SQL.

CREATE TABLE t1 LIKE HIVE 'hive://hive_db.t1';
-- SUCCEED

Example 6: Create a Table from the Parquet files

CREATE TABLE t1 LIKE PARQUET 'file://t1.parquet';
-- SUCCEED

ColumnIndex (optional)#

ColumnIndex ::= 
    'INDEX' <OptionalIndexName> '(' IndexOptionList ')' 
 
IndexOptionList ::= 
    IndexOption ( ',' IndexOption )*
   
IndexOption ::= 
    IndexOptionName '=' expr

Indexes can be used by database search engines to speed up data retrieval. Simply put, an index is a pointer to the data in a table. Configuring a column index generally requires configuring the index key (KEY), index time column (TS), TTL and TTL_TYPE. The index key must be configured, and other configuration items are optional. The following table introduces these configuration items in detail.

Configuration Item

Note

Expression

Example

KEY

It defines the index column (required). OpenMLDB supports single-column indexes as well as joint indexes. When KEY=one column, a single-column index is configured. When KEY=multiple columns, the joint index of these columns is configured: several columns are concatenated into a new string as an index in order.

Single-column index: ColumnName
Joint index:
(ColumnName (, ColumnName)* )

Single-column index: INDEX(KEY=col1)
Joint index: INDEX(KEY=(col1, col2))

TS

It defines the index time column (optional). Data on the same index will be sorted by the index time column. When TS is not explicitly configured, the timestamp of data insertion is used as the index time. The data type of time column should be BigInt or Timestamp

ColumnName

INDEX(KEY=col1, TS=std_time)。 The index column is col1, and the data rows with the same col1 value are sorted by std_time.

TTL_TYPE

It defines the elimination rules (optional). Including four types. When TTL_TYPE is not explicitly configured, the ABSOLUTE expiration configuration is used by default.

Supported expr: ABSOLUTE
LATEST
ABSORLAT
ABSANDLAT

For specific usage, please refer to Configuration Rules for TTL and TTL_TYP below.

TTL

It defines the maximum survival time/number. Different TTL_TYPEs determines different TTL configuration methods. When TTL is not explicitly configured, TTL=0 which means OpenMLDB will not evict records.

Supported expr: int_literal
interval_literal
( interval_literal , int_literal )

For specific usage, please refer to “Configuration Rules for TTL and TTL_TYPE” below.

Configuration details of TTL and TTL_TYPE:

TTL_TYPE

TTL

Note

Example

ABSOLUTE

The value of TTL represents the expiration time. The configuration value is a time period such as 100m, 12h, 1d, 365d. The maximum configurable expiration time is 15768000m (ie 30 years)

When a record expires, it is eliminated.

INDEX(KEY=col1, TS=std_time, TTL_TYPE=absolute, TTL=100m)
OpenMLDB will delete data older than 100 minutes.

LATEST

The value of TTL represents the maximum number of surviving entries. That is, under the same index, the maximum number of data items allowed exists. Up to 1000 can be configured

When the record exceeds the maximum number, it will be eliminated.

INDEX(KEY=col1, TS=std_time, TTL_TYPE=LATEST, TTL=10). OpenMLDB will only keep the last 10 records and delete the previous records.

ABSORLAT

It defines the expiration time and the maximum number of live records. The configuration value is a 2-tuple of the form (100m, 10), (1d, 1). The maximum can be configured (15768000m, 1000).

Eliminates if and only if the record expires** or if the record exceeds the maximum number of records.

INDEX(key=c1, ts=c6, ttl=(120min, 100), ttl_type=absorlat). When the record exceeds 100, OR when the record expires, it will be eliminated

ABSANDLAT

It defines the expiration time and the maximum number of live records. The configuration value is a 2-tuple of the form (100m, 10), (1d, 1). The maximum can be configured (15768000m, 1000).

When records expire OR records exceed the maximum number of records, records will be eliminated.

INDEX(key=c1, ts=c6, ttl=(120min, 100), ttl_type=absandlat). When there are more than 100 records, OR the records expire, they will also be eliminated.

Example#

Example 1

The following sql example creates a table with a single-column index.

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  
 --- -------------------- ------ ---- ------ --------------- 

Example 2

The following sql example creates a table with a joint index.

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  
 --- -------------------- ----------- ---- ------ --------------- 

Example 3

The following sql example creates a table with a single-column index configuring the time column.

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  
 --- -------------------- ------ ---------- ------ --------------- 

Example 4

The following sql example creates a table with a single-column index configuring the time column, TTL_TYPE and TTL.

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  
 --- -------------------- ------ ---------- ---------- --------------- 

Example 5

The following sql commands create a table with a single-column index and set TTL_TYPE=LATEST.

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  
 --- -------------------- ------ ---------- ----- ------------- 

Example 6

The following sql commands create a table with a single-column index, set TTL_TYPE=absandlat and configure the maximum number of retained records as 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  
 --- -------------------- ------ ---------- -------------- ------------ 

Example 7

The following sql commands create a table with a single-column index, set TTL_TYPE=absorlat and configure the maximum number of retained records as 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  
 --- -------------------- ------ ---------- -------------- ----------- 

Example 8

The following sql commands create a multi-index table.

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
 --- -------------------- ------ ---------- ------ ---------------

Table Property TableOptions (optional)#

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'
CompressTypeOption
						::= 'COMPRESS_TYPE' '=' CompressType
CompressType
						::= 'NoCompress'
						    | 'Snappy

Configuration Item

Note

Example

PARTITIONNUM

It defines the number of partitions for the table. OpenMLDB divides the table into different partition blocks for storage. A partition is the basic unit of storage, replica, and fail-over related operations in OpenMLDB. When not explicitly configured, PARTITIONNUM defaults to 8.

OPTIONS (PARTITIONNUM=8)

REPLICANUM

It defines the number of replicas for the table. Note that the number of replicas is only configurable in Cluster version.

OPTIONS (REPLICANUM=3)

DISTRIBUTION

It defines the distributed node endpoint configuration. Generally, it contains a Leader node and several followers. (leader, [follower1, follower2, ..]). Without explicit configuration, OpenMLDB will automatically configure DISTRIBUTION according to the environment and nodes.

DISTRIBUTION = [ ('127.0.0.1:6527', [ '127.0.0.1:6528','127.0.0.1:6529' ])]

STORAGE_MODE

It defines the storage mode of the table. The supported modes are Memory, HDD and SSD. When not explicitly configured, it defaults to Memory.
If you need to support a storage mode other than Memory mode, tablet requires additional configuration options. For details, please refer to tablet configuration file conf/tablet.flags.

OPTIONS (STORAGE_MODE='HDD')

COMPRESS_TYPE

It defines the compress types of the table. The supported compress type are NoCompress and Snappy. The default value is NoCompress

OPTIONS (COMPRESS_TYPE='Snappy')

The Difference between Disk Table and Memory Table#

  • If the value of STORAGE_MODE is HDD or SSD, the table is a disk table. If STORAGE_MODE is Memory, the table is a memory table.

  • Currently, disk tables do not support GC operations

  • When inserting data into a disk table, if (key, ts) are the same under the same index, the old data will be overwritten; a new piece of data will be inserted into the memory table.

  • Disk tables do not support addindex or deleteindex operations, so you need to define all required indexes when creating a disk table. The deploy command will automatically add the required indexes, so for a disk table, if the corresponding index is missing when it is created, deploy will fail.

Example#

The following sql commands create a table and configure the number of partitions as 8, the number of replicas as 3, and the storage_mode as 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
 --- -------------------- ------ ---------- ------ ---------------
 --------------- --------------
  compress_type   storage_mode
 --------------- --------------
  NoCompress      HDD
 --------------- --------------

The following sql command create a table with specified distribution.

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