hive优化总结

一 SQL优化

1、 指定分区查询。 编写SQL语句时，如果可以指定分区进行查询，可以有效避免大范围的遍历HDFS文件路径，减少数据扫描。
2、多重模式。 多重模式是指多个结构类似的SQL语句，可以合并成一个SQL，减少不必要的数据扫描。

insert overwrite table stu_1 partition(class='6') select id,name,age from stu_0 where class = '6';
insert overwrite table stu_2 partition(class='7') select id,name,age from stu_0 where class = '7';
insert overwrite table stu_3 partition(class='8') select id,name,age from stu_0 where class = '8';
  ……
  --可以写成下面形式
from stu_0
insert overwrite table stu_1 partition(class='6') select id,name,age where class = '6'
insert overwrite table stu_2 partition(class='7') select id,name,age where class = '7'
insert overwrite table stu_3 partition(class='8') select id,name,age where class = '8';

3、多个表进行join时，将小表放在前面，大表放在后面。 如果一个表被确定为小表，hive在解析时，会将这个表的数据加载到内存，在map端完成join操作。在高版本中，可以开启mapjoin（默认开启），即使没有将小表放在join的左边，在运行中，系统也会自动调整表的顺序，将小表加载到内存。

-- 小表阈值大小默认25M
set hive.mapjoin.smalltable.filesize = 25000000;
-- 开启mapjoin模式，默认为 true
set hive.auto.convert.join = true; 

4、 使用sort by 代替order by。如果使用order by会导致所有的map走一个reducer进行全局排序，数据量多时。严重影响性能。在不影响逻辑的前提下，使用sort by代替order by可以做到分区内有序。一般sort by还可以与distribute by（按照字段分区）联合使用。

select job_id, update_time, job_status, create_date
from kafka_log
where create_date >= 20220501
and create_date <= 20220606
distribute by job_id
sort by update_time desc,
job_status desc;

5、使用group by 代替 distinct。 统计数量时，使用distinct去重，会导致所有的数据进入到同一个reducer。

select count(distinct stu_id) from student;
--可以修改成下面形式
select count(1) from (select stu_id from student group by stu_id);

但是上面使用group by有一个子查询，会提交两个job进行运算，如果开启两个job的时间比distinct的计算时间还要长，要避免这种优化。

二 配置项优化

1、开启谓词下推。 谓词下推是指：将where谓词逻辑都尽可能提前执行，减少下游处理的数据量。比如将一些条件语句写到子查询中，尽可能的将子查询结果集减少。配置参数如下：

--默认为true
set hive.optimize.ppd = true;

2、 开启mapjoin优化。 MapJoin 是将 Join 双方符合条件的小表直接分发到各个 Map 进程的内存中，在 Map 进程中进行 Join 操作，这样就不用进行 Reduce 步骤，从而提高了速度。
（1）设置自动选择 MapJoin

set hive.auto.convert.join=true; #默认为true

（2）大表小表的阈值设置（默认 25M 以下认为是小表）：

set hive.mapjoin.smalltable.filesize=25000000;

3、 map端预聚合。 是GroupByOptimizer优化器中的功能，只有使用groupby才会生效。map端预聚合的原理是：在map端，对一部分数据预先处理聚合，可以有效减少reducer端的处理数据量，提高整个程序执行效率。配置参数如下：

set hive.map.aggr = true; #默认为true

也可以设置下面两个参数，两个参数的作用是map会自动对前10万调数据进行预聚合，如果预聚合结果/10万 > 0.5，系统则会认为预聚合效果不理想，没必要开启预聚合消耗资源，会自动关闭预聚合功能。

set hive.groupby.mapaggr.checkinterval = 100000;
set hive.map.aggr.hash.min.reduction = 0.5;

4、倾斜均衡配置项。 group by 时如果某些key数据量过大，就会发生数据倾斜。Hive自带了一个均衡数据倾斜的配置项hive.groupby.skewindata，默认值false。当开启后，程序会开启两个job，第一个job将数据打乱分散到不同reducer，第二个job执行时，就会避免同一个map中含有大量相同的key。如果已知不存在均衡问题，不建议打开。

set hive.groupby.skewindata = true;

5、 调整mapper数量。 mapper数量的调整并没有那么简单，hive提供的调整参数为mapred.map.tasks，但是并不一定生效，查询代码，发现mapper的计算逻辑如下：

# default_mapper_num 默认mapper个数
# total_input_size 输入数据量总大小
#  dfs.block.size hdfs块大小
default_mapper_num = total_input_size / dfs.block.size;
# mapred.min.split.size 允许分片的最小值
# mapred.max.split.size 允许分片的最大值
# split_size 分片大小
split_size = MAX(mapred.min.split.size, MIN(mapred.max.split.size, dfs.block.size));
# 分片个数
split_num = total_input_size / split_size;
# mapper_num 最终mapper个数
mapper_num = MIN(split_num, MAX(default_num, mapred.map.tasks));

6、 调整reducer数量。 可以通过参数mapred.reduce.tasks设置reducer的个数，在数据量过大，reducer较少时，可以手动设置reducer个数。

set mapred.reduce.tasks = 20

但是也要注意不要设置过大，过大容易造成生成的小文件过多。如果不设置此参数，系统会根据 hive.exec.reducers.bytes.per.reducer 默认值为256M，这个参数进行自动推测，但是最大reducer个数受hive.exec.reducers.max（默认1009）限制。

7、 合并小文件。 可以通过更改参数hive.input.format，默认值是org.apache.hadoop.hive.ql.io.HiveInputFormat为org.apache.hadoop.hive.ql.io.CombineHiveInputFormat起到合并小文件的功能。
如果使用该参数，还可分别设置mapred.min.split.size.per.node（每个节点最小分片大小）和mapred.min.split.size.per.rack（每个机架最小分片大小）。作用是如果程序发现有split大小小于这两个值（默认都是100MB）。

8、 启用压缩。 启用压缩主要是对数据量进行控制，让系统处理的更加快速。可以通过设置hive.exec.compress.intermediate为true，同时指定压缩方式hive.intermediate.compression.codec为org.apache.hadoop.io.compress.SnappyCodec。

set hive.exec.compress.intermediate = true;
set hive.intermediate.compression.codec = org.apache.hadoop.io.compress.SnappyCodec;

9、 JVM重用。 在MapReduce job中，默认是每执行一个task就启动一个JVM。如果task非常小而碎，那么JVM启动和关闭的耗时就会很长。可以通过调节参数mapred.job.reuse.jvm.num.tasks来重用。例如将这个参数设成5，那么就代表同一个MR job中顺序执行的5个task可以重复使用一个JVM，减少启动和关闭的开销。但它对不同MR job中的task无效。

10、并行执行与本地模式。 在hql执行过程中，互相没有依赖关系的job可以并行执行，例如多个子查询union all。在集群资源相对充足的情况下，可以开启并行执行，即将参数hive.exec.parallel设为true。另外也可设置hive.exec.parallel.thread.number并行执行的线程数，默认值为8。

set hive.exec.parallel = true;
set hive.exec.parallel.thread.number = 8;

11、 严格模式。 开启严格模式，可以有效规避能够严重影响集群执行效率的SQL语法。例如：使用order by全局排序没有使用limit关键字，查询分区表没有指定分区等。严格模式默认是打开状态。

set hive.mapred.mode = strict; #严格模式
set hive.mapred.mode = nostrict; #非严格模式