Hive on Spark调优（大数据技术6）

  文章最前： 我是Octopus，这个名字来源于我的中文名--章鱼；我热爱编程、热爱算法、热爱开源。所有源码在我的个人github ；这博客是记录我学习的点点滴滴，如果您对 Python、Java、AI、算法有兴趣，可以关注我的动态，一起学习，共同进步。

相关文章：

1. Hive on Spark调优（大数据技术1）
2. Hive on Spark调优（大数据技术2） 

3. Hive on Spark调优（大数据技术3）

4. Hive on Spark调优（大数据技术4） 

5. Hive on Spark调优（大数据技术5）

6. Hive on Spark调优（大数据技术6）

7. Hive on Spark调优（大数据技术7）

8. Hive on Spark调优（大数据技术8）

9. Hive on Spark调优（大数据技术9）

---

第6章 Join优化

6.1 Hive Join算法概述

Hive拥有多种join算法，包括common join，map join，sort Merge Bucket Map Join等。下面对每种join算法做简要说明：

1）common join

Map端负责读取参与join的表的数据，并按照关联字段进行分区，将其发送到Reduce端，Reduce端完成最终的关联操作。

2）map join

若参与join的表中，有n-1张表足够小，Map端就会缓存小表全部数据，然后扫描另外一张大表，在Map端完成关联操作。

3）Sort Merge Bucket Map Join

        若参与join的表均为分桶表，且关联字段为分桶字段，且分桶字段是有序的，且大表的分桶数量是小表分桶数量的整数倍。此时，就可以以分桶为单位，为每个Map分配任务了，Map端就无需再缓存小表的全表数据了，而只需缓存其所需的分桶。 

6.2 Map Join优化

示例SQL语句如下：

select
    *
from
(
    select
        *
    from dwd_trade_order_detail_inc
    where dt='2020-06-16'
)fact
left join
(
    select
        *
    from dim_sku_full
    where dt='2020-06-16'
)dim
on fact.sku_id=dim.id;

6.2.1 优化前执行计划

6.2.2 优化思路

上述参与join的两表一大一小，可考虑map join优化。

Map Join相关参数如下：

--启用map join自动转换
set hive.auto.convert.join=true;
--common join转map join小表阈值
set hive.auto.convert.join.noconditionaltask.size

 6.2.3 优化后执行计划

 6.3 Sort Merge Bucket Map Join

6.3.1 优化说明

Sort Merge Bucket Map Join相关参数：

--启动Sort Merge Bucket Map Join优化
set hive.optimize.bucketmapjoin.sortedmerge=true;
--使用自动转换SMB Join
set hive.auto.convert.sortmerge.join=true;

6.3.2 优化案例

1）示例SQL语句

select
    *
from(
    select
        *
from  dim_user_zip  
where dt='9999-12-31'
)duz
join(
    select
        *
    from dwd_trade_order_detail_inc
    where dt='2020-06-16'
)dtodi

on duz.id=dtodi.user_id;

2）优化前

上述SQL语句共有两张表一次join操作，故优化前的执行计划应包含一个Common Join任务，通过一个MapReduce Job实现。

3）优化思路

经分析，参与join的两张表，数据量如下：

表名	大小
dwd_trade_order_detail_inc	162900000000（约160g）
dim_user_zip	12320000000 （约12g）

两张表都相对较大，可以考虑采用SMBSMB Map Join对分桶大小是没有要求的。下面演示如何使用SMB Map Join。

首先需要依据源表创建两个的有序的分桶表，dwd_trade_order_detail_inc建议分36个bucket，dim_user_zip建议分6个bucket,注意分桶个数的倍数关系以及分桶字段和排序字段。

--订单明细表
hive (default)> 
drop table if exists dwd_trade_order_detail_inc_bucketed;
create table dwd_trade_order_detail_inc_bucketed(
id string,
order_id string,
user_id string,
sku_id string,
province_id string,
activity_id string,
activity_rule_id string,
coupon_id string,
date_id string,
create_time string,
source_id string,
source_type_code string,
source_type_name string,
sku_num bigint,
split_original_amount decimal(16,2),
split_activity_amount decimal(16,2),
split_coupon_amount decimal(16,2),
split_total_amount decimal(16,2)
)
clustered by (user_id) sorted by(user_id) into 36 buckets
row format delimited fields terminated by '\t';


--用户表
hive (default)> 
drop table if exists dim_user_zip_bucketed;
create table dim_user_zip_bucketed(
id string,
login_name string,
nick_name string,
name string,
phone_num string,
email string,
user_level string,
birthday string,
gender string,
create_time string,
operate_time string,
start_date string,
end_date string,
dt string
)
clustered by (id) sorted by(id) into 6 buckets
row format delimited fields terminated by '\t';

 然后向两个分桶表导入数据。

--订单明细分桶表
hive (default)> 
insert overwrite table dwd_trade_order_detail_inc_bucketed
select
id ,
order_id ,
user_id ,
sku_id ,
province_id ,
activity_id ,
activity_rule_id ,
coupon_id ,
date_id ,
create_time ,
source_id ,
source_type_code ,
source_type_name ,
sku_num ,
split_original_amount ,
split_activity_amount ,
split_coupon_amount,
split_total_amount
from  dwd_trade_order_detail_inc
where dt='2020-06-16';



--用户分桶表
hive (default)> 
insert overwrite table dim_user_zip_bucketed
select
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date,
dt
from dim_user_zip
where dt='9999-12-31';

然后设置以下参数：

--启动Sort Merge Bucket Map Join优化
set hive.optimize.bucketmapjoin.sortedmerge=true;
--使用自动转换SMB Join
set hive.auto.convert.sortmerge.join=true;

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

hive (default)> 
select
    *
from dwd_trade_order_detail_inc_bucketed od
join dim_user_zip_bucketed duser
on od.user_id = duser.id;

优化后的执行计如图所示：