Hive SQL优化

在有限资源下，提高执行效率 
常见问题： 
数据倾斜， 
map数设置 
reduce数设置

Hive执行，HQL>JOB>MAP/REDUCE 
查看执行计划 
explain  hql

hive查询操作优化 

• join优化 

hive.optimize.skewjoin=true;如果是join过程出现倾斜，应该设置为true 
set hive.skewjoin.key=100000;这个是join的键对应的记录的条数，超过这个值会进行优化 

• mapjoin 

set hive.auto.convert,join=true; 
hive.mapjoin.smalltable.filesize默认值是25mb（其中一个表大小小于25mb时，自动启用mapjoin） 
select /+mapjoin(A)/f.a,f.b from A a join B f on (f.a=t.a); 
简单总结一下，mapjoin的使用场景 
关联操作中有一张表非常小 
不等值的连接操作

bucket join 
连个表以相同方式划分桶； 
两个表的桶个数是倍数关系

• join优化 

优化前 
select m.cid,u.id form order m join customer u on m.cid=u.id where m.dt=’20160801’; 
会先执行join 
优化后 
select m.cid,u.id from (select cid from order where dt=’20160801’)m 
join customer u on m.cid = u.id

• group by 优化 

hive.groupby.skewindata=true;如果group by过程出现倾斜，应该设置为true

• count distinct 优化 

优化前 
select count(distinct id )from tablename 
优化后 
select count(1) from (select distinct id from tablename)tmp; 
select count(1) from (select id from tablename group by id)tmp;

• hive job优化 

并行化执行 
每个查询被hive转化为多个阶段，有些阶段关联性不大，则可以并行化执行，减少执行时间 
set hive.exec.parallel=true; 
set hive.exec.parallel.thread.number=8;

本地化执行 
set hive.exec.mode.local.auto=true; 
当一个job满足如下条件才能真正使用本地模式： 
1.job的输入数据大小不小于参数: 
hive.exec.mode.local.auto.inputbytes.max（默认是128m） 
2.job的map数必须小于参数： 
hive.exec.mode.local.auto.tasks.max（默认为4） 
3.job的reduce必须为0或者1

• job合并输入小文件 

set hive.input.format=org.apache.Hadoop.hive.ql.io.CombineHiveInputFormat 
合并文件数有mapred.max.split.size限制的大小

job合并输出小文件 
set hive.merge.smallfiles.avgsize=256000000;当输出文件平均大小小于改值时，启动新job合并文件 
set hive.merge.size.per.task=6400000000;合并之后的文件大小

jvm重利用 
set mapred.job.reuse.jvm.num.tasks=20; 
jvm重利用可以使job长时间保留slot，直到作业结束，

 

• 如何适当的增加map数？

 
如果表A只有一个文件，大小为120M，包含几千万记录，可以考虑用多个map任务完成
 
set mapred.reduce.tasks=10;
 
                  create table a_1 as
 
                  select * from a
 
                  distribute by rand(123); //将a表的记录，随机的分散到包含10个文件的a_1表中
 

•  hive如何确定reduce数， reduce的个数基于以下参数设定：

 
hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）
 
hive.exec.reducers.max（每个任务最大的reduce数，默认为999）
 
计算reducer数的公式很简单N=min(参数2，总输入数据量/参数1)
 
即，如果reduce的输入（map的输出）总大小不超过1G,那么只会有一个reduce任务；所以调整以下参数：
 
set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
 
set mapred.reduce.tasks = 15;
 
 

•  Group by

 
Group By的方法是在reduce做一些操作，这样会导致两个问题：
 
map端聚合，提前一部分计算：hive.map.aggr = true 同时设置间隔：hive.groupby.mapaggr.checkinterval
 
均衡处理：hive.groupby.skewindata
 
这是针对数据倾斜的，设为ture的时候，任务的reduce会把原来一个job拆分成两个，第一个的job中reduce处理处理不同的随即分发过来的key的数据，生成中间结果，再由最后一个综合处理。
 

•   Order by, Sort by ,Dristribute by,Cluster By

 
1、    order by VS Sort by： order by是在全局的排序，只用一个reduce去跑，所以在set hive.mapred.mode=strict 模式下，order by 必须limit，否则报错。Sort by只保证同一个reduce下排序正确。
 
2、    Distribute by with sort by: Distribute by 是按指定的列把map 输出结果分配到reduce里。所以经常和sort by 来实现对某一字段的相同值分配到同一个reduce排序。
 
3、    Cluster by 实现了Distribute by+ sort by 的功能