Hive优化总结

一、SQL本身的优化

1、只select需要的列，避免select *
2、where条件写在子查询中，先过滤再关联
3、关联条件写在on中，而不是where中
4、数据量大时，用group by代替count distinct
5、数据量小时，用in代替join
6、避免笛卡尔积
7、join时大表放后面，使用相同的连接键
7、严格格式

Hive.mapred.mode，分 nonstrict，strict，默认是nonstrict，
如果设置为strict，对三种情况限制：
（1）分区表必须加分区。
（2）order by 必须使用limit
（3）存在笛卡尔积

二、数据倾斜的处理

数据倾斜的现象：
1、任务进度长时间维持在99%（或100%）;
2、查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。
3、本地读写数据量很大。
导致数据倾斜的原因：
1、空值问题
2、数据类型不一致
3、业务数据本身的问题

1、小表关联大表，开启mapjoin

（1）设置参数
set hive.auto.convert.join=true;
hive.mapjoin.smalltable.filesize=25000000 即25M

（2）手动指定
select /+ mapjoin(A)/ f.a,f.b from A t join B f on ( f.a=t.a and f.ftime=20110802)

2、加盐打散

（1）空值0值 或 关联不上的，用随机数
from a join b
on if(a.key=’’, rand()-1, a.key)=b.key
–rand() 0-1之间的小数

（2）都是有用的key，则加随机数后缀
group by concat(key, cast(round(rand()*10) as int))
缺点是分成10份是提前写好的，数据变更大时，还是会跑得慢。

3、开启combiner，即map端聚合

set hive.map.aggr=true；

4、开启负载均衡，会生成两个MRJob

set hive.groupby.skewindata=true;

第一个 MR Job 中，Map 的输出结果集合会随机分布到Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group ByKey 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce中），最后完成最终的聚合操作。

5、group by 代替count distinct

6、大key单独处理，再union回去

7、增大并行度

三、hive参数的调整

1、多个job无依赖(如union all)，可设置并行执行

//开启任务并行执行
set hive.exec.parallel=true;
//同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;

2、设置map和reduce个数

set mapred.max.split.size=100000000; 每个map的最大输入大小
set mapred.min.split.size.per.node=100000000;一个节点上split的至少的大小，决定了多个data node上的文件是否需要合并
set mapred.min.split.size.per.rack=100000000;一个交换机下split的至少的大小，决定了多个交换机上的文件是否需要合并

3、设置mapjoin

4、设置资源相关参数

【hive on mr】
set hive.execution.engine=mr;
set mapreduce.map.java.opts=-Xmx2048m;
set mapreduce.map.memory.mb=4096;
set mapreduce.reduce.java.opts=-Xmx2048m;
set mapreduce.reduce.memory.mb=4096;
【hive on spark】
set hive.execution.engine=spark;
set spark.executor.cores=1;
set spark.executor.memory=4g;
set spark.yarn.executor.memoryOverhead=2048;

四、小文件的处理

【产生原因】
1、动态分区
2、数据源是小文件
3、reduce个数多
【影响】
1、小文件会开很多map，初始化、启动、执行会浪费资源影响性能。
2、在HDFS中，每个小文件对象约占150byte，如果小文件过多会占用大量内存。
【解决方法】
1、Hadoop achieve命令把小文件归档
2、减少reduce个数
3、参数调节
（1）设置map输入合并小文件
（2）设置map和reduce输出合并小文件
4、少用动态分区，使用distribute by分区

五、数据压缩和存储格式

【数据的压缩与存储格式】
1、压缩 gzip bzip2 snappy
2、存储格式
【行式存储和列式存储】
TextFile：行式存储，Gzip压缩后不支持split
RCFile：数据按行分块，每块按列存储。头信息：行组记录数、每列字节数、
ORC：数据按行分块，每块按列存储，是rcfile的改良版本。头信息：每一列最大小值、该行的偏移量和长度
Parquet：列式存储（压缩比高）。头信息：数据量、偏移量。

六、其他

1、查看sql执行计划 explain sql
2、分区表、分桶表