當前伺服器上創建表(單節點) 創建新表具有幾種種語法形式，具體取決於用例。預設情況下，僅在當前伺服器上創建表。分散式DDL查詢作為子句實現，該子句另外描述。語法形式使用顯式架構 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cl ...

當前伺服器上創建表(單節點)

創建新表具有幾種種語法形式，具體取決於用例。預設情況下，僅在當前伺服器上創建表。分散式DDL查詢作為子句實現，該子句另外描述。

語法形式

使用顯式架構

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

使用[db_name.]參數可以為數據表指定資料庫，如果不指定此參數，則預設會使用default資料庫。

末尾的ENGINE參數，它被用於指定數據表的引擎。表引擎決定了數據表的特性，也決定了數據將會被如何存儲及載入。例如示例中使用的Memory表引擎，是ClickHouse最簡單的表引擎，數據只會被保存在記憶體中，在服務重啟時數據會丟失。

從相同結構的表複製創建

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

創建與另一個表具有相同結構的表。您可以為表指定其他引擎。如果未指定引擎，則將使用與表相同的引擎。

從表函數創建

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()

創建與指定表函數的結果相同的表。創建的表也將以與指定的相應表函數相同的方式工作。

什麼是表函數？簡單來說就是一個可以返回一張表的函數。下麵是一個表函數的例子，from後面跟著的就是一個表函數。

CREATE TABLE Orders
ENGINE = MergeTree
ORDER BY OrderID AS
SELECT *
FROM mysql('10.42.134.136:4000', 'databas', 'Orders', 'root', '1234')

目前的表函數有下麵幾個，這裡暫時不展開講。

ClickHouse表函數

從選擇查詢創建

CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...

使用引擎創建具有類似於查詢結果的結構的表，並使用來自的數據填充該表。創建的時候，還可以顯式指定列說明。

使用IF NOT EXISTS，如果表已存在且已指定，則查詢不會執行任何操作。

查詢中的子句之後可以有其他子句。

分散式集群創建表

ClickHouse支持集群模式，一個集群擁有1到多個節點。CREATE、ALTER、DROP、RENMAE及TRUNCATE這些DDL語句，都支持分散式執行。這意味著，如果在集群中任意一個節點上執行DDL語句，那麼集群中的每個節點都會以相同的順序執行相同的語句。這項特性意義非凡，它就如同批處理命令一樣，省去了需要依次去單個節點執行DDL的煩惱。
將一條普通的DDL語句轉換成分散式執行十分簡單，只需加上ON CLUSTER cluster_name聲明即可。

例如，執行下麵的語句後將會對ch_cluster集群內的所有節點廣播這條DDL語句：

CREATE TABLE partition_v3 ON CLUSTER ch_cluster( 
    ID String,
    URL String,
    EventTime Date
) ENGINE =  MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID

ch_cluster是集群的名稱。

臨時表

ClickHouse也有臨時表的概念，創建臨時表的方法是在普通表的基礎之上添加TEMPORARY關鍵字，它的完整語法如下所示：

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
)

相比普通表而言，臨時表有如下兩點特殊之處：

它的生命周期是會話綁定的，所以它只支持Memory表引擎，如果會話結束，數據表就會被銷毀；
臨時表不屬於任何資料庫，所以在它的建表語句中，既沒有資料庫參數也沒有表引擎參數。

臨時表的優先順序是大於普通表的。當兩張數據表名稱相同的時候，會優先讀取臨時表的數據。

分區表

數據分區（partition）和數據分片（shard）是完全不同的兩個概念。數據分區是針對本地數據而言的，是數據的一種縱向切分。而數據分片是數據的一種橫向切分。藉助數據分區，在後續的查詢過程中能夠跳過不必要的數據目錄，從而提升查詢的性能。
不是所有的表引擎都可以使用分區，目前只有合併樹（MergeTree）家族系列的表引擎才支持數據分區。由PARTITION BY指定分區鍵，下麵的數據表partition_00使用了日期欄位作為分區鍵，並將其格式化為年月的形式：

CREATE TABLE partition_00 ( 
    ID String,
    URL String,
    EventTime Date
) ENGINE =  MergeTree()
PARTITION BY toYYYYMM(EventTime) 
ORDER BY ID

創建表語句關鍵字解析

空值或非空修飾符

列定義中數據類型後面的修飾符可以指定允許或不允許其值為Null。

CREATE TABLE Orders
(

    `order_id` String,
    `created_at` Nullable(DateTime),
    `updated_at` Nullable(DateTime)
)
ENGINE = MergeTree
ORDER BY (order_id)
SETTINGS index_granularity = 8192

上面的例子中created_at和updated_at可以插入一個NULL值，反之不可以。

預設值表達式

[DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1]

表欄位支持四種預設值表達式的定義方法，分別是DEFAULT、MATERIALIZED、EPHEMERAL和ALIAS。無論使用哪種形式，表欄位一旦被定義了預設值，它便不再強制要求定義數據類型，因為ClickHouse會根據預設值進行類型推斷。

例：URLDomain String DEFAULT domain(URL)

預設值表達式的定義方法之間也存在著不同之處，可以從如下三個方面進行比較。

數據寫入：在數據寫入時，只有DEFAULT類型的欄位可以出現在INSERT語句中。而MATERIALIZED、EPHEMERAL和ALIAS都不能被顯式賦值，它們只能依靠計算取值。例如試圖為MATERIALIZED類型的欄位寫入數據，將會得到如下的錯誤。
DB::Exception: Cannot insert column URL, because it is MATERIALIZED column..
數據查詢：在數據查詢時，只有DEFAULT類型的欄位可以通過SELECT *返回。而MATERIALIZED、EPHEMERAL和ALIAS類型的欄位不會出現在SELECT *查詢的返回結果集中。
數據存儲：在數據存儲時，只有DEFAULT和MATERIALIZED類型的欄位才支持持久化。如果使用的表引擎支持物理存儲（例如TinyLog表引擎），那麼這些列欄位將會擁有物理存儲。而ALIAS、EPHEMERAL類型的欄位不支持持久化，它的取值總是需要依靠計算產生，數據不會落到磁碟。
EPHEMERAL只能在CREATE語句的預設值中引用。

怎麼理解這四種表達式呢？它其實就是列值的四種生成方式。DEFAULT是在插入的時候計算填充，MATERIALIZED和ALIAS是在查詢的時候，或者說用到的時候填充，而EPHEMERAL，有點類似於我們在建表的時候，創建一個變數，一個代碼塊。

一般表達式

DEFAULT expr

正常預設值。如果INSERT查詢未指定相應的列，則將通過計算相應的表達式來填充它。

物化表達式

MATERIALIZED expr

物化欄位列。這樣的欄位不能在INSERT語句中指定值插入，因為這樣的欄位總是通過使用其他欄位計算出來的。

臨時表達式

EPHEMERAL expr

臨時欄位列。這樣的列不存儲在表中，不能被SELECT 查詢，但可以在CREATE語句的預設值中引用。

別名錶達式

ALIAS expr

欄位別名。這樣的列根本不存儲在表中。其值不能插入到表中，並且在通過SELECT * 查詢，不會出現在結果集。如果在查詢分析期間擴展了別名，則可以在SELECT中使用它。

主鍵

您可以在創建表時定義主鍵。可以通過兩種方式指定主鍵：

-- 內部定義
CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...,
    PRIMARY KEY(expr1[, expr2,...])]
)
ENGINE = engine;

-- 外部定義
CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
PRIMARY KEY(expr1[, expr2,...]);

警告:不能在一個查詢中以兩種方式組合。

約束

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    ...
    CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
    ...
) ENGINE = engine

boolean_expr_1可以通過任何布爾表達式。如果為表定義了約束，則將針對查詢中的每一行檢查每個約束。如果不滿足任何約束，伺服器將引發包含約束名稱和檢查表達式的異常。INSERT添加大量約束可能會對大型查詢的性能產生負面影響。

數據TTL

TTL即Time To Live，表達式正常為：TTL expr1。只能為合併樹族表指定。

它表示數據的存活時間。在MergeTree中，可以為某個列欄位或整張表設置TTL。當時間到達時，如果是列欄位級別的TTL，則會刪除這一列的數據；如果是表級別的TTL，則會刪除整張表的數據；如果同時設置了列級別和表級別的TTL，則會以先到期的那個為主。

無論是列級別還是表級別的TTL，都需要依托某個DateTime或Date類型的欄位，通過對這個時間欄位的INTERVAL操作，來表述TTL的過期時間。

如下麵的例子。

-- 表示數據的存活時間是time_col時間的3天之後。
TTL time_col + INTERVAL 3 DAY 上述

-- 表示數據的存活時間是time_col時間的1月之後
TTL time_col + INTERVAL 1 MONTH。

-- INTERVAL完整的操作包括SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR。

列級別TTL

CREATE TABLE ttl_table_v1(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 10 SECOND,
    type UInt8 TTL create_time + INTERVAL 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id

表級別TTL

CREATE TABLE ttl_table_v2(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 1 MINUTE,
    type UInt8
)ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY

clickhouse壓縮與編碼

列壓縮

我們可以每個單獨列定義壓縮方法,這樣可以減少數據存儲的空間。可以指定編解碼器以引用預設壓縮，這可能取決於運行時中的不同設置（和數據屬性）

CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(LZ4HC),
    float_value Float32 CODEC(NONE),
    double_value Float64 CODEC(LZ4HC(9)),
    value Float32 CODEC(Delta, ZSTD)
)
ENGINE = <Engine>
...

下表引擎支持壓縮：