hive企业级调优策略之Join优化

最新推荐文章于 2024-05-11 08:46:25 发布

Appreciate(欣赏)

最新推荐文章于 2024-05-11 08:46:25 发布

阅读量1.9k

点赞数 28

分类专栏： hive 文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/m0_52606060/article/details/135080396

版权

hive 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

测试所用到的数据参考：

原文链接：https://blog.csdn.net/m0_52606060/article/details/135080511

本教程的计算环境为Hive on MR。计算资源的调整主要包括Yarn和MR。

Join算法概述

Hive拥有多种join算法，包括Common Join，Map Join，Bucket Map Join，Sort Merge Buckt Map Join等，下面对每种join算法做简要说明：

Common Join

Common Join是Hive中最稳定的join算法，其通过一个MapReduce Job完成一个join操作。Map端负责读取join操作所需表的数据，并按照关联字段进行分区，通过Shuffle，将其发送到Reduce端，相同key的数据在Reduce端完成最终的Join操作(一个Join操作默认执行Common Join)。
如下图所示：
在这里插入图片描述

需要注意的是，sql语句中的join操作和执行计划中的Common Join任务并非一对一的关系，一个sql语句中的相邻的且关联字段相同的多个join操作可以合并为一个Common Join任务。
例如：

select 
    a.val, 
    b.val, 
    c.val 
from a 
join b on (a.key = b.key1) 
join c on (c.key = b.key1)

上述sql语句中两个join操作的关联字段均为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)

上述sql语句中的两个join操作关联字段各不相同，则该语句的两个join操作需要各自通过一个Common Join任务实现，也就是通过两个Map Reduce任务实现。

Map Join

Map Join算法可以通过两个只有map阶段的Job完成一个join操作。其适用场景为大表join小表。若某join操作满足要求，则第一个Job会读取小表数据，将其制作为hash table，并上传至Hadoop分布式缓存（本质上是上传至HDFS）。第二个Job会先从分布式缓存中读取小表数据，并缓存在Map Task的内存中，然后扫描大表数据，这样在map端即可完成关联操作。如下图所示：
在这里插入图片描述
Map Join有两种触发方式，一种是用户在SQL语句中增加hint提示，另外一种是Hive优化器根据参与join表的数据量大小，自动触发。

（1）Hint提示

用户可通过如下方式，指定通过map join算法，并且ta将作为map join中的小表。这种方式已经过时，不推荐使用。

select /*+ mapjoin(ta) */
    ta.id,
    tb.id
from table_a ta
join table_b tb
on ta.id=tb.id;

（2）自动触发

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任务。最终具体采用哪个计划，是在运行时决定的。大致思路如下图所示：
在这里插入图片描述

Map join自动转换的具体判断逻辑如下图所示：
在这里插入图片描述

图中涉及到的参数如下：
–启动Map Join自动转换

set hive.auto.convert.join=true;

–一个Common Join operator转为Map Join operator的判断条件,若该Common Join相关的表中,存在n-1张表的已知大小总和<=该值,则生成一个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;

优化案例

（1）示例SQL

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;

在这里插入图片描述

（2）优化前
上述SQL语句共有三张表进行两次join操作，且两次join操作的关联字段不同。故优化前的执行计划应该包含两个Common Join operator，也就是由两个MapReduce任务实现。执行计划如下图所示：
在这里插入图片描述

(3）优化思路
经分析，参与join的三张表，数据量如下

表名大小
order_detail 1176009934（约1122M）
product_info 25285707（约24M）
province_info 369（约0.36K）

注：可使用如下语句获取表/分区的大小信息

desc formatted table_name partition(partition_col='partition');

在这里插入图片描述

三张表中，product_info和province_info数据量较小，可考虑将其作为小表，进行Map Join优化。
根据前文Common Join任务转Map Join任务的判断逻辑图，可得出以下优化方案：

方案一：

启用Map Join自动转换。

set hive.auto.convert.join=true;

不使用无条件转Map Join。

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作为后备计划，保证计算任务的稳定。调整完的执行计划如下图：
在这里插入图片描述

方案二：

启用Map Join自动转换。

set hive.auto.convert.join=true;

使用无条件转Map Join。

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

调整hive.auto.convert.join.noconditionaltask.size参数，使其大于等于product_info和province_info之和。

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 Task申请的container容器内存大小，否则可能会出现内存溢出等报错。

set  mapreduce.map.memory.mb=2048

在这里插入图片描述

方案三：

启用Map Join自动转换。

set hive.auto.convert.join=true;

使用无条件转Map Join。

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

调整hive.auto.convert.join.noconditionaltask.size参数，使其等于product_info。

set hive.auto.convert.join.noconditionaltask.size=25285707;

这样可直接将两个Common Join operator转为Map Join operator，但不会将两个Map Join的任务合并。该方案计算效率比方案二低，但需要的内存也更少。
调整完的执行计划如下图：
在这里插入图片描述

Bucket Map Join

Bucket Map Join是对Map Join算法的改进，其打破了Map Join只适用于大表join小表的限制，可用于大表join大表的场景。
Bucket Map Join的核心思想是：若能保证参与join的表均为分桶表，且关联字段为分桶字段，且其中一张表的分桶数量是另外一张表分桶数量的整数倍，就能保证参与join的两张表的分桶之间具有明确的关联关系，所以就可以在两表的分桶间进行Map Join操作了。这样一来，第二个Job的Map端就无需再缓存小表的全表数据了，而只需缓存其所需的分桶即可。其原理如图所示：

优化说明

Bucket Map Join不支持自动转换，发须通过用户在SQL语句中提供如下Hint提示，并配置如下相关参数，方可使用。
1）Hint提示

hive (default)> 
select /*+ mapjoin(ta) */
    ta.id,
    tb.id
from table_a ta
join table_b tb on ta.id=tb.id;

2）相关参数
–关闭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;

优化案例

1）示例SQL

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;

在这里插入图片描述

2）优化前
上述SQL语句共有两张表一次join操作，故优化前的执行计划应包含一个Common Join任务，通过一个MapReduce Job实现。执行计划如下图所示：
在这里插入图片描述

3）优化思路
经分析，参与join的两张表，数据量如下。

表名	大小
order_detail	1176009934（约1122M）
payment_detail	334198480（约319M）

两张表都相对较大，若采用普通的Map Join算法，则Map端需要较多的内存来缓存数据，当然可以选择为Map段分配更多的内存，来保证任务运行成功。但是，Map端的内存不可能无上限的分配，所以当参与Join的表数据量均过大时，就可以考虑采用Bucket Map Join算法。下面演示如何使用Bucket Map Join。
首先需要依据源表创建两个分桶表，order_detail建议分16个bucket，payment_detail建议分8个bucket,注意分桶个数的倍数关系以及分桶字段。
–订单表

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_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_bucketed
select
    id,
    order_detail_id,
    user_id,
    payment_time,
    total_amount
from payment_detail
where dt='2020-06-14';

然后设置以下参数：
–关闭cbo优化，cbo会导致hint信息被忽略，需将如下参数修改为false

set hive.cbo.enable=false;

–map join hint默认会被忽略(因为已经过时)，需将如下参数修改为false

set hive.ignore.mapjoin.hint=false;

–启用bucket map join优化功能,默认不启用，需将如下参数修改为true

set hive.optimize.bucketmapjoin = true;

最后在重写SQL语句，如下：

select /*+ mapjoin(payment_detail_bucketed) */
    *
from order_detail_bucketed od
         join payment_detail_bucketed pd on od.id = pd.order_detail_id;

注意：mapjoin里面不要写别名，不然可能会报错。

在这里插入图片描述

优化后的执行计划如图所示：
在这里插入图片描述

需要注意的是，Bucket Map Join的执行计划的基本信息和普通的Map Join无异，若想看到差异，可执行如下语句，查看执行计划的详细信息。详细执行计划中，如在Map Join Operator中看到 “BucketMapJoin: true”，则表明使用的Join算法为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;

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即可。

优化说明

Sort Merge Bucket Map Join有两种触发方式，包括Hint提示和自动转换。Hint提示已过时，不推荐使用。下面是自动转换的相关参数：
–启动Sort Merge Bucket Map Join优化

set hive.optimize.bucketmapjoin.sortedmerge=true;

–使用自动转换SMB Join

set hive.auto.convert.sortmerge.join=true;

优化案例

1）示例SQL语句

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;

2）优化前
上述SQL语句共有两张表一次join操作，故优化前的执行计划应包含一个Common Join任务，通过一个MapReduce Job实现。
3）优化思路
经分析，参与join的两张表，数据量如下

表名	大小
order_detail	1176009934（约1122M）
payment_detail	334198480（约319M）

两张表都相对较大，除了可以考虑采用Bucket Map Join算法，还可以考虑SMB Join。相较于Bucket Map Join，SMB Map Join对分桶大小是没有要求的。下面演示如何使用SMB Map Join。
首先需要依据源表创建两个的有序的分桶表，order_detail建议分16个bucket，payment_detail建议分8个bucket,

注意分桶个数的倍数关系以及分桶字段和排序字段(一张表的分桶数量是另外一张表分桶数量的整数倍，分桶字段和排序字段必须是JOIN关联字段)

订单表

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 8 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 4 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';

然后设置以下参数：
–启动Sort Merge Bucket Map Join优化

set hive.optimize.bucketmapjoin.sortedmerge=true;

–使用自动转换SMB Join

set hive.auto.convert.sortmerge.join=true;

–加大map端容器内存

set  mapreduce.map.memory.mb=2048;

最后在重写SQL语句，如下：

select
    *
from order_detail_sorted_bucketed od
join payment_detail_sorted_bucketed pd
on od.id = pd.order_detail_id;

优化后的执行计如图所示：
在这里插入图片描述

看到执行过程我们发现只用到Map完成了join没有用到reduce，所以测试的开始加大map端内存，否则会应为内存不足导致失败。

Appreciate(欣赏)

关注

28
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
hive企业级调优策略之Join优化

Hive拥有多种join算法，包括Common Join，Map Join，Bucket Map Join，Sort Merge Buckt Map Join等
复制链接

扫一扫

专栏目录

hive企业级调优策略之Join优化

测试所用到的数据参考：

Join算法概述

Common Join

Map Join

（1）Hint提示

（2）自动触发

优化案例

方案一：

方案二：

方案三：

Bucket Map Join

优化说明

优化案例

注意：mapjoin里面不要写别名，不然可能会报错。

Sort Merge Bucket Map Join

优化说明

优化案例

“相关推荐”对你有帮助么？