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 的功能