HIVE SQL 优化

        Hive的优化主要分为：配置优化、SQL语句优化、任务优化等方案。其中在开发过程中主要涉及到的可能是SQL优化这块。Hive作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job或I/O过多、MapReduce分配不合理等等。对Hive的调优既包含对HiveQL语句本身的优化，也包含Hive配置项和MR方面的调整。

优化的核心思想是：

• 减少数据量（例如分区、列剪裁）
• 避免数据倾斜（例如加参数、Key打散）
• 避免全表扫描（例如on添加分区等）
• 减少job数（例如相同的on条件的join放在一起作为一个任务）

注：以下代码案例中dwd_real_order_info_all 为全量订单表，dwd_order_pay_lock_check为订单支付异常表

1、使用分区剪裁、列剪裁

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

错误写法：

select a.*  
from dwd_real_order_info_all a  
left join dwd_order_pay_lock_check b 
on  a.order_id = b.order_id  
where a.dt ='20221010'  
and b.dt ='20221010'

运行结果：7.77s

上面这个SQL主要犯了两个错误：

• 副表(上方b表)的where条件写在join后面，会导致先全表关联在过滤分区。

注：虽然a表的where条件也写在join后面，但是a表会进行谓词下推，也就是先执行where条件，再执行join，但是b表不会进行谓词下推！

• on的条件没有过滤null值的情况，如果两个数据表存在大批量null值的情况，会造成数据倾斜。

正确写法：

select a.*  
from dwd_real_order_info_all a  
left join dwd_order_pay_lock_check b 
on (b.order_id is not null and a.order_id = b.order_id and b.dt='20221010') 
where a.dt ='20221010'  

运行结果：3.19s

或者更改为子查询的方式，其核心思想是将SQL语句中的where谓词逻辑都尽可能提前执行，减少下游处理的数据量。

2、group by代替count distinct

        因为count distinct操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般count distinct使用先group by再count的方式替换，虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。

错误写法：

select count(distinct order_id)   
from dwd_real_order_info_all 
where dt between '20221001' and '20221010'

运行结果：5.51s

正确写法：

select count(a.order_id)  
from   
(select order_id 
 from dwd_real_order_info_all 
 where order_id is not null and dt between '20221001' and '20221010'
 group by order_id
) a

运行结果：2.46s

3、使用with as

        拖慢Hive查询效率除了join产生的shuffle以外，还有一个就是子查询，在SQL语句里面尽量减少子查询。with as是将语句中用到的子查询事先提取出来（类似临时表），使整个查询当中的所有模块都可以调用该查询结果。使用with as可以避免Hive对不同部分的相同子查询进行重复计算。

错误写法：

select a.*  
from dwd_real_order_info_all a  
left join dwd_order_pay_lock_check b 
on  a.order_id = b.order_id  
where a.dt ='20221010'  
and b.dt ='20221010'

运行结果：7.77s

正确写法：

with test1 as 
(
select order_id  
from dwd_order_pay_lock_check b 
where dt ='20221010' and order_id is not null  
)  
select a.*  
from dwd_real_order_info_all a  
left join test1 on a.order_id = test1.order_id  
where a.dt ='20221010' and a.order_id is not null

运行结果：5.51s

4、大小表的join

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

错误写法：

select a.*  
from dwd_real_order_info_all a  
left join dwd_order_pay_lock_check b 
on  a.order_id = b.order_id  
where a.dt ='20221010'  
and b.dt ='20221010'

运行结果：7.77s

正确写法：

select a.*  
from dwd_order_pay_lock_check b  
left join dwd_real_order_info_all a  
on  b.order_id = a.order_id  
where a.dt ='20221010'  
and b.dt ='20221010' 

运行结果：5.27s

注：修改过的代码仍然可以作进一步优化，只是明确大小表切换关联关系带来的运行效率上的增加，读者可使用以上几种优化方法，进一步尝试该代码的优化空间。

5、调整并发参数

        并行度是衡量并行计算并行程度的一个指标，从执行计划上来看，例如ID为M1的任务，使用1000个Instance来执行，我们就说M1的并行度是1000。需要注意的是：调整并行度不一定是越多越好，Instance数量过多会从如下两个方面影响执行速度：

• Instance越多，等待资源需要更长的时间，排队次数也更多。
• 每个Instance初始化需要时间，并行度越高，初始化占用的总时间就越多，有效的执行时间占比就越低。

并行度优化场景如下：

• 强制一个Instance执行某些操作强制一个Instance执行任务，例如：
  • 做聚合的时候，没有进行group by或者group by一个常量。
  • 窗口函数的over语句里指定partition by一个常量。
  • SQL中指定distribute by、cluster by一个常量。

  解决方案：针对此情形，建议您检查这些操作是否必要，能否去掉，尽量取消类似操作，避免强制一个Instance执行任务。

• Instance过多以下一些情形会导致强制使用很多Instance：
  • 需要读取很多小分区的数据：例如一个数据查询SQL语句读取10000个分区，那么系统会强制使用10000个Instance。

    解决方案：您需要设计SQL，减少分区的数量，可以从进行分区裁剪、筛除不需要读的分区、将大作业拆分成小作业方面进行考虑。

  • 数据压缩比很高，解压后256 MB变成了好几百GB的数据，导致读入256MB数据，处理解压后会产生非常多的Instance。解决方案：使用如下命令调小单个并发处理的数据大小。

    set odps.stage.mapper.split.size=<256>;
    set odps.stage.reducer.num =<并发数>;

  • 每次读取256 MB数据太少，导致Instance的执行时间太短，而由于输入数据很大，反而导致了并行度过大，使Instance大多数时间在排队等资源，需要调大单个Instance读取的数据量。解决方案：使用如下命令调大单个并发处理的数据大小。

    set odps.stage.mapper.split.size=<256>;
    set odps.stage.reducer.num =<并发数>;

6、数据倾斜

        数据倾斜的原理都知道，就是某一个或几个key占据了整个数据的90%，这样整个任务的效率都会被这个key的处理拖慢，同时也可能会因为相同的key会聚合到一起造成内存溢出。

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

        从另外角度看数据倾斜，其本质还是在单台节点在执行那一部分数据reduce任务的时候，由于数据量大，跑不动，造成任务卡住。若是这台节点机器内存够大，CPU、网络等资源充足，跑 80G 左右的数据量和跑10M 数据量所耗时间不是很大差距，那么也就不存在问题，倾斜就倾斜吧，反正机器跑的动。所以机器配置和数据量存在一个合理的比例，一旦数据量远超机器的极限，那么不管每个key的数据如何分布，总会有一个key的数据量超出机器的能力，造成 reduce 缓慢甚至卡顿。

        根据使用经验总结，引起数据倾斜的主要原因有如下几类：

• Join：其中一个表较小，但是key集中，分发到某一个或几个Reduce上的数据远高于平均值；大表与大表，但是分桶的判断字段0值或空值过多，这些空值都由一个reduce处理，非常慢

• Group By ：group by 维度过小，某值的数量过多,处理某值的reduce非常耗时

• Count（Distinct **）：某特殊值过多，处理此特殊值的reduce耗时

• ROW_NUMBER（TopN）

• 动态分区

        其中出现的频率排序为JOIN > GroupBy > Count(Distinct) > ROW_NUMBER > 动态分区

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

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

        好多小伙伴使用这两个参数的次数真的很多，但是他们不了解这两个参数是怎么解决数据倾斜的，从哪个角度着手的。

        hive.map.aggr=true：在map中会做部分聚集操作，效率更高但需要更多的内存。

        hive.groupby.skewindata=true：数据倾斜时负载均衡，当选项设定为true，生成的查询计划会有两个MRJob。第一个MRJob 中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupBy Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MRJob再根据预处理的数据结果按照GroupBy Key分布到Reduce中（这个过程可以保证相同的GroupBy Key被分布到同一个Reduce中），最后完成最终的聚合操作。

        由上面可以看出起到至关重要的作用的其实是第二个参数的设置，它使计算变成了两个mapreduce，先在第一个中在 shuffle 过程 partition 时随机给 key 打标记，使每个key 随机均匀分布到各个 reduce 上计算，但是这样只能完成部分计算，因为相同key没有分配到相同reduce上，所以需要第二次的mapreduce,这次就回归正常 shuffle,但是数据分布不均匀的问题在第一次mapreduce已经有了很大的改善，因此基本解决数据倾斜。

2、在 key 上面做文章，在 map 阶段将造成倾斜的key 先分成多组，例如 aaa 这个 key,map 时随机在 aaa 后面加上 1,2,3,4 这四个数字之一，把 key 先分成四组，先进行一次运算，之后再恢复 key 进行最终运算。

3、能先进行 group 操作的时候先进行 group 操作，把 key 先进行一次 reduce,之后再进行 count 或者 distinct count 操作。

4、join 操作中，使用 map join 在 map 端就先进行 join ，免得到reduce 时卡住。另外如果在关连中涉及关连的字段存在大量的null值，可采取类似于2的操作，把空值的key变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。因为空值不参与关联，即使分到不同的reduce上，也不影响最终的结果。

代码示例：

解决1:

Select *
From log a
Join  bmw_users b
On a.user_id is not null
And a.user_id = b.user_id
Union all
Select *
from log a
where a.user_id is null.

  解决2：

Select *
from log a
left outer join bmw_users b
on case when a.user_id is null then concat(‘dp_hive’,rand() ) else a.user_id end 
= b.user_id 

        以上4中方式，都是根据数据倾斜形成的原因进行的一些变化。要么将 reduce 端的隐患在 map 端就解决，要么就是对 key 的操作，以减缓reduce 的压力。总之了解了原因再去寻找解决之道就相对思路多了些。

        当然这些优化都是针对SQL本身的优化，还有一些是通过参数设置去调整的，这里面就不再详细描述了。但是优化的核心思想都差不多：

1. 减少数据量；
2. 避免数据倾斜；
3. 减少JOB数；
4. 核心点：根据业务逻辑对业务实现的整体进行优化；
5. 解决方案：采用presto、impala等专门的查询引擎，采用spark计算引擎替换MR/TEZ。