阅读目录

  • GROUPING SETS
  • 概述
  • CUBE
  • ROLLUP
  • 常见错误

GROUPING SETS

概述

GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

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

GROUPING SETS和GROUPING__ID

说明

在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

GROUPING__ID，表示结果属于哪一个分组集合。

查询语句：

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


等价于：

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

　

查询结果

 

by group hive 别名 hive grouping_id_分析函数

 

结果说明

第一列是按照month进行分组

第二列是按照day进行分组

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

第四列grouping_id表示这一组结果属于哪个分组集合，根据grouping sets中的分组条件month，day，1是代表month，2是代表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,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month
UNION ALL
SELECT NULL,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

by group hive 别名 hive grouping_id_分析函数_02

 

CUBE

说明

根据GROUP BY的维度的所有组合进行聚合

 

查询语句

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


等价于

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM cookie5
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month
UNION ALL
SELECT NULL,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


查询结果

by group hive 别名 hive grouping_id_结果集_03

 

ROLLUP

说明

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合

查询语句

-- 比如，以month维度进行层级聚合

 

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

可以实现这样的上钻过程：
月天的UV->月的UV->总UV

by group hive 别名 hive grouping_id_结果集_04

--把month和day调换顺序，则以day维度进行层级聚合：

可以实现这样的上钻过程：
天月的UV->天的UV->总UV
（这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）

by group hive 别名 hive grouping_id_by group hive 别名_05

  

by group hive 别名 hive grouping_id_by group hive 别名_06

by group hive 别名 hive grouping_id_查询语句_07

-- grouping sets 
select p_short_name
,create_time
,count(1) as num
,GROUPING__ID   --GROUPING__ID，表示结果属于哪一个分组集合。
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name,create_time 
grouping sets(p_short_name,(p_short_name,create_time))
order by p_short_name,create_time 
---执行结果：
+---------------+--------------+------+---------------+--+
| p_short_name  | create_time  | num  | grouping__id  |
+---------------+--------------+------+---------------+--+
| 安徽            | NULL         | 87   | 1             |
| 安徽            | 2020-05-01   | 17   | 3             |
| 安徽            | 2020-05-02   | 24   | 3             |
| 安徽            | 2020-05-03   | 22   | 3             |
| 安徽            | 2020-05-04   | 24   | 3             |
| 江苏            | NULL         | 39   | 1             |
| 江苏            | 2020-05-01   | 11   | 3             |
| 江苏            | 2020-05-02   | 2    | 3             |
| 江苏            | 2020-05-03   | 7    | 3             |
| 江苏            | 2020-05-04   | 19   | 3             |
+---------------+--------------+------+---------------+--+

---- 等价于以下
select p_short_name,NULL as create_time,count(1) as num,1 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name 
order by p_short_name
---执行结果：
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| 安徽         | NULL        | 87  | 1            |
| 江苏         | NULL        | 39  | 1            |
+--------------+-------------+-----+--------------+
Fetched 2 row(s) in 0.68s

union all 

select  p_short_name,create_time,count(1) as num,3 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name,create_time
order by p_short_name,create_time
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| 安徽         | 2020-05-01  | 17  | 1            |
| 安徽         | 2020-05-02  | 24  | 1            |
| 安徽         | 2020-05-03  | 22  | 1            |
| 安徽         | 2020-05-04  | 24  | 1            |
| 江苏         | 2020-05-01  | 11  | 1            |
| 江苏         | 2020-05-02  | 2   | 1            |
| 江苏         | 2020-05-03  | 7   | 1            |
| 江苏         | 2020-05-04  | 19  | 1            |
+--------------+-------------+-----+--------------+
Fetched 8 row(s) in 0.69s

----------------------------------------cube----------------------------------------------------
select p_short_name
,create_time
,count(1) as num
,GROUPING__ID   --GROUPING__ID，表示结果属于哪一个分组集合。
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name,create_time 
with cube 
order by p_short_name,create_time 
+---------------+--------------+------+---------------+--+
| p_short_name  | create_time  | num  | grouping__id  |
+---------------+--------------+------+---------------+--+
| NULL          | NULL         | 126  | 0             |
| NULL          | 2020-05-01   | 28   | 2             |
| NULL          | 2020-05-02   | 26   | 2             |
| NULL          | 2020-05-03   | 29   | 2             |
| NULL          | 2020-05-04   | 43   | 2             |
| 安徽            | NULL         | 87   | 1             |
| 安徽            | 2020-05-01   | 17   | 3             |
| 安徽            | 2020-05-02   | 24   | 3             |
| 安徽            | 2020-05-03   | 22   | 3             |
| 安徽            | 2020-05-04   | 24   | 3             |
| 江苏            | NULL         | 39   | 1             |
| 江苏            | 2020-05-01   | 11   | 3             |
| 江苏            | 2020-05-02   | 2    | 3             |
| 江苏            | 2020-05-03   | 7    | 3             |
| 江苏            | 2020-05-04   | 19   | 3             |
+---------------+--------------+------+---------------+--+

---- 等价于以下
select NULl as p_short_name,NULL as create_time,count(1) as num,0 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| NULL         | NULL        | 126 | 0            |
+--------------+-------------+-----+--------------+


union all 

select p_short_name,NULL as create_time,count(1) as num,1 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name 
order by p_short_name
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| 安徽         | NULL        | 87  | 1            |
| 江苏         | NULL        | 39  | 1            |
+--------------+-------------+-----+--------------+
Fetched 2 row(s) in 0.55s


union all 
select NULl as p_short_name, create_time,count(1) as num,2 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by create_time 
order by create_time
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| NULL         | 2020-05-01  | 28  | 2            |
| NULL         | 2020-05-02  | 26  | 2            |
| NULL         | 2020-05-03  | 29  | 2            |
| NULL         | 2020-05-04  | 43  | 2            |
+--------------+-------------+-----+--------------+

Fetched 4 row(s) in 0.55s


union all 
select p_short_name, create_time,count(1) as num,3 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name, create_time
order by p_short_name, create_time
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| 安徽         | 2020-05-01  | 17  | 3            |
| 安徽         | 2020-05-02  | 24  | 3            |
| 安徽         | 2020-05-03  | 22  | 3            |
| 安徽         | 2020-05-04  | 24  | 3            |
| 江苏         | 2020-05-01  | 11  | 3            |
| 江苏         | 2020-05-02  | 2   | 3            |
| 江苏         | 2020-05-03  | 7   | 3            |
| 江苏         | 2020-05-04  | 19  | 3            |
+--------------+-------------+-----+--------------+

Fetched 8 row(s) in 0.55s

------------------------------  rollup------------------------------
select p_short_name
,create_time
,count(1) as num
,GROUPING__ID   --GROUPING__ID，表示结果属于哪一个分组集合。
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name,create_time 
with rollup 
order by p_short_name,create_time 
+---------------+--------------+------+---------------+--+
| p_short_name  | create_time  | num  | grouping__id  |
+---------------+--------------+------+---------------+--+
| NULL          | NULL         | 126  | 0             |
| 安徽            | NULL         | 87   | 1             |
| 安徽            | 2020-05-01   | 17   | 3             |
| 安徽            | 2020-05-02   | 24   | 3             |
| 安徽            | 2020-05-03   | 22   | 3             |
| 安徽            | 2020-05-04   | 24   | 3             |
| 江苏            | NULL         | 39   | 1             |
| 江苏            | 2020-05-01   | 11   | 3             |
| 江苏            | 2020-05-02   | 2    | 3             |
| 江苏            | 2020-05-03   | 7    | 3             |
| 江苏            | 2020-05-04   | 19   | 3             |
+---------------+--------------+------+---------------+--+
---- 等价于以下
select NULl as p_short_name,NULL as create_time,count(1) as num,0 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| NULL         | NULL        | 126 | 0            |
+--------------+-------------+-----+--------------+


union all 

select p_short_name,NULL as create_time,count(1) as num,1 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name 
order by p_short_name
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| 安徽         | NULL        | 87  | 1            |
| 江苏         | NULL        | 39  | 1            |
+--------------+-------------+-----+--------------+
Fetched 2 row(s) in 0.55s

union all 
select p_short_name, create_time,count(1) as num,3 as GROUPING__ID
from xyy_test.data_20200615_temp 
where create_time>='2020-05-01' and create_time<='2020-05-04'
and  p_short_name in ('安徽','江苏')
group by p_short_name, create_time
order by p_short_name, create_time
+--------------+-------------+-----+--------------+
| p_short_name | create_time | num | grouping__id |
+--------------+-------------+-----+--------------+
| 安徽         | 2020-05-01  | 17  | 3            |
| 安徽         | 2020-05-02  | 24  | 3            |
| 安徽         | 2020-05-03  | 22  | 3            |
| 安徽         | 2020-05-04  | 24  | 3            |
| 江苏         | 2020-05-01  | 11  | 3            |
| 江苏         | 2020-05-02  | 2   | 3            |
| 江苏         | 2020-05-03  | 7   | 3            |
| 江苏         | 2020-05-04  | 19  | 3            |
+--------------+-------------+-----+--------------+

Fetched 8 row(s) in 0.55s

grouping sets 综合案列

 

常见错误

内存不足

22-06-2020 08:02:18 CST goods INFO - Process completed unsuccessfully in 111 seconds.
22-06-2020 08:02:18 CST goods ERROR - Job run failed!
java.lang.RuntimeException: azkaban.jobExecutor.utils.process.ProcessFailureException: Process exited with code 1
    at azkaban.jobExecutor.ProcessJob.run(ProcessJob.java:305)
    at azkaban.execapp.JobRunner.runJob(JobRunner.java:813)
    at azkaban.execapp.JobRunner.doRun(JobRunner.java:602)
解决方案，脚本里需要加上参数如下：
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
set hive.exec.parallel=true;
set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize=25000000;
set mapreduce.map.memory.mb=3072;