高性能Hive学习笔记（一）

*本次学习主要参考如下网页：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)问题