Hive调优汇总

一、参数调优

1. explain

一个HIVE查询被转换为由一个或多个stage组成的序列（有向无环图DAG）。这些stage可以是mapreduce stage，也可以是负责元数据存储的stage，也可以是负责文件系统的操作（比如移动和重命名）的stage。
EXPLAIN的输出包含以下三部分：

1. 查询的抽象语法树
2. 执行计划中的不同stage的依赖关系
3. 每个stage的细致描述

TableScan：表扫描操作，map端第一个操作肯定是加载表，所以就是表扫描操作，
常见的属性：
alias： 表名称
Statistics： 表统计信息，包含表中数据条数，数据大小等
Select Operator： 选取操作，
常见的属性 ：
expressions：需要的字段名称及字段类型
outputColumnNames：输出的列名称
Statistics：表统计信息，包含表中数据条数，数据大小等
Group By Operator：分组聚合操作，
常见的属性：
aggregations：显示聚合函数信息
mode：聚合模式，值有 hash：随机聚合，就是hash partition；
partial：局部聚合；final：最终聚合
keys：分组的字段，如果没有分组，则没有此字段
outputColumnNames：聚合之后输出列名
Statistics： 表统计信息，包含分组聚合之后的数据条数，数据大小等
Reduce Output Operator：输出到reduce操作，
常见属性：
sort order：值为空 不排序；值为 + 正序排序，值为 - 倒序排序；值为 ± 排序的列为两列，第一列为正序，第二列为倒序
Filter Operator：过滤操作，
常见的属性：
predicate：过滤条件，如sql语句中的where id>=1，则此处显示(id >= 1)
Map Join Operator：join 操作，
常见的属性：
condition map：join方式 ，如Inner Join 0 to 1 Left Outer Join0 to 2
keys: join 的条件字段
outputColumnNames： join 完成之后输出的字段
Statistics： join 完成之后生成的数据条数，大小等
File Output Operator：文件输出操作，
常见的属性
compressed：是否压缩
table：表的信息，包含输入输出文件格式化方式，序列化方式等
Fetch Operator 客户端获取数据操作，
常见的属性：
limit，值为 -1 表示不限制条数，其他值为限制的条数

2. 开启Fetch抓取

只要Hive不走MR的时候最快，直接返回文件内容

3. limit限制

一般情况下，使用了limit限制后仍然会全部执行，然后再返回部分结果。

hive.limit.optimize.enable=true --- 开启对数据源进行采样的功能
hive.limit.row.max.size=100000  ---设置最小的采样容量
hive.limit.optimize.limit.file --- 设置最大的采样样本数

4. 列裁剪和分区裁剪

列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。
如果 select * 或者不指定分区，全列扫描和全表扫描效率都很低

5. 开启严格模式

1、对分区表查询必须带分区条件，否则会查询失败
2、带order by的查询，必须使用limit限制查询数据条数，否则会查询失败。
3、不能进行笛卡尔积的查询
4、查询条件里面字段类型赋值时必须一致，比如日期分区dt字段类型为字符串，那么分区条件必须指定为dt=‘20160508’，而不能用dt=20160508

6.开启并行模式

通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。

7. 开启本地模式

hive 默认会将所有任务,提交到yarn上执行,由yarn负责整个job的调度与监控当数据集非常小时(只有一个block,只存在一个datanode节点上),提交job的时间 将远大于 job运行的时间,此时可以开启本地模式,将job在本地运行,不提交到yarn上避免了和yarn的交互,大大提高了数据计算的效率

8. Map Join

MapJoin 是将 Join 双方比较小的表直接分发到各个 Map 进程的内存中，在 Map 进程中进行 Join 操 作，这样就不用进行 Reduce 步骤，从而提高了速度。如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成Join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在Map端进行Join，避免Reducer处理。

9.开启Map端聚合&负载均衡

当选项设定为 true，生成的查询计划会有两个MR Job

第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作（虽然能解决数据倾斜，但是不能让运行速度的更快）

二、SQL调优

1. 谓词下推

就是在不影响结果的情况下，尽量将过滤条件提前执行。谓词下推后，过滤条件在map端执行，减少了map端的输出，降低了数据在集群上传输的量，节约了集群的资源，也提升了任务的性能。
官网：https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior

1.规则

保留表的谓词写在join中不能下推，需要用where；
空表的谓词写在join之后不能下推，需要用on；
在 join关联情况下，过滤条件无论在join中还是where中谓词下推都生效；
在full join关联情况下，过滤条件无论在join中还是where中谓词下推都不生效。

2.left semi join

left semi join 只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表；遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，在关联的时候会更加的高效。
LEFT SEMI JOIN 的限制是，右侧表只能在连接条件（ON 子句）中引用，而不能在 WHERE 或 SELECT 子句等中引用。left semi join只能查询左表字段，不能查询右表字段。left semi join只能展示两个表能够关联上的数据。

三、特殊场景调优

1. 数据倾斜

要区分是数据量太大还是数据倾斜

原因：单表数据倾斜 Join数据倾斜
解决办法：

1. 启Map端聚合&负载均衡
   1. 当选项设定为 true，生成的查询计划会有两个MR Job。
2. key值进行加盐（少用）

3. Map Join
   1. MapJoin 是将 Join 双方比较小的表直接分发到各个 Map 进程的内存中，在 Map 进程中进行 Join 操 作，这样就不用进行 Reduce 步骤，从而提高了速度。如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成Join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在Map端进行Join，避免Reducer处理。

1.小文件

1. 小文件的危害

1、文件存储在HDFS上，每个文件的元数据信息（位置、大小、分块信息）大约占150个字节，文件的元数据信息会分别存储在内存和磁盘中，会增加NameNode的存储量。
2、文件的数量决定了MapReduce中Mapper数量，小文件越多，Mapper的任务越多，每个Mapper都会对应启动一个JVM来运行，每个Mapper执行数据很少、个数多，导致占用资源多，甚至这些任务的初始化可能比执行的时间还要多，严重影响性能；

2. 使用HAR归档文件

运用于每日定时脚本，对于已经产生小文件的hive表可以使用har归档，而且hive提供了原生支持

set  hive.archive.enabled=  true ;
set  hive.archive.har.parentdir.settable=  true ;
set  har.partfile.size=256000000;
ALTER TABLE  ad_dev.wlb_tmp_smallfile_20210118 ARCHIVE PARTITION(pt='2022-03-01');

3. JVM重用

Hadoop的默认配置通常是使用派生JVM来执行map和Reduce任务的。这时JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含有成百上千task任务的情况。JVM重用可以使得JVM实例在同一个job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中进行配置。通常在10-20之间，具体多少需要根据具体业务场景测试得出。

4. map执行前合并小文件

在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能

5. Concatenate

如果是orc格式存储的表，还可以使用这个命令进行小文件的合并

6. distribute by

它的作用是控制在map端如何拆分数据给reduce端的，distribute by后面列是控制落地文件数，默认是采用hash算法;

insert overwrite table test partition(dt)
select * 
from test
distribute by cast(rand()*100 as int);