hive做过哪些参数的优化? hive grouping sets优化

1、背景

在数据开发统计相关计算过程中，group by 和group sets 是我们经常用的方法。但当统计指标过程中如果维度特别多，会使得数据任务运行特别慢，导致任务延迟经常出现。针对上面问题对group sets 常用指标进行调研优化。

grouping sets是对select语句中group by子句的扩展，允许您采用多种方式对结果分组，而不必使用多个select语句再union all来实现。这样能够引擎给出更有效的执行计划，从而提高执行性能。

注：group by cube (a, b, c)
–等效于以下语句。
grouping sets ((a,b,c),(a,b),(a,c),(b,c),(a),(b),©,())

group by cube ( (a, b), (c, d) )
–等效于以下语句。
grouping sets (
( a, b, c, d ),
( a, b ),
( c, d ),
( )
)

2、优化方式

1、减少数据中间过程数据膨胀，（如果中间数据膨胀很多，在大数据量和多维度组合情况下极易导致数据某几个job 运行过慢。也非常容易导致数据出现倾斜问题）

解决方式1：增加partitions 数量
方案2：分析sql 重点分析数据膨胀原因，优化sql 降低中间数据量。
具体参照：https://zhuanlan.zhihu.com/p/78804934
方案3：通用参数解决group by 运行中出现数据倾斜问题
参照：

2、分析group by （sets）运行方式。应用数据局部分组排序的+group sets的方式任务运行效率。
具体使用方式：

select dt,
       dt_name,
       city_id,
       city_level,
       sum(user_num),
       sum(ord_amt)
  from table_name1
 where dt>=2020-09-01
 group by dt,
          dt_name,
          city_id,
          city_level
grouping sets((dt), (dt,dt_name,city_id), (dt,dt_name,city_level), (dt,dt_name,city_id,city_level))

优化为
select dt,
       dt_name,
       city_id,
       city_level,
       sum(user_num),
       sum(ord_amt)
  from (
        select *
          from table_name1
         where dt>=2020-09-01 distribute by dt,
                  dt_name,
                  city_id,
                  city_level
          sort by dt,
                  dt_name,
                  city_id,
                  city_level
       ) a
 group by dt,
          dt_name,
          city_id,
          city_level
grouping sets((dt), (dt,dt_name,city_id), (dt,dt_name,city_level), (dt,dt_name,city_id,city_level))

具体分析原理：
使用distribute by +sort by即 实现执行优化器执行时通过索引扫描即能直接得到group by 分组。这样就可以做到group by 的优化了。进一步来讲，就是hive执行sql其实底层是执行一个或多个job 每个job里又有一个或者多个mapreduce,通完distribute by +sort by我们可以将数据分别在各自的map和reduce里进行局部排序。这样在进入外层的group by 时就不能进行排序，直接使用分组即可了
具体分析：
SELECT
help_category_id,
help_topic_id
FROM help_topic_test
GROUP BY
help_category_id,
help_topic_id

发现即使没有sort by 。数据已经是有序的了

从上面知道group by 过程中会对group by 的key进行排序。
由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。当 MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到 GROUP BY 的结果之后，他就不得不选择通过使用临时表然后再排序的方式来实现 GROUP BY了。
在这样示例中即是这样的情况。 group_id 并不是一个常量条件，而是一个范围，而且 GROUP BY 字段为 user_id。所以 MySQL 无法根据索引的顺序来帮助 GROUP BY 的实现，只能先通过索引范围扫描得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成 GROUP BY。