前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >大数据ClickHouse（十）：MergeTree系列表引擎之SummingMergeTree

大数据ClickHouse（十）：MergeTree系列表引擎之SummingMergeTree

原创
作者头像
Lansonli
发布于 2022-08-26 05:33:02
5660
发布于 2022-08-26 05:33:02
举报
文章被收录于专栏：Lansonli技术博客Lansonli技术博客

​MergeTree系列表引擎之SummingMergeTree

一、SummingMergeTree基本讲解

该引擎继承了MergeTree引擎，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值，即如果存在重复的数据，会对对这些重复的数据进行合并成一条数据，类似于group by的效果，可以显著减少存储空间并加快数据查询速度。

如果用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的，即GROUP BY的分组字段是确定的，可以使用该表引擎。

  • SummingMergeTree建表语句：
代码语言：javascript
复制
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数据不会被合并

代码语言：javascript
复制
#创建表 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 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

注意：不同分区内相同的排序key的数据不能被合并

2、测试指定一个聚合字段

代码语言：javascript
复制
#删除表 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、测试指定多个聚合字段

代码语言：javascript
复制
#删除表 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 │
└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘

注意：我们可以看到当指定多个聚合字段时，有相同排序字段行进行聚合时，会按照指定的多个数值字段进行合并，其他的保留最开始一条数据的信息。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

大数据处理套件 TBDS
大数据

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

大数据处理套件 TBDS
大数据
#大数据
#ClickHouse
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • ​MergeTree系列表引擎之SummingMergeTree
    • 一、SummingMergeTree基本讲解
      • 二、使用SummingMergeTree注意以下几点
        • 三、测试实例
          • 1、测试不指定聚合字段同时测试不同分区内，相同排序key数据不会被合并
          • 2、测试指定一个聚合字段
          • 3、测试指定多个聚合字段
      相关产品与服务
      大数据处理套件 TBDS
      腾讯大数据处理套件（Tencent Big Data Suite，TBDS）依托腾讯多年海量数据处理经验，基于云原生技术和泛 Hadoop 生态开源技术对外提供的可靠、安全、易用的大数据处理平台。 TBDS可在公有云、私有云、非云化环境，根据不同数据处理需求组合合适的存算分析组件，包括 Hive、Spark、HBase、Flink、presto、Iceberg、Alluxio 等，以快速构建企业级数据湖、数据仓库。
      产品介绍产品文档
      精选特惠 用云无忧
      领券

      腾讯云开发者

      扫码关注腾讯云开发者

      扫码关注腾讯云开发者

      领取腾讯云代金券

      热门产品

      热门推荐

      更多推荐

      Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

      深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

      腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

      Copyright © 2013 - 2024 Tencent Cloud.

      All Rights Reserved. 腾讯云 版权所有

      登录 后参与评论