CC00027.hadoop——|Hadoop&Hive.V27|——|Hive.v27|Hive优化策略|实战.v03|

一、SQL优化

### --- SQL优化

~~~     列裁剪和分区裁剪
~~~     列裁剪是在查询时只读取需要的列；分区裁剪就是只读取需要的分区。
~~~     简单的说：select 中不要有多余的列，坚决避免 select * from tab;

### --- 查询分区表，不读多余的数据；

select uid, event_type, record_data
from calendar_record_log
where pt_date >= 20190201 and pt_date <= 20190224
and status = 0;

### --- sort by 代替 order by

~~~     # HiveQL中的order by与其他关系数据库SQL中的功能一样，是将结果按某字段全局排序，
~~~     这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完。
~~~     如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。
~~~     # 为了控制map端数据分配到reducer的key，往往还要配合distribute by 一同使用。
~~~     如果不加 distribute by 的话，map端数据就会随机分配到reducer。

### --- group by 代替 count(distinct)

~~~     当要统计某一列的去重数时，如果数据量很大，count(distinct) 会非常慢。
~~~     原因与order by类似，count(distinct)逻辑只会有很少的reducer来处理。
~~~     此时可以用group by 来改写：
~~~     这样写会启动两个MR job(单纯distinct只会启动一个)，
~~~     所以要确保数据量大到启动job的overhead远小于计算耗时，才考虑这种方法。
~~~     当数据集很小或者key的倾斜比较明显时，group by还可能会比distinct慢。

~~~     # 原始SQL
select count(distinct uid)
from tab;

~~~     # 优化后的SQL
select count(1)
from (select uid
from tab
group by uid) tmp;

二、group by 配置调整

### --- map端预聚合
~~~     group by时，如果先起一个combiner在map端做部分预聚合，可以有效减少shuffle数据量。
~~~     -- 默认为true

set hive.map.aggr = true

~~~     # Map端进行聚合操作的条目数
~~~     通过hive.groupby.mapaggr.checkinterval 参数也可以设置map端预聚合的行数阈值，
~~~     超过该值就会分拆job，默认值10W。

set hive.groupby.mapaggr.checkinterval 1 = 100000

### --- 倾斜均衡配置项

~~~     group by时如果某些key对应的数据量过大，就会发生数据倾斜。
~~~     Hive自带了一个均衡数据倾斜的配置项hive.groupby.skewindata ，默认值false。
~~~     其实现方法是在group by时启动两个MR job。第一个job会将map端数据随机输入reducer，
~~~     每个reducer做部分聚合，相同的key就会分布在不同的reducer中。
~~~     第二个job再将前面预处理过的数据按key聚合并输出结果，这样就起到了均衡的效果。
~~~     但是，配置项毕竟是死的，单纯靠它有时不能根本上解决问题，
~~~     建议了解数据倾斜的细节，并优化查询语句。

三、join基础优化：Hive join的三种方式

### --- common join

~~~     普通连接，在SQL中不特殊指定连接方式使用的都是这种普通连接。
~~~     缺点：性能较差(要将数据分区，有shuffle)
~~~     优点：操作简单，普适性强

### --- map join
~~~     map端连接，与普通连接的区别是这个连接中不会有reduce阶段存在，连接在map端完成

~~~     # 适用场景：
~~~     大表与小表连接，小表数据量应该能够完全加载到内存，否则不适用
~~~     # 优点：
~~~     在大小表连接时性能提升明显，

~~~     # 备注：
~~~     Hive 0.6 的时候默认认为写在select 后面的是大表，前面的是小表， 
~~~     或者使用/*+mapjoin(map_table) / 提示进行设定。select a., b.* from a join b on a.id =b.id【要求小表在前，大表之后】
~~~     hive 0.7 的时候这个计算是自动化的，它首先会自动判断哪个是小表，哪个是大表，
~~~     这个参数由(hive.auto.convert.join=true)来控制，
~~~     然后控制小表的大小由(hive.smalltable.filesize=25000000)参数控制(默认是25M)，
~~~     当小表超过这个大小，hive 会默认转化成common join。
~~~     Hive 0.8.1，hive.smalltable.filesize => hive.mapjoin.smalltable.filesize
~~~     # 缺点：
~~~     使用范围较小，只针对大小表且小表能完全加载到内存中的情况。

### --- bucket map join

~~~     # 分桶连接：Hive 建表的时候支持hash 分区通过指定clustered by (col_name,xxx )into number_buckets buckets 关键字.
~~~     当连接的两个表的join key 就是bucketcolumn 的时候，
~~~     就可以通过设置hive.optimize.bucketmapjoin= true 来执行优化。

~~~     # 原理：通过两个表分桶在执行连接时会将小表的每个分桶映射成hash表，
~~~     每个task节点都需要这个小表的所有hash表，但是在执行时只需要加载该task所持有大表分桶对应的小表部分的hash表就可以，
~~~     所以对内存的要求是能够加载小表中最大的hash块即可。

~~~     # 注意点：小表与大表的分桶数量需要是倍数关系，这个是因为分桶策略决定的，
~~~     分桶时会根据分桶字段对桶数取余后决定哪个桶的，所以要保证成倍数关系。
~~~     # 优点：比map join对内存的要求降低，能在逐行对比时减少数据计算量(不用比对小表全量)
~~~     # 缺点：只适用于分桶表

### --- 利用map join特性

~~~     map join特别适合大小表join的情况。Hive会将build table和probe table在map端直接完成join过程，
~~~     消灭了reduce，效率很高。
~~~     map join的配置项是hive.auto.convert.join ，默认值true。
~~~     当build table大小小于hive.mapjoin.smalltable.filesize 会启用map join，
~~~     默认值25000000(约25MB)。还有hive.mapjoin.cache.numrows ，
~~~     表示缓存build table的多少行数据到内存，默认值25000。

select a.event_type, b.upload_time
from calendar_event_code a
inner join (
select event_type, upload_time from calendar_record_log
where pt_date = 20190225
) b on a.event_type = b.event_type;

### --- 分桶表map join

~~~     map join对分桶表还有特别的优化。
~~~     由于分桶表是基于一列进行hash存储的，因此非常适合抽样(按桶或按块抽样)。
~~~     它对应的配置项是hive.optimize.bucketmapjoin 。

### --- 倾斜均衡配置项

~~~     这个配置与 group by 的倾斜均衡配置项异曲同工，通过hive.optimize.skewjoin来配置，默认false。
~~~     如果开启了，在join过程中Hive会将计数超过阈值hive.skewjoin.key (默认100000)
~~~     的倾斜key对应的行临时写进文件中，然后再启动另一个job做map join生成结果。
~~~     通过hive.skewjoin.mapjoin.map.tasks 参数还可以控制第二个job的mapper数量，默认10000。

### --- 处理空值或无意义值

~~~     日志类数据中往往会有一些项没有记录到，其值为null，或者空字符串、-1等。
~~~     如果缺失的项很多，在做join时这些空值就会非常集中，拖累进度【备注：这个字段是连接字段】。
~~~     若不需要空值数据，就提前写 where 语句过滤掉。需要保留的话，
~~~     将空值key用随机方式打散，例如将用户ID为null的记录随机改为负值：

select a.uid, a.event_type, b.nickname, b.age
from (
select
(case when uid is null then cast(rand()*-10240 as int) else
uid end) as uid,
event_type from calendar_record_log
where pt_date >= 20190201
) a left outer join (
select uid,nickname,age from user_info where status = 4
) b on a.uid = b.uid;

### --- 单独处理倾斜key

~~~     如果倾斜的 key 有实际的意义，一般来讲倾斜的key都很少，此时可以将它们单独抽取出来，
~~~     对应的行单独存入临时表中，然后打上一个较小的随机数前缀(比如0~9)，最后再进行聚合。
~~~     不要一个Select语句中，写太多的Join。一定要了解业务，了解数据。
~~~     (A0-A9)分成多条语句，分步执行；(A0-A4; A5-A9)；先执行大表与小表的关联；

### --- 调整 Map 数

~~~     通常情况下，作业会通过输入数据的目录产生一个或者多个map任务。主要因素包括：
~~~     输入文件总数
~~~     输入文件大小
~~~     HDFS文件块大小
~~~     map越多越好吗。当然不是，合适的才是最好的。
~~~     如果一个任务有很多小文件(<< 128M)，每个小文件也会被当做一个数据块，用一个 Map Task 来完成。
~~~     一个 Map Task 启动和初始化时间 >> 处理时间，会造成资源浪费，而且系统中同时可用的map数是有限的。

### --- 对于小文件采用的策略是合并。

~~~     每个map处理接近128M的文件块，会有其他问题吗。也不一定。
~~~     有一个125M的文件，一般情况下会用一个Map Task完成。
~~~     假设这个文件字段很少，但记录数却非常多。
~~~     如果Map处理的逻辑比较复杂，用一个map任务去做，性能也不好。

### --- 对于复杂文件采用的策略是增加 Map 数。
~~~     调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。
~~~     建议用set的方式，针对SQL语句进行调整。

computeSliteSize(max(minSize, min(maxSize, blocksize))) =
blocksize
minSize : mapred.min.split.size (默认值1)
maxSize : mapred.max.split.size (默认值256M)

### --- 调整 Reduce 数
~~~     reducer数量的确定方法比mapper简单得多。使用参数mapred.reduce.tasks 可以直接设定reducer数量。如果未设置该参数，Hive会进行自行推测，逻辑如下：
~~~     参数hive.exec.reducers.bytes.per.reducer 用来设定每个reducer能够处理的最大数据量，默认值256M
~~~     参数hive.exec.reducers.max 用来设定每个job的最大reducer数量，默认值999(1.2版本之前)或1009(1.2版本之后)
~~~     得出reducer数： reducer_num = MIN(total_input_size /reducers.bytes.per.reducer, reducers.max)

~~~     # 即： min(输入总数据量 / 256M, 1009)
~~~     reducer数量与输出文件的数量相关。如果reducer数太多，会产生大量小文件，对HDFS造成压力。如果reducer数太少，每个reducer要处理很多数据，容易拖慢运行时间或者造成OOM。

三、优化小结

### --- 优化小结
~~~     深入理解 Hadoop 的核心能力，对Hive优化很有帮助。Hadoop/Hive 处理数据过程，

~~~     # 有几个显著特征：
~~~     不怕数据多，就怕数据倾斜
~~~     对 job 数比较多的作业运行效率相对比较低，比如即使有几百行的表，多次关联多次汇总，
~~~     产生十几个jobs，执行也需要较长的时间。MapReduce 作业初始化的时间是比较长的
~~~     对sum、count等聚合操作而言，不存在数据倾斜问题
~~~     count(distinct) 效率较低，数据量大容易出问题

### --- 从大的方面来说，优化可以从几个方面着手：

~~~     # 好的模型设计，事半功倍
~~~     解决数据倾斜问题。仅仅依靠参数解决数据倾斜，是通用的优化手段，收获有限。
~~~     开发人员应该熟悉业务，了解数据规律，通过业务逻辑解决数据倾斜往往更可靠
~~~     # 减少 job 数
~~~     设置合理的map、reduce task数
~~~     对小文件进行合并，是行之有效的提高Hive效率的方法
~~~     优化把握整体，单一作业的优化不如整体最优