Hive优化原则及对应优化方法

未经允许禁止转载

学习过程自行维护的笔记，记录内容与公司业务无关。
后续还会不断丰富，本人大数据领域新人，欢迎交流。

A、执行过程查询

#查询输入表和输入分区
explain dependency --SQL语句
#查询执行计划
explain --SQL语句

B、优化原则

参考

分区修剪(Partition Pruning)

基于分区和桶的扫描修剪(Scan pruning)

如果查询基于抽样，则扫描修剪

在某些情况下，在 map 端应用 Group By

在 mapper 上执行 Join

优化 Union，使Union只在 map 端执行

在多路 Join 中，根据用户提示决定最后流哪个表

删除不必要的 ReduceSinkOperators

对于带有Limit子句的查询，减少需要为该表扫描的文件数

对于带有Limit子句的查询，通过限制 ReduceSinkOperator 生成的内容来限制来自 mapper 的输出

减少用户提交的SQL查询所需的Tez作业数量

如果是简单的提取查询，避免使用MapReduce作业

对于带有聚合的简单获取查询，执行不带 MapReduce 任务的聚合

重写 Group By 查询使用索引表代替原来的表

当表扫描之上的谓词是相等谓词且谓词中的列具有索引时，使用索引扫描

1、提前过滤数据

列剪裁

子查询中通过select选择有需要的列，忽略其他列，实现列剪裁

select x.a,y.d from
(select a,b from t1 where e<10)x
join
(select c,d) from t2 where e>10)y
on x.a=y.d

子查询过滤

在join前使用where过滤掉不需要的数据

#效率低的写法
select t1.a,t2.v from t1 join t2 on t1.key=t2.key where t1.ds='2022-06-06' and t2.ds='2022-06-06'
#效率高的写法
select t1.a,t2.v from
(select key,a from t1 where t1.ds='2022-06-06')x
join
(select key,b from t2 where t2.ds='2022-06-06')y
on x.key=y.key

分区剪裁

指定dt分区避免全查询，不同表对dt的使用规范参照第一部分

写明连接条件

表join时，一定通过on指定表连接条件，避免笛卡尔积

2、减少Job

多表选用相同key连接

多个表join使用相同的key，会合并为一个mapreduce任务

#效率低写法
select a.v,b.v,c.v from a 
	join b on a.key=b.key1
	join c on c.key=b.key2

#效率高写法
select a.v,b.v,c.v from a 
	join b on a.key=b.key1
	join c on c.key=b.key1

union all减少group by使用

如果多个表的group by分组依据相同时，可以先union all，再集中group by

#产生3个job，其中子查询两个job，主查询一个job
select * from
	(select v from t1 group by C
    union all
    select v from t2 group by C)x

#只产生一个job
select * from
	(select v from t1
    union all
    select v from t2)x
group by c

使用同一表union all

不同表的union all相当于multiple inputs，同一个表的union all相当于map一次输出多条，减少job数；

#低效率写法，表a加载两次
insert overwrite table t1
	select... from a where 条件1
union all
insert overwrite table t2
	select... from a where 条件2

#高效率写法，对于共用表a只加载一次
from a
    insert overwrite table t1
        select... where 条件1
    union all
    insert overwrite table t2
        select... where 条件2

合理使用UDTF函数

explode

3、解决数据倾斜

原因：大小表join，导致任务分配不均匀，少量reduce任务卡在99%

小表放前大表放后

join操作中将条目少的表放在左边，因为在reduce阶段中，左边的表会加载到内存中

使用mapjoin

select/*+MAPJOIN(b)*/a.v,b.v
from a join b on a.key=b.key

使用map端group by

# 问题分析：默认情况下 Map阶段相同的key 会被发送到同一个reduce中去聚合,当某个key数据量过大时,就造成了数据倾斜.
# 因此，并不是所有聚合操作,都需要在reduce端聚合,可以在map端先将部分数据,进行聚合,最终数据在reduce聚合

-- 是否开启map的聚合(默认为true)
set hive.map.aggr=true;
-- 在 Map 端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval=100000;
-- 发生数据倾斜时,进行负载均衡
set hive.groupby.skewindata=true;
	说明 :
	当选项设定为 true,生成的查询计划会有两个 MR Job。
    第一个 MR Job 中,Map 的输出结果(outKey) 会随机分布到 Reduce 中,
    每个 Reduce 做部分聚合操作,并输出结果
　　	这样处理好处是 相同的 Group By Key 有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的
     第二 个 MR Job 再根据预处理的数据结果 
    按照 Group By Key 分布到 Reduce 中(这个过程可以保证 相同的 Group By Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作

4、设置合理的mapreduce的task数

复杂文件增加map个数

当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。

增加map的方法为：根据

computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M

调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。

#设置切片文件大小
set mapreduce.input.fileinputformat.split.maxsize=100;

小文件合并

map前合并

在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。

set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

mapreduce结束后合并

在Map-Reduce的任务结束时合并小文件的设置：

在map-only任务结束时合并小文件，默认true

SET hive.merge.mapfiles = true;

在map-reduce任务结束时合并小文件，默认false

SET hive.merge.mapredfiles = true;

合并文件的大小，默认256M

SET hive.merge.size.per.task = 268435456;

当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge

SET hive.merge.smallfiles.avgsize = 16777216;

合理设置reduce个数

调整reduce个数

方法一

（1）每个Reduce处理的数据量默认是256MB

hive.exec.reducers.bytes.per.reducer=256000000

（2）每个任务最大的reduce数，默认为1009

hive.exec.reducers.max=1009

（3）计算reducer数的公式

N=min(参数2，总输入数据量/参数1)

方法二

在hadoop的mapred-default.xml文件中修改

设置每个job的Reduce个数

set mapreduce.job.reduces = 15;

reduce个数并不是越多越好

（1）过多的启动和初始化reduce也会消耗时间和资源；

（2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；

5、去重与排序

使用group by去重

避免使用distinct去重

#低效率写法
select distinct key from a
#高效率写法
select from a group by key

使用distribute by分组+sort by组内排序

order by会导致全局排序，cluster by可以实现分组和组内排序，但不能指定排序规则(如升序、降序)，但只能排序，不会生成排名

distribute by决定的是分到不同reduce的分组依据，sort by是在每个reduce内部排序

窗口函数排序

#排名 1 2 3 4
row_number() over(partition by c1 order by c2) as rank
#排名 1 1 2 3
dense_rank() over(partition by c1 order by c2) as rank
#排名 1 1 3 4
rank() over(partition by c1 order by c2) as rank

6、设置动态分区

动态分区DP（dynamic partition）：分区不固定；关键在于“动态”，不需要人为使用 alter table 命令执行添加分区

静态分区SP（static partition）：分区固定，需要认为使用 alter table 命令添加分区

最大区别：动态分区与静态分区区别就是不指定分区目录，由系统自己选择，在涉及多个分区插入数据的场景，可以显著加快写入速度。

#启动动态分区
set hive.exec.dynamic.partition=true
#设置所有分区列都是动态分区列,默认为strict，否则不允许主分区采取动态列而副分区采用静态列
set hive.exec.dynamic.partition.mode=nonstrict

#使用插入语句时,在分区加入变量，变量可以从后面的查询语句获取
insert overwrite table t_people partition(city='hongkong',dt) select name,age,dt from t_teacher;

#常规静态分区插入，必须指定分区
insert overwrite table t_people partition(city='hongkong',dt='2022-06-06') 

未经允许禁止转载