clickhouse CollapsingMergeTree表引擎

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);

-- 檢視一下結果
select * from emp_collapsingmergetree ;
┌─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 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

-- 執行分區合并操作
optimize table emp_collapsingmergetree;
-- 再次查詢，sign=1與sign=-1的資料互相抵消了，即被删除
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;
┌─emp_id─┬─name─┬─sum(multiply(salary, sign))─┐
│      1 │ tom  │                    30000.00 │
└────────┴──────┴─────────────────────────────┘

隻有

相同分區内

的資料才有可能被折疊。其實，當我們修改或删除資料時，這些被修改的資料通常是在一個分區内的，是以不會産生影響。

資料寫入順序

值得注意的是：

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);

-- 先不執行合并，檢視表資料
select * from emp_versioned;
┌─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 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00 │    1 │       2 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

-- 擷取正确查詢結果
SELECT emp_id,name,sum(salary * sign) FROM emp_versioned GROUP BY emp_id,name HAVING sum(sign) > 0;
┌─emp_id─┬─name─┬─sum(multiply(salary, sign))─┐
│      1 │ tom  │                    30000.00 │
└────────┴──────┴─────────────────────────────┘

-- 手動合并
optimize table 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

。

clickhouse CollapsingMergeTree表引擎

CollapsingMergeTree表引擎

建表文法

建表示例

使用方式

注意點

VersionedCollapsingMergeTree表引擎

建表文法

建表示例

繼續閱讀

clickhouse字元串函數

Clickhouse 字元串函數

ClickHouse JDBC Bridge 的安裝及連接配接sqlserver

Clickhouse 專欄---資料模型之留存分析

Kettle連接配接Clickhouse 自定義插件遇到的問題：注意：

CentOS7 Linux 安裝 clickhouse

ClickHouse部署CentOS7

clickhouse 一（centos7安裝clickhouse，設定使用者名密碼）

Clickhouse centos7叢集搭建Clickhouse centos7叢集搭建

centos7 yum 安裝及解除安裝 Clickhouse

clickhouse離線安裝和解除安裝1 clickhouse簡介2 安裝3 Clickhouse 啟動與驗證4 解除安裝

ClickHouse單節點安裝配置（CentOS7系統）關閉防火牆驗證是否支援SSE4.2指令集RPM包下載下傳安裝依賴下載下傳安裝啟動ClickHouse遠端通路配置密碼配置

clickhouse-1-clickhouse在centos7中的安裝和解除安裝1 clickhouse簡介2 離線安裝3 線上安裝

SqlServer替換換行符

Clickhouse常用系統表概覽一、常用系統表

【大資料開發】ClickHouse——深入ClickHouse第一章 clickhouse概述第二章單機版安裝第三章叢集安裝第四章入門操作第五章庫表引擎第六章函數第六章函數