HiveSQL优化技巧总结

大家好，我是风云，欢迎大家关注我的个人原创公众号【笑看风云路】获取更多大数据技术干货，在未来的日子里我们一起来学习大数据相关的技术，一起努力奋斗，遇见更好的自己！

扫码进，更快捷

：

前言

基于Hive的开发过程中主要涉及到的可能是SQL优化这块。
优化的核心思想是：

• 减少数据量（例如分区、列剪裁）
• 避免数据倾斜（例如加参数、Key打散）
• 避免全表扫描（例如on添加加上分区等）
• 减少job数（例如相同的on条件的join放在一起作为一个任务）

一、SQL语句的结构

SELECT*     （必须）
FROM            表（数据源）
WHERE              条件
GROUPBY          字段
HAVING           条件
ORDERBY          字段(排序 默认 ASC)LIMIT             限制数据条数

二、 SQL语句的执行顺序

第一步：确定数据源
FROMJOINON 
第二步：过滤数据
WHEREGROUPBY(开始使用SELECT 中的别名，后面的语句中都可以使用)
avg，sum.......HAVING
第三步：查询数据
SELECT
第四步：显示数据
DISTINCTORDERBYLIMIT

三、HQL语句优化

1. 列裁剪和分区裁剪

列裁剪：就是在查询时只读取需要的列。如果select * 或者不指定分区，导致全表扫描和全分区扫描效率都很低。配置项：hive.optimize.cp , 默认是true
分区裁剪：就是在查询时只读取需要的分区。配置项：hive.optimize.pruner，默认是true
例如：

select a.*from a  
leftjoin b on  a.uid = b.uid  
where a.ds='2020-08-10'and b.ds='2020-08-10'

1. b表的过滤条件写在了where后面，会导致先全表关联，之后再过滤分区。而a表的过滤条件写在where后面是可以的，因为a表会进行谓词下推，就是先执行where，再执行join，但是b表不会进行谓词下推。
2. on的条件没有过滤null值情况，如果两个数据表存在大批量null值情况，会造成数据倾斜。

优化1：

select a.*from a  
leftjoin b on(a.uid isnotnulland a.uid = b.uid and b.ds='2020-08-10')where a.ds='2020-08-10'

优化2：如果null也是需要的

select a.*from a  
leftjoin b on(a.uid isnotnulland a.uid = b.uid and b.ds='2020-08-10')where a.ds='2020-08-10'unionallselect a.*from a where a.uid isnull

2. 使用sort by 代替 order by

order by：将结果按某个字段全局排序，会导致所有map端数据都进入一个reducer中，当数据量大时可能会长时间计算不完。
sort by：就会根据情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reduce的key，往往还要配合distribute by一起使用。如果不加distribute by，map端数据就会随机分配给reducer。

-- 未优化写法select a,b,c
fromtablewhere xxx
orderby a
limit10;-- 优化写法select a,b,c
fromtablewhere xxx
distribute by a
sort by a
limit10;

3. 使用group by 代替 distinct

例子1：

-- 取出user_trade表中全部支付用户-- 原有写法SELECTdistinct user_name
FROM user_trade
WHERE dt>'0';--测试时长 43 s-- 优化写法SELECT user_name
FROM user_trade
WHERE dt>'0'GROUPBY user_name;--测试时长 29 s

例子2：

-- 原有写法selectcount(distinct uid)from test  
where ds='2020-08-10'and uid isnotnull-- 优化写法selectcount(a.uid)from(select uid 
 from test 
 where uid isnotnulland ds ='2020-08-10'groupby uid
) a

注意：COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，使用先GROUP BY再COUNT的方式替换，虽然会多用一个Job来完成，但在数据量大的情况下（且很多重复值），效率高于直接count（distinct）。

4. 使用with as

with as：是将语句中用到的子查询事先提取出来（类似临时表），使整个查询当中的所有模块都可以调用该查询结果，避免Hive对不同部分的相同子查询进行重复计算。

select a.*from  a  
leftjoin b on  a.uid = b.uid  
where a.ds='2020-08-10'and b.ds='2020-08-10'-- 转化为：with test1 as(select uid  
from b  
where ds ='2020-08-10'and uid isnotnull)select a.*from a  
leftjoin test1 on a.uid = test1.uid  
where a.ds='2020-08-10'and a.uid isnotnull

5. 聚合操作 – grouping sets、cube、rollup

5.1 grouping sets

grouping sets： 指定分组的维度，聚合结果均在同一列，分类字段用不同列来区分

例如：统计用户的性别分布，城市分布，等级分布

-- 通常使用三条sql语句实现-- 1. 性别分布select sex,count(distinct user_id)from user_info
groupby sex;-- 2. 城市分布select city,count(distinct user_id)from user_info
groupby city;-- 3. 等级分布selectlevel,count(distinct user_id)from user_info
groupbylevel;

优化后：

-- 使用 grouping sets 实现select sex,city,levelcount(distinct user_id)from user_info
groupby sex,city,level
grouping sets (sex,city,level)

5.2 cube

cube：根据group by维度的所有组合进行聚合

-- 统计性别、城市、等级等维度的各种组合的用户分布SELECT sex,
   city,level,count(distinct user_id)FROM user_info
GROUPBY sex,city,level
GROUPING SETS (sex,city,level,(sex,city),(sex,level),(city,level),(sex,city,level));

优化后：

select sex
       city,level,count(distinct user_id)FROM user_info
GROUPBY sex,city,levelwith cube;

5.3 rollup

rollup：以最左侧的维度为主，进行层级聚合，是cube的子集

例如：统计每个月的支付金额，以及每年的总支付金额

SELECT a.dt,sum(a.year_amount),sum(a.month_amount)FROM(SELECT substr(dt,1,4)as dt,sum(pay_amount) year_amount,0as month_amount
   FROM user_trade
   WHERE dt>'0'GROUPBY substr(dt,1,4)UNIONALLSELECT substr(dt,1,7)as dt,0as year_amount,sum(pay_amount)as month_amount
   FROM user_trade
   WHERE dt>'0'GROUPBY substr(dt,1,7))a
GROUPBY a.dt;

优化后：

SELECTyear(dt)asyear,month(dt)asmonth,sum(pay_amount)FROM user_trade
WHERE dt>'0'GROUPBYyear(dt),month(dt)with rollup;

6. union all时可以开启并发执行

Hive中互相没有依赖关系的job间是可以并行执行的，最典型的就是多个子查询union all。在集群资源相对充足的情况下，可以开启并行执行。
参数设置：

set hive.exec.parallel=true;

例如：统计每个用户的支付和退款金额汇总

SELECT a.user_name,sum(a.pay_amount),sum(a.refund_amount)FROM(SELECT user_name,sum(pay_amount)as pay_amount,0as refund_amount
   FROM user_trade
   WHERE dt>'0'GROUPBY user_name
  UNIONALLSELECT user_name,0as pay_amount,sum(refund_amount)as
refund_amount
   FROM user_refund
   WHERE dt>'0'GROUPBY user_name
 )a
GROUPBY a.user_name;-- 时间对比：-- 未开并发执行 103 s-- 开启并发执行 64 s

7. 表的join优化

Join操作的原则
原则1：应该将条目少的表、子查询放在join操作符的左边。
新版hive已经对小表join大表和大表join小表进行了优化。小表放左边和右边已经没有明显区别。不过在做join的过程中通过小表在前可以适当减少数据量，提高效率。
原则2：使用相同的连接键
当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
原则3：尽早的过滤数据
减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的字段。

8. 数据倾斜

8.1 参数调优

-- 开启map端预聚合功能set hive.map.aggr=true;-- 开启group by倾斜时自动进行负载均衡功能set hive.groupby.skewindata = ture;

8.2 倾斜key添加随机值打散

具体步骤如下：

1. sample采样，获取哪些集中的key；
2. 将集中的key按照一定规则添加随机数；
3. 进行join，由于打散了，所以数据倾斜避免了；
4. 在处理结果中对之前的添加的随机数进行切分，变成原始的数据。

9. 遵循严格模式

严格模式：强制不允许用户执行3种有风险的Hive SQL语句，一旦执行会直接报错。

set hive.mapred.mode=strict

3种有风险的Hive SQL语句：

1. 查询分区表时不限定分区列的语句
2. 两表join产生了笛卡尔积的语句
3. 使用order by排序但没有指定limit语句

好了，今天就为大家分享到这里了。咱们下期见！
如果本文对你有帮助的话，欢迎点赞&收藏&分享，这对我继续分享&创作优质文章非常重要。感谢🙏🏻