ClickHouse的表引擎是什么

發(fā)布時(shí)間：2021-12-16 13:56:11 來(lái)源：億速云閱讀：180 作者：iii 欄目：大數(shù)據(jù)

本篇內(nèi)容主要講解“ClickHouse的表引擎是什么”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“ClickHouse的表引擎是什么”吧!

表引擎的作用是什么

決定表存儲(chǔ)在哪里以及以何種方式存儲(chǔ)
支持哪些查詢以及如何支持
并發(fā)數(shù)據(jù)訪問(wèn)
索引的使用
是否可以執(zhí)行多線程請(qǐng)求
數(shù)據(jù)復(fù)制參數(shù)

表引擎分類

引擎分類	引擎名稱
MergeTree系列	MergeTree 、ReplacingMergeTree 、SummingMergeTree 、 AggregatingMergeTree CollapsingMergeTree 、 VersionedCollapsingMergeTree 、GraphiteMergeTree
Log系列	TinyLog 、StripeLog 、Log
Integration Engines	Kafka 、MySQL、ODBC 、JDBC、HDFS
Special Engines	Distributed 、MaterializedView、 Dictionary 、Merge 、File、Null 、Set 、Join 、 URL View、Memory 、 Buffer

Log系列表引擎

應(yīng)用場(chǎng)景

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

Log系列表引擎的特點(diǎn)

共性特點(diǎn)

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

區(qū)別

TinyLog
TinyLog是Log系列引擎中功能簡(jiǎn)單、性能較低的引擎。它的存儲(chǔ)結(jié)構(gòu)由數(shù)據(jù)文件和元數(shù)據(jù)兩部分組成。其中，數(shù)據(jù)文件是按列獨(dú)立存儲(chǔ)的，也就是說(shuō)每一個(gè)列字段都對(duì)應(yīng)一個(gè)文件。除此之外，TinyLog不支持并發(fā)數(shù)據(jù)讀取。
StripLog支持并發(fā)讀取數(shù)據(jù)文件，當(dāng)讀取數(shù)據(jù)時(shí)，ClickHouse會(huì)使用多線程進(jìn)行讀取，每個(gè)線程處理一個(gè)單獨(dú)的數(shù)據(jù)塊。另外，StripLog將所有列數(shù)據(jù)存儲(chǔ)在同一個(gè)文件中，減少了文件的使用數(shù)量。
Log支持并發(fā)讀取數(shù)據(jù)文件，當(dāng)讀取數(shù)據(jù)時(shí)，ClickHouse會(huì)使用多線程進(jìn)行讀取，每個(gè)線程處理一個(gè)單獨(dú)的數(shù)據(jù)塊。Log引擎會(huì)將每個(gè)列數(shù)據(jù)單獨(dú)存儲(chǔ)在一個(gè)獨(dú)立文件中。

TinyLog表引擎使用

該引擎適用于一次寫入，多次讀取的場(chǎng)景。對(duì)于處理小批數(shù)據(jù)的中間表可以使用該引擎。值得注意的是，使用大量的小表存儲(chǔ)數(shù)據(jù)，性能會(huì)很低。

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

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

進(jìn)入默認(rèn)數(shù)據(jù)存儲(chǔ)目錄，查看底層數(shù)據(jù)存儲(chǔ)形式,可以看出：TinyLog引擎表每一列都對(duì)應(yīng)的文件

[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數(shù)據(jù)
## 在sizes.json文件內(nèi)使用JSON格式記錄了每個(gè)．bin文件內(nèi)對(duì)應(yīng)的數(shù)據(jù)大小的信息
{
    "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"
        }
    }
}

當(dāng)我們執(zhí)行ALTER操作時(shí)會(huì)報(bào)錯(cuò)，說(shuō)明該表引擎不支持ALTER操作

-- 以下操作會(huì)報(bào)錯(cuò)：
-- 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標(biāo)記文件，支持并行查詢），同時(shí)其使用了更少的文件描述符（所有數(shù)據(jù)使用同一個(gè)文件保存）。

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

SELECT *
FROM emp_stripelog

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

進(jìn)入默認(rèn)數(shù)據(jù)存儲(chǔ)目錄，查看底層數(shù)據(jù)存儲(chǔ)形式

[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表引擎對(duì)應(yīng)的存儲(chǔ)結(jié)構(gòu)包括三個(gè)文件：

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

提示：
StripeLog引擎將所有數(shù)據(jù)都存儲(chǔ)在了一個(gè)文件中，對(duì)于每次的INSERT操作，ClickHouse會(huì)將數(shù)據(jù)塊追加到表文件的末尾
StripeLog引擎同樣不支持ALTER UPDATE 和ALTER DELETE 操作

Log表引擎使用

Log引擎表適用于臨時(shí)數(shù)據(jù)，一次性寫入、測(cè)試場(chǎng)景。Log引擎結(jié)合了TinyLog表引擎和StripeLog表引擎的長(zhǎng)處，是Log系列引擎中性能最高的表引擎。

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

SELECT *
FROM emp_log

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

進(jìn)入默認(rèn)數(shù)據(jù)存儲(chǔ)目錄，查看底層數(shù)據(jù)存儲(chǔ)形式

[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引擎的存儲(chǔ)結(jié)構(gòu)包含三部分：

列.bin：數(shù)據(jù)文件，數(shù)據(jù)文件按列單獨(dú)存儲(chǔ)
__marks.mrk：數(shù)據(jù)標(biāo)記，統(tǒng)一保存了數(shù)據(jù)在各個(gè).bin文件中的位置信息。利用數(shù)據(jù)標(biāo)記能夠使用多個(gè)線程，以并行的方式讀取。.bin內(nèi)的壓縮數(shù)據(jù)塊，從而提升數(shù)據(jù)查詢的性能。
sizes.json：記錄了.bin和__marks.mrk大小的信息

提示：
Log表引擎會(huì)將每一列都存在一個(gè)文件中，對(duì)于每一次的INSERT操作，都會(huì)對(duì)應(yīng)一個(gè)數(shù)據(jù)塊

MergeTree系列引擎

在所有的表引擎中，最為核心的當(dāng)屬M(fèi)ergeTree系列表引擎，這些表引擎擁有最為強(qiáng)大的性能和最廣泛的使用場(chǎng)合。對(duì)于非MergeTree系列的其他引擎而言，主要用于特殊用途，場(chǎng)景相對(duì)有限。而MergeTree系列表引擎是官方主推的存儲(chǔ)引擎，支持幾乎所有ClickHouse核心功能。

MergeTree表引擎

MergeTree在寫入一批數(shù)據(jù)時(shí)，數(shù)據(jù)總會(huì)以數(shù)據(jù)片段的形式寫入磁盤，且數(shù)據(jù)片段不可修改。為了避免片段過(guò)多，ClickHouse會(huì)通過(guò)后臺(tái)線程，定期合并這些數(shù)據(jù)片段，屬于相同分區(qū)的數(shù)據(jù)片段會(huì)被合成一個(gè)新的片段。這種數(shù)據(jù)片段往復(fù)合并的特點(diǎn)，也正是合并樹(shù)名稱的由來(lái)。

MergeTree作為家族系列最基礎(chǔ)的表引擎，主要有以下特點(diǎn)：

存儲(chǔ)的數(shù)據(jù)按照主鍵排序：允許創(chuàng)建稀疏索引，從而加快數(shù)據(jù)查詢速度
支持分區(qū)，可以通過(guò)PRIMARY KEY語(yǔ)句指定分區(qū)字段。
支持?jǐn)?shù)據(jù)副本
支持?jǐn)?shù)據(jù)采樣

建表語(yǔ)法

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引擎沒(méi)有參數(shù)
ORDER BY：排序字段。比如ORDER BY (Col1, Col2)，值得注意的是，如果沒(méi)有指定主鍵，默認(rèn)情況下 sorting key(排序字段)即為主鍵。如果不需要排序，則可以使用**ORDER BY tuple()**語(yǔ)法，這樣的話，創(chuàng)建的表也就不包含主鍵。這種情況下，ClickHouse會(huì)按照插入的順序存儲(chǔ)數(shù)據(jù)。必選。
PARTITION BY：分區(qū)字段，可選。
PRIMARY KEY：指定主鍵，如果排序字段與主鍵不一致，可以單獨(dú)指定主鍵字段。否則默認(rèn)主鍵是排序字段。可選。
SAMPLE BY：采樣字段，如果指定了該字段，那么主鍵中也必須包含該字段。比如 SAMPLE BY intHash42(UserID) ORDER BY (CounterID, EventDate, intHash42(UserID))。可選。
TTL：數(shù)據(jù)的存活時(shí)間。在MergeTree中，可以為某個(gè)列字段或整張表設(shè)置TTL。當(dāng)時(shí)間到達(dá)時(shí)，如果是列字段級(jí)別的TTL，則會(huì)刪除這一列的數(shù)據(jù)；如果是表級(jí)別的TTL，則會(huì)刪除整張表的數(shù)據(jù)。可選。
SETTINGS：額外的參數(shù)配置。可選。

建表示例

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

-- 查詢數(shù)據(jù)
-- 按work_place進(jìn)行分區(qū)
cdh04 :) select * from emp_mergetree;

SELECT *
FROM emp_mergetree

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

查看一下數(shù)據(jù)存儲(chǔ)格式，可以看出，存在三個(gè)分區(qū)文件夾，每一個(gè)分區(qū)文件夾內(nèi)存儲(chǔ)了對(duì)應(yīng)分區(qū)的數(shù)據(jù)。

[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

進(jìn)入一個(gè)分區(qū)目錄查看

ClickHouse的表引擎是什么

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

columns.txt：列信息文件，使用明文格式存儲(chǔ)。用于保存此數(shù)據(jù)分區(qū)下的列字段信息，例如

[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：計(jì)數(shù)文件，使用明文格式存儲(chǔ)。用于記錄當(dāng)前數(shù)據(jù)分區(qū)目錄下數(shù)據(jù)的總行數(shù)
primary.idx：一級(jí)索引文件，使用二進(jìn)制格式存儲(chǔ)。用于存放稀疏索引，一張MergeTree表只能聲明一次一級(jí)索引，即通過(guò)ORDER BY或者PRIMARY KEY指定字段。借助稀疏索引，在數(shù)據(jù)查詢的時(shí)能夠排除主鍵條件范圍之外的數(shù)據(jù)文件，從而有效減少數(shù)據(jù)掃描范圍，加速查詢速度。
列.bin：數(shù)據(jù)文件，使用壓縮格式存儲(chǔ)，默認(rèn)為L(zhǎng)Z4壓縮格式，用于存儲(chǔ)某一列的數(shù)據(jù)。由于MergeTree采用列式存儲(chǔ)，所以每一個(gè)列字段都擁有獨(dú)立的.bin數(shù)據(jù)文件，并以列字段名稱命名。
列.mrk2：列字段標(biāo)記文件，使用二進(jìn)制格式存儲(chǔ)。標(biāo)記文件中保存了.bin文件中數(shù)據(jù)的偏移量信息
partition.dat與minmax_[Column].idx：如果指定了分區(qū)鍵，則會(huì)額外生成partition.dat與minmax索引文件，它們均使用二進(jìn)制格式存儲(chǔ)。partition.dat用于保存當(dāng)前分區(qū)下分區(qū)表達(dá)式最終生成的值，即分區(qū)字段值；而minmax索引用于記錄當(dāng)前分區(qū)下分區(qū)字段對(duì)應(yīng)原始數(shù)據(jù)的最小和最大值。比如當(dāng)使用EventTime字段對(duì)應(yīng)的原始數(shù)據(jù)為2020-09-17、2020-09-30，分區(qū)表達(dá)式為PARTITION BY toYYYYMM(EventTime)，即按月分區(qū)。partition.dat中保存的值將會(huì)是2019-09，而minmax索引中保存的值將會(huì)是2020-09-17 2020-09-30。

注意點(diǎn)

多次插入數(shù)據(jù)，會(huì)生成多個(gè)分區(qū)文件

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

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

可以看出，新插入的數(shù)據(jù)新生成了一個(gè)數(shù)據(jù)塊，并沒(méi)有與原來(lái)的分區(qū)數(shù)據(jù)在一起，我們可以執(zhí)行optimize命令，執(zhí)行合并操作

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

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

執(zhí)行上面的合并操作之后，會(huì)新生成一個(gè)該分區(qū)的文件夾，原理的分區(qū)文件夾不變。

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

-- 插入一條相同主鍵的數(shù)據(jù)
 INSERT INTO emp_mergetree
VALUES (1,'sam','杭州',35,'財(cái)務(wù)部',50000);
-- 會(huì)發(fā)現(xiàn)該條數(shù)據(jù)可以插入，由此可知，并不會(huì)對(duì)主鍵進(jìn)行去重

ReplacingMergeTree表引擎

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

建表語(yǔ)法

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]：可選參數(shù)，列的版本，可以是UInt、Date或者DateTime類型的字段作為版本號(hào)。該參數(shù)決定了數(shù)據(jù)去重的方式。
當(dāng)沒(méi)有指定[ver]參數(shù)時(shí)，保留最新的數(shù)據(jù)；如果指定了具體的值，保留最大的版本數(shù)據(jù)。

建表示例

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

注意點(diǎn)

當(dāng)我們?cè)俅蜗蛟摫聿迦刖哂邢嗤麈I的數(shù)據(jù)時(shí)，觀察查詢數(shù)據(jù)的變化

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

SELECT *
FROM emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術(shù)部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財(cái)務(wù)部 │ 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 │ 技術(shù)部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
-- 執(zhí)行合并操作
optimize table emp_replacingmergetree final;
-- 再次查詢，相同主鍵的數(shù)據(jù)，保留最近插入的數(shù)據(jù)，舊的數(shù)據(jù)被清除
cdh04 :) select * from emp_replacingmergetree;       

SELECT *
FROM emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術(shù)部 │ 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 │ 財(cái)務(wù)部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

從上面的示例中可以看出，ReplacingMergeTree是支持對(duì)數(shù)據(jù)去重的，那么是根據(jù)什么進(jìn)行去重呢？答案是：ReplacingMergeTree在去除重復(fù)數(shù)據(jù)時(shí)，是以O(shè)RDERBY排序鍵為基準(zhǔn)的，而不是PRIMARY KEY。我們?cè)诳匆粋€(gè)示例：

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

再次向該表中插入相同emp_id和name的數(shù)據(jù)，并執(zhí)行合并操作，再觀察數(shù)據(jù)

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

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

至此，我們知道了ReplacingMergeTree是支持去重的，并且是按照ORDERBY排序鍵為基準(zhǔn)進(jìn)行去重的。細(xì)心的你會(huì)發(fā)現(xiàn)，上面的重復(fù)數(shù)據(jù)是在一個(gè)分區(qū)內(nèi)的，那么如果重復(fù)的數(shù)據(jù)不在一個(gè)分區(qū)內(nèi)，會(huì)發(fā)生什么現(xiàn)象呢？我們?cè)俅蜗蛏厦娴?strong>emp_replacingmergetree1表插入不同分區(qū)的重復(fù)數(shù)據(jù)

-- 插入數(shù)據(jù)
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','北京',26,'技術(shù)部',10000);
-- 執(zhí)行合并操作
optimize table emp_replacingmergetree1 final;
-- 再次查詢
-- 發(fā)現(xiàn)  1 │ tom  │ 北京       │  26 │ 技術(shù)部 │ 10000.00
-- 與    1 │ tom  │ 上海       │  25 │ 技術(shù)部 │ 50000.00
-- 數(shù)據(jù)重復(fù)，因?yàn)檫@兩行數(shù)據(jù)不在同一個(gè)分區(qū)內(nèi)
-- 這是因?yàn)镽eplacingMergeTree是以分區(qū)為單位刪除重復(fù)數(shù)據(jù)的。
-- 只有在相同的數(shù)據(jù)分區(qū)內(nèi)重復(fù)的數(shù)據(jù)才可以被刪除，而不同數(shù)據(jù)分區(qū)之間的重復(fù)數(shù)據(jù)依然不能被剔除
cdh04 :) select * from emp_replacingmergetree1;       

SELECT *
FROM emp_replacingmergetree1

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

總結(jié)

如何判斷數(shù)據(jù)重復(fù)

ReplacingMergeTree在去除重復(fù)數(shù)據(jù)時(shí)，是以O(shè)RDERBY排序鍵為基準(zhǔn)的，而不是PRIMARY KEY。

何時(shí)刪除重復(fù)數(shù)據(jù)

在執(zhí)行分區(qū)合并時(shí)，會(huì)觸發(fā)刪除重復(fù)數(shù)據(jù)。optimize的合并操作是在后臺(tái)執(zhí)行的，無(wú)法預(yù)測(cè)具體執(zhí)行時(shí)間點(diǎn)，除非是手動(dòng)執(zhí)行。

不同分區(qū)的重復(fù)數(shù)據(jù)不會(huì)被去重

ReplacingMergeTree是以分區(qū)為單位刪除重復(fù)數(shù)據(jù)的。只有在相同的數(shù)據(jù)分區(qū)內(nèi)重復(fù)的數(shù)據(jù)才可以被刪除，而不同數(shù)據(jù)分區(qū)之間的重復(fù)數(shù)據(jù)依然不能被剔除。

數(shù)據(jù)去重的策略是什么

如果沒(méi)有設(shè)置**[ver]版本號(hào)**，則保留同一組重復(fù)數(shù)據(jù)中的最新插入的數(shù)據(jù)；如果設(shè)置了**[ver]版本號(hào)**，則保留同一組重復(fù)數(shù)據(jù)中ver字段取值最大的那一行。

optimize命令使用

一般在數(shù)據(jù)量比較大的情況，盡量不要使用該命令。因?yàn)樵诤Ａ繑?shù)據(jù)場(chǎng)景下，執(zhí)行optimize要消耗大量時(shí)間

SummingMergeTree表引擎

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

推薦將該引擎和 MergeTree 一起使用。例如，將完整的數(shù)據(jù)存儲(chǔ)在 MergeTree 表中，并且使用 SummingMergeTree 來(lái)存儲(chǔ)聚合數(shù)據(jù)。這種方法可以避免因?yàn)槭褂貌徽_的主鍵組合方式而丟失數(shù)據(jù)。

如果用戶只需要查詢數(shù)據(jù)的匯總結(jié)果，不關(guān)心明細(xì)數(shù)據(jù)，并且數(shù)據(jù)的匯總條件是預(yù)先明確的，即GROUP BY的分組字段是確定的，可以使用該表引擎。

建表語(yǔ)法

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 '工作地點(diǎn)',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=SummingMergeTree(salary)
  ORDER BY (emp_id,name) -- 注意排序key是兩個(gè)字段
  PRIMARY KEY emp_id     -- 主鍵是一個(gè)字段
  PARTITION BY work_place
  ;
 -- 插入數(shù)據(jù) 
INSERT INTO emp_summingmergetree
VALUES (1,'tom','上海',25,'技術(shù)部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_summingmergetree
VALUES (3,'bob','北京',33,'財(cái)務(wù)部',50000),(4,'tony','杭州',28,'銷售事部',50000);

當(dāng)我們?cè)俅尾迦刖哂邢嗤琫mp_id,name的數(shù)據(jù)時(shí)，觀察結(jié)果

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 │ 財(cái)務(wù)部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術(shù)部 │ 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 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
-- 執(zhí)行合并操作
optimize table emp_summingmergetree final;
cdh04 :) select * from emp_summingmergetree;       
-- 再次查詢，新插入的數(shù)據(jù) 1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
-- 原來(lái)的數(shù)據(jù) ：        1 │ tom  │ 上海       │  25 │ 技術(shù)部 │ 20000.00
-- 這兩行數(shù)據(jù)合并成：    1 │ tom  │ 上海       │  25 │ 技術(shù)部 │ 30000.00
SELECT *
FROM emp_summingmergetree

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

注意點(diǎn)

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

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

這種強(qiáng)制約束保障了即便在兩者定義不同的情況下，主鍵仍然是排序鍵的前綴，不會(huì)出現(xiàn)索引與數(shù)據(jù)順序混亂的問(wèn)題。

總結(jié)

SummingMergeTree是根據(jù)什么對(duì)兩條數(shù)據(jù)進(jìn)行合并的

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

僅對(duì)分區(qū)內(nèi)的相同排序key的數(shù)據(jù)行進(jìn)行合并

以數(shù)據(jù)分區(qū)為單位來(lái)聚合數(shù)據(jù)。當(dāng)分區(qū)合并時(shí)，同一數(shù)據(jù)分區(qū)內(nèi)聚合Key相同的數(shù)據(jù)會(huì)被合并匯總，而不同分區(qū)之間的數(shù)據(jù)則不會(huì)被匯總。

如果沒(méi)有指定聚合字段，會(huì)怎么聚合

如果沒(méi)有指定聚合字段，則會(huì)按照非主鍵的數(shù)值類型字段進(jìn)行聚合

對(duì)于非匯總字段的數(shù)據(jù)，該保留哪一條

如果兩行數(shù)據(jù)除了排序字段相同，其他的非聚合字段不相同，那么在聚合發(fā)生時(shí)，會(huì)保留最初的那條數(shù)據(jù)，新插入的數(shù)據(jù)對(duì)應(yīng)的那個(gè)字段值會(huì)被舍棄

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

-- 聚合合并的結(jié)果：      1 │ tom  │ 上海       │  25 │ 技術(shù)部 │ 30000.00

Aggregatingmergetree表引擎

該表引擎繼承自MergeTree，可以使用 AggregatingMergeTree 表來(lái)做增量數(shù)據(jù)統(tǒng)計(jì)聚合。如果要按一組規(guī)則來(lái)合并減少行數(shù)，則使用 AggregatingMergeTree 是合適的。AggregatingMergeTree是通過(guò)預(yù)先定義的聚合函數(shù)計(jì)算數(shù)據(jù)并通過(guò)二進(jìn)制的格式存入表內(nèi)。

與SummingMergeTree的區(qū)別在于：SummingMergeTree對(duì)非主鍵列進(jìn)行sum聚合，而AggregatingMergeTree則可以指定各種聚合函數(shù)。

建表語(yǔ)法

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 '工作地點(diǎn)',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary  AggregateFunction(sum,Decimal32(2)) COMMENT '工資'  
  )ENGINE=AggregatingMergeTree()
  ORDER BY (emp_id,name) -- 注意排序key是兩個(gè)字段
  PRIMARY KEY emp_id     -- 主鍵是一個(gè)字段
  PARTITION BY work_place
  ;

對(duì)于AggregateFunction類型的列字段，在進(jìn)行數(shù)據(jù)的寫入和查詢時(shí)與其他的表引擎有很大區(qū)別，在寫入數(shù)據(jù)時(shí)，需要調(diào)用** -State 函數(shù)；而在查詢數(shù)據(jù)時(shí)，則需要調(diào)用相應(yīng)的 -Merge 函數(shù)。對(duì)于上面的建表語(yǔ)句而言，需要使用sumState**函數(shù)進(jìn)行數(shù)據(jù)插入

-- 插入數(shù)據(jù)，
-- 注意：需要使用INSERT…SELECT語(yǔ)句進(jìn)行數(shù)據(jù)插入
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));
-- 查詢數(shù)據(jù)
SELECT 
  emp_id,
  name , 
  sumMerge(salary) 
FROM emp_aggregatingmergeTree
GROUP BY emp_id,name;
-- 結(jié)果輸出
┌─emp_id─┬─name─┬─sumMerge(salary)─┐
│      1 │ tom  │         30000.00 │
└────────┴──────┴──────────────────┘

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

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

-- 創(chuàng)建一個(gè)MereTree引擎的明細(xì)表
-- 用于存儲(chǔ)全量的明細(xì)數(shù)據(jù)
-- 對(duì)外提供實(shí)時(shí)查詢
CREATE TABLE emp_mergetree_base (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點(diǎn)',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=MergeTree()
  ORDER BY (emp_id,name)
  PARTITION BY work_place
  ;
  
-- 創(chuàng)建一張物化視圖
-- 使用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;

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

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

CollapsingMergeTree表引擎

CollapsingMergeTree就是一種通過(guò)以增代刪的思路，支持行級(jí)數(shù)據(jù)修改和刪除的表引擎。它通過(guò)定義一個(gè)sign標(biāo)記位字段，記錄數(shù)據(jù)行的狀態(tài)。如果sign標(biāo)記為1，則表示這是一行有效的數(shù)據(jù)；如果sign標(biāo)記為-1，則表示這行數(shù)據(jù)需要被刪除。當(dāng)CollapsingMergeTree分區(qū)合并時(shí)，同一數(shù)據(jù)分區(qū)內(nèi)，sign標(biāo)記為1和-1的一組數(shù)據(jù)會(huì)被抵消刪除。

每次需要新增數(shù)據(jù)時(shí)，寫入一行sign標(biāo)記為1的數(shù)據(jù)；需要?jiǎng)h除數(shù)據(jù)時(shí)，則寫入一行sign標(biāo)記為-1的數(shù)據(jù)。

建表語(yǔ)法

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, ...]

建表示例

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

CREATE TABLE emp_collapsingmergetree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點(diǎn)',
  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同樣是以O(shè)RDER BY排序鍵作為判斷數(shù)據(jù)唯一性的依據(jù)。

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

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

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

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

SELECT *
FROM emp_collapsingmergetree

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

SELECT *
FROM emp_collapsingmergetree

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

注意點(diǎn)

分區(qū)合并

分?jǐn)?shù)數(shù)據(jù)折疊不是實(shí)時(shí)的，需要后臺(tái)進(jìn)行Compaction操作，用戶也可以使用手動(dòng)合并命令，但是效率會(huì)很低，一般不推薦在生產(chǎn)環(huán)境中使用。

當(dāng)進(jìn)行匯總數(shù)據(jù)操作時(shí)，可以通過(guò)改變查詢方式，來(lái)過(guò)濾掉被刪除的數(shù)據(jù)

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

只有相同分區(qū)內(nèi)的數(shù)據(jù)才有可能被折疊。其實(shí)，當(dāng)我們修改或刪除數(shù)據(jù)時(shí)，這些被修改的數(shù)據(jù)通常是在一個(gè)分區(qū)內(nèi)的，所以不會(huì)產(chǎn)生影響。

數(shù)據(jù)寫入順序

值得注意的是：CollapsingMergeTree對(duì)于寫入數(shù)據(jù)的順序有著嚴(yán)格要求，否則導(dǎo)致無(wú)法正常折疊。

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

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

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

VersionedCollapsingMergeTree表引擎

上面提到CollapsingMergeTree表引擎對(duì)于數(shù)據(jù)寫入亂序的情況下，不能夠?qū)崿F(xiàn)數(shù)據(jù)折疊的效果。VersionedCollapsingMergeTree表引擎的作用與CollapsingMergeTree完全相同，它們的不同之處在于，VersionedCollapsingMergeTree對(duì)數(shù)據(jù)的寫入順序沒(méi)有要求，在同一個(gè)分區(qū)內(nèi)，任意順序的數(shù)據(jù)都能夠完成折疊操作。

VersionedCollapsingMergeTree使用version列來(lái)實(shí)現(xiàn)亂序情況下的數(shù)據(jù)折疊。

建表語(yǔ)法

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, ...]

可以看出：該引擎除了需要指定一個(gè)sign標(biāo)識(shí)之外，還需要指定一個(gè)UInt8類型的version版本號(hào)。

建表示例

CREATE TABLE emp_versioned (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點(diǎn)',
  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
  ;
  
  -- 先插入需要被刪除的數(shù)據(jù)，即sign=-1的數(shù)據(jù)
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技術(shù)部',20000,-1,1);
-- 再插入sign=1的數(shù)據(jù)
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技術(shù)部',20000,1,1);
-- 在插入一個(gè)新版本數(shù)據(jù)
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技術(shù)部',30000,1,2);

-- 先不執(zhí)行合并，查看表數(shù)據(jù)
cdh04 :) select * from emp_versioned;

SELECT *
FROM emp_versioned

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

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

-- 手動(dòng)合并
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 │ 技術(shù)部 │ 30000.00 │    1 │       2 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

可見(jiàn)上面雖然在插入數(shù)據(jù)亂序的情況下，依然能夠?qū)崿F(xiàn)折疊的效果。之所以能夠達(dá)到這種效果，是因?yàn)樵诙xversion字段之后，VersionedCollapsingMergeTree會(huì)自動(dòng)將version作為排序條件并增加到ORDER BY的末端，就上述的例子而言，最終的排序字段為ORDER BY emp_id,name，version desc。

GraphiteMergeTree表引擎

該引擎用來(lái)對(duì) Graphite數(shù)據(jù)進(jìn)行'瘦身'及匯總。對(duì)于想使用CH來(lái)存儲(chǔ)Graphite數(shù)據(jù)的開(kāi)發(fā)者來(lái)說(shuō)可能有用。

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

外部集成表引擎

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

例如直接讀取HDFS的文件或者M(jìn)ySQL數(shù)據(jù)庫(kù)的表。這些表引擎只負(fù)責(zé)元數(shù)據(jù)管理和數(shù)據(jù)查詢，而它們自身通常并不負(fù)責(zé)數(shù)據(jù)的寫入，數(shù)據(jù)文件直接由外部系統(tǒng)提供。目前ClickHouse提供了下面的外部集成表引擎：

ODBC：通過(guò)指定odbc連接讀取數(shù)據(jù)源
JDBC：通過(guò)指定jdbc連接讀取數(shù)據(jù)源；
MySQL：將MySQL作為數(shù)據(jù)存儲(chǔ)，直接查詢其數(shù)據(jù)
HDFS：直接讀取HDFS上的特定格式的數(shù)據(jù)文件；
Kafka：將Kafka數(shù)據(jù)導(dǎo)入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 '工作地點(diǎn)',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
) ENGINE=HDFS('hdfs://cdh03:8020/user/hive/hdfs_engine_table', 'CSV');

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

SELECT *
FROM hdfs_engine_table

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

SELECT *
FROM hdfs_engine_table

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

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

值得注意的是：ClickHouse并不能夠刪除HDFS上的數(shù)據(jù)，當(dāng)我們?cè)贑lickHouse客戶端中刪除了對(duì)應(yīng)的表，只是刪除了表結(jié)構(gòu)，HDFS上的文件并沒(méi)有被刪除，這一點(diǎn)跟Hive的外部表十分相似。

MySQL

在上一篇文章[篇一|ClickHouse快速入門]中介紹了MySQL數(shù)據(jù)庫(kù)引擎，即ClickHouse可以創(chuàng)建一個(gè)MySQL數(shù)據(jù)引擎，這樣就可以在ClickHouse中操作其對(duì)應(yīng)的數(shù)據(jù)庫(kù)中的數(shù)據(jù)。其實(shí)，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數(shù)據(jù)庫(kù)的test表
CREATE TABLE mysql_engine_table(
    id Int32,
    name String
) ENGINE = MySQL(
 '192.168.200.241:3306',
 'clickhouse',
 'test', 
 'root', 
 '123qwe');
-- 查詢數(shù)據(jù)
cdh04 :) SELECT * FROM mysql_engine_table;

SELECT *
FROM mysql_engine_table

┌─id─┬─name──┐
│  1 │ tom   │
│  2 │ jack  │
│  3 │ lihua │
└────┴───────┘
-- 插入數(shù)據(jù)，會(huì)將數(shù)據(jù)插入MySQL對(duì)應(yīng)的表中
-- 所以當(dāng)查詢MySQL數(shù)據(jù)時(shí)，會(huì)發(fā)現(xiàn)新增了一條數(shù)據(jù)
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 │
└────┴───────┘

注意：對(duì)于MySQL表引擎，不支持UPDATE和DELETE操作，比如執(zhí)行下面命令時(shí)，會(huì)報(bào)錯(cuò)：

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

-- 報(bào)錯(cuò)
DB::Exception: Mutations are not supported by storage MySQL.

JDBC

使用方式

JDBC表引擎不僅可以對(duì)接MySQL數(shù)據(jù)庫(kù)，還能夠與PostgreSQL等數(shù)據(jù)庫(kù)。為了實(shí)現(xiàn)JDBC連接，ClickHouse使用了clickhouse-jdbc-bridge的查詢代理服務(wù)。

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

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

其中--driver-path是MySQL驅(qū)動(dòng)的jar所在的路徑，listen-host是代理服務(wù)綁定的主機(jī)。默認(rèn)情況下，綁定的端口是：9019。上述jar包的下載：

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

然后我們?cè)倥渲?code>/etc/clickhouse-server/config.xml，在文件中添加如下配置，然后重啟服務(wù)。

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

使用示例

直接查詢MySQL中對(duì)應(yīng)的表

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

創(chuàng)建一張映射表

-- 語(yǔ)法
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');

-- 查詢數(shù)據(jù)
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 ：逗號(hào)分隔的brokers地址 (localhost:9092).
kafka_topic_list ：Kafka 主題列表，多個(gè)主題用逗號(hào)分隔.
kafka_group_name ：消費(fèi)者組.
kafka_format – Message format. 比如 JSONEachRow、JSON、CSV等等

使用示例

在kafka中創(chuàng)建ck_topic主題，并向該主題寫入數(shù)據(jù)

 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 │
└────┴──────┘

注意點(diǎn)

當(dāng)我們一旦查詢完畢之后，ClickHouse會(huì)刪除表內(nèi)的數(shù)據(jù)，其實(shí)Kafka表引擎只是一個(gè)數(shù)據(jù)管道，我們可以通過(guò)物化視圖的方式訪問(wèn)Kafka中的數(shù)據(jù)。

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

--  創(chuàng)建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'
;

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

SELECT *
FROM kafka_table_mergetree

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

其他特殊的表引擎

Memory表引擎

Memory表引擎直接將數(shù)據(jù)保存在內(nèi)存中，數(shù)據(jù)既不會(huì)被壓縮也不會(huì)被格式轉(zhuǎn)換。當(dāng)ClickHouse服務(wù)重啟的時(shí)候，Memory表內(nèi)的數(shù)據(jù)會(huì)全部丟失。一般在測(cè)試時(shí)使用。

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

Distributed表引擎

使用方式

Distributed表引擎是分布式表的代名詞，它自身不存儲(chǔ)任何數(shù)據(jù)，數(shù)據(jù)都分散存儲(chǔ)在某一個(gè)分片上，能夠自動(dòng)路由數(shù)據(jù)至集群中的各個(gè)節(jié)點(diǎn)，所以Distributed表引擎需要和其他數(shù)據(jù)表引擎一起協(xié)同工作。

所以，一張分布式表底層會(huì)對(duì)應(yīng)多個(gè)本地分片數(shù)據(jù)表，由具體的分片表存儲(chǔ)數(shù)據(jù)，分布式表與分片表是一對(duì)多的關(guān)系

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

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

各個(gè)參數(shù)的含義分別如下：

cluster_name：集群名稱，與集群配置中的自定義名稱相對(duì)應(yīng)。
database_name：數(shù)據(jù)庫(kù)名稱
table_name：表名稱
sharding_key：可選的，用于分片的key值，在數(shù)據(jù)寫入的過(guò)程中，分布式表會(huì)依據(jù)分片key的規(guī)則，將數(shù)據(jù)分布到各個(gè)節(jié)點(diǎn)的本地表。

尖叫提示：
創(chuàng)建分布式表是讀時(shí)檢查的機(jī)制，也就是說(shuō)對(duì)創(chuàng)建分布式表和本地表的順序并沒(méi)有強(qiáng)制要求。
同樣值得注意的是，在上面的語(yǔ)句中使用了ON CLUSTER分布式DDL，這意味著在集群的每個(gè)分片節(jié)點(diǎn)上，都會(huì)創(chuàng)建一張Distributed表，這樣便可以從其中任意一端發(fā)起對(duì)所有分片的讀、寫請(qǐng)求。

使用示例

-- 創(chuàng)建一張分布式表
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);

創(chuàng)建完成上面的分布式表時(shí)，在每臺(tái)機(jī)器上查看表，發(fā)現(xiàn)每臺(tái)機(jī)器上都存在一張剛剛創(chuàng)建好的表。

接下來(lái)就需要?jiǎng)?chuàng)建本地表了，在每臺(tái)機(jī)器上分別創(chuàng)建一張本地表：

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

我們先在一臺(tái)機(jī)器上，對(duì)user_local表進(jìn)行插入數(shù)據(jù)，然后再查詢user_cluster表

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

接下來(lái)，我們?cè)傧騯ser_cluster中插入一些數(shù)據(jù)，觀察user_local表數(shù)據(jù)變化，可以發(fā)現(xiàn)數(shù)據(jù)被分散存儲(chǔ)到了其他節(jié)點(diǎn)上了。

-- 向user_cluster插入數(shù)據(jù)
cdh04 :)  INSERT INTO user_cluster VALUES(3,'lilei'),(4,'lihua'); 
-- 查看user_cluster數(shù)據(jù)
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 │
└────┴───────┘

到此，相信大家對(duì)“ClickHouse的表引擎是什么”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！

向AI問(wèn)一下細(xì)節(jié)

ClickHouse的表引擎是什么

表引擎的作用是什么

表引擎分類

Log系列表引擎

應(yīng)用場(chǎng)景

Log系列表引擎的特點(diǎn)

共性特點(diǎn)

區(qū)別

TinyLog表引擎使用

StripLog表引擎使用

Log表引擎使用

MergeTree系列引擎

MergeTree表引擎

建表語(yǔ)法

建表示例

注意點(diǎn)

ReplacingMergeTree表引擎

建表語(yǔ)法

建表示例

注意點(diǎn)

總結(jié)

SummingMergeTree表引擎

建表語(yǔ)法

建表示例

注意點(diǎn)

總結(jié)

Aggregatingmergetree表引擎

建表語(yǔ)法

建表示例

CollapsingMergeTree表引擎

建表語(yǔ)法

建表示例

使用方式

注意點(diǎn)

VersionedCollapsingMergeTree表引擎

建表語(yǔ)法

建表示例

GraphiteMergeTree表引擎

外部集成表引擎

HDFS

使用方式

使用示例

MySQL

使用方式

使用示例

JDBC

使用方式

使用示例

Kafka

使用方式

使用示例

注意點(diǎn)

其他特殊的表引擎

Memory表引擎

Distributed表引擎

使用方式

使用示例

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽