什么是hive的高级分组聚合，它的用法和注意事项以及性能分析-6ren

什么是hive的高级分组聚合，它的用法和注意事项以及性能分析

转载作者：我是一只小鸟更新时间：2023-06-29 22:31:26

25

4

目录。

1.Grouping sets 的使用
2.cube 和rollup的使用
3.使用高级分组聚合函数的性能分析

。

hive的高级分组聚合是指在聚合时使用GROUPING SETS、CUBE和ROLLUP的分组聚合.

高级分组聚合在很多数据库类SQL中都有出现，并非hive独有，这里只说明hive中的情况.

使用高级分组聚合不仅可以简化SQL语句，而且通常情况下会提升SQL语句的性能.

反回顶部

1.Grouping sets 的使用

示例:

                        
                          -- 使用方式
select a,b,sum(c) from tbl group by a,b grouping sets(a,b)

Grouping sets的子句允许在一个group by 语句中，指定多个分组聚合列。所有含有Grouping sets 的子句都可以用union连接的多个group by 查询逻辑来表示.

如下一些常见的等价替换示例:

                        
                          -- 语句1
select a, b sum(c) from tbl group by a,b grouping sets((a,b))
-- 相当于 
select a,b,sum(c) from tbl group by a,b

-- 语句2
select a,b,sum(c) from tbl group by a,b grouping sets((a,b),a)
-- 相当于
select a,b,sum(c) from tbl group by a,b
union
select a,null ,sum(c) from tbl group by a

-- 语句3
select a,b,sum(c) from tbl group by a,b grouping sets(a,b)
-- 相当于
select a,null,sum(c) from tbl group by a
union
select null ,b,sum(c) from tbl group by b

-- 语句4
select a,b,sum(c) from tbl group by a,b grouping sets((a,b),a,b,())
-- 相当于
select a,b,sum(c) from tbl group by a,b
union
select a,null,sum(c) from tbl group by a
union
select null,b,sum(c) from tbl group by b
union
select null,null,sum(c) from tbl

可以看到通过等价替换的改写之后，语句会变得简洁，性能我们之后分析.

反回顶部

2.cube 和rollup的使用

示例:

                        
                          -- cube使用示例
select a,b,c,count(1) from tbl group by a,b,c with cube
-- rollup使用示例
select a,b,c,count(1) from tbl group by a,b,c with rollup

用法说明:

以上两个高级分组函数都可以在一个group by 语句中完成多个分组聚合，它们都可以用grouping sets来等价替换.

cube 会计算所有group by 列的所有组合

                        
                          -- cube语句
select a,b,c,count(1) from tbl group by a,b,c with cube
-- 相当于
select a,b,c count(1) from tbl group by a,b,c
grouping sets((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())

rollup 会按照group by 指定的列从左到右进行分组聚合

                        
                          -- rollup语句 滚动式聚合
select a,b,c,count(1) from tbl group by a,b,c with rollup
-- 相当于
select a,b,c,count(1) from tbl group by a,b,c s
grouping sets((a,b,c),(a,b),(a),())

反回顶部

3.使用高级分组聚合函数的性能分析

我们可以通过执行计划的执行来分析高级分组聚合SQL语句的执行过程，比对其优化的节点.

例1 含grouping sets关键词的SQL执行案例.

                        
                          set hive.map.aggr=true;
explain
-- 小于30岁人群的不同性别平均年龄
select gender,avg(age) as avg_age from temp.user_info_all where ymd = '20230505'
and age < 30 
group by gender;

-- 将以上语句改为grouping sets关键词执行语句
set hive.map.aggr=true;
explain
select gender,avg(age) as num from temp.user_info_all 
where ymd = '20230505'
and age < 30 
group by gender grouping sets((gender));

查看其执行计划:

                        
                          STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: user_info_all
            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (age < 30) (type: boolean)
              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: avg(age)
                keys: gender (type: int), 0 (type: int)
                mode: hash
                outputColumnNames: _col0, _col1, _col2
                Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: int), _col1 (type: int)
                  sort order: ++
                  Map-reduce partition columns: _col0 (type: int), _col1 (type: int)
                  Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col2 (type: struct<count:bigint,sum:double,input:bigint>)
      Reduce Operator Tree:
        Group By Operator
          aggregations: avg(VALUE._col0)
          keys: KEY._col0 (type: int), KEY._col1 (type: int)
          mode: mergepartial
          outputColumnNames: _col0, _col2
          Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: int), _col2 (type: double)
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: true
              Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

对以上内容进行关键字解读:

map阶段:

Group By Operator ：Map端开启聚合操作
aggregations：分组聚合的算法，该案例采取avg(age)
keys: 这里是分组列+ 一个固定列 0
mode:Hash
outputColumnNames:最终输出三列。_col0, _col1, _col2
Reduce Output Operator：该阶段为map阶段聚合后的操作
key expressions：map端最终输出的key，该例为gender和0两列。
sort order：输出两列都正序排序
Map-reduce partition columns：表示Map阶段数据输出的分区列，该案例为gender和0两列进行分区。
value expressions:map端最终输出value，为一个结构体。

Reduce阶段:

Group By Operator：reduce阶段的分组聚合操作。
aggregations: 分组聚合算法，avg(VALUE._col0)表示对map阶段输出的 value expressions的 _col0取平均值。
keys:指定分组聚合的key，有两列。为map阶段输出的key。
mode: mergepartial
outputColumnNames: 表示最终输出的列，该例为gender和num。
pruneGroupingSetId: 表示是否对最终输出的grouping id进行修剪，如果为true，则表示将keys最后一列抛弃。案例中为0列。
Select Operator：进行列投影操作。
expressions:输出的列。gender和num。

通过查看以上的执行计划，可以看出在使用含有grouping sets语句的SQL中，hive执行计划并没有给出具体的实现细节.

再执行具有多个聚合列的实例来看看:

例2 聚合年龄和聚合性别多列合并测试.

                        
                          set hive.map.aggr=true;
explain
select gender,age,count(0) as num from temp.user_info_all 
where ymd = '20230505'
and age < 30 
group by gender,age grouping sets(gender,age);

注：grouping sets后进行分组的列一定要在之前的group by中进行申明.

                        
                          STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: user_info_all
            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (age < 30) (type: boolean)
              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: count(0)
                keys: gender (type: int), age (type: bigint), 0 (type: int)
                mode: hash
                outputColumnNames: _col0, _col1, _col2, _col3
                Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
                  sort order: +++
                  Map-reduce partition columns: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
                  Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col3 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: int), KEY._col1 (type: bigint), KEY._col2 (type: int)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col3
          Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: int), _col1 (type: bigint), _col3 (type: bigint)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: true
              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

通过以上两个例子可以看出hive执行计划中没有具体的高级分组聚合如何实现分组方案。两者执行方式基本上差不多.

在数据扫描和查询上的确减少了多次数据扫描和数据io操作。在一定程度上节省了计算资源.

例3 使用cube替代grouping sets .

                        
                          set hive.map.aggr=true;
explain
select gender,age,count(0) as num from temp.user_info_all 
where ymd = '20230505'
and age < 30 
group by gender,age with cube;

-- 等价语句
select gender,age,count(0) as num from temp.user_info_all 
where ymd = '20230505'
and age < 30 
group by gender,age grouping sets((gender,age),(gender),(age),());

                        
                          STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: user_info_all
            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (age < 30) (type: boolean)
              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: count(0)
                keys: gender (type: int), age (type: bigint), 0 (type: int)
                mode: hash
                outputColumnNames: _col0, _col1, _col2, _col3
                Statistics: Num rows: 43512392 Data size: 1044297408 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
                  sort order: +++
                  Map-reduce partition columns: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
                  Statistics: Num rows: 43512392 Data size: 1044297408 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col3 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: int), KEY._col1 (type: bigint), KEY._col2 (type: int)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col3
          Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: int), _col1 (type: bigint), _col3 (type: bigint)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: true
              Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

以上例3 cube语句和例2语句输出数据完全是不一样的。但其输出执行计划内容基本和例2一致。可以看出hive的执行计划对高级分组聚合拆分执行计划的支持还不是很好.

使用高级分组聚合，要注意开启map端聚合模式.

使用高级分组聚合，如上案例，仅使用一个作业就能够实现union写法需要多个作业才能实现的逻辑.

从这点上来看能够减少多个作业在磁盘和网络I/O时的负担，是一种优化.

但是同时也要注意因过度使用高级分组聚合语句而导致的数据急速膨胀问题.

通常使用简单的group by 语句，一份数据只有一种聚合结果，一个分组聚合通常只有一个记录；。
使用高级分组聚合，例如cube，在一个作业中一份数据会存在多种聚合情况，最终输出是，每种聚合情况各自对应一条数据.

注意事项:

如果使用高级分组聚合的语句处理的底表，在数据量很大的情况下容易导致Map或者Reduce任务因硬件资源不足而崩溃.

hive中使用 hive.new.job.grouping.set.cardinality 配置项来应对以上情况.

如果SQL语句中处理分组聚合情况超过该配置项指定的值，默认值为（30），则会创建一个新的作业.

下一期：hive窗口分析函数解读以及带窗口分析函数的SQL性能分析。

按例，欢迎点击此处关注我的个人公众号，交流更多知识.

后台回复关键字 hive，随机赠送一本鲁边备注版珍藏大数据书籍.

最后此篇关于什么是hive的高级分组聚合，它的用法和注意事项以及性能分析的文章就讲到这里了,如果你想了解更多关于什么是hive的高级分组聚合，它的用法和注意事项以及性能分析的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

25

4

0

文章推荐：【.NET源码解读】深入剖析中间件的设计与实现

文章推荐： ABP-本地事件总线

文章推荐：无缝数据转换！使用C++实现Excel文件与CSV之间的相互转换

文章推荐：【C#/.NET】使用Automapper映射record类型

sql - 分析/分析 PostgreSQL 上的查询
我刚刚继承了一个旧的 PostgreSQL 安装，需要进行一些诊断以找出该数据库运行缓慢的原因。在 MS SQL 上，您可以使用 Profiler 等工具来查看正在运行的查询，然后查看它们的执行计划。
google-analytics - 将目标从Google Analytics(分析)导入AdWords并更改Google Analytics(分析)中的目标条件
将目标从Analytics（分析）导入到AdWords中，然后在Analytics（分析）中更改目标条件时，是否可以通过更改将目标“重新导入”到AdWords，还是可以自动选择？最佳答案更改目标值
google-analytics - 如何从Google Analytics(分析)界面检查Google Analytics(分析)API参数
我正在使用google analytics api来获取数据。我正在获取数据，但我想验证两个参数，它们在特定日期范围内始终为0。我正在获取['ga:transactions']和['ga:goalCo
google-analytics - 为什么我从Google Analytics(分析)API获取的数据与Google Analytics(分析)网络界面不匹配？
我使用Google API从Google Analytics（分析）获取数据，但指标与Google Analytics（分析）的网络界面不同。即：我在2015年3月1日获得数据-它返回综合浏览量79
knockout.js - Sammy.js与Google Analytics(分析)应用程式中的Google Analytics(分析)
我在我的Web应用程序中使用sammy.js进行剔除。我正在尝试向其中添加Google Analytics（分析）。我很快找到了following plugin来实现页面跟踪。我按照步骤操作，页面如
objective-c - 使用 Xcode 分析(产品>分析)时，有没有办法忽略给定文件中的任何错误？
当使用 Xcode 分析 (product>analyze) 时，有没有办法忽略给定文件中的任何错误？例如编译指示之类的？我们只想忽略第三方代码的任何警告，这样当我们的代码出现问题时，它对我们
日志采集/分析
目录 EFK 1. 日志系统 2. 部署ElasticSearch 2.1 创建handless服务 2.2 创建s
分析 - 跟踪每个用户的操作
关闭。这个问题不满足Stack Overflow guidelines .它目前不接受答案。想改善这个问题吗？更新问题，使其成为 on-topic对于堆栈溢出。 7年前关闭。 Improve thi
GCC代码统计/分析
GCC/G++ 是否有可用于输出分析的选项？能够比较以前的代码与新代码之间的差异(大小、类/结构的大小)将很有用。然后可以将它们与之前的输出进行比较以进行比较，这对于许多目的都是有用的。如果没有此
Haskell 分析
我正在浏览 LYAH，并一直在研究处理列表时列表理解与映射/过滤器的使用。我已经分析了以下两个函数，并包含了教授的输出。如果我正确地阅读了教授的内容，我会说 FiltB 的运行速度比 FiltA 慢很
MySQL 分析
在 MySQL 中可以使用 SET profiling = 1; 设置分析查询 SHOW PROFILES; 显示每个查询所用的时间。我想知道这个时间是只包括服务器的执行时间还是还包括将结果发送到前
Python 分析
我用 Python 编写了几个用于生成阶乘的模块，我想测试运行时间。我找到了一个分析示例 here我使用该模板来分析我的模块: import profile #fact def main():
mysqld_safe启动脚本源码阅读、分析
前几天读了下mysqld_safe脚本，个人感觉还是收获蛮大的，其中细致的交代了MySQL数据库的启动流程，包括查找MySQL相关目录，解析配置文件以及最后如何调用mysqld程序来启动实例等，有着
人工智能规模法则的起源、分析、内幕以及对齐方法
上一篇：《人工智能大语言模型起源篇，低秩微调（LoRA）》（14）Rae 和同事（包括78位合著者！）于2022年发表的《Scaling Language Models: Methods, A
内网之工作组、域分析
1 内网基础内网/局域网（Local Area Network，LAN），是指在某一区域内有多台计算机互联而成的计算机组，组网范围通常在数千米以内。在局域网中，可以实现文件管理、应用软件共享、打印机
内网之工作组、域分析
1 内网基础内网/局域网（Local Area Network，LAN），是指在某一区域内有多台计算机互联而成的计算机组，组网范围通常在数千米以内。在局域网中，可以实现文件管理、应用软件共享、打印机
vba - Excel中的自动数据排序/分析
我有四列形式的数据。前三列代表时间，value1，value 2。第四列是二进制，全为 0 或 1。当第四列中对应的二进制值为0时，有没有办法告诉excel删除时间、值1和值2？我知道这在 C++ 或
Haskell 分析 - clock_gettime
我正在运行一个进行长时间计算的 Haskell 程序。经过一些分析和跟踪后，我注意到以下内容: $ /usr/bin/time -v ./hl test.hl 9000045000050000 Com
ASP.NET 分析
我有一个缓慢的 asp.net 程序正在运行。我想分析生产服务器以查看发生了什么，但我不想显着降低生产服务器的速度。一般而言，配置生产盒或仅本地开发盒是标准做法吗？另外，您建议使用哪些程序来实现这一
固定时间的 Haskell 分析
我目前正在尝试分析 Haskell 服务器。服务器永远运行，所以我只想要一个固定时间的分析报告。我尝试只运行该程序 3 分钟，然后礼貌地要求它终止，但不知何故，haskell 分析器不遵守术语信号，并

首页

博学

6Ren·AI

商城