这里分享关于如何提升Hive执行效率,帮助你学会企业级的优化技巧,学会思路变换,多种方法解决问题。还在等什么,让我们开始吧!
提升Hive执行效率
技巧1:去重技巧——用group by来替换distinct
我们通过一个需求例子来比较下group by 和 distinct两者的执行时长。
这里有张user_trade表:
需求1:取出user_trade表中全部支付用户
distinct
写法:
SELECT distinct user_name
FROM user_trade
WHERE dt>'0';
dt 是分区字段,dt>‘0’ 是扫描所有分区。
执行时长:
group by
优化写法:
SELECT user_name
FROM user_trade
WHERE dt>'0'
GROUP BY user_name;
执行时长:
可以看到group by较distinct的执行时长更短些。
原因:
distinct会将所有的数据都shuffle到一个reducer里面,而group by将数据分布到多台机器上执行,效率更高。
Hive 去重统计中都会在map阶段count,但reduce阶段,distinct只有一个,group by可以有多个进行并行聚合,所以group by 会更快。
结论:
能使用group by代替distinc就不要使用distinct
需求2:
在2019年购买后又退款的用户
distinct写法:
SELECT a.user_name
FROM
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2019)a
JOIN
(SELECT distinct user_name
FROM user_refund
WHERE year(dt)=2019)b on a.user_name=b.user_name;
GROUP BY优化写法:
SELECT a.user_name
FROM
(SELECT user_name
FROM user_trade
WHERE year(dt)=2019
GROUP BY user_name)a
JOIN
(SELECT user_name
FROM user_refund
WHERE year(dt)=2019
GROUP BY user_name)b on a.user_name=b.user_name;
注意:
在极大的数据量(且很多重复值)时,可以先group by去重,再count()计数,效率高于直接count(distinct **)
技巧2:聚合技巧——利用窗口函数grouping sets、cube、rollup
1、grouping sets
GROUPING SETS:根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL
通俗的说grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。
需求3:
我们想知道用户的性别分布、城市分布、等级分布,该怎么实现?
通常我们会分开写,写三次SQL,需要执行三次
性别分布
SELECT sex,
count(distinct user_id)
FROM user_info
GROUP BY sex;
城市分布
SELECT city,
count(distinct user_id)
FROM user_info
GROUP BY city;
等级分布
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);
注意:
聚合结果均在同一列,分类字段用不同列来进行区分。
GROUPING SETS():在group by查询中,根据不同的维度组合进行聚合,等价于将不同维度的group by结果集进行union all。聚合规则在括号中进行指定。
如果我们想知道用户的性别分布以及每个性别的城市分布,那么应该怎么写?
性别分布
SELECT sex,
count(distinct user_id)
FROM user_info
GROUP BY sex;
每个性别的城市分布
SELECT sex,
city,
count(distinct user_id)
FROM user_info
GROUP BY sex,
city;
优化后的效果:
性别、性别&城市的用户分布
SELECT sex,
city,
count(distinct user_id)
FROM user_info
GROUP BY sex,city
GROUPING SETS (sex,(sex,city));
注意:
第二列为NULL的,即是性别的用户分布,其余有城市的均为每个性别的城市分布。
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;
3、rollup
rollup:以最左侧的维度为主,从该维度进行层级聚合,是cube的子集。
举例:
我们想要计算出每个月的支付金额,以及每年的总支付金额,应该怎么办?
SELECT a.dt dt,
sum(a.year_amount) year_amount,
sum(a.month_amount) 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;
技巧3:换个思路解题
条条大路通罗马,写SQL亦是如此,能达到同样效果的SQL有很多种,要学
会思路转换,灵活应用。
之前的需求案例:
查询在2017年和2018年都购买的用户。
写法一:
SELECT a.user_name
FROM
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2017)a
JOIN
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2018)b on a.user_name=b.user_name;
写法二:
SELECT a.user_name
FROM
(SELECT user_name,
count(distinct year(dt)) as year_num
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY user_name)a
WHERE a.year_num=2;
写法三:
SELECT user_name,
count(distinct year(dt)) as year_num
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY user_name
having count(distinct year(dt))=2;
技巧4: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;
开启并发执行与未开启并发执行的区别。
时间对比:
- 未开启并发执行:
- 开启并发执行:
技巧5:利用lateral view进行行转列
我们查询下这张表的信息简单看下。
需求举例:每个品类的购买用户数
SELECT b.category,
count(distinct a.user_name)
FROM user_goods_category a
lateral view explode(split(category_detail,',')) b as
category
GROUP BY b.category;
split()
:字符串分割函数
explode
:行转列函数
拓展:
列转行函数
:concat_ws(’,’,collect_set(column))
不清楚Hive lateral view 和 explode的用法,请点击这里!
技巧6:表连接优化
小表在前,大表在后
Hive假定查询中最后的一个表是大表,它会将其它表缓存起来,然后扫描最后那个表。使用相同的连接键
当对3个或者更多个表进行join连接时,如果每个on子句都使用相同的连接键的话,那么只会产生一个MapReduce job。尽早的过滤数据
减少每个阶段的数据量,对于分区表要加分区,同时只选择需要使用到的字段。逻辑过于复杂时,引入中间表
技巧7:如何解决数据倾斜
数据倾斜的表现:
任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
数据倾斜的原因与解决办法:
-
空值产生的数据倾斜
解决:如果两个表连接时,使用的连接条件有很多空值,建议在连接条件中增加过滤。 -
大小表连接(其中一张表很大,另一张表非常小)
解决:将小表放到内存里,在map端做Join
例如:
SELECT /+mapjoin(a)/,
b.*
FROM a join b on a.=b.
- 两个表连接条件的字段数据类型不一致
解决:将连接条件的字段数据类型转换成一致的
例如:
on a.user_id=cast(b.user_id as string)
技巧8:如何计算按月累计去重
大家都知道用sum() over()来计算按一定周期进行累计求和,但如何计算按
月累计去重呢?
需求案例:
2017、2018年按月累计去重的购买用户数。
SELECT b.year,
b.month,
sum(b.user_num) over(partition by b.year order by
b.month)
FROM
(SELECT a.year,
a.month,
count(distinct a.user_name) user_num
FROM
(SELECT year(dt) as year,
user_name,
min(month(dt)) as month
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY year(dt),
user_name)a
GROUP BY a.year,
a.month)b
ORDER BY b.year,
b.month
limit 24;
其他写法:
2017、2018年按月累计去重的购买用户数。
set hive.mapred.mode=nonstrict;
SELECT b.month,
count(distinct a.user_name)
FROM
(SELECT substr(dt,1,7) as month,
user_name
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY substr(dt,1,7),
user_name)a
CROSS JOIN
(SELECT month
FROM dim_month)b
WHERE b.month>=a.month and
substr(a.month,1,4)=substr(b.month,1,4)
GROUP BY b.month;
总结
- 巧用group by
- 利用窗口函数提升聚合计算效率
- 必要时开启并发执行
- 行转列、列转行
- 小技巧提升表连接效率
- 多思路解题