HIVESQL优化方法

(这是我2年前总结的东西，当时是用华为Paas平台的大数据集群的时候，因为我们公司分配的资源较少，自己总结的部分优化方法，如有不足，望大家指正）

 

1) set hive.map.aggr=true/false;
当使用聚合函数时会在ma阶段进行聚合  效率更高同时消耗更多资源

2) 当join表的时候有一个大表时用mapjoin
select /*+ mapjoin(a) */ a.key,a,value,b,key,b.value from table_name_1 a join table_name_2 b on a.key=b.key;(a表为小表)
当其中的小表的数据量也非常大的时候，将小表上的数据分别分发到map上也是不小的资源开销，而且mapjoin可能不支持这么大的小表
这时候需要利用深层次的mapjoin进行处理 
比如select * from a join b on a.id=b.id; a为小表且a也非常大
则select /*+ mapjoin(a) */ * from a join b on a.id=b.id;这样的语句依旧非常耗费资源 我们可以进行这样的操作
select /*+ mapjoin(d) */ * from b join (select /*+ mapjoin(c) */ from (select distinct id from b) c join a on c.id=a.id) d on a.id=b.id;
先取大表的关联字段去重跟小表关联 取小表的的字段 然后得出关联后的小表记录再去用mapjoin与大表关联 这样节省了集群的开销。但是会加大SQL书写人员的难度

3) order by会对所有查询结果进行一次全局排序，会将所有数据传给一个reduce进行排序。当数据量级很大是，这个过程会非常慢
而sort by 会对每个reduce处理的数据进行局部排序，这样就大大提高了全局排序的效率

4) set mapreduce.job.priority=HIGH; 
改变队列的优先级（当队列中没有任务时）

5) 使用本地模式★
set hive.exec.mode.local.auto=true;(设置本地模式，一般默认为false)
当提交任务量较小时会在本地区处理而不是在集群上去处理，大大减少了处理时间
当一个job满足以下条件时会启用本地模式
job的数据大小参数必须小于 hive.exec.mode.local.auto.inputbytes.max  默认128M
job的map数必须小于参数 hive.exec.mode.local.auto.tasks.max   默认4
job的reduce数必须为1或者0

6) 并行执行
set hive.exec.parallel=true;(默认是false)
当一个SQL会产生多个stage（即mr任务）   多个stage会逐步执行
当上述设置为true是就会并行执行其stage  提高效率
但是当多个stage之间存在依赖时还是会逐步执行

7) 分区裁剪优化 
例如 select * from (select * from A) a where a.receive_day='XX' (a.receive_day为分区字段) 肯定不如将receive_day的限制分区放在子查询内占用的资源少
设置 hive.optimize.pruner设置为true时hive会自动执行这种优化

8) hive.groupby.skewindata=true; 
当使用groupby或者distinct的时候 在执行第二个mapreduce预处理过程中会保证相同的 group by key 分布到同一个reduce上面（如不设置会随机分配），会提高效率

9) hive的union all（hive不支持union语法）
hive的union all 的优化只局限于非嵌套循环，如select * from a unin all select * from b union all select * from c join d on c.id=d.id
上述语句会在hadoop上产生4个job任务  我们利用union all的特性 对其进行 insert overwrite table e select * from c join d and c.id=d.id;
select * from a union select * from b union all select * from e 这样只会产生2个job  而如果只是单纯的加括号进行嵌套的话还是会产生4个job任务

10) 如果多个union all进行操作时且其数据量巨大，应该拆分成多个insert into 效率会提高大概50%

11) insert overwrite directory 'hdfs目录' select * from table; 当次过程产生多个文件的情况一般有2种情况 第一种是文件过大，超过其限制的文件快，集群会自动切割其分成文件块
第二种是由于其reduce过多导致，每个reduce产生一个文件，会生成许许多多的小文件 甚至每个文件的大小都不到1KB  这样会使统计数据变得繁琐
使用hive参数hive.merge.mapredfiles参数更改  是否合并reduce输出文件 默认是false

12) left semi join 
select a.* from a where a.id,a.type in (select id,type from b);这种方法在hive中是不可取的  需要通过left simi join来进行操作
select a.* from a left simi join b on a.id=b.id and a.type=b.type;
切记select和where中不能出现b表的字段