hive执行语句优化

一、类SQL语句优化

1、跟SQL基本一致的优化原则

1.1 尽量原子化操作

尽量避免一个SQL包含复杂逻辑，可以使用中间表来完成复杂的逻辑。

1.2 尽量尽早地过滤数据

尽量先where筛选后再join，减少每个阶段的数据量，对于分区表要加分区条件，只选择需要使用到的字段。

1.3 表链接时尽量使用相同的连接键

当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

2、跟SQL不一致的优化原则

2.1 小表联大表

表连接时将大表放后头（即小表联大表，跟传统sql相反）。Hive假定查询中最后的一个表是大表。它会将其它表缓存起来，然后扫描最后那个表。

2.2 用insert into替换union all

如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，实际测试过程中，执行时间能大幅提升。

2.3 尽量将order by替换为sort by

order by : 对查询结果进行全局排序，消耗时间长。需要 set hive.mapred.mode=nostrict
sort by : 局部排序，并非全局有序，提高效率
order by 排序，只存在一个reduce，这样效率比较低。可以用sort by操作,通常结合distribute by使用做reduce分区键

2.4 limit 语句快速出结果优化

一般情况下，Limit语句还是需要执行整个查询语句，然后再返回部分结果。
有一个配置属性可以开启，避免这种情况—对数据源进行抽样
hive.limit.optimize.enable=true — 开启对数据源进行采样的功能
hive.limit.row.max.size — 设置最小的采样容量
hive.limit.optimize.limit.file — 设置最大的采样样本数
缺点：有可能部分数据永远不会被处理到

二、数据倾斜优化

1、现象描述

数据量大不是问题，数据倾斜是个问题。
在hive里经常遇见数据倾斜问题。表现为：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。
因为单一reduce的处理的数据量和其他reduce差异过大。通常可能达到3倍甚至更多。

2、原因

1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜

关键词	情形	后果
join	其中一个表较小，但是key集中	分发到某一个或几个Reduce上的数据远高于平均值
join	大表与大表，但是分桶的判断字段0值或空值过多	这些空值都由一个reduce处理，灰常慢
group by	group by 维度过小，某值的数量过多	处理某值的reduce灰常耗时
count distinct	某特殊值过多	处理此特殊值reduce耗时

hive性能优化时，把HiveQL当做M/R程序来读，即从M/R的运行角度来考虑优化性能，从更底层思考如何优化运算性能，而不仅仅局限于逻辑代码的替换层面。

RAC（Real Application Cluster）真正应用集群就像一辆机动灵活的小货车，响应快；Hadoop就像吞吐量巨大的轮船，启动开销大，如果每次只做小数量的输入输出，利用率将会很低。所以用好Hadoop的首要任务是增大每次任务所搭载的数据量。

Hadoop的核心能力是parition和sort，因而这也是优化的根本。数据的大规模并不是负载重点，造成运行压力过大是因为运行数据的倾斜。

3、解决办法

核心是减少job数。
设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。

3.1 常用的处理办法（配置角度优化）

其实，hive已经做了很多优化的默认设置，如列裁剪、分区裁剪、MAP JOIN操作、合并小文件等，可参考参考资料3。除此之外，可重点调整如下参数：
第一通过hive.groupby.skewindata=true控制生成两个MR Job,第一个MR Job Map的输出结果随机分配到reduce做次预汇总,每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；减少某些key值条数过多某些key条数过小造成的数据倾斜问题。【主要针对有GROUP BY操作的】

第二通过hive.map.aggr = true(默认为true)在Map端做combiner,假如map各条数据基本上不一样, 聚合没什么意义，做combiner反而画蛇添足,hive里也考虑的比较周到通过参数hive.groupby.mapaggr.checkinterval = 100000 (默认)hive.map.aggr.hash.min.reduction=0.5(默认),预先取100000条数据聚合,如果聚合后的条数/100000>0.5，则不再聚合

3.2 SQL语句调节

无效ID在关联时容易数据倾斜：
把空值的key变成一个字符串加上随机数（或者先过滤），把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。

count distinct大量相同特殊值时：
count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union。

尽量避免count(distinct)：
对sum，count来说，不存在数据倾斜问题，尽量采用sum() group by的方式来替换count(distinct)完成计算。【大部分情况，GROUP BY替代COUNT(DISTINCT)可以达到优化效果】

特殊情况特殊处理：
在业务逻辑优化效果的不大情况下，有些时候是可以将倾斜的数据单独拿出来处理。最后union回去。

三、调整mapper和reducer的阶段优化

1、Map阶段优化

map个数的主要的决定因素有： input的文件总个数，input的文件大小，集群设置的文件块大小（默认128M，不可自定义）。
由三个配置决定，

mapred.min.split.size.per.node  一个节点上split的至少的大小
mapred.min.split.size.per.rack  一个交换机下split至少的大小
mapred.max.split.size  一个split最大的大小

例如，若有大量小文件（小于128M），会产生多个map，为减少map数，处理方法是：

set mapred.max.split.size=100000000; 
set mapred.min.split.size.per.node=100000000; 
set mapred.min.split.size.per.rack=100000000;  
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- 执行前进行小文件合并

前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的）进行合并
同理，增加map数也做相应设置

2、Reduce阶段优化

reduce数量由以下三个参数决定：

mapred.reduce.tasks  强制指定reduce的任务数量
hive.exec.reducers.bytes.per.reducer  每个reduce任务处理的数据量，默认为1000^3=1G
hive.exec.reducers.max  每个任务最大的reduce数，默认为999  

一般根据输入文件的总大小,用它的estimation函数来自动计算reduce的个数：计算reducer数的公式很简单N=min( hive.exec.reducers.max ，总输入数据量/ hive.exec.reducers.bytes.per.reducer )

---

四、学习参考资料

1、https://www.cnblogs.com/sandbank/p/6408762.html
2、https://www.cnblogs.com/xd502djj/p/3799432.html
3、https://www.cnblogs.com/smartloli/p/4356660.html