使用CREATE TABLE語句建表 - Lindorm

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）

主鍵是表中資料的唯一標識，由一列或多列組成，建表時必須指定主鍵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天（202460601000ms=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;查看錶是否已建立成功。
在叢集管理系統的概覽頁面，單擊目標資料庫下的目標表名。在當前詳情表格地區，單擊查看錶屬性，查看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;語句，驗證建表結果。