表引擎的作用是什么

• 決定表存儲在哪里以及以何種方式存儲
• 支持哪些查詢以及如何支持
• 并發數據訪問
• 索引的使用
• 是否可以執行多線程請求
• 數據復制參數 

表引擎分類

Log系列表引擎 

應用場景

Log系列表引擎功能相對簡單，主要用于快速寫入小表(1百萬行左右的表)，然后全部讀出的場景。即一次寫入多次查詢。 

Log系列表引擎的特點 

共性特點

• 數據存儲在磁盤上
• 當寫數據時，將數據追加到文件的末尾
• 不支持     并發讀寫，當向表中寫入數據時，針對這張表的查詢會被阻塞，直至寫入動作結束
• 不支持索引
• 不支持原子寫：如果某些操作(異常的服務器關閉)中斷了寫操作，則可能會獲得帶有損壞數據的表
• 不支持     ALTER操作(這些操作會修改表設置或數據，比如delete、update等等)

區別

• TinyLog

  TinyLog是Log系列引擎中功能簡單、性能較低的引擎。它的存儲結構由數據文件和元數據兩部分組成。其中，數據文件是按列獨立存儲的，也就是說每一個列字段都對應一個文件。除此之外，TinyLog不支持并發數據讀取。

• StripLog支持并發讀取數據文件，當讀取數據時，ClickHouse會使用多線程進行讀取，每個線程處理一個單獨的數據塊。另外，StripLog將所有列數據存儲在同一個文件中，減少了文件的使用數量。

• Log支持并發讀取數據文件，當讀取數據時，ClickHouse會使用多線程進行讀取，每個線程處理一個單獨的數據塊。Log引擎會將每個列數據單獨存儲在一個獨立文件中。 

TinyLog表引擎使用

該引擎適用于一次寫入，多次讀取的場景。對于處理小批數據的中間表可以使用該引擎。值得注意的是，使用大量的小表存儲數據，性能會很低。

CREATE TABLE emp_tinylog (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=TinyLog();

INSERT INTO emp_tinylog 
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_tinylog
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);

進入默認數據存儲目錄，查看底層數據存儲形式,可以看出：TinyLog引擎表每一列都對應的文件

[root@cdh04 emp_tinylog]# pwd
/var/lib/clickhouse/data/default/emp_tinylog
[root@cdh04 emp_tinylog]# ll
總用量 28
-rw-r----- 1 clickhouse clickhouse  56 9月  17 14:33 age.bin
-rw-r----- 1 clickhouse clickhouse  97 9月  17 14:33 depart.bin
-rw-r----- 1 clickhouse clickhouse  60 9月  17 14:33 emp_id.bin
-rw-r----- 1 clickhouse clickhouse  70 9月  17 14:33 name.bin
-rw-r----- 1 clickhouse clickhouse  68 9月  17 14:33 salary.bin
-rw-r----- 1 clickhouse clickhouse 185 9月  17 14:33 sizes.json
-rw-r----- 1 clickhouse clickhouse  80 9月  17 14:33 work_place.bin
## 查看sizes.json數據
## 在sizes.json文件內使用JSON格式記錄了每個．bin文件內對應的數據大小的信息
{
    "yandex":{
        "age%2Ebin":{
            "size":"56"
        },
        "depart%2Ebin":{
            "size":"97"
        },
        "emp_id%2Ebin":{
            "size":"60"
        },
        "name%2Ebin":{
            "size":"70"
        },
        "salary%2Ebin":{
            "size":"68"
        },
        "work_place%2Ebin":{
            "size":"80"
        }
    }
}

當我們執行ALTER操作時會報錯，說明該表引擎不支持ALTER操作

-- 以下操作會報錯：
-- DB::Exception: Mutations are not supported by storage TinyLog.
ALTER TABLE emp_tinylog DELETE WHERE emp_id = 5;
ALTER TABLE emp_tinylog UPDATE age = 30 WHERE emp_id = 4;  
  

StripLog表引擎使用

相比TinyLog而言，StripeLog擁有更高的查詢性能（擁有.mrk標記文件，支持并行查詢），同時其使用了更少的文件描述符（所有數據使用同一個文件保存）。

CREATE TABLE emp_stripelog (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=StripeLog;
-- 插入數據  
INSERT INTO emp_stripelog
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_stripelog 
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);
-- 查詢數據
-- 由于是分兩次插入數據，所以查詢時會有兩個數據塊
cdh04 :) select * from emp_stripelog;

SELECT *
FROM emp_stripelog

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部   │ 50000.00 │
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

進入默認數據存儲目錄，查看底層數據存儲形式

[root@cdh04 emp_stripelog]# pwd
/var/lib/clickhouse/data/default/emp_stripelog
[root@cdh04 emp_stripelog]# ll
總用量 12
-rw-r----- 1 clickhouse clickhouse 673 9月  17 15:11 data.bin
-rw-r----- 1 clickhouse clickhouse 281 9月  17 15:11 index.mrk
-rw-r----- 1 clickhouse clickhouse  69 9月  17 15:11 sizes.json

可以看出StripeLog表引擎對應的存儲結構包括三個文件：

• data.bin：數據文件，所有的列字段使用同一個文件保存，它們的數據都會被寫入data.bin。
• index.mrk：數據標記，保存了數據在data.bin文件中的位置信息(每個插入數據塊對應列的offset)，利用數據標記能夠使用多個線程，以并行的方式讀取data.bin內的壓縮數據塊，從而提升數據查詢的性能。
• sizes.json：元數據文件，記錄了data.bin和index.mrk大小的信息

提示：

StripeLog引擎將所有數據都存儲在了一個文件中，對于每次的INSERT操作，ClickHouse會將數據塊追加到表文件的末尾

StripeLog引擎同樣不支持ALTER UPDATE 和ALTER DELETE 操作 

Log表引擎使用

Log引擎表適用于臨時數據，一次性寫入、測試場景。Log引擎結合了TinyLog表引擎和StripeLog表引擎的長處，是Log系列引擎中性能最高的表引擎。

CREATE TABLE emp_log (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=Log;
  
INSERT INTO emp_log VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_log VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);
-- 查詢數據，
-- 由于是分兩次插入數據，所以查詢時會有兩個數據塊
cdh04 :) select * from emp_log;

SELECT *
FROM emp_log

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部   │ 50000.00 │
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

進入默認數據存儲目錄，查看底層數據存儲形式

[root@cdh04 emp_log]# pwd
/var/lib/clickhouse/data/default/emp_log
[root@cdh04 emp_log]# ll
總用量 32
-rw-r----- 1 clickhouse clickhouse  56 9月  17 15:55 age.bin
-rw-r----- 1 clickhouse clickhouse  97 9月  17 15:55 depart.bin
-rw-r----- 1 clickhouse clickhouse  60 9月  17 15:55 emp_id.bin
-rw-r----- 1 clickhouse clickhouse 192 9月  17 15:55 __marks.mrk
-rw-r----- 1 clickhouse clickhouse  70 9月  17 15:55 name.bin
-rw-r----- 1 clickhouse clickhouse  68 9月  17 15:55 salary.bin
-rw-r----- 1 clickhouse clickhouse 216 9月  17 15:55 sizes.json
-rw-r----- 1 clickhouse clickhouse  80 9月  17 15:55 work_place.bin

Log引擎的存儲結構包含三部分：

• 列.bin：數據文件，數據文件按列單獨存儲
• __marks.mrk：數據標記，統一保存了數據在各個.bin文件中的位置信息。利用數據標記能夠使用多個線程，以并行的方式讀取。.bin內的壓縮數據塊，從而提升數據查詢的性能。
• sizes.json：記錄了.bin和__marks.mrk大小的信息

提示：

Log表引擎會將每一列都存在一個文件中，對于每一次的INSERT操作，都會對應一個數據塊 

MergeTree系列引擎

在所有的表引擎中，最為核心的當屬MergeTree系列表引擎，這些表引擎擁有最為強大的性能和最廣泛的使用場合。對于非MergeTree系列的其他引擎而言，主要用于特殊用途，場景相對有限。而MergeTree系列表引擎是官方主推的存儲引擎，支持幾乎所有ClickHouse核心功能。 

MergeTree表引擎

MergeTree在寫入一批數據時，數據總會以數據片段的形式寫入磁盤，且數據片段不可修改。為了避免片段過多，ClickHouse會通過后臺線程，定期合并這些數據片段，屬于相同分區的數據片段會被合成一個新的片段。這種數據片段往復合并的特點，也正是合并樹名稱的由來。

MergeTree作為家族系列最基礎的表引擎，主要有以下特點：

• 存儲的數據按照主鍵排序：允許創建稀疏索引，從而加快數據查詢速度
• 支持分區，可以通過PRIMARY KEY語句指定分區字段。
• 支持數據副本
• 支持數據采樣

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

• ENGINE：ENGINE = MergeTree()，MergeTree引擎沒有參數
• ORDER BY：排序字段。比如ORDER BY (Col1, Col2)，值得注意的是，如果沒有指定主鍵，默認情況下 sorting key(排序字段)即為主鍵。如果不需要排序，則可以使用**ORDER BY tuple()**語法，這樣的話，創建的表也就不包含主鍵。這種情況下，ClickHouse會按照插入的順序存儲數據。     必選。
• PARTITION BY：分區字段，     可選。
• PRIMARY KEY：指定主鍵，如果排序字段與主鍵不一致，可以單獨指定主鍵字段。否則默認主鍵是排序字段。     可選。
• SAMPLE BY：采樣字段，如果指定了該字段，那么主鍵中也必須包含該字段。比如     SAMPLE BY intHash42(UserID) ORDER BY (CounterID, EventDate, intHash42(UserID))。     可選。
• TTL：數據的存活時間。在MergeTree中，可以為某個列字段或整張表設置TTL。當時間到達時，如果是列字段級別的TTL，則會刪除這一列的數據；如果是表級別的TTL，則會刪除整張表的數據。     可選。
• SETTINGS：額外的參數配置。     可選。

建表示例

CREATE TABLE emp_mergetree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=MergeTree()
  ORDER BY emp_id
  PARTITION BY work_place
  ;
 -- 插入數據 
INSERT INTO emp_mergetree 
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_mergetree 
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000); 

-- 查詢數據
-- 按work_place進行分區
cdh04 :) select * from emp_mergetree;

SELECT *
FROM emp_mergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

查看一下數據存儲格式，可以看出，存在三個分區文件夾，每一個分區文件夾內存儲了對應分區的數據。

[root@cdh04 emp_mergetree]# pwd
/var/lib/clickhouse/data/default/emp_mergetree
[root@cdh04 emp_mergetree]# ll
總用量 16
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:45 1c89a3ba9fe5fd53379716a776c5ac34_3_3_0
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:44 40d45822dbd7fa81583d715338929da9_1_1_0
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:45 a6155dcc1997eda1a348cd98b17a93e9_2_2_0
drwxr-x--- 2 clickhouse clickhouse    6 9月  17 17:43 detached
-rw-r----- 1 clickhouse clickhouse    1 9月  17 17:43 format_version.txt

進入一個分區目錄查看

• checksums.txt：校驗文件，使用二進制格式存儲。它保存了余下各類文件(primary. idx、count.txt等)的size大小及size的哈希值，用于快速校驗文件的完整性和正確性。

• columns.txt：列信息文件，使用明文格式存儲。用于保存此數據分區下的列字段信息，例如

  [root@cdh04 1c89a3ba9fe5fd53379716a776c5ac34_3_3_0]# cat columns.txt
  columns format version: 1
  6 columns:
  `emp_id` UInt16
  `name` String
  `work_place` String
  `age` UInt8
  `depart` String
  `salary` Decimal(9, 2)
  

• count.txt：計數文件，使用明文格式存儲。用于記錄當前數據分區目錄下數據的總行數

• primary.idx：一級索引文件，使用二進制格式存儲。用于存放稀疏索引，一張MergeTree表只能聲明一次一級索引，即通過ORDER BY或者PRIMARY KEY指定字段。借助稀疏索引，在數據查詢的時能夠排除主鍵條件范圍之外的數據文件，從而有效減少數據掃描范圍，加速查詢速度。

• 列.bin：數據文件，使用壓縮格式存儲，默認為LZ4壓縮格式，用于存儲某一列的數據。由于MergeTree采用列式存儲，所以每一個列字段都擁有獨立的.bin數據文件，并以列字段名稱命名。

• 列.mrk2：列字段標記文件，使用二進制格式存儲。標記文件中保存了.bin文件中數據的偏移量信息

• partition.dat與minmax_[Column].idx：如果指定了分區鍵，則會額外生成partition.dat與minmax索引文件，它們均使用二進制格式存儲。partition.dat用于保存當前分區下分區表達式最終生成的值，即分區字段值；而minmax索引用于記錄當前分區下分區字段對應原始數據的最小和最大值。比如當使用EventTime字段對應的原始數據為2020-09-17、2020-09-30，分區表達式為PARTITION BY toYYYYMM(EventTime)，即按月分區。partition.dat中保存的值將會是2019-09，而minmax索引中保存的值將會是2020-09-17 2020-09-30。

注意點

• 多次插入數據，會生成多個分區文件

-- 新插入兩條數據
cdh04 :) INSERT INTO emp_mergetree
VALUES (5,'robin','北京',35,'財務部',50000),(6,'lilei','北京',38,'銷售事部',50000);

-- 查詢結果
cdh04 :) select * from emp_mergetree;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      5 │ robin │ 北京       │  35 │ 財務部   │ 50000.00 │
│      6 │ lilei │ 北京       │  38 │ 銷售事部 │ 50000.00 │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

可以看出，新插入的數據新生成了一個數據塊，并沒有與原來的分區數據在一起，我們可以執行optimize命令，執行合并操作

-- 執行合并操作
cdh04 :) OPTIMIZE TABLE emp_mergetree PARTITION '北京';
-- 再次執行查詢
cdh04 :) select * from emp_mergetree;                  
SELECT *
FROM emp_mergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob   │ 北京       │  33 │ 財務部   │ 50000.00 │
│      5 │ robin │ 北京       │  35 │ 財務部   │ 50000.00 │
│      6 │ lilei │ 北京       │  38 │ 銷售事部 │ 50000.00 │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

執行上面的合并操作之后，會新生成一個該分區的文件夾，原理的分區文件夾不變。

• 在MergeTree中主鍵并不用于去重，而是用于索引，加快查詢速度

-- 插入一條相同主鍵的數據
 INSERT INTO emp_mergetree
VALUES (1,'sam','杭州',35,'財務部',50000);
-- 會發現該條數據可以插入，由此可知，并不會對主鍵進行去重

ReplacingMergeTree表引擎

上文提到MergeTree表引擎無法對相同主鍵的數據進行去重，ClickHouse提供了ReplacingMergeTree引擎，可以針對相同主鍵的數據進行去重，它能夠在合并分區時刪除重復的數據。值得注意的是，ReplacingMergeTree只是在一定程度上解決了數據重復問題，但是并不能完全保障數據不重復。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

• [ver]：可選參數，列的版本，可以是UInt、Date或者DateTime類型的字段作為版本號。該參數決定了數據去重的方式。
• 當沒有指定[ver]參數時，保留最新的數據；如果指定了具體的值，保留最大的版本數據。

建表示例

CREATE TABLE emp_replacingmergetree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=ReplacingMergeTree()
  ORDER BY emp_id
  PRIMARY KEY emp_id
  PARTITION BY work_place
  ;
 -- 插入數據 
INSERT INTO emp_replacingmergetree
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_replacingmergetree
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000); 

注意點

當我們再次向該表插入具有相同主鍵的數據時，觀察查詢數據的變化

INSERT INTO emp_replacingmergetree
VALUES (1,'tom','上海',25,'技術部',50000);
-- 查詢數據，由于沒有進行合并，所以存在主鍵重復的數據
cdh04 :) select * from emp_replacingmergetree;                                        

SELECT *
FROM emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
-- 執行合并操作
optimize table emp_replacingmergetree final;
-- 再次查詢，相同主鍵的數據，保留最近插入的數據，舊的數據被清除
cdh04 :) select * from emp_replacingmergetree;       

SELECT *
FROM emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

從上面的示例中可以看出，ReplacingMergeTree是支持對數據去重的，那么是根據什么進行去重呢？答案是：ReplacingMergeTree在去除重復數據時，是以ORDERBY排序鍵為基準的，而不是PRIMARY KEY。我們在看一個示例：

CREATE TABLE emp_replacingmergetree1 (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=ReplacingMergeTree()
  ORDER BY (emp_id,name) -- 注意排序key是兩個字段
  PRIMARY KEY emp_id     -- 主鍵是一個字段
  PARTITION BY work_place
  ;
 -- 插入數據 
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_replacingmergetree1
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000); 

再次向該表中插入相同emp_id和name的數據，并執行合并操作，再觀察數據

-- 插入數據
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','上海',25,'技術部',50000),(1,'sam','上海',25,'技術部',20000);
-- 執行合并操作
optimize table emp_replacingmergetree1 final;
-- 再次查詢，可見相同的emp_id和name數據被去重，而形同的主鍵emp_id不會去重
-- ReplacingMergeTree在去除重復數據時，是以ORDERBY排序鍵為基準的，而不是PRIMARY KEY
cdh04 :) select * from emp_replacingmergetree1;                                                                           
SELECT *
FROM emp_replacingmergetree1

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

至此，我們知道了ReplacingMergeTree是支持去重的，并且是按照ORDERBY排序鍵為基準進行去重的。細心的你會發現，上面的重復數據是在一個分區內的，那么如果重復的數據不在一個分區內，會發生什么現象呢？我們再次向上面的emp_replacingmergetree1表插入不同分區的重復數據

-- 插入數據
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','北京',26,'技術部',10000);
-- 執行合并操作
optimize table emp_replacingmergetree1 final;
-- 再次查詢
-- 發現  1 │ tom  │ 北京       │  26 │ 技術部 │ 10000.00
-- 與    1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00
-- 數據重復，因為這兩行數據不在同一個分區內
-- 這是因為ReplacingMergeTree是以分區為單位刪除重復數據的。
-- 只有在相同的數據分區內重復的數據才可以被刪除，而不同數據分區之間的重復數據依然不能被剔除
cdh04 :) select * from emp_replacingmergetree1;       

SELECT *
FROM emp_replacingmergetree1

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 技術部 │ 10000.00 │
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

總結

• 如何判斷數據重復

ReplacingMergeTree在去除重復數據時，是以ORDERBY排序鍵為基準的，而不是PRIMARY KEY。

• 何時刪除重復數據

在執行分區合并時，會觸發刪除重復數據。optimize的合并操作是在后臺執行的，無法預測具體執行時間點，除非是手動執行。

• 不同分區的重復數據不會被去重

ReplacingMergeTree是以分區為單位刪除重復數據的。只有在相同的數據分區內重復的數據才可以被刪除，而不同數據分區之間的重復數據依然不能被剔除。

• 數據去重的策略是什么

如果沒有設置**[ver]版本號**，則保留同一組重復數據中的最新插入的數據；如果設置了**[ver]版本號**，則保留同一組重復數據中ver字段取值最大的那一行。

• optimize命令使用

一般在數據量比較大的情況，盡量不要使用該命令。因為在海量數據場景下，執行optimize要消耗大量時間

SummingMergeTree表引擎

該引擎繼承了MergeTree引擎，當合并 SummingMergeTree 表的數據片段時，ClickHouse 會把所有具有相同主鍵的行合并為一行，該行包含了被合并的行中具有數值數據類型的列的匯總值，即如果存在重復的數據，會對對這些重復的數據進行合并成一條數據，類似于group by的效果。

推薦將該引擎和 MergeTree 一起使用。例如，將完整的數據存儲在 MergeTree 表中，并且使用 SummingMergeTree 來存儲聚合數據。這種方法可以避免因為使用不正確的主鍵組合方式而丟失數據。

如果用戶只需要查詢數據的匯總結果，不關心明細數據，并且數據的匯總條件是預先明確的，即GROUP BY的分組字段是確定的，可以使用該表引擎。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns]) -- 指定合并匯總字段
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

CREATE TABLE emp_summingmergetree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=SummingMergeTree(salary)
  ORDER BY (emp_id,name) -- 注意排序key是兩個字段
  PRIMARY KEY emp_id     -- 主鍵是一個字段
  PARTITION BY work_place
  ;
 -- 插入數據 
INSERT INTO emp_summingmergetree
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_summingmergetree
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000); 

當我們再次插入具有相同emp_id,name的數據時，觀察結果

INSERT INTO emp_summingmergetree
VALUES (1,'tom','上海',25,'信息部',10000),(1,'tom','北京',26,'人事部',10000);
cdh04 :) select * from emp_summingmergetree;
-- 查詢
SELECT *
FROM emp_summingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
-- 執行合并操作
optimize table emp_summingmergetree final;
cdh04 :) select * from emp_summingmergetree;       
-- 再次查詢，新插入的數據 1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
-- 原來的數據 ：        1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00
-- 這兩行數據合并成：    1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00
SELECT *
FROM emp_summingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 人事部 │ 10000.00 │
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

注意點

要保證PRIMARY KEY expr指定的主鍵是ORDER BY expr 指定字段的前綴，比如

-- 允許
ORDER BY (A,B,C) 
PRIMARY KEY A  
-- 會報錯
--  DB::Exception: Primary key must be a prefix of the sorting key
ORDER BY (A,B,C) 
PRIMARY KEY B

這種強制約束保障了即便在兩者定義不同的情況下，主鍵仍然是排序鍵的前綴，不會出現索引與數據順序混亂的問題。

總結

• SummingMergeTree是根據什么對兩條數據進行合并的

用ORBER BY排序鍵作為聚合數據的條件Key。即如果排序key是相同的，則會合并成一條數據，并對指定的合并字段進行聚合。

• 僅對分區內的相同排序key的數據行進行合并

以數據分區為單位來聚合數據。當分區合并時，同一數據分區內聚合Key相同的數據會被合并匯總，而不同分區之間的數據則不會被匯總。

• 如果沒有指定聚合字段，會怎么聚合

如果沒有指定聚合字段，則會按照非主鍵的數值類型字段進行聚合

• 對于非匯總字段的數據，該保留哪一條

如果兩行數據除了排序字段相同，其他的非聚合字段不相同，那么在聚合發生時，會保留最初的那條數據，新插入的數據對應的那個字段值會被舍棄

-- 新插入的數據:        1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
-- 最初的數據 ：        1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00

-- 聚合合并的結果：      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00

Aggregatingmergetree表引擎

該表引擎繼承自MergeTree，可以使用 AggregatingMergeTree 表來做增量數據統計聚合。如果要按一組規則來合并減少行數，則使用 AggregatingMergeTree 是合適的。AggregatingMergeTree是通過預先定義的聚合函數計算數據并通過二進制的格式存入表內。

與SummingMergeTree的區別在于：SummingMergeTree對非主鍵列進行sum聚合，而AggregatingMergeTree則可以指定各種聚合函數。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

CREATE TABLE emp_aggregatingmergeTree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary  AggregateFunction(sum,Decimal32(2)) COMMENT '工資'  
  )ENGINE=AggregatingMergeTree()
  ORDER BY (emp_id,name) -- 注意排序key是兩個字段
  PRIMARY KEY emp_id     -- 主鍵是一個字段
  PARTITION BY work_place
  ;

對于AggregateFunction類型的列字段，在進行數據的寫入和查詢時與其他的表引擎有很大區別，在寫入數據時，需要調用**       -State 函數；而在查詢數據時，則需要調用相應的          -Merge     函數。對于上面的建表語句而言，需要使用sumState**函數進行數據插入

-- 插入數據，
-- 注意：需要使用INSERT…SELECT語句進行數據插入
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(10000,2));
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(20000,2));
-- 查詢數據
SELECT 
  emp_id,
  name , 
  sumMerge(salary) 
FROM emp_aggregatingmergeTree
GROUP BY emp_id,name;
-- 結果輸出
┌─emp_id─┬─name─┬─sumMerge(salary)─┐
│      1 │ tom  │         30000.00 │
└────────┴──────┴──────────────────┘

上面演示的用法非常的麻煩，其實更多的情況下，我們可以結合物化視圖一起使用，將它作為物化視圖的表引擎。而這里的物化視圖是作為其他數據表上層的一種查詢視圖。

AggregatingMergeTree通常作為物化視圖的表引擎，與普通MergeTree搭配使用。

-- 創建一個MereTree引擎的明細表
-- 用于存儲全量的明細數據
-- 對外提供實時查詢
CREATE TABLE emp_mergetree_base (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=MergeTree()
  ORDER BY (emp_id,name)
  PARTITION BY work_place
  ;
  
-- 創建一張物化視圖
-- 使用AggregatingMergeTree表引擎
CREATE MATERIALIZED VIEW view_emp_agg
ENGINE = AggregatingMergeTree()
PARTITION BY emp_id
ORDER BY (emp_id,name)
AS SELECT
     emp_id,
     name,
     sumState(salary) AS salary
FROM emp_mergetree_base
GROUP BY emp_id,name;

-- 向基礎明細表emp_mergetree_base插入數據
INSERT INTO emp_mergetree_base
VALUES (1,'tom','上海',25,'技術部',20000),
(1,'tom','上海',26,'人事部',10000);

-- 查詢物化視圖
SELECT 
  emp_id,
  name , 
  sumMerge(salary) 
FROM view_emp_agg
GROUP BY emp_id,name;
-- 結果
┌─emp_id─┬─name─┬─sumMerge(salary)─┐
│      1 │ tom  │         30000.00 │
└────────┴──────┴──────────────────┘

CollapsingMergeTree表引擎

CollapsingMergeTree就是一種通過以增代刪的思路，支持行級數據修改和刪除的表引擎。它通過定義一個sign標記位字段，記錄數據行的狀態。如果sign標記為1，則表示這是一行有效的數據；如果sign標記為-1，則表示這行數據需要被刪除。當CollapsingMergeTree分區合并時，同一數據分區內，sign標記為1和-1的一組數據會被抵消刪除。

每次需要新增數據時，寫入一行sign標記為1的數據；需要刪除數據時，則寫入一行sign標記為-1的數據。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

上面的建表語句使用CollapsingMergeTree(sign)，其中字段sign是一個Int8類型的字段

CREATE TABLE emp_collapsingmergetree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資',
  sign Int8
  )ENGINE=CollapsingMergeTree(sign)
  ORDER BY (emp_id,name)
  PARTITION BY work_place
  ; 
  

使用方式

CollapsingMergeTree同樣是以ORDER BY排序鍵作為判斷數據唯一性的依據。

-- 插入新增數據,sign=1表示正常數據
INSERT INTO emp_collapsingmergetree 
VALUES (1,'tom','上海',25,'技術部',20000,1);

-- 更新上述的數據
-- 首先插入一條與原來相同的數據(ORDER BY字段一致),并將sign置為-1
INSERT INTO emp_collapsingmergetree 
VALUES (1,'tom','上海',25,'技術部',20000,-1);

-- 再插入更新之后的數據
INSERT INTO emp_collapsingmergetree 
VALUES (1,'tom','上海',25,'技術部',30000,1);

-- 查看一下結果
cdh04 :) select * from emp_collapsingmergetree ;

SELECT *
FROM emp_collapsingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │   -1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
-- 執行分區合并操作
optimize table emp_collapsingmergetree;
-- 再次查詢，sign=1與sign=-1的數據相互抵消了，即被刪除
cdh04 :) select * from emp_collapsingmergetree ;

SELECT *
FROM emp_collapsingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

注意點

• 分區合并

分數數據折疊不是實時的，需要后臺進行Compaction操作，用戶也可以使用手動合并命令，但是效率會很低，一般不推薦在生產環境中使用。

當進行匯總數據操作時，可以通過改變查詢方式，來過濾掉被刪除的數據

SELECT 
    emp_id, 
    name, 
    sum(salary * sign)
FROM emp_collapsingmergetree
GROUP BY 
    emp_id, 
    name
HAVING sum(sign) > 0

只有相同分區內的數據才有可能被折疊。其實，當我們修改或刪除數據時，這些被修改的數據通常是在一個分區內的，所以不會產生影響。

• 數據寫入順序

值得注意的是：CollapsingMergeTree對于寫入數據的順序有著嚴格要求，否則導致無法正常折疊。

-- 建表
CREATE TABLE emp_collapsingmergetree_order (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資',
  sign Int8
  )ENGINE=CollapsingMergeTree(sign)
  ORDER BY (emp_id,name)
  PARTITION BY work_place
  ; 
  
-- 先插入需要被刪除的數據，即sign=-1的數據
INSERT INTO emp_collapsingmergetree_order 
VALUES (1,'tom','上海',25,'技術部',20000,-1);
-- 再插入sign=1的數據
INSERT INTO emp_collapsingmergetree_order 
VALUES (1,'tom','上海',25,'技術部',20000,1);
-- 查詢表
SELECT *
FROM emp_collapsingmergetree_order

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │   -1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
-- 執行合并操作
optimize table emp_collapsingmergetree_order;
-- 再次查詢表
-- 舊數據依然存在
SELECT *
FROM emp_collapsingmergetree_order;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │   -1 │
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

如果數據的寫入程序是單線程執行的，則能夠較好地控制寫入順序；如果需要處理的數據量很大，數據的寫入程序通常是多線程執行的，那么此時就不能保障數據的寫入順序了。在這種情況下，CollapsingMergeTree的工作機制就會出現問題。但是可以通過VersionedCollapsingMergeTree的表引擎得到解決。

VersionedCollapsingMergeTree表引擎

上面提到CollapsingMergeTree表引擎對于數據寫入亂序的情況下，不能夠實現數據折疊的效果。VersionedCollapsingMergeTree表引擎的作用與CollapsingMergeTree完全相同，它們的不同之處在于，VersionedCollapsingMergeTree對數據的寫入順序沒有要求，在同一個分區內，任意順序的數據都能夠完成折疊操作。

VersionedCollapsingMergeTree使用version列來實現亂序情況下的數據折疊。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

可以看出：該引擎除了需要指定一個sign標識之外，還需要指定一個UInt8類型的version版本號。

建表示例

CREATE TABLE emp_versioned (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資',
  sign Int8,
  version Int8
  )ENGINE=VersionedCollapsingMergeTree(sign, version)
  ORDER BY (emp_id,name)
  PARTITION BY work_place
  ;
  
  -- 先插入需要被刪除的數據，即sign=-1的數據
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技術部',20000,-1,1);
-- 再插入sign=1的數據
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技術部',20000,1,1);
-- 在插入一個新版本數據
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技術部',30000,1,2);

-- 先不執行合并，查看表數據
cdh04 :) select * from emp_versioned;

SELECT *
FROM emp_versioned

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00 │    1 │       2 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │    1 │       1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │   -1 │       1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

-- 獲取正確查詢結果
SELECT 
    emp_id, 
    name, 
    sum(salary * sign)
FROM emp_versioned
GROUP BY 
    emp_id, 
    name
HAVING sum(sign) > 0;

-- 手動合并
optimize table emp_versioned;

-- 再次查詢
cdh04 :) select * from emp_versioned;

SELECT *
FROM emp_versioned

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00 │    1 │       2 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

可見上面雖然在插入數據亂序的情況下，依然能夠實現折疊的效果。之所以能夠達到這種效果，是因為在定義version字段之后，VersionedCollapsingMergeTree會自動將version作為排序條件并增加到ORDER BY的末端，就上述的例子而言，最終的排序字段為ORDER BY emp_id,name，version desc。

GraphiteMergeTree表引擎

該引擎用來對 Graphite數據進行'瘦身'及匯總。對于想使用CH來存儲Graphite數據的開發者來說可能有用。

如果不需要對Graphite數據做匯總，那么可以使用任意的CH表引擎；但若需要，那就采用 GraphiteMergeTree 引擎。它能減少存儲空間，同時能提高Graphite數據的查詢效率。

外部集成表引擎

ClickHouse提供了許多與外部系統集成的方法，包括一些表引擎。這些表引擎與其他類型的表引擎類似，可以用于將外部數據導入到ClickHouse中，或者在ClickHouse中直接操作外部數據源。

例如直接讀取HDFS的文件或者MySQL數據庫的表。這些表引擎只負責元數據管理和數據查詢，而它們自身通常并不負責數據的寫入，數據文件直接由外部系統提供。目前ClickHouse提供了下面的外部集成表引擎：

• ODBC：通過指定odbc連接讀取數據源
• JDBC：通過指定jdbc連接讀取數據源；
• MySQL：將MySQL作為數據存儲，直接查詢其數據
• HDFS：直接讀取HDFS上的特定格式的數據文件；
• Kafka：將Kafka數據導入ClickHouse
• RabbitMQ：與Kafka類似

HDFS

使用方式

ENGINE = HDFS(URI, format)

• URI：HDFS文件路徑
• format：文件格式，比如CSV、JSON、TSV等

使用示例

-- 建表
CREATE TABLE hdfs_engine_table(
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
) ENGINE=HDFS('hdfs://cdh03:8020/user/hive/hdfs_engine_table', 'CSV');

-- 寫入數據
INSERT INTO hdfs_engine_table 
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
-- 查詢數據
cdh04 :) select * from hdfs_engine_table;

SELECT *
FROM hdfs_engine_table

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
--再在HDFS上其對應的文件，添加幾條數據，再次查看
cdh04 :) select * from hdfs_engine_table;

SELECT *
FROM hdfs_engine_table

┌─emp_id─┬─name───┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom    │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack   │ 上海       │  26 │ 人事部 │ 10000.00 │
│      3 │ lili   │ 北京       │  28 │ 技術部 │ 20000.00 │
│      4 │ jasper │ 杭州       │  27 │ 人事部 │  8000.00 │
└────────┴────────┴────────────┴─────┴────────┴──────────┘

可以看出，這種方式與使用Hive類似，我們直接可以將HDFS對應的文件映射成ClickHouse中的一張表，這樣就可以使用SQL操作HDFS上的文件了。

值得注意的是：ClickHouse并不能夠刪除HDFS上的數據，當我們在ClickHouse客戶端中刪除了對應的表，只是刪除了表結構，HDFS上的文件并沒有被刪除，這一點跟Hive的外部表十分相似。

MySQL

在上一篇文章[篇一|ClickHouse快速入門]中介紹了MySQL數據庫引擎，即ClickHouse可以創建一個MySQL數據引擎，這樣就可以在ClickHouse中操作其對應的數據庫中的數據。其實，ClickHouse同樣支持MySQL表引擎，即映射一張MySQL中的表到ClickHouse中。

使用方式

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

使用示例

-- 連接MySQL中clickhouse數據庫的test表
CREATE TABLE mysql_engine_table(
    id Int32,
    name String
) ENGINE = MySQL(
 '192.168.200.241:3306',
 'clickhouse',
 'test', 
 'root', 
 '123qwe');
-- 查詢數據
cdh04 :) SELECT * FROM mysql_engine_table;

SELECT *
FROM mysql_engine_table

┌─id─┬─name──┐
│  1 │ tom   │
│  2 │ jack  │
│  3 │ lihua │
└────┴───────┘
-- 插入數據，會將數據插入MySQL對應的表中
-- 所以當查詢MySQL數據時，會發現新增了一條數據
INSERT INTO mysql_engine_table VALUES(4,'robin');
-- 再次查詢
cdh04 :) select * from mysql_engine_table;                

SELECT *
FROM mysql_engine_table

┌─id─┬─name──┐
│  1 │ tom   │
│  2 │ jack  │
│  3 │ lihua │
│  4 │ robin │
└────┴───────┘

注意：對于MySQL表引擎，不支持UPDATE和DELETE操作，比如執行下面命令時，會報錯：

-- 執行更新
ALTER TABLE mysql_engine_table UPDATE name = 'hanmeimei' WHERE id = 1;
-- 執行刪除
ALTER TABLE mysql_engine_table DELETE WHERE id = 1;

-- 報錯
DB::Exception: Mutations are not supported by storage MySQL.

JDBC

使用方式

JDBC表引擎不僅可以對接MySQL數據庫，還能夠與PostgreSQL等數據庫。為了實現JDBC連接，ClickHouse使用了clickhouse-jdbc-bridge的查詢代理服務。

首先我們需要下載clickhouse-jdbc-bridge，然后按照ClickHouse的github中的步驟進行編譯，編譯完成之后會有一個clickhouse-jdbc-bridge-1.0.jar的jar文件，除了需要該文件之外，還需要JDBC的驅動文件，本文使用的是MySQL，所以還需要下載MySQL驅動包。將MySQL的驅動包和clickhouse-jdbc-bridge-1.0.jar文件放在了/opt/softwares路徑下，執行如下命令：

[root@cdh04 softwares]# java -jar clickhouse-jdbc-bridge-1.0.jar  --driver-path .  --listen-host cdh04 

其中--driver-path是MySQL驅動的jar所在的路徑，listen-host是代理服務綁定的主機。默認情況下，綁定的端口是：9019。上述jar包的下載：

鏈接：https://pan.baidu.com/s/1ZcvF22GvnvAQpVTleNry7Q 提取碼：la9n

然后我們再配置/etc/clickhouse-server/config.xml，在文件中添加如下配置，然后重啟服務。

<jdbc_bridge>
    <host>cdh04</host>
    <port>9019</port>
</jdbc_bridge>

使用示例

• 直接查詢MySQL中對應的表

SELECT * 
FROM
jdbc(
'jdbc:mysql://192.168.200.241:3306/?user=root&password=123qwe', 
'clickhouse',
'test');

• 創建一張映射表

-- 語法
CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    columns list...
)
ENGINE = JDBC(dbms_uri, external_database, external_table)

-- MySQL建表
CREATE TABLE jdbc_table_mysql (
  order_id INT NOT NULL AUTO_INCREMENT,
  amount FLOAT NOT NULL,
PRIMARY KEY (order_id));
INSERT INTO  jdbc_table_mysql VALUES (1,200);

-- 在ClickHouse中建表
CREATE TABLE jdbc_table
(
    order_id Int32,
    amount Float32
)
ENGINE JDBC(
'jdbc:mysql://192.168.200.241:3306/?user=root&password=123qwe', 
'clickhouse',
'jdbc_table_mysql');

-- 查詢數據
cdh04 :) select * from jdbc_table;

SELECT *
FROM jdbc_table

┌─order_id─┬─amount─┐
│        1 │    200 │
└──────────┴────────┘

Kafka

使用方式

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'host:port',
    kafka_topic_list = 'topic1,topic2,...',
    kafka_group_name = 'group_name',
    kafka_format = 'data_format'[,]
    [kafka_row_delimiter = 'delimiter_symbol',]
    [kafka_schema = '',]
    [kafka_num_consumers = N,]
    [kafka_max_block_size = 0,]
    [kafka_skip_broken_messages = N,]
    [kafka_commit_every_batch = 0,]
    [kafka_thread_per_consumer = 0]

• kafka_broker_list ：逗號分隔的brokers地址 (localhost:9092).
• kafka_topic_list ：Kafka 主題列表，多個主題用逗號分隔.
• kafka_group_name ：消費者組.
• kafka_format – Message format. 比如     JSONEachRow、JSON、CSV等等

使用示例

在kafka中創建ck_topic主題，并向該主題寫入數據

 CREATE TABLE kafka_table (
    id UInt64,
    name String
  ) ENGINE = Kafka()
    SETTINGS
    kafka_broker_list = 'cdh04:9092',
    kafka_topic_list = 'ck_topic',
    kafka_group_name = 'group1',
    kafka_format = 'JSONEachRow'
;
-- 查詢
cdh04 :) select * from kafka_table ;

SELECT *
FROM kafka_table

┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘
┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘

注意點

當我們一旦查詢完畢之后，ClickHouse會刪除表內的數據，其實Kafka表引擎只是一個數據管道，我們可以通過物化視圖的方式訪問Kafka中的數據。

• 首先創建一張Kafka表引擎的表，用于從Kafka中讀取數據
• 然后再創建一張普通表引擎的表，比如MergeTree，面向終端用戶使用
• 最后創建物化視圖，用于將Kafka引擎表實時同步到終端用戶所使用的表中

--  創建Kafka引擎表
 CREATE TABLE kafka_table_consumer (
    id UInt64,
    name String
  ) ENGINE = Kafka()
    SETTINGS
    kafka_broker_list = 'cdh04:9092',
    kafka_topic_list = 'ck_topic',
    kafka_group_name = 'group1',
    kafka_format = 'JSONEachRow'
;

-- 創建一張終端用戶使用的表
CREATE TABLE kafka_table_mergetree (
  id UInt64 ,
  name String
  )ENGINE=MergeTree()
  ORDER BY id
  ;
  
-- 創建物化視圖，同步數據
CREATE MATERIALIZED VIEW consumer TO kafka_table_mergetree
    AS SELECT id,name FROM kafka_table_consumer ;
-- 查詢，多次查詢，已經被查詢的數據依然會被輸出
cdh04 :) select * from kafka_table_mergetree;

SELECT *
FROM kafka_table_mergetree

┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘
┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘

其他特殊的表引擎

Memory表引擎

Memory表引擎直接將數據保存在內存中，數據既不會被壓縮也不會被格式轉換。當ClickHouse服務重啟的時候，Memory表內的數據會全部丟失。一般在測試時使用。

 CREATE TABLE table_memory (
    id UInt64,
    name String
  ) ENGINE = Memory();

Distributed表引擎

使用方式

Distributed表引擎是分布式表的代名詞，它自身不存儲任何數據，數據都分散存儲在某一個分片上，能夠自動路由數據至集群中的各個節點，所以Distributed表引擎需要和其他數據表引擎一起協同工作。

所以，一張分布式表底層會對應多個本地分片數據表，由具體的分片表存儲數據，分布式表與分片表是一對多的關系

Distributed表引擎的定義形式如下所示

Distributed(cluster_name, database_name, table_name[, sharding_key])

各個參數的含義分別如下：

• cluster_name：集群名稱，與集群配置中的自定義名稱相對應。
• database_name：數據庫名稱
• table_name：表名稱
• sharding_key：可選的，用于分片的key值，在數據寫入的過程中，分布式表會依據分片key的規則，將數據分布到各個節點的本地表。

尖叫提示：

創建分布式表是讀時檢查的機制，也就是說對創建分布式表和本地表的順序并沒有強制要求。

同樣值得注意的是，在上面的語句中使用了ON CLUSTER分布式DDL，這意味著在集群的每個分片節點上，都會創建一張Distributed表，這樣便可以從其中任意一端發起對所有分片的讀、寫請求。

使用示例

-- 創建一張分布式表
CREATE TABLE IF NOT EXISTS user_cluster ON CLUSTER cluster_3shards_1replicas
(
    id Int32,
    name String
)ENGINE = Distributed(cluster_3shards_1replicas, default, user_local,id);

創建完成上面的分布式表時，在每臺機器上查看表，發現每臺機器上都存在一張剛剛創建好的表。

接下來就需要創建本地表了，在每臺機器上分別創建一張本地表：

CREATE TABLE IF NOT EXISTS user_local 
(
    id Int32,
    name String
)ENGINE = MergeTree()
ORDER BY id
PARTITION BY id
PRIMARY KEY id;

我們先在一臺機器上，對user_local表進行插入數據，然后再查詢user_cluster表

-- 插入數據
cdh04 :) INSERT INTO user_local VALUES(1,'tom'),(2,'jack');
-- 查詢user_cluster表,可見通過user_cluster表可以操作所有的user_local表
cdh04 :) select * from user_cluster;
┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘
┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘

接下來，我們再向user_cluster中插入一些數據，觀察user_local表數據變化，可以發現數據被分散存儲到了其他節點上了。

-- 向user_cluster插入數據
cdh04 :)  INSERT INTO user_cluster VALUES(3,'lilei'),(4,'lihua'); 
-- 查看user_cluster數據
cdh04 :) select * from user_cluster;
┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘
┌─id─┬─name──┐
│  3 │ lilei │
└────┴───────┘
┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘
┌─id─┬─name──┐
│  4 │ lihua │
└────┴───────┘

-- 在cdh04上查看user_local
cdh04 :) select * from user_local;
┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘
┌─id─┬─name──┐
│  3 │ lilei │
└────┴───────┘
┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘
-- 在cdh05上查看user_local
cdh05 :) select * from user_local;
┌─id─┬─name──┐
│  4 │ lihua │
└────┴───────┘