CC00016.clickhouse——|Hadoop&OLAP_ClickHouse.V13|——|ClickHouse.v13|MergeTree家族表引擎|

yanqi_vip

已于 2022-04-14 17:08:21 修改

阅读量111

点赞数

分类专栏： bigdatav021——olapclickHouse 文章标签： java hive 大数据 mysql python

于 2022-04-13 15:38:00 首次发布

不予转载

本文链接：https://blog.csdn.net/yanqi_vip/article/details/124162417

版权

bigdatav021——olapclickHouse 专栏收录该内容

45 篇文章 0 订阅

订阅专栏

一、MergeTree家族表引擎：SummingMergeTree

### --- SummingMergeTree

~~~     该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数据片段时，
~~~     ClickHouse 会把所有具有相同聚合数据的条件Key的行合并为一行，
~~~     该行包含了被合并的行中具有数值数据类型的列的汇总值。
~~~     如果聚合数据的条件Key的组合方式使得单个键值对应于大量的行，
~~~     则可以显著的减少存储空间并加快数据查询的速度，对于不可加的列，会取一个最先出现的值。

### --- 特征：

~~~     用ORDER BY排序键作为聚合数据的条件Key
~~~     合并分区的时候触发汇总逻辑
~~~     以数据分区为单位聚合数据，不同分区的数据不会被汇总
~~~     如果在定义引擎时指定了Columns汇总列(非主键)则SUM汇总这些字段
~~~     如果没有指定，则汇总所有非主键的数值类型字段
~~~     SUM汇总相同的聚合Key的数据，依赖ORDER BY排序
~~~     同一分区的SUM汇总过程中，非汇总字段的数据保留第一行取值
~~~     支持嵌套结构，但列字段名称必须以Map后缀结束。

### --- 语法：
~~~     columns — 包含将要被汇总的列的列名的元组

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

二、SummingMergeTree案例一

### --- 创建表

~~~     # 建表
hadoop01 :) create table smt_table (date Date, name String, a UInt16, b UInt16) ENGINE=SummingMergeTree(date, (date, name), 8192, (a));

CREATE TABLE smt_table
(
    `date` Date,
    `name` String,
    `a` UInt16,
    `b` UInt16
)
ENGINE = SummingMergeTree(date, (date, name), 8192, a)

Ok.

### --- 插入数据：

~~~     # 加载数据
hadoop01 :)insert into smt_table (date, name, a, b) values ('2021-11-01', 'a', 1, 2);
hadoop01 :)insert into smt_table (date, name, a, b) values ('2021-11-01', 'b', 2, 1);
hadoop01 :)insert into smt_table (date, name, a, b) values ('2021-11-02', 'b', 3, 8);
hadoop01 :)insert into smt_table (date, name, a, b) values ('2021-11-02', 'b', 3, 8);
hadoop01 :)insert into smt_table (date, name, a, b) values ('2021-11-02', 'a', 3, 1);
hadoop01 :)insert into smt_table (date, name, a, b) values ('2021-11-03', 'c', 1, 3);

### --- 等待一段时间或optimize table smt_table手动触发merge，后查询
~~~     发现2021-11-02，b的a列合并相加了，b列取了8(因为b列为8的数据最先插入)。

hadoop01 :) select * from smt_table;

SELECT *
FROM smt_table

┌───────date─┬─name─┬─a─┬─b─┐
│ 2021-11-01 │ a    │ 1 │ 2 │
│ 2021-11-01 │ b    │ 2 │ 1 │
│ 2021-11-02 │ a    │ 3 │ 1 │
│ 2021-11-02 │ b    │ 6 │ 8 │
└────────────┴──────┴───┴───┘
┌───────date─┬─name─┬─a─┬─b─┐
│ 2021-11-03 │ c    │ 1 │ 3 │
└────────────┴──────┴───┴───┘

三、SummingMergeTree案例二

### --- 创建表

~~~     # 创建表
hadoop01 :) create table summing_table( id String, city String, v1 UInt32, v2 Float64, create_time DateTime )ENGINE=SummingMergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (id,city) PRIMARY KEY id;

CREATE TABLE summing_table
(
    `id` String,
    `city` String,
    `v1` UInt32,
    `v2` Float64,
    `create_time` DateTime
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
PRIMARY KEY id
ORDER BY (id, city)

Ok.

### --- 加载数据

~~~     # 加载数据
hadoop01 :) insert into table summing_table values('A000','beijing',10,20,'2021-11-01 08:00:00');
hadoop01 :) insert into table summing_table values('A000','beijing',20,30,'2021-11-01 08:00:00');
hadoop01 :) insert into table summing_table values('A000','shanghai',10,20,'2021-12-01 08:00:00');
hadoop01 :) insert into table summing_table values('A000','beijing',10,20,'2021-10-01 08:00:00');
hadoop01 :) insert into table summing_table values('A001','beijing',50,60,'2021-10-01 08:00:00');

### --- 查看表

~~~     # 重新触发数据
hadoop01 :) OPTIMIZE TABLE summing_table;

~~~     # 查看表

hadoop01 :) select * from summing_table;

┌─id───┬─city────┬─v1─┬─v2─┬─────────create_time─┐
│ A000 │ beijing │ 10 │ 20 │ 2021-10-01 08:00:00 │
│ A001 │ beijing │ 50 │ 60 │ 2021-10-01 08:00:00 │
└──────┴─────────┴────┴────┴─────────────────────┘
┌─id───┬─city────┬─v1─┬─v2─┬─────────create_time─┐
│ A000 │ beijing │ 10 │ 20 │ 2021-11-01 08:00:00 │
└──────┴─────────┴────┴────┴─────────────────────┘
┌─id───┬─city────┬─v1─┬─v2─┬─────────create_time─┐
│ A000 │ beijing │ 20 │ 30 │ 2021-11-01 08:00:00 │
└──────┴─────────┴────┴────┴─────────────────────┘
┌─id───┬─city─────┬─v1─┬─v2─┬─────────create_time─┐
│ A000 │ shanghai │ 10 │ 20 │ 2021-12-01 08:00:00 │
└──────┴──────────┴────┴────┴─────────────────────┘

~~~     # 通过观察，根据ORDER BY排序键(id,city)作为聚合Key,因为没有在建表指定SummingMergeTree的时候没有指定Sum列，所以把所有非主键数值类型的列都进行了SUM逻辑

┌─id───┬─city────┬─v1─┬─v2─┬─────────create_time─┐
│ A000 │ beijing │ 10 │ 20 │ 2021-11-01 08:00:00 │
└──────┴─────────┴────┴────┴─────────────────────┘
┌─id───┬─city────┬─v1─┬─v2─┬─────────create_time─┐
│ A000 │ beijing │ 20 │ 30 │ 2021-11-01 08:00:00 │

### --- 这两条数据v1和v2分别相加SUM后结果为30,50

┌─id───┬─city────┬─v1─┬─v2─┬─────────create_time─┐
│ A000 │ beijing │ 10 │ 20 │ 2021-10-01 08:00:00 │
│ A001 │ beijing │ 50 │ 60 │ 2021-10-01 08:00:00 │
└──────┴─────────┴────┴────┴─────────────────────┘

四、SummingMergeTree案例三

### --- 创建表

~~~     # 创建表：SummingMergeTree支持嵌套类型的字段，但列字段名称必须以Map后缀结束。
hadoop01 :) create table summing_table_nested( id String, nestMap Nested( id UInt32, key UInt32, val UInt64 ), create_time DateTime )ENGINE=SummingMergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY id;

CREATE TABLE summing_table_nested
(
    `id` String,
    `nestMap` Nested(    id UInt32,     key UInt32,     val UInt64),
    `create_time` DateTime
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY id

Ok.

### --- 查看表结构

~~~     # 查看表结构
hadoop01 :) desc summing_table_nested;

┌─name────────┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id          │ String        │              │                    │         │                  │                │
│ nestMap.id  │ Array(UInt32) │              │                    │         │                  │                │
│ nestMap.key │ Array(UInt32) │              │                    │         │                  │                │
│ nestMap.val │ Array(UInt64) │              │                    │         │                  │                │
│ create_time │ DateTime      │              │                    │         │                  │                │
└─────────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘