大資料ClickHouse（十）：MergeTree系清單引擎之SummingMergeTree

文章目錄

MergeTree系清單引擎之SummingMergeTree

一、SummingMergeTree基本講解

二、使用SummingMergeTree注意以下幾點

三、測試執行個體

1、測試不指定聚合字段同時測試不同分區内，相同排序key資料不會被合并

2、測試指定一個聚合字段

3、測試指定多個聚合字段

MergeTree系清單引擎之SummingMergeTree

一、SummingMergeTree基本講解

該引擎繼承了MergeTree引擎，當合并 SummingMergeTree 表的資料片段時，ClickHouse 會把所有具有相同主鍵的行合并為一行，該行包含了被合并的行中具有數值資料類型的列的彙總值，即如果存在重複的資料，會對對這些重複的資料進行合并成一條資料，類似于group by的效果，可以顯著減少存儲空間并加快資料查詢速度。

如果使用者隻需要查詢資料的彙總結果，不關心明細資料，并且資料的彙總條件是預先明确的，即GROUP BY的分組字段是确定的，可以使用該表引擎。

SummingMergeTree建表語句：

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

對以上建表語句的解釋如下：

[columns]: 将要被彙總的列,或者多個列，多個列需要寫在元組中。可選參數。所選的列必須是數值類型，并且不可位于主鍵中。如果沒有指定 [columns]，ClickHouse 會把所有不在主鍵中的數值類型的列都進行彙總。

二、使用SummingMergeTree注意以下幾點

SummingMergeTree是根據什麼對兩條資料進行合并的

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

僅對分區内的相同排序key的資料行進行合并

以資料分區為機關來聚合資料。當分區合并時，同一資料分區内聚合Key相同的資料會被合并彙總，而不同分區之間的資料則不會被彙總。

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

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

對于非彙總字段的資料，該保留哪一條

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

三、測試執行個體

1、測試不指定聚合字段同時測試不同分區内，相同排序key資料不會被合并

#建立表 t_summing_mt ,使用SummingMergeTree表引擎
node1 :) create table t_summing_mt(
:-] id UInt8,
:-] name String,
:-] age UInt8,
:-] loc String,
:-] dept String,
:-] workdays UInt8,
:-] salary Decimal32(2)
:-] ) engine = SummingMergeTree()
:-] order by (id,age)
:-] primary key id
:-] partition by loc;

#向表 t_summing_mt 中插入以下資料
node1 :) insert into t_summing_mt values (1,'張三',18,'北京','大資料',24,10000),
:-] (2,'李四',19,'上海','java',22,8000),
:-] (3,'王五',20,'北京','java',26,12000);

#檢視表 t_summing_mt 中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 北京   │ 大資料  │       24   │ 10000.00 │
│  3  │ 王五  │  20 │ 北京   │ java   │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19 │ 上海   │ java │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

#向表 t_summing_mt 中插入一條排序鍵相同的資料
node1 :) insert into t_summing_mt values (1,'馬六',18,'北京','前端',27,15000);

#檢視表 t_summing_mt中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 北京  │ 大資料   │       24   │ 10000.00 │
│  3  │ 王五  │  20 │ 北京  │ java    │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐
│  1  │ 馬六  │  18  │ 北京  │ 前端  │       27   │ 15000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19 │ 上海  │ java  │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

#手動執行optimize 指令觸發合并相同分區資料
node1 :) optimize table t_summing_mt;

#檢視表 t_summing_mt 中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18  │ 北京  │ 大資料  │       51   │ 25000.00 │
│  3  │ 王五  │  20  │ 北京  │ java   │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19  │ 上海  │ java │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘
注意：我們可以看到當不指定 聚合字段時，有相同排序字段行進行聚合時，會将數值類型的字段進行聚合合并。

#繼續向表 t_summing_mt中插入以下資料：
node1 :) insert into t_summing_mt values (1,'張三',18,'南京','java',18,12000);

#檢視表 t_summing_mt中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 南京  │ java  │       18  │ 12000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 北京  │ 大資料   │       51  │ 25000.00 │
│  3  │ 王五  │  20 │ 北京  │ java     │       26 │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19 │ 上海  │ java │       22   │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

#手動指定optimize 指令合并相同排序key的資料
node1 :) optimize table t_summing_mt;

#檢視表 t_summing_mt中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 南京  │ java  │       18  │ 12000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 北京  │ 大資料   │       51  │ 25000.00 │
│  3  │ 王五  │  20 │ 北京  │ java     │       26 │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19 │ 上海  │ java │       22   │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

2、測試指定一個聚合字段

#删除表 t_summing_mt，重新建立表 t_summing_mt ，使用SummingMergeTree引擎
node1 :) create table t_summing_mt(
:-] id UInt8,
:-] name String,
:-] age UInt8,
:-] loc String,
:-] dept String,
:-] workdays UInt8,
:-] salary Decimal32(2)
:-] ) engine = SummingMergeTree(salary)
:-] order by (id,age)
:-] primary key id
:-] partition by loc;

#向表 t_summing_mt 中插入以下資料
node1 :) insert into t_summing_mt values (1,'張三',18,'北京','大資料',24,10000),
:-] (2,'李四',19,'上海','java',22,8000),
:-] (3,'王五',20,'北京','java',26,12000);

#檢視表 t_summing_mt 中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 北京   │ 大資料  │       24   │ 10000.00 │
│  3  │ 王五  │  20 │ 北京   │ java   │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19 │ 上海   │ java │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

#向表 t_summing_mt 中插入一條排序鍵相同的資料
node1 :) insert into t_summing_mt values (1,'馬六',18,'北京','前端',27,15000);

#檢視表 t_summing_mt中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 北京  │ 大資料   │       24   │ 10000.00 │
│  3  │ 王五  │  20 │ 北京  │ java    │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐
│  1  │ 馬六  │  18  │ 北京  │ 前端  │       27   │ 15000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19 │ 上海  │ java  │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

#手動執行optimize 指令觸發合并相同分區資料
node1 :) optimize table t_summing_mt;

#檢視表 t_summing_mt 中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18  │ 北京  │ 大資料  │       24   │ 25000.00 │
│  3  │ 王五  │   20 │ 北京  │ java   │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19  │ 上海  │ java │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

3、測試指定多個聚合字段

#删除表 t_summing_mt，重新建立表 t_summing_mt ，使用SummingMergeTree引擎
node1 :) create table t_summing_mt(
:-] id UInt8,
:-] name String,
:-] age UInt8,
:-] loc String,
:-] dept String,
:-] workdays UInt8,
:-] salary Decimal32(2)
:-] ) engine = SummingMergeTree((salary,workdays))
:-] order by (id,age)
:-] primary key id
:-] partition by loc;

#向表 t_summing_mt 中插入以下資料
node1 :) insert into t_summing_mt values (1,'張三',18,'北京','大資料',24,10000),
:-] (2,'李四',19,'上海','java',22,8000),
:-] (3,'王五',20,'北京','java',26,12000);

#檢視表 t_summing_mt 中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 北京   │ 大資料  │       24   │ 10000.00 │
│  3  │ 王五  │  20 │ 北京   │ java   │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19 │ 上海   │ java │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

#向表 t_summing_mt 中插入一條排序鍵相同的資料
node1 :) insert into t_summing_mt values (1,'馬六',18,'北京','前端',27,15000);

#檢視表 t_summing_mt中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18 │ 北京  │ 大資料   │       24   │ 10000.00 │
│  3  │ 王五  │  20 │ 北京  │ java    │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐
│  1  │ 馬六  │  18  │ 北京  │ 前端  │       27   │ 15000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19 │ 上海  │ java  │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

#手動執行optimize 指令觸發合并相同分區資料
node1 :) optimize table t_summing_mt;

#檢視表 t_summing_mt 中的資料
node1 :) select * from t_summing_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐
│  1  │ 張三  │  18  │ 北京  │ 大資料  │       51   │ 25000.00 │
│  3  │ 王五  │   20 │ 北京  │ java   │       26   │ 12000.00 │
└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐
│  2  │ 李四  │  19  │ 上海  │ java │       22  │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

📢歡迎點贊 👍 收藏 ⭐留言 📝 如有錯誤敬請指正！
📢本文由 Lansonli 原創
📢停下休息的時候不要忘了别人還在奔跑，希望大家抓緊時間學習，全力奔赴更美好的生活✨

大資料ClickHouse（十）：MergeTree系清單引擎之SummingMergeTree

MergeTree系清單引擎之SummingMergeTree

一、SummingMergeTree基本講解

二、使用SummingMergeTree注意以下幾點

三、測試執行個體

1、測試不指定聚合字段同時測試不同分區内，相同排序key資料不會被合并

2、測試指定一個聚合字段

3、測試指定多個聚合字段

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Ambari介紹和架構原理

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

win10本地scala和spark安裝安裝scala安裝spark