前言
hive调优,面试、工作中常用,非常重要。
重在理解优化思路、参数,主要分为两大块:计算资源调优和执行计划调优
一、计算资源配置(资源没给够)
本教程的计算环境为
Hive on MR
,yarn上提交任务。计算资源的调整主要包括Yarn
和MR
1. Yarn资源配置(整个集群)
🥝需要调整的Yarn参数均与CPU、内存等资源有关,vim /opt/module/hadoop/etc/hadoop/yarn-site.xml
核心配置参数如下:
yarn.nodemanager.resource.memory-mb
:该参数的含义是,一个NodeManager节点分配给Container使用的内存。该参数的配置,取决于NodeManager所在节点的总内存容量和该节点运行的其他服务的数量。考虑上述因素,此处可将该参数设置为64G(默认8G必须得调,一般给到总内存的1/2~2/3之间),如下
<property>
<name>yarn.nodemanager.resource.memory-mb</name>
<value>65536</value>
</property>
yarn.nodemanager.resource.cpu-vcores
:该参数的含义是,一个NodeManager节点分配给Container使用的CPU核数。该参数的配置,同样取决于NodeManager所在节点的总CPU核数和该节点运行的其他服务。考虑上述因素,此处可将该参数设置为16(一般与内存为1:4的关系,一核4G)
<property>
<name>yarn.nodemanager.resource.cpu-vcores</name>
<value>16</value>
</property>
yarn.scheduler.maximum-allocation-mb
:该参数的含义是,单个Container能够使用的最大内存。推荐配置如下:
<property>
<name>yarn.scheduler.maximum-allocation-mb</name>
<value>16384</value>
</property>
yarn.scheduler.minimum-allocation-mb
:该参数的含义是,单个Container能够使用的最小内存,推荐配置如下:
<property>
<name>yarn.scheduler.minimum-allocation-mb</name>
<value>512</value>
</property>
最后记得分发
xsync yarn-site.xml
2. MapReduce资源配置
🥝MapReduce资源配置主要包括Map Task
的内存和CPU核数,以及Reduce Task
的内存和CPU核数。viim /opt/module/hadoop/etc/hadoop/mapreduce-site.xml
核心配置参数如下:
mapreduce.map.memory.mb
:该参数的含义是,单个Map Task申请的container容器内存大小,其默认值为1024MB。该值不能超出yarn.scheduler.maximum-allocation-mb
和yarn.scheduler.minimum-allocation-mb
规定的范围。该参数需要根据不同的计算任务单独进行配置,在hive中,可直接使用如下方式为每个SQL语句单独进行配置:
-- 直接在hive中
set mapreduce.map.memory.mb=2048;
mapreduce.map.cpu.vcores
:该参数的含义是,单个Map Task申请的container容器cpu核数,其默认值为1。该值一般无需调整mapreduce.reduce.memory.mb
:该参数的含义是,单个Reduce Task申请的container容器内存大小,其默认值为1024。该值同样不能超出yarn.scheduler.maximum-allocation-mb
和yarn.scheduler.minimum-allocation-mb
规定的范围。该参数需要根据不同的计算任务单独进行配置,在hive中,可直接使用如下方式为每个SQL语句单独进行配置:
set mapreduce.reduce.memory.mb=2048;
mapreduce.reduce.cpu.vcores
:该参数的含义是,单个Reduce Task申请的container容器cpu核数,其默认值为1。该值一般无需调整
👉查看计算任务到底实际会使用多少,根据此来设置参数大小。
1️⃣设置为Yarn模式,set mapreduce.framework.name=yarn;
,然后测试一条sql语句(默认1024的内存)
2️⃣👉网络端查看jobhistory日志log查看执行情况,先要启动集群myhadoop.sh start
,查看后发现小了就调大内存即可
二、Explain查看执行计划(重点)
1. 测试用表
RAM没有32G就不尝试了,看着视频理解为主,也可以去开个云服务器
1️⃣订单表(2000w条数据)
2️⃣支付表(600w条数据)
3️⃣商品信息表(100w条数据)
4️⃣省份信息表(34条数据)
2. Explain执行计划概述
🥝Explain
呈现的执行计划,由一系列Stage
组成,这一系列Stage
具有依赖关系(执行完一个执行下一个),每个Stage
对应一个MapReduce Job
,或者一个文件系统操作等。若某个Stage
对应的一个MapReduce Job
,其Map
端和Reduce
端的计算逻辑分别由Map Operator Tree
和Reduce Operator Tree
进行描述,Operator Tree
由一系列的Operator
组成,一个Operator
代表在Map
或Reduce
阶段的一个单一的逻辑操作,例如TableScan Operator
,Select Operator
,Join Operator
等。下图是由一个执行计划绘制而成:
👉常见的Operator及其作用如下:
TableScan
:表扫描操作,通常map端第一个操作肯定是表扫描操作Select Operator
:选取操作Group By Operator
:分组聚合操作Reduce Output Operator
:输出到 reduce 操作Filter Operator
:过滤操作Join Operator
:join 操作File Output Operator
:文件输出操作Fetch Operator
:客户端获取数据操作(将结果拉取到终端显示)
3. 查看执行计划基本语法
EXPLAIN [FORMATTED | EXTENDED | DEPENDENCY] query-sql
👉FORMATTED
、EXTENDED
、DEPENDENCY
关键字为可选项,各自作用如下
关键词 | 作用 |
---|---|
FORMATTED | 将执行计划以JSON字符串的形式输出 |
EXTENDED | 输出执行计划中的额外信息,通常是读写的文件名(临时目录名)等信息 |
DEPENDENCY | 输出执行计划读取的表及分区(不太常用) |
4. 案例实操演示
explain
select
user_id,
count(*)
from order_detail
group by user_id;
👇执行计划如下图:
三、HQL语法优化
1. 分组聚合优化(map-site聚合)
🥝Hive中未经优化的分组聚合,是通过一个MapReduce Job
实现的。Map端负责读取数据,并按照分组字段分区,通过Shuffle
,将数据发往Reduce端,各组数据在Reduce端完成最终的聚合运算
🥝Hive对分组聚合的优化主要围绕着减少Shuffle
数据量进行,具体做法是map-side聚合
。所谓map-side聚合
,就是在map端维护一个hash table
,利用其完成部分的聚合,然后将部分聚合的结果,按照分组字段分区,发送至reduce端,完成最终的聚合。map-side聚合
能有效减少shuffle
的数据量,提高分组聚合运算的效率
map-side
聚合相关的参数如下:
-- 启用map-side聚合
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;
2. Join优化概述(是什么)
🥝Hive拥有多种join算法,包括Common Join
,Map Join
,Bucket Map Join
,Sort Merge Buckt Map Join
等,下面对每种join算法做简要说明:
2.1. Common Join
Common Join是Hive中最稳定的join算法,其通过一个MapReduce Job完成一个join操作。Map端负责读取join操作所需表的数据,并按照关联字段进行分区,通过Shuffle,将其发送到Reduce端,相同key的数据在Reduce端完成最终的Join操作。
需要注意的是,sql语句中的
join
操作和执行计划中的Common Join
任务并非一对一的关系,一个sql
语句中的相邻的且关联字段相同的多个join
操作可以合并为一个Common Join
任务
-- a join b join c关联字段相同
-- 可由一个Common Join任务实现
-- 也就是可通过一个Map Reduce任务实现
select
a.val,
b.val,
c.val
from a
join b on (a.key = b.key1)
join c on (c.key = b.key1)
-- 关联字段各不相同,则该语句的两个join操作需要各自通过一个Common Join任务实现
-- 也就是通过两个Map Reduce任务实现
select
a.val,
b.val,
c.val
from a
join b on (a.key = b.key1)
join c on (c.key = b.key2)
2.2. Map Join(大表join小表)
🥝Map Join
算法可以通过两个只有map
阶段(无reduce)的Job
完成一个join
操作。其适用场景为大表join
小表。若某join
操作满足要求,则第一个Job
会读取小表数据,将其制作为hash table
,并上传至Hadoop分布式缓存(本质上是上传至HDFS)。第二个Job
会先从分布式缓存中读取小表数据,并缓存在Map Task
的内存中,然后扫描大表数据,这样在map端即可完成关联操作
2.3. Bucket Map Join(大表join大表)
🥝Bucket Map Join
是对Map Join
算法的改进,其打破了Map Join
只适用于大表join
小表的限制,可用于大表join
大表的场景。
🥝Bucket Map Join
的核心思想是:若能保证参与join的表均为分桶表,且关联字段为分桶字段,且其中一张表的分桶数量是另外一张表分桶数量的整数倍,就能保证参与join的两张表的分桶之间具有明确的关联关系,所以就可以在两表的分桶间进行Map Join操作了。这样一来,第二个Job的Map端就无需再缓存小表的全表数据了,而只需缓存其所需的分桶即可(桶内存别超过map端能处理的)
2.4. Sort Merge Bucket Map Join(对内存没有要求)
🥝Sort Merge Bucket Map Join
(简称SMB Map Join
)基于Bucket Map Join
。SMB Map Join
要求,参与join
的表均为分桶表,且需保证分桶内的数据是有序的,且分桶字段、排序字段和关联字段为相同字段,且其中一张表的分桶数量是另外一张表分桶数量的整数倍
🥝SMB Map Join
同Bucket Join
一样,同样是利用两表各分桶之间的关联关系,在分桶之间进行join
操作,不同的是,分桶之间的join
操作的实现原理。Bucket Map Join
,两个分桶之间的join
实现原理为Hash Join
算法;而SMB Map Join
,两个分桶之间的join
实现原理为Sort Merge Join
算法
🥝Hash Join
和Sort Merge Join
均为关系型数据库中常见的Join
实现算法。Hash Join
的原理相对简单,就是对参与join
的一张表构建hash table
,然后扫描另外一张表,然后进行逐行匹配。Sort Merge Join
需要在两张按照关联字段排好序的表中进行
🥝Hive中的SMB Map Join
就是对两个分桶的数据按照上述思路进行Join
操作。可以看出,SMB Map Join
与Bucket Map Join
相比,在进行Join
操作时,Map
端是无需对整个Bucket
构建hash table
,也无需在Map
端缓存整个Bucket
数据的,每个Mapper
只需按顺序逐个key
读取两个分桶的数据进行join
即可
3. Map join详解
🥝Map Join
有两种触发方式,一种是用户在SQL语句中增加hint
提示(这种方式已经过时,不推荐使用),另外一种是Hive优化器根据参与join表的数据量大小,自动触发
3.1. 自动触发
🥝Hive在编译SQL语句阶段,起初所有的join操作均采用Common Join
算法实现。之后在物理优化阶段,Hive会根据每个Common Join
任务所需表的大小判断该Common Join
任务是否能够转换为Map Join
任务,若满足要求(是否满足小表的要求,表内存大于某个值),便将Common Join
任务自动转换为Map Join
任务。
👉但有些Common Join
任务所需的表大小,在SQL的编译阶段是未知的(例如对子查询进行join操作),所以这种Common Join
任务是否能转换成Map Join
任务在编译阶是无法确定的。
👉针对这种情况,Hive会在编译阶段生成一个条件任务Conditional Task
(可选),其下会包含一个计划列表,计划列表中包含转换后的所有可能的Map Join
任务以及原有的Common Join
任务。最终具体采用哪个计划,是在运行时决定的,思路如下图:
📌在编译阶段生成一个条件任务,其自动转换的具体判断逻辑如下图所示(针对的是执行计划中的common join Task
,而不是sql语句中的join):
-- 启动Map Join自动转换开关
set hive.auto.convert.join=true;
-- 开启条件任务conditnoal Task
-- 一个Common Join operator转为Map Join operator的判断条件
-- 若该Common Join相关的表中,不存在一张表大于该值,则生成一个Map Join计划
-- 此时可能存在多种n-1张表的组合均满足该条件,则hive会为每种满足条件的组合均生成一个Map Join计划
-- 同时还会保留原有的Common Join计划作为后备(back up)计划
-- 实际运行时,优先执行Map Join计划,若不能执行成功,则启动Common Join后备计划。
set hive.mapjoin.smalltable.filesize=250000;
-- 开启无条件转Map Join
set hive.auto.convert.join.noconditionaltask=true;
-- 无条件转Map Join时的小表之和阈值
-- 若一个Common Join operator相关的表中,存在n-1张表的大小总和<=该值
-- 此时hive便不会再为每种n-1张表的组合均生成Map Join计划,同时也不会保留Common Join作为后备计划。而是只生成一个最优的Map Join计划。
set hive.auto.convert.join.noconditionaltask.size=10000000;
大表候选人的选法:
inner join
哪个做大表都行,left join
左表做大表,right join
右表做大表,full join
无法用map join只能common join
最优map join:选择一个最大的表作为大表,使其他表加起来将尽可能小
3.2. 优化案例(优化前–>优化后)
- 优化前common task:
set hive.auto.convert.join=false;
select
*
from order_detail od
join product_info product on od.product_id = product.id
join province_info province on od.province_id = province.id;
👉上述SQL语句共有三张表进行两次join
,且两次join
操作的关联字段不同。故优化前的执行计划应该包含两个Common Join operator
,也就是由两个MapReduce
任务实现。执行计划如下图所示:
- 采用优化(干掉reduce阶段)
🤓三张表中,product_info和province_info数据量较小,可考虑将其作为小表,进行Map Join优化。根据前文Common Join任务转Map Join任务的判断逻辑图,可得出以下优化方案:
1️⃣启用Map Join
自动转换,不使用无条件转Map Join
(9min41s)
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask=false;
-- 调整hive.mapjoin.smalltable.filesize参数,使其大于等于product_info(),即第二张表的大小
set hive.mapjoin.smalltable.filesize=25285707;
💡这样可保证将两个Common Join operator
均可转为Map Join operator
,并保留Common Join
作为后备计划,保证计算任务的稳定。调整好的执行计划如下图:
运行后发现沿着上图最左边的那条路走到底,保留
Common Join
作为后备计划实属多余,于是乎打开无条件转map join
2️⃣启用Map Join
自动转换,使用无条件转Map Join
(4min52s)
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask=true;
-- 等于两个小表大小之和,方便以后两个map join合并为一个
set hive.auto.convert.join.noconditionaltask.size=25286076;
💡这样可直接将两个Common Join operator
转为两个Map Join operator
,并且由于两个Map Join operator
的小表大小之和小于等于hive.auto.convert.join.noconditionaltask.size
,故两个Map Join operator
任务可合并为同一个。这个方案计算效率最高,但需要的内存也是最多的。执行计划如下图:
相比于方案一走了两个map job进行了合并只有一个map job速度大大加快
3️⃣方法二,但是不合并版本(跟方案一差不多)
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask=true;
-- 改为product_info第二张表的大小,意味着后面无法合并
set hive.auto.convert.join.noconditionaltask.size=25285707;
💡这样可直接将两个Common Join operator
转为Map Join operator
,但不会将两个Map Join
的任务合并。该方案计算效率比方案二低,但需要的内存也更少。执行计划如下图:
日后调优,有关map join方面的参数共有4个:
1️⃣hive.auto.convert.join
2️⃣hive.auto.convert.join.noconditionaltask
3️⃣hive.auto.convert.join.noconditionaltask.size
4️⃣hive.mapjoin.smalltable.filesize
其中参数1️⃣2️⃣在实际中通常全局设置为true
,在hive-site.xml
中配置好,参数3️⃣4️⃣都是文件在磁盘中的大小,跟内存在内存中大小不一致,会有解压缩、文件IO等开销,大致换算内存*10=文件大小
(小表)。map端拿大概在 1 2 → 2 3 \frac{1}{2}\rightarrow\frac{2}{3} 21→32 之间的内存缓存一张表,再除以10即为参数3️⃣4️⃣大小
4. Bucket Map Join详解
🥝优化说明:Bucket Map Join
不支持自动转换(快被淘汰不给他更新了),发须通过用户在SQL语句中提供如下Hint
提示,并配置如下相关参数,方可使用
-- Hint提示
select /*+ mapjoin(ta) */
ta.id,
tb.id
from table_a ta
join table_b tb on ta.id=tb.id;
-- 相关参数配置
-- 关闭cbo优化,cbo会导致hint信息被忽略
set hive.cbo.enable=false;
-- map join hint默认会被忽略(因为已经过时),需将如下参数设置为false
set hive.ignore.mapjoin.hint=false;
-- 启用bucket map join优化功能
set hive.optimize.bucketmapjoin = true;
🌰优化案例
-- 两张大表,1G和300MB,但是作为小表要*10变成文件大小3G,那么要map内存在4G以上才能map join
select
*
from(
select
*
from order_detail
where dt='2020-06-14'
)od
join(
select
*
from payment_detail
where dt='2020-06-14'
)pd
on od.id=pd.order_detail_id;
-
优化前:上述SQL语句共有两张表一次
join
操作,故优化前的执行计划应包含一个Common Join
任务,通过一个MapReduce Job
实现。执行计划如下图所示:
👉若采用普通的Map Join
算法,则Map端需要较多的内存来缓存数据,当然可以选择为Map段分配更多的内存,来保证任务运行成功。但是,Map端的内存不可能无上限的分配,所以当参与Join的表数据量均过大时,就可以考虑采用Bucket Map Join
算法 -
优化后:首先需要依据源表创建两个分桶表,
order_detail
建议分16个bucket
,payment_detail
建议分8个bucket
,注意分桶个数的倍数关系以及分桶字段(320M/8=40M==>400M(HDFS),相比map端内存1024MB还是比较合理)
-- 建立订单表分桶表
drop table if exists order_detail_bucketed;
create table order_detail_bucketed(
id string comment '订单id',
user_id string comment '用户id',
product_id string comment '商品id',
province_id string comment '省份id',
create_time string comment '下单时间',
product_num int comment '商品件数',
total_amount decimal(16, 2) comment '下单金额'
)
clustered by (id) into 16 buckets
row format delimited fields terminated by '\t';
-- 建立支付表分桶表
drop table if exists payment_detail_bucketed;
create table payment_detail_bucketed(
id string comment '支付id',
order_detail_id string comment '订单明细id',
user_id string comment '用户id',
payment_time string comment '支付时间',
total_amount decimal(16, 2) comment '支付金额'
)
clustered by (order_detail_id) into 8 buckets
row format delimited fields terminated by '\t';
-- 导入订单表、支付表数据
insert overwrite table order_detail_sorted_bucketed select id, user_id,product_id,province_id,create_time,product_num,total_amount from order_detail where dt='2020-06-14';
insert overwrite table payment_detail_sorted_bucketed select id,order_detail_id,user_id,payment_time,total_amount from payment_detail where dt='2020-06-14';
-- 查看重写SQL语句的执行计划,看看是不是Bucket Map Join
explain extended select /*+ mapjoin(pd) */
*
from order_detail_bucketed od
join payment_detail_bucketed pd on od.id = pd.order_detail_id;
Bucket Map Join
启用小技巧
- 参与join的表都要创建好分桶表
- 关联字段要相同
- 不同分桶表的分桶成倍数关系
- 参数配置好,估计分桶大小
5. Sort Merge Bucket Map Join详解
🥝 优化说明:Sort Merge Bucket Map Join
有两种触发方式,包括Hint提示和自动转换。Hint提示已过时,不推荐使用。相较于Bucket Map Join
,SMB Map Join
对分桶大小是没有要求,且只要求多一个排序。下面是自动转换的相关参数:
-- 启动Sort Merge Bucket Map Join优化
set hive.optimize.bucketmapjoin.sortedmerge=true;
-- 使用自动转换SMB Join
set hive.auto.convert.sortmerge.join=true;
🌰优化案例:
-- 两个大表关联操作
select
*
from(
select
*
from order_detail
where dt='2020-06-14'
)od
join(
select
*
from payment_detail
where dt='2020-06-14'
)pd
on od.id=pd.order_detail_id;
- 优化前:上述SQL语句共有两张表一次join操作,故优化前的执行计划应包含一个
Common Join
任务,通过一个MapReduce Job
实现 - 优化后:虽然分桶多少都可以,但是多一点桶能提高并行效率
-- 订单表
drop table if exists order_detail_sorted_bucketed;
create table order_detail_sorted_bucketed(
id string comment '订单id',
user_id string comment '用户id',
product_id string comment '商品id',
province_id string comment '省份id',
create_time string comment '下单时间',
product_num int comment '商品件数',
total_amount decimal(16, 2) comment '下单金额'
)
clustered by (id) sorted by(id) into 16 buckets
row format delimited fields terminated by '\t';
-- 支付表
drop table if exists payment_detail_sorted_bucketed;
create table payment_detail_sorted_bucketed(
id string comment '支付id',
order_detail_id string comment '订单明细id',
user_id string comment '用户id',
payment_time string comment '支付时间',
total_amount decimal(16, 2) comment '支付金额'
)
clustered by (order_detail_id) sorted by(order_detail_id) into 8 buckets
row format delimited fields terminated by '\t';
-- 导入数据
insert overwrite table order_detail_sorted_bucketed select id, user_id,product_id,province_id,create_time,product_num,total_amount from order_detail where dt='2020-06-14';
insert overwrite table payment_detail_sorted_bucketed select id,order_detail_id,user_id,payment_time,total_amount from payment_detail where dt='2020-06-14';
-- 执行sql
select
*
from order_detail_sorted_bucketed od
join payment_detail_sorted_bucketed pd
on od.id = pd.order_detail_id;
👉优化后的执行计如图所示:
6. 数据倾斜(reduce端)
6.1. 数据倾斜概述
🥝指参与计算的数据分布不均,即某个key
或者某些key
的数据量远超其他key
,导致在shuffle
阶段,大量相同key的数据被发往同一个Reduce
,进而导致该Reduce
所需的时间远超其他Reduce
,成为整个任务的瓶颈。Hive中的数据倾斜常出现在分组聚合和join操作的场景中,下面分别介绍在上述两种场景下的优化思路。
6.2. 分组聚合导致的数据倾斜
🥝优化说明:Hive中未经优化的分组聚合,是通过一个MapReduce Job
实现的。Map
端负责读取数据,并按照分组字段分区,通过Shuffle,将数据发往Reduce
端,各组数据在Reduce
端完成最终的聚合运算。如果group by
分组字段的值分布不均,就可能导致大量相同的key
进入同一Reduce
,从而导致数据倾斜问题。
💡由分组聚合导致的数据倾斜问题,有以下两种解决思路:
Map-Side聚合
:开启Map-Side聚合
后,数据会现在Map端完成部分聚合工作。这样一来即便原始数据是倾斜的,经过Map端的初步聚合后,发往Reduce的数据也就不再倾斜了。最佳状态下,Map端聚合能完全屏蔽数据倾斜问题。相关参数如下(与分组聚合优化相同,且更快):
-- 启用map-side聚合
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;
Skew-GroupBy优化
:Skew-GroupBy
的原理是启动两个MR任务,第一个MR按照随机数分区,将数据分散发送到Reduce,完成部分聚合,第二个MR按照分组字段分区,完成最终聚合。相关参数如下:
-- 启用分组聚合数据倾斜优化
set hive.groupby.skewindata=true;
👉Skew-GroupBy优化
执行计划:
🌰优化案例:
-- 99%的数据province_id都为1
select
province_id,
count(*)
from order_detail
group by province_id;
- 优化前:
- 优化后:
在性能上
map-site聚合
完胜,在没有内存处理时表数据时,才用Skew-GroupBy优化
6.3. Join导致的数据倾斜
🥝优化说明:前文提到过,未经优化的join操作,默认是使用common join
算法,也就是通过一个MapReduce Job
完成计算。Map端负责读取join操作所需表的数据,并按照关联字段进行分区,通过Shuffle,将其发送到Reduce端,相同key的数据在Reduce端完成最终的Join操作。如果关联字段的值分布不均,就可能导致大量相同的key进入同一Reduce,从而导致数据倾斜问题
💡由join
导致的数据倾斜问题,有如下三种解决方案:
map join
使用map join算法,join操作仅在map端就能完成,没有shuffle操作,没有reduce阶段,自然不会产生reduce端的数据倾斜。该方案适用于大表join小表时发生数据倾斜的场景。相关参数如下(与上面同):
-- 启动Map Join自动转换开关
set hive.auto.convert.join=true;
-- 开启条件任务conditnoal Task
-- 一个Common Join operator转为Map Join operator的判断条件
-- 若该Common Join相关的表中,不存在一张表大于该值,则生成一个Map Join计划
-- 此时可能存在多种n-1张表的组合均满足该条件,则hive会为每种满足条件的组合均生成一个Map Join计划
-- 同时还会保留原有的Common Join计划作为后备(back up)计划
-- 实际运行时,优先执行Map Join计划,若不能执行成功,则启动Common Join后备计划。
set hive.mapjoin.smalltable.filesize=250000;
-- 开启无条件转Map Join
set hive.auto.convert.join.noconditionaltask=true;
-- 无条件转Map Join时的小表之和阈值
-- 若一个Common Join operator相关的表中,存在n-1张表的大小总和<=该值
-- 此时hive便不会再为每种n-1张表的组合均生成Map Join计划,同时也不会保留Common Join作为后备计划。而是只生成一个最优的Map Join计划。
set hive.auto.convert.join.noconditionaltask.size=10000000;
skew join
因为map join只适用与大表join小表的情况,因而设计出来解决大表join大表数据倾斜的问题(分桶join不行,因为分桶的过程就是一个数据倾斜的过程),倾斜的大key单独启动一个map join任务进行计算,其余key进行正常的common join。原理图如下:
-- 启用skew join优化
set hive.optimize.skewjoin=true;
-- 触发skew join的阈值,若某个key的行数超过该参数值,则触发
set hive.skewjoin.key=100000;
这种方案对参与join的源表大小没有要求,但是对两表中倾斜的key的数据量有要求,要求一张表中的倾斜key的数据量比较小(方便走map join)
- 调整SQL语句
若参与join的两表均为大表,其中一张表的数据是倾斜的,此时也可通过以下方式对SQL语句进行相应的调整。假设原始SQL语句如下:A,B两表均为大表,且其中一张表的数据是倾斜的
select
*
from A
join B
on A.id=B.id;
👉调整SQL语句如下:
select
*
from(
select --打散操作
concat(id,'_',cast(rand()*2 as int)) id,
value
from A
)ta
join(
select --扩容操作
concat(id,'_',0) id,
value
from B
union all
select
concat(id,'_',1) id,
value
from B
)tb
on ta.id=tb.id;
7. 任务并行度
🥝优化说明:对于一个分布式的计算任务而言,设置一个合适的并行度十分重要。Hive的计算任务由MapReduce
完成,故并行度的调整需要分为Map
端和Reduce
端。
7.1. Map端并行度
🥝Map端的并行度,也就是Map的个数。是由输入文件的切片数决定的。一般情况下,Map端的并行度无需手动调整。以下特殊情况可考虑调整map端并行度:
1️⃣查询的表中存在大量小文件
按照Hadoop默认的切片策略,一个小文件会单独启动一个map task
负责计算。若查询的表中存在大量小文件,则会启动大量map task
,造成计算资源的浪费。这种情况下,可以使用Hive提供的CombineHiveInputFormat
,多个小文件合并为一个切片,从而控制map task
个数。相关参数如下(默认开启,省心!):
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
2️⃣map端有复杂的查询逻辑
若SQL语句中有正则替换、json解析等复杂耗时的查询逻辑时,map端的计算会相对慢一些。若想加快计算速度,在计算资源充足的情况下,可考虑增大map端的并行度,令map task
多一些,每个map task
计算的数据少一些。相关参数如下:
-- 一个切片的最大值(调小即可增加map个数)
set mapreduce.input.fileinputformat.split.maxsize=256000000;
7.2. reduce端并行度(关注)
🥝Reduce端的并行度,也就是Reduce个数。相对来说,更需要关注。Reduce端的并行度,可由用户自己指定,也可由Hive自行根据该MR Job
输入的文件大小进行估算。Reduce端的并行度的相关参数如下:
-- 指定Reduce端并行度,默认值为-1,表示用户未指定,自己估算
set mapreduce.job.reduces;
-- Reduce端并行度最大值
set hive.exec.reducers.max;
-- 单个Reduce Task计算的数据量,用于估算Reduce并行度
set hive.exec.reducers.bytes.per.reducer;
🥝Reduce端并行度的确定逻辑如下:
若指定参数mapreduce.job.reduces
的值为一个非负整数,则Reduce并行度为指定值。否则,Hive自行估算Reduce并行度,估算逻辑如下:假设Job输入的文件大小为totalInputBytes
。参数hive.exec.reducers.bytes.per.reducer
的值为bytesPerReducer
。参数hive.exec.reducers.max
的值为maxReducers
。则Reduce端的并行度为:
可以看出,Hive自行估算Reduce并行度时,是以整个MR Job
输入的文件大小作为依据的(map端如果进行分组聚合那么进入reduce端的数据会远小于mr job输入的数据)。因此,在某些情况下其估计的并行度很可能并不准确,此时就需要用户根据实际情况来指定Reduce并行度了
🌰reduce优化案例:
select
province_id,
count(*)
from order_detail
group by province_id;
- 优化前:上述sql语句,在不指定Reduce并行度时,Hive自行估算并行度的逻辑如下
totalInputBytes= 1136009934
bytesPerReducer=256000000
maxReducers=1009
- 优化思路:上述sql语句,在默认情况下,是会进行
map-side
聚合的,也就是Reduce
端接收的数据,实际上是map
端完成聚合之后的结果。观察任务的执行过程,会发现,每个map
端输出的数据只有34条记录,共有5个map task
,也就是说Reduce
端实际只会接收170(34*5)条记录,故理论上Reduce
端并行度设置为1就足够了。这种情况下,用户可通过以下参数,自行设置Reduce端并行度为1
(节省了资源)
-- 指定Reduce端并行度,默认值为-1,表示用户未指定
set mapreduce.job.reduces=1;
8. 小文件合并
优化说明:小文件合并优化,分为两个方面,分别是Map端输入的小文件合并,和Reduce端输出的小文件合并
8.1. Map端输入文件合并
🥝合并Map端
输入的小文件,是指将多个小文件划分到一个切片中,进而由一个Map Task
去处理。目的是防止为单个小文件启动一个Map Task
,浪费计算资源。相关参数为:
-- 可将多个小文件切片,合并为一个切片,进而由一个map任务处理
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
8.2. Reduce输出文件合并
🥝合并Reduce端输出的小文件,是指将多个小文件合并成大文件。目的是减少HDFS小文件数量。其原理是根据计算任务输出文件的平均大小进行判断,若符合条件,则单独启动一个额外的任务进行合并。相关参数为:
-- 开启合并map only任务输出的小文件(只有map的任务,默认false)
set hive.merge.mapfiles=true;
-- 开启合并map+reduce任务输出的小文件,默认false
set hive.merge.mapredfiles=true;
-- 合并后的文件大小
set hive.merge.size.per.task=256000000;
-- 触发小文件合并任务的阈值,若某计算任务输出的文件平均大小低于该值,则触发合并
set hive.merge.smallfiles.avgsize=16000000;
🌰优化案例:现有一个需求,计算各省份订单金额总和,下表为结果表
drop table if exists order_amount_by_province;
create table order_amount_by_province(
province_id string comment '省份id',
order_amount decimal(16,2) comment '订单金额'
)
location '/order_amount_by_province';
-- 导入数据,不开优化生成5个文件
insert overwrite table order_amount_by_province
select
province_id,
sum(total_amount)
from order_detail
group by province_id;
- 优化思路:若想避免小文件的产生,可采取方案有两个。
1️⃣合理设置任务的Reduce端并行度
若将上述计算任务的并行度设置为1,就能保证其输出结果只有一个文件
2️⃣启用Hive合并小文件优化
设置以下参数:
-- 开启合并map reduce任务输出的小文件
set hive.merge.mapredfiles=true;
-- 合并后的文件大小
set hive.merge.size.per.task=256000000;
-- 触发小文件合并任务的阈值,若某计算任务输出的文件平均大小低于该值,则触发合并
set hive.merge.smallfiles.avgsize=16000000;
四、其他优化
1. 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;
🌰优化案例:
select
*
from order_detail od
join product_info product on od.product_id=product.id
join province_info province on od.province_id=province.id;
-- 关闭cbo优化(join顺序跟SQL语句写的顺序一致)
set hive.cbo.enable=false;
-- 为了测试效果更加直观,关闭map join自动转换
set hive.auto.convert.join=false;
👇重新开启CBO后:
根据上述案例可以看出,
CBO
优化对于执行计划中join
顺序是有影响的,其之所以会将province_info
的join顺序提前,是因为province info
的数据量较小,将其提前,会有更大的概率使得中间结果的数据量变小,从而使整个计算任务的数据量减小,也就是使计算成本变小
2. 谓词下推(早点用where过滤)
🥝优化说明:谓词下推(predicate pushdown
)是指,尽量将where过滤操作前移,以减少后续计算步骤的数据量。相关参数为:
-- 是否启动谓词下推(predicate pushdown)优化,管他三七二十一开就完事
set hive.optimize.ppd = true;
需要注意的是:
CBO
优化也会完成一部分的谓词下推优化工作,因为在执行计划中,谓词越靠前,整个计划的计算成本就会越低。
🌰优化案例:
select
*
from order_detail
join province_info
where order_detail.province_id='2';
-- 关闭谓词下推(predicate pushdown)优化,按照SQL语句顺序执行,先join后where过滤
set hive.optimize.ppd = false;
-- 为了测试效果更加直观,关闭cbo优化
set hive.cbo.enable=false;
👇开启谓词下推后:
不管有没有谓词下推,写SQL时养成良好习惯,先过滤再join,写子查询不会影响太多执行速度
3. 矢量化查询(向量加法)
🥝Hive的矢量化查询优化,依赖于CPU的矢量化计算,CPU的矢量化计算的基本原理如下图:
📐Hive的矢量化查询,可以极大的提高一些典型查询场景(例如scans
, filters
, aggregates
, joins
)下的CPU使用效率。相关参数如下:
set hive.vectorized.execution.enabled=true;
⚡若执行计划中,出现Execution mode: vectorized
字样,即表明使用了矢量化计算
📌当然矢量化查询是有限制的,不是什么时候都能用的,👉点击前往官网查看有关Vectorized支持的操作
4. Fetch抓取(简单任务不开MR)
🥝Fetch
抓取是指,Hive中对某些情况的查询可以不必使用MapReduce计算。例如:select * from emp;
在这种情况下,Hive可以简单地读取emp对应的存储目录下的文件,然后输出查询结果到控制台。相关参数如下:
-- 是否在特定场景转换为fetch 任务
-- 设置为none表示不转换
-- 设置为minimal表示支持select *,分区字段过滤,Limit等
-- 设置为more表示支持select 任意字段,包括函数,过滤,和limit等
set hive.fetch.task.conversion=more;
5. 本地模式(单干,很少用,学习用)
🥝优化说明:大多数的Hadoop Job
是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过,有时Hive的输入数据量是非常小的。在这种情况下,为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况,Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集,执行时间可以明显被缩短。相关参数如下:
-- 开启自动转换为本地模式(会进行判断,小的会走本地,大的仍是提交集群)
set hive.exec.mode.local.auto=true;
-- 设置local MapReduce的最大输入数据量,当输入数据量小于这个值时采用local MapReduce的方式
-- 默认为134217728,即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
--设置local MapReduce的最大输入文件个数,当输入文件个数小于这个值时采用local MapReduce的方式,默认为4
set hive.exec.mode.local.auto.input.files.max=10;
6. Stage并行执行
🥝Hive会将一个SQL语句转化成一个或者多个Stage
,每个Stage
对应一个MR Job
。默认情况下,Hive同时只会执行一个Stage
。但是某SQL语句可能会包含多个Stage
,但这多个Stage
可能并非完全互相依赖,也就是说有些Stage
是可以并行执行的。此处提到的并行执行就是指这些Stage
的并行执行。相关参数如下:
-- 启用并行执行优化(默认false)
set hive.exec.parallel=true;
-- 同一个sql允许最大并行度,默认为8,资源充足的情况下越多越快
set hive.exec.parallel.thread.number=8;
7. 严格模式
🥝Hive可以通过设置某些参数防止危险操作:
-
分区表不使用分区过滤
将hive.strict.checks.no.partition.filter
设置为true
时,对于分区表,除非where
语句中含有分区字段过滤条件来限制范围,否则不允许执行。换句话说,就是用户不允许扫描所有分区。进行这个限制的原因是,通常分区表都拥有非常大的数据集,而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表 -
使用order by没有limit过滤
将hive.strict.checks.orderby.no.limit
设置为true
时,对于使用了order by
语句的查询,要求必须使用limit语句。因为order by
为了执行排序过程会将所有的结果数据分发到同一个Reduce
中进行处理,强制要求用户增加这个limit
语句可以防止Reduce
额外执行很长一段时间(开启了limit可以在数据进入到Reduce
之前就减少一部分数据) -
笛卡尔积
将hive.strict.checks.cartesian.product
设置为true
时,会限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN查询
的时候不使用ON语句
而是使用where语句
,这样关系数据库的执行优化器就可以高效地将WHERE语句
转化成那个ON语句
。不幸的是,Hive并不会执行这种优化,因此,如果表足够大,那么这个查询就会出现不可控的情况。
总结
✍从安装部署到HQL语法讲解,从函数详解到企业级性能调优
✍hive是一个 Hadoop客户端,用于将HQL (Hive SQL)转化成MapReduce程序,Hive中每张表的数据存储在HDFS中,Hive分析数据底层的实现是MapReduce(也可配置为Spark或者Tez),执行程序运行在 Yarn 上
✍下一站,spark!