Hive SQL 及 hive参数 优化

Hive的优化

主要分为：配置优化、SQL语句优化、任务优化等方案。其中在开发过程中主要涉及到的可能是SQL优化这块。

优化的核心思想是：

减少数据量（例如分区、列剪裁）

避免数据倾斜（例如加参数、Key打散）

避免全表扫描（例如on添加加上分区等）

减少job数（例如相同的on条件的join放在一起作为一个任务）

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. 参数调优

1）Map Join

Hive中默认最稳定的Join算法是Common Join。其通过一个MapReduce Job完成一个Join操作。Map端负责读取Join操作所需表的数据，并按照关联字段进行分区，通过Shuffle，将其发送到Reduce端，相同key的数据在Reduce端完成最终的Join操作。
优化Join的最为常用的手段就是Map Join，其可通过两个只有Map阶段的Job完成一个join操作。第一个Job会读取小表数据，将其制作为Hash Table，并上传至Hadoop分布式缓存（本质上是上传至HDFS）。第二个Job会先从分布式缓存中读取小表数据，并缓存在Map Task的内存中，然后扫描大表数据，这样在map端即可完成关联操作。
注：由于Map Join需要缓存整个小表的数据，故只适用于大表Join小表的场景。
相关参数如下：

 --启动Map Join自动转换 
 set hive.auto.convert.join=true;
 --开启无条件转Map Join 
 set hive.auto.convert.join.noconditionaltask=true;
 --无条件转Map Join小表阈值，默认值10M，推荐设置为Map Task总内存的三分之一到二分之一 
 set hive.auto.convert.join.noconditionaltask.size=10000000;

2）SMB Map Join

上节提到，Map Join只适用于大表Join小表的场景。若想提高大表Join大表的计算效率，可使用Sort Merge Bucket Map Join。
需要注意的是SMB Map Join有如下要求：
（1）参与Join的表均为分桶表，且分桶字段为Join的关联字段。
（2）两表分桶数呈倍数关系。
（3）数据在分桶内是按关联字段有序的。
SMB Join的核心原理如下：只要保证了上述三点要求的前两点，就能保证参与Join的两张表的分桶之间具有明确的关联关系，因此就可以在两表的分桶间进行Join操作了。

 set hive.optimize.bucketmapjoin = true;  
set hive.optimize.bucketmapjoin.sortedmerge = true;  
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; 

具体案例看: https://blog.csdn.net/yjgithub/article/details/66972966

3）Reduce并行度

Reduce端的并行度，也就是Reduce个数，可由用户自己指定，也可由Hive自行根据该MR Job输入的文件大小进行估算。
Reduce端的并行度的相关参数如下：

 - -指定Reduce端并行度，默认值为-1，表示用户未指定 set mapreduce.job.reduces; 
 - -Reduce端并行度最大值 
  set hive.exec.reducers.max;  --  默认为999
 - -单个Reduce Task计算的数据量，用于估算Reduce并行度
  set hive.exec.reducers.bytes.per.reducer;  --默认（1000^3=1G）1G

Reduce端并行度的确定逻辑如下：
若指定参数mapreduce.job.reduces的值为一个非负整数，则Reduce并行度为指定值。否则，Hive自行估算Reduce并行度，估算逻辑如下：
假设Job输入的文件大小为totalInputBytes
参数hive.exec.reducers.bytes.per.reducer的值为bytesPerReducer。
参数hive.exec.reducers.max的值为maxReducers。
则Reduce端的并行度为：
min⁡(ceil(totalInputBytes/bytesPerReducer),maxReducers)
根据上述描述，可以看出，Hive自行估算Reduce并行度时，是以整个MR Job输入的文件大小作为依据的。因此，在某些情况下其估计的并行度很可能并不准确，此时就需要用户根据实际情况来指定Reduce并行度了。
需要说明的是：若使用Tez或者是Spark引擎，Hive可根据计算统计信息（Statistics）估算Reduce并行度，其估算的结果相对更加准确。

4）小文件合并

若Hive的Reduce并行度设置不合理，或者估算不合理，就可能导致计算结果出现大量的小文件。该问题可由小文件合并任务解决。其原理是根据计算任务输出文件的平均大小进行判断，若符合条件，则单独启动一个额外的任务进行合并。
相关参数为：

--开启合并map only任务输出的小文件
set hive.merge.mapfiles=true;

--开启合并map reduce任务输出的小文件
set hive.merge.mapredfiles=true;

--合并后的文件大小
set hive.merge.size.per.task=256000000;

--触发小文件合并任务的阈值，若某计算任务输出的文件平均大小低于该值，则触发合并
set hive.merge.smallfiles.avgsize=16000000;

5）谓词下推

谓词下推（predicate pushdown）是指，尽量将过滤操作前移，以减少后续计算步骤的数据量。开启谓词下推优化后，无需调整SQL语句，Hive就会自动将过滤操作尽可能的前移动。
相关参数为：

--是否启动谓词下推（predicate pushdown）优化
set hive.optimize.ppd = true;

6）CBO优化

CBO是指Cost based Optimizer，即基于计算成本的优化。
在Hive中，计算成本模型考虑到了：数据的行数、CPU、本地IO、HDFS IO、网络IO等方面。Hive会计算同一SQL语句的不同执行计划的计算成本，并选出成本最低的执行计划。目前CBO在Hive的MR引擎下主要用于Join的优化，例如多表Join的Join顺序。
相关参数为：

--是否启用cbo优化 
set hive.cbo.enable=true;

7）并行执行

Hive会将一个SQL语句转化成一个或者多个Stage，每个Stage对应一个MR Job。默认情况下，Hive同时只会执行一个Stage。但是某SQL语句可能会包含多个Stage，但这多个Stage可能并非完全互相依赖，也就是说有些Stage是可以并行执行的。此处提到的并行执行就是指这些Stage的并行执行。相关参数如下：

--启用并行执行优化，默认是关闭的
set hive.exec.parallel=true;       
    
--同一个sql允许最大并行度，默认为8
set hive.exec.parallel.thread.number=8; 

8）列式存储

采用ORC列式存储加快查询速度
相关参数为：

CREATE TABLE A_ORC ( 
customerID int, 
name string,
age int, 
address string 
) STORED AS ORC tblproperties (“orc.compress" = “SNAPPY”)

9）压缩

1）开启Hive中MR中间文件压缩：
hive> set hive.exec.compress.intermediate=true;

2）开启Hadoop的MapReduce任务中Map输出压缩功能：
hive> set mapreduce.map.output.compress=true;

3）设置Hadoop的MapReduce任务中Map阶段压缩算法（对应的编/解码器）：
hive> set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

1）开启Hive最终查询结果输出文件压缩功能：
hive> set hive.exec.compress.output=true;

2）开启Hadoop中的MR任务的最终输出文件压缩：
hive> set mapreduce.output.fileoutputformat.compress=true;

3）设置Hadoop中MR任务的最终输出文件压缩算法（对应的编/解码器）：
hive> set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

4）设置Hadoop中MR任务序列化文件的压缩类型，默认为RECORD即按照记录RECORD级别压缩（建议设置成BLOCK）：
hive> set mapreduce.output.fileoutputformat.compress.type=BLOCK;

5）执行查询语句，观察执行结果文件（后缀名为.snappy）：
hive> insert overwrite local directory '/tmp/hive/data/export/' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from emp;

6）加载本地的缩数据文件到临时表中：
hive> create table tmp like emp;
hive> load data local inpath '/tmp/hive/data/export/000000_0.snappy' overwrite into table tmp;

7）查询临时表结果
hive> select * from tmp;
8）至此就完成了Reduce阶段的压缩配置

10）分区和分桶

（1）创建分区表 防止后续全表扫描
（2）创建分桶表 对未知的复杂的数据进行提前采样

11）更换引擎

MR/Tez/Spark区别：
MR引擎：多Job串联，基于磁盘，落盘的地方比较多。虽然慢，但一定能跑出结果。一般处理，周、月、年指标。
Spark引擎：虽然在Shuffle过程中也落盘，但是并不是所有算子都需要Shuffle，尤其是多算子过程，中间过程不落盘 DAG有向无环图。 兼顾了可靠性和效率。一般处理天指标。
Tez引擎的优点
（1）使用DAG描述任务，可以减少MR中不必要的中间节点，从而减少磁盘IO和网络IO。
（2）可更好的利用集群资源，例如Container重用、根据集群资源计算初始任务的并行度等。
（3）可在任务运行时，根据具体数据量，动态的调整后续任务的并行度。

数据倾斜

数据倾斜现象：

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

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

hive的数据倾斜一般的处理方案：

1）分组聚合导致的数据倾斜

Hive中的分组聚合是由一个MapReduce Job完成的。Map端负责读取数据，并按照分组字段分区，通过Shuffle，将数据发往Reduce端，各组数据在Reduce端完成最终的聚合运算。若group by分组字段的值分布不均，就可能导致大量相同的key进入同一Reduce，从而导致数据倾斜。
（1）判断倾斜的值是否为null
若倾斜的值为null，可考虑最终结果是否需要这部分数据，若不需要，只要提前将null过滤掉，就能解决问题。若需要保留这部分数据，考虑以下思路。
（2）Map-Side聚合
开启Map-Side聚合后，数据会现在Map端完成部分聚合工作。这样一来即便原始数据是倾斜的，经过Map端的初步聚合后，发往Reduce的数据也就不再倾斜了。最佳状态下，Map端聚合能完全屏蔽数据倾斜问题。
一个分组聚合的查询语句，默认是通过一个MapReduce Job完成的。Map端负责读取数据，并按照分组字段分区，通过Shuffle，将数据发往Reduce端，各组数据在Reduce端完成最终的聚合运算。分组聚合的优化主要围绕着减少Shuffle数据量进行，具体做法是map-side聚合。所谓map-side聚合，就是在map端维护一个Hash Table，利用其完成部分的聚合，然后将部分聚合的结果，按照分组字段分区，发送至Reduce端，完成最终的聚合。
相关参数如下：

--启用map-side聚合，默认是true
set hive.map.aggr=true;

--用于检测源表数据是否适合进行map-side聚合。检测的方法是：先对若干条数据进行map-side聚合，若聚合后的条数和聚合前的条数比值小于该值，则认为该表适合进行map-side聚合；否则，认为该表数据不适合进行map-side聚合，后续数据便不再进行map-side聚合。
set hive.map.aggr.hash.min.reduction=0.5;

--用于检测源表是否适合map-side聚合的条数。
set hive.groupby.mapaggr.checkinterval=100000;

--map-side聚合所用的hash table，占用map task堆内存的最大比例，若超出该值，则会对hash table进行一次flush。
set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

（3）Skew-GroupBy优化
Skew-GroupBy是Hive提供的一个专门用来解决分组聚合导致的数据倾斜问题的方案。其原理是启动两个MR任务，第一个MR按照随机数分区，将数据分散发送到Reduce，并完成部分聚合，第二个MR按照分组字段分区，完成最终聚合。
相关参数如下：

--启用分组聚合数据倾斜优化
set hive.groupby.skewindata=true;

2）Join导致的数据倾斜

若Join操作使用的是Common Join算法，就会通过一个MapReduce Job完成计算。Map端负责读取Join操作所需表的数据，并按照关联字段进行分区，通过Shuffle，将其发送到Reduce端，相同key的数据在Reduce端完成最终的Join操作。
如果关联字段的值分布不均，就可能导致大量相同的key进入同一Reduce，从而导致数据倾斜问题。
由Join导致的数据倾斜问题，有如下解决思路：
（1）Map Join
使用Map Join算法，Join操作仅在Map端就能完成，没有Shuffle操作，没有Reduce阶段，自然不会产生Reduce端的数据倾斜。该方案适用于大表Join小表时发生数据倾斜的场景。
相关参数如下：

set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10000000;

（2）Skew Join
若参与Join的两表均为大表，Map Join就难以应对了。此时可考虑Skew Join，其核心原理是Skew Join的原理是，为倾斜的大key单独启动一个Map Join任务进行计算，其余key进行正常的Common Join。
相关参数如下：

--启用skew join优化
set hive.optimize.skewjoin=true;
--触发skew join的阈值，若某个key的行数超过该参数值，则触发
set hive.skewjoin.key=100000;

3）调整SQL语句
若参与Join的两表均为大表，其中一张表的数据是倾斜的，此时也可通过以下方式对SQL语句进行相应的调整。
假设原始SQL语句如下：A，B两表均为大表，且其中一张表的数据是倾斜的。

hive (default)>
select
    *
from A
join B
on A.id=B.id;

其Join过程如下：

图中1001为倾斜的大key，可以看到，其被发往了同一个Reduce进行处理。
调整之后的SQL语句执行计划如下图所示：

调整SQL语句如下：

hive (default)>
select
    *
from(
    select --打散操作
        concat(id,'_',cast(rand()*2 as int)) id,
        value
    from A
)ta
join(
    select --扩容操作
        concat(id,'_',1) id,
        value
    from B
    union all
    select
        concat(id,'_',2) id,
        value
    from B
)tb
on ta.id=tb.id;