Hive的优化次要分为：配置优化、SQL语句优化、工作优化等计划。其中在开发过程中次要波及到的可能是SQL优化这块。

优化的核心思想是：

  • 缩小数据量（例如分区、列剪裁）
  • 防止数据歪斜（例如加参数、Key打散）
  • 防止全表扫描（例如on增加加上分区等）
  • 缩小job数（例如雷同的on条件的join放在一起作为一个工作）
本文首发在公众号【五分钟学大数据】

HQL语句优化

1. 应用分区剪裁、列剪裁

在分区剪裁中，当应用外关联时，如果将副表的过滤条件写在Where前面，那么就会先全表关联，之后再过滤。

select a.*  from a  left join b on  a.uid = b.uid  where a.ds='2020-08-10'  and b.ds='2020-08-10'

下面这个SQL次要犯了两个谬误：

  1. 副表(上方b表)的where条件写在join前面，会导致先全表关联在过滤分区。
注：尽管a表的where条件也写在join前面，然而a表会进行谓词下推，也就是先执行where条件，再执行join，然而b表不会进行谓词下推！
  1. on的条件没有过滤null值的状况，如果两个数据表存在大批量null值的状况，会造成数据歪斜。

正确写法：

select a.*  from a  left join b on (d.uid is not null and a.uid = b.uid and b.ds='2020-08-10') where a.ds='2020-08-10'

如果null值也是须要的，那么须要在条件上转换，或者独自拿进去

select a.*  from a  left join b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10')  where a.ds='2020-08-10'  union all  select a.* from a where a.uid is null

或者：

select a.*  from a  left join b on   case when a.uid is null then concat("test",RAND()) else a.uid end = b.uid and b.ds='2020-08-10'  where a.ds='2020-08-10'

或者（子查问）：

select a.*  from a  left join   (select uid from where ds = '2020-08-10' and uid is not null) b on a.uid = b.uid where a.uid is not null  and a.ds='2020-08-10'

2. 尽量不要用COUNT DISTINCT

因为COUNT DISTINCT操作须要用一个Reduce Task来实现，这一个Reduce须要解决的数据量太大，就会导致整个Job很难实现，个别COUNT DISTINCT应用先GROUP BY再COUNT的形式替换，尽管会多用一个Job来实现，但在数据量大的状况下，这个相对是值得的。

select count(distinct uid)  from test  where ds='2020-08-10' and uid is not null

转换为：

select count(a.uid)  from   (select uid  from test  where uid is not null and ds = '2020-08-10'  group by uid) a

3. 应用with as

拖慢Hive查问效率除了join产生的shuffle以外，还有一个就是子查问，在SQL语句外面尽量减少子查问。with as是将语句中用到的子查问当时提取进去（相似长期表），使整个查问当中的所有模块都能够调用该查问后果。应用with as能够防止Hive对不同局部的雷同子查问进行反复计算。

select a.*  from  a  left join b on  a.uid = b.uid  where a.ds='2020-08-10'  and b.ds='2020-08-10'

能够转化为：

with test1 as (select uid  from b  where ds = '2020-08-10' and uid is not null  )  select a.*  from a  left join test1 on a.uid = test1.uid  where a.ds='2020-08-10' and a.uid is not null

4. 大小表的join

写有Join操作的查问语句时有一条准则：应该将条目少的表/子查问放在Join操作符的右边。起因是在Join操作的Reduce阶段，位于Join操作符右边的表的内容会被加载进内存，将条目少的表放在右边，能够无效缩小产生OOM谬误的几率。但新版的hive曾经对小表JOIN大表和大表JOIN小表进行了优化。小表放在右边和左边曾经没有显著区别。不过在做join的过程中通过小表在前能够适当的缩小数据量，提高效率。

5. 数据歪斜

数据歪斜的原理都晓得，就是某一个或几个key占据了整个数据的90%，这样整个工作的效率都会被这个key的解决拖慢，同时也可能会因为雷同的key会聚合到一起造成内存溢出。

数据歪斜只会产生在shuffle过程中。这里给大家列举一些罕用的并且可能会触发shuffle操作的算子：distinct、 groupByKey、reduceByKey、aggregateByKey、join、cogroup、repartition等。呈现数据歪斜时，可能就是你的代码中应用了这些算子中的某一个所导致的。

hive的数据歪斜个别的解决计划：

常见的做法，通过参数调优：

set hive.map.aggr=true;  set hive.groupby.skewindata = ture;

当选项设定为true时，生成的查问打算有两个MapReduce工作。

在第一个MapReduce中，map的输入后果汇合会随机散布到reduce中，每个reduce做局部聚合操作，并输入后果。

这样解决的后果是，雷同的Group By Key有可能散发到不同的reduce中，从而达到负载平衡的目标；

第二个MapReduce工作再依据预处理的数据后果依照Group By Key散布到reduce中（这个过程能够保障雷同的Group By Key散布到同一个reduce中），最初实现最终的聚合操作。

然而这个解决计划对于咱们来说是个黑盒，无奈把控。

个别解决计划是将对应的key值打散即可。

例如：

select a.*  from a  left join b on  a.uid = b.uid  where a.ds='2020-08-10'  and b.ds='2020-08-10'

如果有90%的key都是null，这样不可避免的呈现数据歪斜。

select a.uid  from test1 as a  join(     select case when uid is null then cast(rand(1000000) as int)     else uid     from test2 where ds='2020-08-10') b   on a.uid = b.uid  where a.ds='2020-08-10'

当然这种只是实践上的解决计划。

失常的计划是null进行过滤，然而日常状况下不是这种非凡的key。

那么在日常需要的状况下如何解决这种数据歪斜的状况呢：

  1. sample采样，获取哪些集中的key；
  2. 将集中的key依照肯定规定增加随机数；
  3. 进行join，因为打散了，所以数据歪斜防止了；
  4. 在处理结果中对之前的增加的随机数进行切分，变成原始的数据。

当然这些优化都是针对SQL自身的优化，还有一些是通过参数设置去调整的，这外面就不再详细描述了。

然而优化的核心思想都差不多：

  1. 缩小数据量
  2. 防止数据歪斜
  3. 缩小JOB数
  4. 虚外围点：依据业务逻辑对业务实现的整体进行优化；
  5. 虚解决方案：采纳presto、impala等专门的查问引擎，采纳spark计算引擎替换MR/TEZ

举荐浏览：

  1. 最强最全面的数仓建设标准指南
  2. 美团数据平台及数仓建设实际，超十万字总结
  3. 上百本优质大数据书籍，附必读清单(大数据宝藏)
  4. 五万字 | 耗时一个月整顿出这份Hadoop吐血宝典
  5. 数仓建设保姆级教程PDF文档
  6. 最强最全面的大数据SQL经典面试题残缺PDF版