*本次学习主要参考如下网页:Hive高性能优化
1 SQL书写规范
尽量尽早地过滤数据,减少每个阶段的数据量,对于分区表要加分区。
案例一:
-- 案例一:
SELECT a.key,a.col1,a.col2,b.col3,b.col4
FROM
A a
LEFT OUTER JOIN
B b
ON a.key = b.key and a.dt=‘20150101’ and b.dt=‘20150101';
优化方法一:
--优化方法一: 使用where,尽早分区
SELECT a.key,a.col1,a.col2,b.col3,b.col4
FROM
A a
LEFT OUTER JOIN
B b
ON a.key = b.key
where a.dt=‘20150101’ and b.dt=‘20150101';
优化方法二:
SELECT a.key,a.col1,a.col2,b.col3,b.col4
from
(select key,col1,clo2 from A where dt = '20150101')a
left outer join
(select key,col3,clo4 from B where dt='20150101')b
on a.key = b.key
2 设置合理的Map、Reduce数
1. splitSize的计算方式
Map数的决定因素有:(1)input的文件总个数(2)input的文件大小(3)集群设置的文件快大小(blockSize默认为64M)(4)maxSize(mapred.max.split.size)
splitSize的计算公式:max{minSplitSize,min{maxSize,blockSize}},其中minSplitSize大小默认为1B,maxSize为mapred.max.split.size的值。
2. Map数的计算方式
单个文件:文件大小/splitSize>1.1,创建一个split0,文件剩余大小=文件大小-splitSize…剩余文件大小/splitSize<=1.1 将剩余的部分作为一个split每一个分片对应一个map任务。总map数:每个文件的map数之和。
2.3 调整map数
通过设置参数,来合并map输入的小文件:set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ##执行Map前进行小文件合并set mapred.max.split.size=128000000; ##每个Map最大输入大小注:mapred.map.tasks设置map个数无效。
2.4 调整Reduce数
不指定reducer个数的情况下,Hive会猜测确定一个reducer个数,基于以下两个设定: 参数1:hive.exec.reducers.bytes.per.reducer(每个reduce处理的数据大小,默认为1G) 参数2 :hive.exec.reducers.max(每个job使用的最大reduce个数,默认为999)计算reducer数的公式:N=min(参数2,总输入数据量/参数1)通过设置这两个参数来减少或增加reduce数,案例:set hive.exec.reducers.bytes.per.reducer=256000000;##每个reduce处理的数据大小set mapred.reduce.tasks=10;##设置reduce个数set hive.exec.reducers.max=2000;##增加最大reduce个数
3 尽量减少job数
Hive对union all 优化只局限于非嵌套查询
案例二:
-- 案例二:
select * from
(select * from t1 group by c1,c2,c3
union all
select * from t2 group by c1,c2,c3
)t3 group by c1,c2,c3
优化方法:
select * from
(select * from t1
union all
select * from t2
)t3
group by c1,c2,c3
结果:优化后并未出现union all后数据不正确的hive bug,数据是一致的。mr的作业书从3减少到1。
4 解决数据倾斜问题
4.1无效数值取值过多
常用方法:将无效的数据转换为随机数,均匀分配到不同的Reduce上处理。
案例:
--案例
select * from A a
join B b
on a.user_id = b.user_id
union all
select * from A a
where a.user_id is null
-- 优化方法:
select * from A a
left outer join B b
on case when a.user_id is null concat('dp_hive',rand()) else a.user_id end = b.user_id
4.2有效数值取值过多
(1)现象举例:如业务需要加工一张订单商品表,订单表order和商品表item通过item_id相关联,每天“热销的商品”是固定的几个item,在未改造前经常出现数据倾斜(这个几个热销的item_id在对应的Reduce上处理时间过长)。
(2)解决思路:先生成一张每天的热销商品清单表(table_item_top),然后选取top前4的item_id(根据实际情况而定),在关联时,若order的item_id是top前4的item_id,则将item_id转换为item_id_rand的形式,rand的随机取值可能时1~n,随机份数视情况而定;且商品表通过列转行方式,将商品表中的item_id也生成对应的若干行item_id_rand格式数据,然后两表通过item_id_rand关联,热销商品将会随机分配若干份到reduce上并行处理,从而解决倾斜问题。
(3)案例
-- 案例
select t1.order_id,t2.item_name,t2.price
from (
select order_id,item_id from order where dt = '20200305'
)t1
left outer join (
select item_id ,item_name,price from item where dt = '20200305'
)t2
on t1.item_id = t2.item_id;
(4)优化方法:
select t1.order_id, t3.item_name,t3.classname,t3.price
from (
select order_id ,item_id,getRand(item_id,'table_item_top','rand') as item_id_rand
from order where dt = '20200305'
)t1
left outer join (
select col1 as item_id_rand,itemname,classname,price from (
select item_id,getRand(item_id,'table_item_top','read') as item_id_rand,itemname,classname,price from item where dt = '20200305'
)t2
lateral view explode(split(item_id_rand,','))mytable as col1
)t3 on t1.item_id_rand = t3.item_id_rand
4.3不同数据类型关联
(1)现象:一张日志表,每个商品一条记录,要和商品关联。但关联却碰到倾斜的问题。日志表中欧字符串id,也有数字的商品id,字段类型是string,但商品中的数字id是bigint的。猜测问题的原因是吧s8的商品id转换成数字id做hash来分配reduce,所以字符串id的s8日志,都到一个reduce上了。
(2)优化方法:把数据类型转换成字符串类型
select * from s8_log a
left outer joun r_auction b
on a.auction_id = cast(b.auction_id as string)
4.4 count(distinct)问题