大数据ClickHouse(十一):MergeTree系列表引擎之AggregatingMergeTree
2023-06-13 09:11:31 时间
MergeTree系列表引擎之AggregatingMergeTree
一、AggregatingMergeTree基本讲解
该表引擎继承自MergeTree,可以使用 AggregatingMergeTree 表来做增量数据统计聚合。如果要按一组规则来合并减少行数,则使用 AggregatingMergeTree 是合适的。AggregatingMergeTree是通过预先定义的聚合函数计算数据并通过二进制的格式存入表内。
与SummingMergeTree的区别在于:SummingMergeTree对非主键列进行sum聚合,而AggregatingMergeTree则可以指定各种聚合函数。对某些字段需要进行聚合时,需要在创建表字段时指定成AggregateFunction类型。
- AggregatingMergeTree建表语句如下:
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]
[TTL expr]
[SETTINGS name=value, ...]
二、测试实例
#创建表 t_aggregating_mt ,使用AggregatingMergeTree引擎,指定salary字段是聚合字段
node1 :) create table t_aggregating_mt(
:-] id UInt8,
:-] name String,
:-] age UInt8,
:-] loc String,
:-] dept String,
:-] workdays UInt8,
:-] salary AggregateFunction(sum,Decimal32(2))
:-] ) engine = AggregatingMergeTree()
:-] order by (id,age)
:-] primary key id
:-] partition by loc;
- 注意:
对于AggregateFunction类型的列字段,在进行数据的写入和查询时与其他的表引擎有很大区别,在写入数据时,需要调用 *-State 函数;而在查询数据时,则需要调用相应的 *-Merge 函数。
对于上面的建表语句而言,需要使用sumState函数进行数据插入。
#向表 t_aggregating_mt 中插入数据,插入方式与之前方式不同
node1 :) insert into t_aggregating_mt select 1,'张三',18,'北京','java',18,sumState(toDecimal32(10000,2));
node1 :) insert into t_aggregating_mt select 2,'李四',19,'上海','java',22,sumState(toDecimal32(8000,2));
node1 :) insert into t_aggregating_mt select 3,'王五',20,'北京','java',26,sumState(toDecimal32(12000,2));
查询数据时,如果正常语句查询,aggregateFunction类型的列不会正常显示数据,针对以上的数据需要使用sumMerge来展示数据。
#错误方式查询表 t_aggregating_mt 中的数据
node1 :) select * from t_aggregating_mt;
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┐
│ 1 │ 张三 │ 18 │ 北京 │ java │ 18 │ @B │
└────┴──────┴─────┴──────┴──────┴──────────┴────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┐
│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 5 │
└────┴──────┴─────┴──────┴──────┴──────────┴────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┐
│ 3 │ 王五 │ 20 │ 北京 │ java│ 26 │ O │
└────┴──────┴─────┴──────┴──────┴──────────┴────────┘
#正确方式查询表 t_aggregating_mt中的数据,注意需要跟上groupBy
node1 :) select * ,sumMerge(salary) from t_aggregating_mt group by id,name ,age, loc,dept,workdays,salary ;
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┬─sumMerge(salary)─┐
│ 1 │ 张三 │ 18 │ 北京 │ java │ 18 │ @B │ 10000.00 │
│ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ O │ 12000.00 │
│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 5 │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴────────┴──────────────────┘
向表中插入排序字段相同的数据进行分区聚合时,数据按照建表指定的聚合字段进行合并,其他的非聚合字段会保留最初的那条数据,新插入的数据对应的字段值会被舍弃。
# 向表中插入新的一条数据
node1 :) insert into t_aggregating_mt select 1,'张三三',18,'北京','前端',22,sumState(toDecimal32(5000,2));
#查询表中的数据,这里为了方便看到分区不合并,直接查询
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┐
│ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ O │
└────┴──────┴─────┴──────┴──────┴──────────┴────────┘
┌─id─┬─name───┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┐
│ 1 │ 张三三 │ 18 │ 北京 │ 前端 │ 22 │ ¡ │
└────┴────────┴─────┴──────┴──────┴──────────┴────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┐
│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 5 │
└────┴──────┴─────┴──────┴──────┴──────────┴────────┘
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┐
│ 1 │ 张三 │ 18 │ 北京 │ java │ 18 │ @B │
└────┴──────┴─────┴──────┴──────┴──────────┴────────┘
#使用optimize 命令合并相同分区数据
node1 :) optimize table t_aggregating_mt;
#再次查询表 t_aggregating_mt 表数据,salary 字段已经按照相同分区数据聚合
node1 :) select *,sumMerge(salary) from t_aggregating_mt group by id,name ,age,loc,dept,workdays,salary;
┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬─salary─┬─sumMerge(salary)─┐
│ 1 │ 张三 │ 18 │ 北京 │ java │ 18 │ `ᔠ │ 15000.00 │
│ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ O │ 12000.00 │
│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 5 │ 8000.00 │
└────┴──────┴─────┴──────┴──────┴──────────┴────────┴──────────────────┘
以上方式使用AggregatingMergeTree表引擎比较不方便,更多情况下,我们将AggregatingMergeTree作为物化视图的表引擎与MergeeTree搭配使用。
- 示例:
#创建表 t_merge_base 表,使用MergeTree引擎
node1 :) create table t_merge_base(
:-] id UInt8,
:-] name String,
:-] age UInt8,
:-] loc String,
:-] dept String,
:-] workdays UInt8,
:-] salary Decimal32(2)
:-] )engine = MergeTree()
:-] order by (id,age)
:-] primary key id
:-] partition by loc;
#创建物化视图 view_aggregating_mt ,使用AggregatingMergeTree引擎
node1 :) create materialized view view_aggregating_mt
:-] engine = AggregatingMergeTree()
:-] order by id
:-] as select
:-] id,
:-] name,
:-] sumState(salary) as ss
:-] from t_merge_base
:-] group by id ,name ;
#向表 t_merge_base 中插入数据
node1 :) insert into t_merge_base values (1,'张三',18,'北京','大数据',24,10000),
:-] (2,'李四',19,'上海','java',22,8000),
:-] (3,'王五',20,'北京','java',26,12000);
#查看 view_aggregating_mt视图数据
node1 :) select *,sumMerge(ss) from view_aggregating_mt group by id,name,ss;
┌─id─┬─name─┬─ss─┬─sumMerge(ss)─┐
│ 2 │ 李四 │ 5 │ 8000.00 │
│ 3 │ 王五 │ O │ 12000.00 │
│ 1 │ 张三 │ @B │ 10000.00 │
└────┴──────┴────┴──────────────┘
#继续向表 t_merge_base中插入排序键相同的数据
node1 :) insert into t_merge_base values (1,'张三三',18,'北京','前端',22,5000);
#手动执行optimize 命令,合并物化视图 view_aggregating_mt 相同分区数据
node1 :) optimize table view_aggregating_mt;
#查询视图 view_aggregating_mt数据
node1 :) select *,sumMerge(ss) from view_aggregating_mt group by id,name,ss;
┌─id─┬─name─┬─ss─┬─sumMerge(ss)─┐
│ 2 │ 李四 │ 5 │ 8000.00 │
│ 1 │ 张三 │ `ᔠ│ 15000.00 │
│ 3 │ 王五 │ O │ 12000.00 │
└────┴──────┴────┴──────────────┘
注意:通过普通MergeTree表与AggregatingMergeTree物化视图结合使用,MergeTree中存放原子数据,物化视图中存入聚合结果数据,可以提升数据查询效率。
相关文章
- 大数据生态圈常用组件(一):数据库、查询引擎、ETL工具、调度工具等
- 大数据ClickHouse(十二):MergeTree系列表引擎之CollapsingMergeTree
- # MySQL server 层和存储引擎层是怎么交互数据的?
- 大数据ClickHouse进阶(二):MergeTree表引擎
- 爱奇艺开源的高性能网络安全监控引擎
- 【MySQL】存储引擎
- 探索Wiredtiger引擎基于B-Tree数据写入分析
- Web 3D 圈摸爬滚打十多年的老兵热血自述:立志做中国跨时代 Web 渲染引擎
- NeuroDB图数据库引擎 发布,一款全自主研发的国产图数据库引擎
- 火山引擎DataLeap:3个关键步骤,复制字节跳动一站式数据治理经验
- 火山引擎数智平台VeDI发布《数据智能知识图谱》
- MySQL的InnoDB存储引擎的数据页结构详解
- 基于Kafka的实时计算引擎如何选择Flink or Spark详解大数据
- Kafka – SQL 引擎分享详解大数据
- Kafka分布式查询引擎详解大数据
- 《Drools7.0.0.Final规则引擎教程》Drools简介详解编程语言
- 探索MySQL引擎:查看的方法(如何查看mysql的引擎)
- MySQL引擎:充分理解有何不同(mysql的引擎有哪些)
- Redis 集群:极速裂变的大数据引擎(redis 集群)
- 取MySQL存储引擎C语言编程实现数据读取(c mysql读)
- 撮合引擎Redis助力交易性能提升(撮合引擎redis 性能)
- 深入了解Oracle中的数据引擎(oracle中的数据引擎)
- 千万级大数据同步之Redis智能引擎(千万数据redis同步)
- MySql数据引擎简介与选择方法
- MyISAM和InnoDB引擎优化分析
- ThinkPHP使用smarty模板引擎的方法