关于hive:Hive-SQL优化思路

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 表不会进行谓词下推！

2. 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 版