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

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中存放原子数据，物化视图中存入聚合结果数据，可以提升数据查询效率。

• 📢博客主页：https://lansonli.blog.csdn.net
• 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正！
• 📢本文由 Lansonli 原创，首发于 CSDN博客🙉
• 📢停下休息的时候不要忘了别人还在奔跑，希望大家抓紧时间学习，全力奔赴更美好的生活✨