【hive】hive grouping sets和GROUPING__ID的用法

已于 2023-05-15 15:58:27 修改
阅读量599 收藏
点赞数
分类专栏： hive 文章标签： hive hadoop 数据仓库
于 2023-05-15 14:17:43 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mrerlou/article/details/130683344
版权

前言​

GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

grouping sets根据不同的维度组合进行聚合，等价于将不同维度的group by的结果进行 union all，简单来说就是将多个不同维度的group by逻辑写在了 一个sql中。

数据准备：

vim /root/test.txt

2015-03,2015-03-10,cookie1
2015-03,2015-03-10,cookie5
2015-03,2015-03-12,cookie7
2015-04,2015-04-12,cookie3
2015-04,2015-04-13,cookie2
2015-04,2015-04-13,cookie4
2015-04,2015-04-16,cookie4
2015-03,2015-03-10,cookie2
2015-03,2015-03-10,cookie3
2015-04,2015-04-12,cookie5
2015-04,2015-04-13,cookie6
2015-04,2015-04-15,cookie3
2015-04,2015-04-15,cookie2
2015-04,2015-04-16,cookie1

将数据导入到hdfs目录上：

hdfs dfs -put /root/test.txt /tmp

创建表

use test;

create table cookie5(month string, day string, cookieid string) 
row format delimited fields terminated by ',';

load data inpath "/tmp/test.txt" into table cookie5;

select * from cookie5;

在这里插入图片描述

开始使用grouping sets

来条sql语句：

select 
    month,day,count(cookieid) 
from cookie5 
    group by month,day 
grouping sets (month,day);

查询结果如下：
在这里插入图片描述

上面这个sql等同于多个group by ＋ union all

select month,NULL as day,count(cookieid) as nums 
from cookie5 
group by month 
union all select NULL as month,day,count(cookieid) as nums 
from cookie5 
group by day;

注意点：使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的。union all的表字段必须匹配，也就是上文的month 需要用 NULL as month 来进行填充。

在这里插入图片描述

结果一致！但是grouping sets速度要比group by + union 快！！！！！

但是有引出一个问题？为什么grouping sets 要比 group by + union 速度要快？

首先：用explain来解释下hive执行计划：

explain select 
    month,day,count(cookieid) 
from cookie5 
    group by month,day 
grouping sets (month,day);

执行计划如下：

+----------------------------------------------------+--+
|                      Explain                       |
+----------------------------------------------------+--+
| 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: cookie5                         |
|             Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: month (type: string), day (type: string), cookieid (type: string) |
|               outputColumnNames: month, day, cookieid |
|               Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|               Group By Operator                    |
|                 aggregations: count(cookieid)      |
|                 keys: month (type: string), day (type: string), '0' (type: string) |
|                 mode: hash                         |
|                 outputColumnNames: _col0, _col1, _col2, _col3 |
|                 Statistics: Num rows: 2 Data size: 756 Basic stats: COMPLETE Column stats: NONE |
|                 Reduce Output Operator             |
|                   key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string) |
|                   sort order: +++                  |
|                   Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string) |
|                   Statistics: Num rows: 2 Data size: 756 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: string), KEY._col1 (type: string), KEY._col2 (type: string) |
|           mode: mergepartial                       |
|           outputColumnNames: _col0, _col1, _col3   |
|           Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|           pruneGroupingSetId: true                 |
|           Select Operator                          |
|             expressions: _col0 (type: string), _col1 (type: string), _col3 (type: bigint) |
|             outputColumnNames: _col0, _col1, _col2 |
|             Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|             File Output Operator                   |
|               compressed: false                    |
|               Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|               table:                               |
|                   input format: org.apache.hadoop.mapred.TextInputFormat |
|                   output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
|                   serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|                                                    |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         ListSink

再看另外一个：

explain select month,NULL as day,count(cookieid) as nums from cookie5 group by month 
union all 
select NULL as month,day,count(cookieid) as nums from cookie5 group by day;

+----------------------------------------------------+--+
|                      Explain                       |
+----------------------------------------------------+--+
| STAGE DEPENDENCIES:                                |
|   Stage-1 is a root stage                          |
|   Stage-2 depends on stages: Stage-1, Stage-3      |
|   Stage-3 is a root stage                          |
|   Stage-0 depends on stages: Stage-2               |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-1                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             alias: cookie5                         |
|             Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: month (type: string), cookieid (type: string) |
|               outputColumnNames: month, cookieid   |
|               Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|               Group By Operator                    |
|                 aggregations: count(cookieid)      |
|                 keys: month (type: string)         |
|                 mode: hash                         |
|                 outputColumnNames: _col0, _col1    |
|                 Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|                 Reduce Output Operator             |
|                   key expressions: _col0 (type: string) |
|                   sort order: +                    |
|                   Map-reduce partition columns: _col0 (type: string) |
|                   Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|                   value expressions: _col1 (type: bigint) |
|       Reduce Operator Tree:                        |
|         Group By Operator                          |
|           aggregations: count(VALUE._col0)         |
|           keys: KEY._col0 (type: string)           |
|           mode: mergepartial                       |
|           outputColumnNames: _col0, _col1          |
|           Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|           Select Operator                          |
|             expressions: _col0 (type: string), UDFToString(null) (type: string), _col1 (type: bigint) |
|             outputColumnNames: _col0, _col1, _col2 |
|             Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|             File Output Operator                   |
|               compressed: false                    |
|               table:                               |
|                   input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                   output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                   serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe |
|                                                    |
|   Stage: Stage-2                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             Union                                  |
|               Statistics: Num rows: 2 Data size: 756 Basic stats: COMPLETE Column stats: NONE |
|               File Output Operator                 |
|                 compressed: false                  |
|                 Statistics: Num rows: 2 Data size: 756 Basic stats: COMPLETE Column stats: NONE |
|                 table:                             |
|                     input format: org.apache.hadoop.mapred.TextInputFormat |
|                     output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
|                     serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|           TableScan                                |
|             Union                                  |
|               Statistics: Num rows: 2 Data size: 756 Basic stats: COMPLETE Column stats: NONE |
|               File Output Operator                 |
|                 compressed: false                  |
|                 Statistics: Num rows: 2 Data size: 756 Basic stats: COMPLETE Column stats: NONE |
|                 table:                             |
|                     input format: org.apache.hadoop.mapred.TextInputFormat |
|                     output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
|                     serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|                                                    |
|   Stage: Stage-3                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             alias: cookie5                         |
|             Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: day (type: string), cookieid (type: string) |
|               outputColumnNames: day, cookieid     |
|               Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|               Group By Operator                    |
|                 aggregations: count(cookieid)      |
|                 keys: day (type: string)           |
|                 mode: hash                         |
|                 outputColumnNames: _col0, _col1    |
|                 Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|                 Reduce Output Operator             |
|                   key expressions: _col0 (type: string) |
|                   sort order: +                    |
|                   Map-reduce partition columns: _col0 (type: string) |
|                   Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|                   value expressions: _col1 (type: bigint) |
|       Reduce Operator Tree:                        |
|         Group By Operator                          |
|           aggregations: count(VALUE._col0)         |
|           keys: KEY._col0 (type: string)           |
|           mode: mergepartial                       |
|           outputColumnNames: _col0, _col1          |
|           Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
+----------------------------------------------------+--+
|                      Explain                       |
+----------------------------------------------------+--+
|           Select Operator                          |
|             expressions: UDFToString(null) (type: string), _col0 (type: string), _col1 (type: bigint) |
|             outputColumnNames: _col0, _col1, _col2 |
|             Statistics: Num rows: 1 Data size: 378 Basic stats: COMPLETE Column stats: NONE |
|             File Output Operator                   |
|               compressed: false                    |
|               table:                               |
|                   input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                   output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                   serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe |
|                                                    |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
+----------------------------------------------------+--+

比较：通过explain 可以发现

  • grouping sets时，有2个stage，一次reduce
  • group by + union时，有4个stage，两次reduce。

那么肯定用grouping sets时，速度会快。

GROUPING__ID的使用：

来条sql语句：

select 
  month,
  day,
  count(distinct cookieid) as uv,
  GROUPING__ID
from cookie5 
group by month,day 
grouping sets (month,day) 
order by GROUPING__ID;

在这里插入图片描述

效果等价于：

SELECT month,NULL as day,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month
UNION ALL 
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5 GROUP BY day;

结果说明:

  • 第一列是按照month进行分组

  • 第二列是按照day进行分组

  • 第三列是按照month或day分组是，统计这一组有几个不同的cookieid

  • 第四列grouping_id表示这一组结果属于哪个分组集合，根据grouping sets中的分组条件month，day

再来个例子：

SELECT  month, day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM cookie5 
GROUP BY month,day 
GROUPING SETS (month,day,(month,day)) 
ORDER BY GROUPING__ID;

等价于：

SELECT month,NULL as day,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month 
UNION ALL 
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM cookie5 GROUP BY month,day;

在这里插入图片描述

grouping_id计算方法

grouping sets 中的每一种粒度，都对应唯一的 grouping__id 值，其计算公式与 group by 的顺序、当前粒度的字段有关。
具体计算方法如下：

  1. 将每个字段都抽象成二进制，有2个字段就是2位，有5个字段就有5位二进制数。
  2. 靠近 group by 的字段是低位， 远离的则是高位。
  3. hive中参与了组合的字段是1，未参与是0。
  4. 这样就形成了一个二进制数，这个二进制数转为十进制，即为当前粒度对应的 grouping__id。

在这里插入图片描述

举例

以上述对 cookie5 的3种粒度的统计结果为例：

我们的上面的sql 为 group by month, day, 遵循第一条原则，month 在二进制中处于低位，day 处于高位。

序号grouping set二进制值对应的十进制（grouping__id 的值）
0（）000
1（month）011
2（day）102
3(month, day)113

这就是上面 grouping sets 的结果中 grouping__id 值的由来。

​

kiraraLou
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive安装讲义(linux_hive linux_mysql)
09-14
Hive安装讲义(linux_hive linux_mysql)
Hive安装讲义(linux_hive windows_mysql)
09-14
Hive安装讲义(linux_hive windows_mysql)
对于新旧hive版本中grouping_id计算方式不同造成的差异化解决方案
zwmonk的博客
10-10 629
工作中使用多维度分组时并不想写一大堆union all，为了便于代码简洁喜欢使用grouping_id这个函数，使用久了会发现hive_1.2.1版本以前的grouping_id计算方式和以后的计算方式会有所不同步。这样造成使用这个函数的时候如果存在历史数据使用时存在着矛盾，这时很多聪明人会想到的解决办法就是把不同的grouping_ID产生的数据按照时间分段开来重新计算，这样会造成许多大量重复的代码。在没有想到下面这个解决方案时，那种根据不同时间段groupingID 分段计算是一种解决方案但不是最佳方案
大数据重要操作
javaMylief的博客
03-09 758
经纬度计算 GeoHash,相同区域内根据lng，lat和GeoHash方法得出的结果相同 <dependency> <groupId>ch.hsr</groupId> <artifactId>geohash</artifactId> <version>1.3.0</version> </dependency> GeoHash.geoHashStringWithCharacterPrecision(l
常用窗口函数总结
qqCEM的博客
08-05 3728
目录   SUM,AVG,MIN,MAX,NTILE, ROW_NUMBER,RANK,DENSE_RANK, CUME_DIST,PERCENT_RANK,LAG,LEAD, FIRST_VALUE,LAST_VALUEGROUPING SETS,GROUPING__ID, CUBE,ROLLUP --Hive分析窗口函数(一) SUM,AVG,MIN,MAX --Hive分析窗...
Hive窗口函数之GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
LMR的博客
06-17 5029
数据： 2015-03,2015-03-10,cookie1 2015-03,2015-03-10,cookie5 2015-03,2015-03-12,cookie7 2015-04,2015-04-12,cookie3 2015-04,2015-04-13,cookie2 2015-04,2015-04-13,cookie4 2015-04,2015-04-16,cookie4 2015-...
Hive之grouping sets用法及grouping_id计算方法
热门推荐
HappyRocking的专栏
06-04 2万+
grouping sets 用法 在Hive中，会出现对同一个数据表进行不同粒度的汇总，这时可以有两种方案： 用多个sql，对不同粒度使用不同的 group by 方法。 用1个sql，使用 grouping sets 方法一次性得到所有粒度的汇总。 以测试数据表 tmp_student 为例: 1、生成测试数据 在 hive 环境中创建临时表： create table tmp.tmp_student ( name string, class int, sex string,
grouping_id&grouping sets
weixin_46680124的博客
09-05 682
grouping sets 用法在Hive中，会出现对同一个数据表进行不同粒度的汇总，这时可以有两种方案：用多个sql，对不同粒度使用不同的 group by 方法。
Hive知识之GROUPING运算符
恋恋风尘的博客
04-12 1612
前言：Hive的GROUPING运算符和SQL语句非常类似，如果有SQL基础，学习Hive的GROUPING运算符会比较容易。 可参考SQL的GROUPING运算符： https://blog.csdn.net/weixin_42384784/article/details/105462526 1、GROUPING运算符 GROUPING运算符主要包括 GROUPING SETS()、CUBE、...
Aggregation聚合函数--grouping,grouping_id,grouping set,cube,rollup
tangyaling0422的专栏
08-06 578
1.grouping() 列值为空，不聚合，返回1 列值非空，聚合，返回0 2.grouping_id() 可以接受多列，对于单组查询，grouping()与grouping_id()等价，grouping_id()将每个输出行中的为其列表中的每个列返回的值作为0,1字符串输入，然后将二进制转换成十进制。 多个参数的形式则将其每个参数进行grouping_id(arg)运算后返回的值拼成
hive_site.zip
10-08
hive_site.xml，供参考。 hive_site.xml，供参考 hive_site.xml，供参考 hive_site.xml，供参考 hive_site.xml，供参考 hive_site.xml，供参考
hive客户端安装_hive客户端安装_hive_
10-01
基于hadoop，进行hive客户端安装与使用
hive_have_null_id.tar.gz
12-08
hive_have_null_id.tar.gz
大数据第五天(操作hive的方式)
weixin_51885039的博客
04-23 612
操作hive的方式
springboot+mybatis+hive启动失败
weixin_45336151的博客
04-23 437
失败原因见最后几行，说明[jetty-runner-9.3.20.v20170531.jar:2.3.2]版本冲突，删除即可。
java-springmvc 01 补充 javaweb 三大组件Servlet，Filter、Listener（源码都是tomcat8.5项目中的）
qq_38757863的博客
04-27 157
01。
MySQL 和 Hive 存储引擎对表数量、索引有那些限制？
小湘西的博客
04-30 406
MySQL支持多种存储引擎，如InnoDB和MyISAM，每种引擎都有自己的特性和限制。
Spring Security介绍（三）过滤器（2）自定义
w_t_y_y的博客
04-27 242
Component@Slf4j@Overridelog.info("进入UrlFilter");@Component@Slf4j@Overridelog.info("进入UrlFilter-one");修改自定义的UrlFilter，修改为继承OncePerRequestFilter@Component@Slf4j@Overridelog.info("进入UrlFilter");
Spring MVC入门程序
最新发布
u010288901的博客
04-30 219
掌握Spring MVC入门程序
hive中grouping sets 用法
05-30
在Hive中，GROUPING SETS是一种聚合操作，它可以同时对多个维度进行分组聚合，而不需要使用多个GROUP BY子句。使用GROUPING SETS可以极大地简化SQL语句的编写，并提高查询性能。以下是一个使用GROUPING SETS的示例： ``` SELECT col1, col2, SUM(col3) FROM table_name GROUP BY GROUPING SETS((col1), (col2), (col1, col2)); ``` 在上述示例中，使用GROUPING SETS对col1和col2进行分组聚合，并计算col3的总和。GROUPING SETS子句的参数是一个包含多个分组集的列表。在上述示例中，我们使用了三个分组集：(col1)、(col2)和(col1, col2)。这意味着我们将按照col1、col2和(col1, col2)这三个维度进行分组聚合，并计算col3的总和。 值得注意的是，使用GROUPING SETS时，查询结果中可能会包含空行，因为GROUPING SETS会尝试对所有可能的分组组合进行聚合。如果您想要过滤掉空行，可以使用HAVING子句。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值