全部產品
Search
文件中心

Lindorm:CREATE TABLE

更新時間:Sep 11, 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;語句,驗證建表結果。