HiveSQL优化技巧总结-阿里云开发者社区

前言

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

优化的核心思想是：

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

一、SQL语句的结构

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

二、 SQL语句的执行顺序

第一步：确定数据源
FROM
JOIN
ON 
第二步：过滤数据
WHERE 
GROUP BY (开始使用SELECT 中的别名，后面的语句中都可以使用)
avg，sum.......
HAVING
第三步：查询数据
SELECT
第四步：显示数据
DISTINCT
ORDER BY 
LIMIT

三、HQL语句优化

1. 列裁剪和分区裁剪

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

分区裁剪：就是在查询时只读取需要的分区。配置项：hive.optimize.pruner，默认是true

例如：

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

b表的过滤条件写在了where后面，会导致先全表关联，之后再过滤分区。而a表的过滤条件写在where后面是可以的，因为a表会进行谓词下推，就是先执行where，再执行join，但是b表不会进行谓词下推。

on的条件没有过滤null值情况，如果两个数据表存在大批量null值情况，会造成数据倾斜。

优化1：

select a.*  
from a  
left join b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10') 
where a.ds='2020-08-10'

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

select a.*  
from a  
left join b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10')  
where a.ds='2020-08-10'  
union all  
select a.* from a where a.uid is null

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
from table
where xxx
order by a
limit 10;
-- 优化写法
select a,b,c
from table
where xxx
distribute by a
sort by a
limit 10;

3. 使用group by 代替 distinct

例子1：

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

例子2：

-- 原有写法
select count(distinct uid)  
from test  
where ds='2020-08-10' and uid is not null  
-- 优化写法
select count(a.uid)  
from   
(select uid 
 from test 
 where uid is not null and ds = '2020-08-10' 
 group by uid
) a

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

4. 使用with as

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

select a.*  
from  a  
left join 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 is not null  
)  
select a.*  
from a  
left join test1 on a.uid = test1.uid  
where a.ds='2020-08-10' and a.uid is not null

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

5.1 grouping sets

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

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

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

优化后：

-- 使用 grouping sets 实现
select sex,city,level
         count(distinct user_id)
from user_info
group by 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
GROUP BY 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
GROUP BY sex,city,level
with 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,
      0 as month_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY substr(dt,1,4)
   UNION ALL
   SELECT substr(dt,1,7) as dt,
      0 as year_amount,
      sum(pay_amount) as month_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY substr(dt,1,7)
  )a
GROUP BY a.dt;

优化后：

SELECT year(dt) as year,
   month(dt) as month,
   sum(pay_amount)
FROM user_trade
WHERE dt>'0'
GROUP BY year(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,
      0 as refund_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY user_name
  UNION ALL
   SELECT user_name,
      0 as pay_amount,
      sum(refund_amount) as
refund_amount
   FROM user_refund
   WHERE dt>'0'
   GROUP BY user_name
 )a
GROUP BY 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添加随机值打散

具体步骤如下：

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

9. 遵循严格模式

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

set hive.mapred.mode=strict

3种有风险的Hive SQL语句：

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

好了，今天就为大家分享到这里了。咱们下期见！

如果本文对你有帮助的话，欢迎点赞&收藏&分享，这对我继续分享&创作优质文章非常重要。感谢🙏🏻

HiveSQL优化技巧总结

前言

一、SQL语句的结构

二、 SQL语句的执行顺序

三、HQL语句优化

1. 列裁剪和分区裁剪

2. 使用sort by 代替 order by

3. 使用group by 代替 distinct

4. 使用with as

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

5.1 grouping sets

5.2 cube

5.3 rollup

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

7. 表的join优化

8. 数据倾斜

8.1 参数调优

8.2 倾斜key添加随机值打散

9. 遵循严格模式

热门文章

最新文章

相关课程

相关电子书

相关实验场景