表引擎 - ApsaraDB for ClickHouse

雲資料庫ClickHouse支援的表引擎分為MergeTree、Log、Integrations和Special四個系列。本文主要對這四類表引擎進行概要介紹，並通過樣本介紹常用表引擎的功能。

概述

表引擎即表的類型，在雲資料庫ClickHouse中決定了如何儲存和讀取資料、是否支援索引、是否支援主備複製等。雲資料庫ClickHouse支援的表引擎，請參見下表。

系列	描述	表引擎	特點
MergeTree	MergeTree系列引擎適用於高負載任務，支援巨量資料量的快速寫入並進行後續的資料處理，通用程度高且功能強大。該系列引擎的共同特點是支援資料副本、分區、資料採樣等特性。	MergeTree	用於插入極大量的資料到一張表中，資料以資料片段的形式一個接著一個的快速寫入，資料片段按照一定的規則進行合并。
		Data Replication	用於將資料從一個節點複製到其他節點，並保證資料的一致性。
		Custom Partitioning Key	用於自訂資料的分區，根據您的需求定義分區鍵，以將資料分布到不同的分區中。
		ReplacingMergeTree	用於解決MergeTree表引擎相同主鍵無法去重的問題，可以刪除主索引值相同的重複項。
		CollapsingMergeTree	在建表語句中新增標記列`Sign`，用於消除ReplacingMergeTree表引擎的如下功能限制。在分布式情境下，相同主鍵的資料可能被分布到不同節點上，不同分區間可能無法去重。在沒有徹底optimize之前，可能無法達到主鍵去重的效果，比如部分資料已經被去重，而另外一部分資料仍舊有主鍵重複。 optimize是後台動作，無法預測具體執行時間點。手動執行optimize在海量資料情境下需要消耗大量時間，無法滿足業務即時查詢的需求。
		VersionedCollapsingMergeTree	在建表語句中新增`Version`列，用於解決CollapsingMergeTree表引擎亂序寫入導致無法正常摺疊（刪除）的問題。
		SummingMergeTree	用於對主鍵列進行預先彙總，將所有相同主鍵的行合并為一行，從而大幅度降低儲存空間佔用，提升彙總計算效能。
		AggregatingMergeTree	預先彙總引擎的一種，用於提升彙總計算的效能，可以指定各種彙總函式。
		GraphiteMergeTree	用於儲存Graphite資料並進行匯總，可以減少儲存空間，提高Graphite資料的查詢效率。
		Approximate Nearest Neighbor Search Indexes	用於近似最近鄰搜尋的索引引擎，在大規模資料集中高效地尋找最接近給定查詢點的資料點。
		Full-text Search using Inverted Indexes	使用倒排索引進行全文檢索搜尋，用於在大規模文本資料中進行全文檢索搜尋和檢索。
Log	Log系列引擎適用於快速寫入小表（1百萬行左右的表）並讀取全部資料的情境。該系列引擎的共同特點如下。資料被追加寫入磁碟中。不支援`delete`、`update`。不支援索引。不支援原子性寫。 `insert`會阻塞`select`操作。	TinyLog	不支援並發讀取資料檔案，格式簡單，查詢效能較差，適用於暫存中間資料。
		StripeLog	支援並發讀取資料檔案，將所有列儲存在同一個大檔案中，減少了檔案數，查詢效能比TinyLog好。
		Log	支援並發讀取資料檔案，每個列會單獨儲存在一個獨立檔案中，查詢效能比TinyLog好。
Integrations	Integrations系列引擎適用於將外部資料匯入到雲資料庫ClickHouse中，或者在雲資料庫ClickHouse中直接使用外部資料源。	Kafka	將Kafka Topic中的資料直接匯入到雲資料庫ClickHouse。
		MySQL	將MySQL作為儲存引擎，直接在雲資料庫ClickHouse中對MySQL表進行`select`等操作。
		JDBC	通過指定JDBC串連串讀取資料來源。
		ODBC	通過指定ODBC串連串讀取資料來源。
		HDFS	直接讀取HDFS上特定格式的資料檔案。
Special	Special系列引擎適用於特定的功能情境。	Distributed	本身不儲存資料，可以在多個伺服器上進行分散式查詢。
		MaterializedView	用於建立物化視圖。
		Dictionary	將字典資料展示為一個雲資料庫ClickHouse表。
		Merge	本身不儲存資料，可以同時從任意多個其他表中讀取資料。
		File	直接將本地檔案作為資料存放區。
		NULL	寫入資料被丟棄，讀取資料為空白。
		Set	資料總是儲存在RAM中。
		Join	資料總是儲存在記憶體中。
		URL	用於管理遠程HTTP、HTTPS伺服器上的資料。
		View	本身不儲存資料，僅儲存指定的`SELECT`查詢。
		Memory	資料存放區在記憶體中，重啟後會導致資料丟失。查詢效能極好，適合於對於資料持久性沒有要求的1億以下的小表。在雲資料庫ClickHouse中，通常用來做暫存資料表。
		Buffer	為目標表設定一個記憶體Buffer，當Buffer達到了一定條件之後會寫入到磁碟。

說明

表引擎的更多資訊，具體請參見表引擎介紹。

MergeTree

MergeTree表引擎主要用于海量資料分析，支援資料分區、儲存有序、主鍵索引、稀疏索引和資料TTL等。MergeTree表引擎支援雲資料庫ClickHouse的所有SQL文法，但是部分功能與標準SQL存在差異。

本文以主鍵為例進行介紹。雲資料庫ClickHouse的SQL文法中主鍵用於去重，保持資料唯一，而在MergeTree表引擎中，其主要作用是加速查詢，即便在Compaction完成後，主鍵相同的資料行也仍舊共同存在。

說明

MergeTree表引擎的更多資訊，具體請參見MergeTree。

樣本如下。

建立表test_tbl，主鍵為id和create_time，並且按照主鍵進行儲存排序，按照create_time進行資料分區。

CREATE TABLE test_tbl ON CLUSTER default (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = MergeTree()
   PARTITION BY create_time
     ORDER BY  (id, create_time)
     PRIMARY KEY (id, create_time)
     SETTINGS index_granularity=8192;

寫入主鍵重複的資料。

insert into test_tbl values(1, '2019-12-13', null);
insert into test_tbl values(1, '2019-12-13', null);
insert into test_tbl values(2, '2019-12-14', null);
insert into test_tbl values(3, '2019-12-15', null);
insert into test_tbl values(3, '2019-12-15', null);

查詢資料。

select * from test_tbl;

查詢結果如下。

┌─id─┬─create_time─┬─comment──┐
│  1 │ 2019-12-13  │   NULL   │
│  1 │ 2019-12-13  │   NULL   │
│  2 │ 2019-12-14  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
└────┴─────────────┴──────────┘

由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。
```
optimize table test_tbl final;
```

重新查詢資料。

select * from test_tbl;

查詢結果如下，主鍵重複的資料仍存在。

┌─id─┬─create_time─┬─comment──┐
│  1 │ 2019-12-13  │   NULL   │
│  1 │ 2019-12-13  │   NULL   │
│  2 │ 2019-12-14  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
└────┴─────────────┴──────────┘

ReplacingMergeTree

為瞭解決MergeTree表引擎相同主鍵無法去重的問題，雲資料庫ClickHouse提供了ReplacingMergeTree表引擎，用於刪除主索引值相同的重複項。

雖然ReplacingMergeTree表引擎提供了主鍵去重的能力，但是仍然存在很多限制，因此ReplacingMergeTree表引擎更多被用於確保資料最終被去重，而無法保證查詢過程中主鍵不重複，主要限制如下。

在分布式情境下，相同主鍵的資料可能被分布到不同節點上，不同分區間可能無法去重。
在沒有徹底optimize之前，可能無法達到主鍵去重的效果，比如部分資料已經被去重，而另外一部分資料仍舊有主鍵重複。
optimize是後台動作，無法預測具體執行時間點。
手動執行optimize在海量資料情境下需要消耗大量時間，無法滿足業務即時查詢的需求。

說明

ReplacingMergeTree表引擎的更多資訊，具體請參見ReplacingMergeTree。

樣本如下。

建立表test_tbl_replacing。

CREATE TABLE test_tbl_replacing (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = ReplacingMergeTree()
   PARTITION BY create_time
     ORDER BY  (id, create_time)
     PRIMARY KEY (id, create_time)
     SETTINGS index_granularity=8192;

寫入主鍵重複的資料。

insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(2, '2019-12-14', null);
insert into test_tbl_replacing values(3, '2019-12-15', null);
insert into test_tbl_replacing values(3, '2019-12-15', null);

查詢資料。

select * from test_tbl_replacing;

查詢結果如下。

┌─id─┬─create_time─┬─comment──┐
│  1 │ 2019-12-13  │   NULL   │
│  1 │ 2019-12-13  │   NULL   │
│  2 │ 2019-12-14  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
└────┴─────────────┴──────────┘

由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。
```
optimize table test_tbl_replacing final;
```

重新查詢資料。

select * from test_tbl_replacing;

查詢結果如下，主鍵重複的資料已消除。

┌─id─┬─create_time─┬─comment──┐
│  1 │ 2019-12-13  │   NULL   │
│  2 │ 2019-12-14  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
└────┴─────────────┴──────────┘

CollapsingMergeTree

CollapsingMergeTree表引擎用於消除ReplacingMergeTree表引擎的功能限制。該表引擎要求在建表語句中指定一個標記列Sign，按照Sign的值將行分為兩類：Sign=1的行稱為狀態行，用於新增狀態。Sign=-1的行稱為取消行，用於刪除狀態。

說明

CollapsingMergeTree表引擎雖然解決了主鍵相同資料即時刪除的問題，但是狀態持續變化且多線程並行寫入情況下，狀態行與取消行位置可能亂序，導致無法正常摺疊（刪除）。

後台Compaction時會將主鍵相同、Sign相反的行進行摺疊（刪除），而尚未進行Compaction的資料，狀態行與取消行同時存在。因此為了能夠達到主鍵摺疊（刪除）的目的，需要業務層進行如下操作。

記錄原始狀態行的值，或者在執行刪除狀態操作前先查詢資料庫擷取原始狀態行的值。
具體原因：執行刪除狀態操作時需要寫入取消行，而取消行中需要包含與原始狀態行主鍵一樣的資料（Sign列除外）。
在進行count()、sum(col)等彙總計算時，可能會存在資料冗餘的情況。為了獲得正確結果，業務層需要改寫SQL，將count()、sum(col)分別改寫為sum(Sign)、sum(col * Sign)。
具體原因如下。
- 後台Compaction時機無法預測，在發起查詢時，狀態行和取消行可能尚未進行摺疊（刪除）。
- 雲資料庫ClickHouse無法保證主鍵相同的行落在同一個節點上，不在同一節點上的資料無法進行摺疊（刪除）。

說明

CollapsingMergeTree表引擎的更多資訊，具體請參見CollapsingMergeTree。

樣本如下。

建立表test_tbl_collapsing。

CREATE TABLE test_tbl_collapsing
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

插入狀態行Sign=1。
```
INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, 1);
```
說明
如果先插入取消行，再插入狀態行，可能會導致位置亂序，即使強制後台Compaction，也無法進行主鍵摺疊（刪除）。
插入取消行Sign=-1，除Sign列外其他值與插入的狀態行一致。同時，插入一行相同主鍵資料的新狀態行。
```
INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);
```

查詢資料。

SELECT * FROM test_tbl_collapsing;

查詢結果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐ 
│ 4324182021466249494 │    5      │    146   │   1   │ 
│ 4324182021466249494 │    5      │    146   │  -1   │ 
│ 4324182021466249494 │    6      │    185   │   1   │ 
└─────────────────────┴───────────┴──────────┴───────┘

如果您需要對指定列進行彙總計算，以sum(col)為例，為了獲得正確結果，需改寫SQL語句如下。

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM test_tbl_collapsing
GROUP BY UserID
HAVING sum(Sign) > 0;

進行彙總計算後，查詢結果如下。

┌────────UserID───────┬─PageViews─┬─Duration──┐ 
│ 4324182021466249494 │    6      │    185    │ 
└─────────────────────┴───────────┴───────────┘

由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。
```
optimize table test_tbl_collapsing final;
```

重新查詢資料。

SELECT * FROM test_tbl_collapsing;

查詢結果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐  
│ 4324182021466249494 │    6      │    185   │   1   │ 
└─────────────────────┴───────────┴──────────┴───────┘

VersionedCollapsingMergeTree

為瞭解決CollapsingMergeTree表引擎亂序寫入導致無法正常摺疊（刪除）問題，雲資料庫ClickHouse提供了VersionedCollapsingMergeTree表引擎，在建表語句中新增一列Version，用於在亂序情況下選項組行與取消行的對應關係。後台Compaction時會將主鍵相同、Version相同、Sign相反的行摺疊（刪除）。

與CollapsingMergeTree表引擎類似，在進行count()、sum(col)等彙總計算時，業務層需要改寫SQL，將count()、sum(col)分別改寫為sum(Sign)、sum(col * Sign)。

說明

VersionedCollapsingMergeTree表引擎的更多資訊，具體請參見VersionedCollapsingMergeTree。

樣本如下。

建立表test_tbl_Versioned。

CREATE TABLE test_tbl_Versioned
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8,
    Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;

插入取消行Sign=-1。

INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, -1, 1);

插入狀態行Sign=1、Version=1，其他列值與插入的取消行一致。同時，插入一行相同主鍵資料的新狀態行。
```
INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);
```

查詢資料。

SELECT * FROM test_tbl_Versioned;

查詢結果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
│ 4324182021466249494 │    5      │    146   │  -1    │    1   │
│ 4324182021466249494 │    5      │    146   │   1    │    1   │
│ 4324182021466249494 │    6      │    185   │   1    │    2   │
└─────────────────────┴───────────┴──────────┴────────┴────────┘

如果您需要對指定列進行彙總計算，以sum(col)為例，為了獲得正確結果，需改寫SQL語句如下。

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM test_tbl_Versioned
GROUP BY UserID
HAVING sum(Sign) > 0;

進行彙總計算後，查詢結果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┐ 
│ 4324182021466249494 │    6      │    185   │
└─────────────────────┴───────────┴──────────┘

由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。
```
optimize table test_tbl_Versioned final;
```

重新查詢資料。

SELECT * FROM test_tbl_Versioned;

查詢結果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
│ 4324182021466249494 │    6      │    185   │   1    │    2   │
└─────────────────────┴───────────┴──────────┴────────┴────────┘

SummingMergeTree

SummingMergeTree表引擎用於對主鍵列進行預先彙總，將所有相同主鍵的行合并為一行，從而大幅度降低儲存空間佔用，提升彙總計算效能。

使用SummingMergeTree表引擎時，需要注意如下幾點。

雲資料庫ClickHouse只在後台Compaction時才會對主鍵列進行預先彙總，而Compaction的執行時機無法預測，所以可能存在部分資料已經被預先彙總、部分資料尚未被彙總的情況。因此，在執行彙總計算時，仍需要使用GROUP BY子句。
在預先彙總時，雲資料庫ClickHouse會對主鍵列之外的其他所有列進行預彙總。如果這些列是可彙總的（比如數實值型別），則直接sum求和。如果不可彙總（比如String類型），則會隨機播放一個值。
通常建議將SummingMergeTree表引擎與MergeTree表引擎組合使用，MergeTree表引擎儲存完整的資料，SummingMergeTree表引擎用於儲存預先彙總的結果。

說明

SummingMergeTree表引擎的更多資訊，具體請參見SummingMergeTree。

樣本如下。

建立表test_tbl_summing。

CREATE TABLE test_tbl_summing
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key;

寫入資料。

INSERT INTO test_tbl_summing Values(1,1),(1,2),(2,1);

查詢資料。

select * from test_tbl_summing;

查詢結果如下。

┌─key─┬value─┐
│  1  │  1   │
│  1  │  2   │
│  2  │  1   │
└─────┴──────┘

由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。
```
optimize table test_tbl_summing final;
```

強制後台Compaction後，仍舊需要執行GROUP BY子句進行彙總計算，重新查詢資料。

SELECT key, sum(value) FROM test_tbl_summing GROUP BY key;

查詢結果如下，主鍵重複的資料已進行了彙總。

┌─key─┬value─┐
│  1  │  3   │
│  2  │  1   │
└─────┴──────┘

AggregatingMergeTree

AggregatingMergeTree表引擎也是預先彙總引擎的一種，用於提升彙總計算的效能。與SummingMergeTree表引擎的區別在於，SummingMergeTree表引擎用於對非主鍵列進行sum彙總，而AggregatingMergeTree表引擎可以指定各種彙總函式。

AggregatingMergeTree的文法比較複雜，需要結合物化視圖或雲資料庫ClickHouse的特殊資料類型AggregateFunction一起使用。

說明

AggregatingMergeTree表引擎的更多資訊，具體請參見AggregatingMergeTree。

樣本如下。

結合物化視圖使用

建立明細表visits。

CREATE TABLE visits
(
    UserID UInt64,
    CounterID UInt8,
    StartDate Date,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

對明細表visits建立物化視圖visits_agg_view，並使用sumState和uniqState函數對明細表進行預先彙總。

CREATE MATERIALIZED VIEW visits_agg_view
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECT
    CounterID,
    StartDate,
    sumState(Sign)    AS Visits,
    uniqState(UserID) AS Users
FROM visits
GROUP BY CounterID, StartDate;

寫入資料至明細表visits中。

INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

使用彙總函式sumMerge和uniqMerge對物化視圖進行彙總，並查詢彙總資料。

SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate

說明

函數sum和uniq不能再使用，否則會出現SQL報錯：Illegal type AggregateFunction(sum, Int8) of argument for aggregate function sum...

查詢結果如下。

┌──StartDate──┬─Visits─┬─Users──┐
│  2019-11-11 │   1    │   1    │
│  2019-11-12 │   1    │   1    │
└─────────────┴────────┴────────┘

結合特殊資料類型AggregateFunction使用

建立明細表detail_table。

CREATE TABLE detail_table
(   CounterID UInt8,
    StartDate Date,
    UserID UInt64
) ENGINE = MergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

寫入資料至明細表detail_table中。

INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
INSERT INTO detail_table VALUES(1, '2019-11-12', 1);

建立彙總表agg_table，其中UserID列的類型為AggregateFunction。

CREATE TABLE agg_table
(   CounterID UInt8,
    StartDate Date,
    UserID AggregateFunction(uniq, UInt64)
) ENGINE = AggregatingMergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

使用彙總函式uniqState將明細表的資料插入至彙總表中。
```
INSERT INTO agg_table
select CounterID, StartDate, uniqState(UserID)
from detail_table
group by CounterID, StartDate;
```
說明
不能使用INSERT INTO agg_table VALUES(1, '2019-11-12', 1);語句向彙總表插入資料，否則會出現SQL報錯：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)...

使用彙總函式uniqMerge對彙總表進行彙總，並查詢彙總資料。

SELECT uniqMerge(UserID) AS state 
FROM agg_table 
GROUP BY CounterID, StartDate;

查詢結果如下。

┌─state─┐
│   1   │
│   1   │
└───────┘