《离线和实时大数据开发实战》（五）Hive 优化实践_大数据hive离线计算开发实战

最新推荐文章于 2024-05-21 11:40:29 发布

2401_84160361

最新推荐文章于 2024-05-21 11:40:29 发布

阅读量838

点赞数 29

分类专栏：程序员文章标签：大数据 hive hadoop

本文链接：https://blog.csdn.net/2401_84160361/article/details/138332532

版权

程序员专栏收录该内容

186 篇文章 0 订阅

订阅专栏

原理为：先利用 group by 去重，再统计 group by 的行数目。

四、大表 Join 小表优化

join 相关的优化主要分为 mapjoin 可以解决的优化（即大表 join 小表）和 mapjoin 无法解决的优化（即大表 join 大表）。大表 join 小表相对容易解决，大表 join 大表相对复杂和难以解决，但也不是不可解决的，只是相对比较麻烦而已。

首先介绍大表 join 小表优化。仍以销售明细事实表为例来说明大表 join 小表的场景。

假如供应商会进行评级，比如（五星、四星、两星、一星），此时业务人员希望能够分析各供应商星级的每天销售情况及其占比。

开发人员一般会写出如下 SQL:

select Seller_srar, count(order_id) as ordre_cnt
from (
	select order_id,seller_id 
    from dwd_sls_fact_detail_table
    where partition_value ='20170101'
) a
Left outer join(
	select seller_id,seller_star
    from dim_seller
    where partition_value='20170101'
) b
on a.seller_id = b.seller_id
group by b.seller_star;

但正如上述所言，现实世界的二八准则将导致订单集中在部分供应商上，而好的供应商的评级通常会更高，此时更加剧了数据倾斜的程度。如果不加以优化，上述 SQL 将会耗费很长时间，甚至运行不出结果！

通常来说，供应商是有限的，比如上千家、上万家，数据量不会很大，而销售明细事实表比较大，这就是典型的大表 join 小表问题，可以通过 mapjoin 的方式来优化，只需添加 mapjoin hint 即可，优化后的 SQL 如下：

select /\*+mapjoin(b)\*/ Seller_srar, count(order_id) as ordre_cnt
from (
	select order_id,seller_id 
    from dwd_sls_fact_detail_table
    where partition_value ='20170101'
) a
Left outer join(
	select seller_id,seller_star
    from dim_seller
    where partition_value='20170101'
) b
on a.seller_id = b.seller_id
group by b.seller_star;

/*+mapjoin(b)*/ 即 mapjoin himt，如果需要 mapjoin 多个表，则格式为/*+mapjoin(b,c,d)*/ 。

Hive 对于 mapjoin 是默认开启的，设置参数为：

Set hive.auto.convert.join=ture;

mapjoin 优化是在 Map 阶段进行 join ，而不是像通常那样在 Reduce 阶段按照 join 列进行分发后在每个 Reduce 任务节点上进行 join ，不需要分发也就没有倾斜的问题，相反 Hive 会将小表全量复制到每个 Map 任务节点（对于本例是 dim_seller ，当然仅全量复制 b表 sql 指定的列），然后每个 Map 任务节点执行 lookup 小表即可。

从上述分析可以看出，小表不能太大，否则全量复制分发得不偿失。

实际上 Hive 根据参数 hive.mapjoin.smalltable.filesize ( 0.11.0 本后是 hive.auto.convert.join.noconditionaltask.size ）来确定小表的大小是否满足条件（默认 25M）。
实际中此参数值所允许的最大值可以修改，但是一般最大不能超过 1GB （太大的话 Map 任务所在的节点内存会撑爆， Hive 会报错。另外需要注意的是， HDFS 显示的文件大小是压缩后的大小，当实际加载到内存的时候，容量会增大很多，很多场景下可能会膨胀 10 倍）。

五、大表 Join 大表优化

如果上述 mapjoin 中小表 dim_seller 很大呢？比如超过了 1GB 的大小？这种就是大表join 大表的问题。

这类问题相对比较复杂，我们首先引入具体的问题场景，然后基于此介绍各种优化方案。

5.1 问题场景

我们先假设一个问题场景：

A 表为一个汇总表，汇总的是卖家买家最近 N 天交易汇总信息，即对于每个卖家最近 N 天，其每个买家共成交了多少单、总金额是多少，我们这里 N 先只取 90 天，汇总值仅取成交单数。A 表的字段有：buyer_id 、seller_id 和 pay_cnt_90d 。

B 表为卖家基本信息表，其中包含卖家的一个分层评级信息，比如把卖家分为 6 个级别： S0、S1、S2、S3、S4、S5、S6 。

要获得的结果是每个买家在各个级别卖家的成交比例信息，比如：

某买家 S0:10%; S1:20%; S2:20%; S3:10%; S4:20%; S4:10%; S5:10%。

B表的字段有： seller_id 和 s_level。

正如 mapjoin 中的例子一样，我们的第一反应是直接 join 表并统计：

select 
m.buyer_id 
,sum(pay_cnt_90d) as pay_cnt_90d 
,sum(case when m.s_level=O then pay_cnt_90d end) as pay_cnt_90d_s0 
,sum(case when m.s_level=l then pay_cnt_90d end) as pay_cnt_90d_sl 
,sum(case when m.s_level=2 then pay_cnt_90d end) as pay_cnt_90d_s2 
,sum(case when m.s level=3 then pay cnt 90d end) as pay_cnt_90d_s3
,sum(case when m.s_level=4 then pay_cnt_90d end) as pay_cnt_90d_s4 
,sum(case when m.s_level=S then pay_cnt_90d end) as pay_cnt_90d_s5 
from 
(
select 
a.buyer_id,a.seller_id,b.s_level,a.pay_cnt_90d 
from 
(
select buyer_id ,seller_id,pay_cnt_90d 
from table A 
) a 
join 
(
select seller_id,s_level 
from table B 
) b 
on a.seller_id=b.seller_id 
) m 
group by m.buyer_id

但是此 SQL 会引起数据倾斜，原因在于卖家的二八准则。某些卖家 90 天内会有几百万甚至上千万的买家，但是大部分卖家 90 天内的买家数目并不多， join table_A 和table_B 的时候 ODPS 会按照 Seller_id 进行分发， table_A 的大卖家引起了数据倾斜。

但是本数据倾斜问题无法用 mapjoin table_B 解决，因为卖家有超过千万条、文件大小几个GB ，超过了 mapjoin 表最大 1GB 的限制。

方案 1：转化为 mapjoin

大表无法直接mapjoin，那么是否可以间接呢？实际上此思路有两种途径：限制行和限制列。

限制行: 不需要join B全表，只需要join其在A表中存在的。对于本问题场景，就是过滤掉 90 天内没有成交的卖家。
限制列: 只取需要的字段。

select 
m.buyer_id 
,sum(pay_cnt_90d) as pay_cnt_90d 
,sum(case when m.s_level=O then pay_cnt_90d end) as pay_cnt_90d_s0 
,sum(case when m.s_level=l then pay_cnt_90d end) as pay_cnt_90d_sl 
,sum(case when m.s_level=2 then pay_cnt_90d end) as pay_cnt_90d_s2 
,sum(case when m.s level=3 then pay cnt 90d end) as pay_cnt_90d_s3
,sum(case when m.s_level=4 then pay_cnt_90d end) as pay_cnt_90d_s4 
,sum(case when m.s_level=S then pay_cnt_90d end) as pay_cnt_90d_s5 
from 
(
select /\*+mapjoin(b)\*/
a.buyer_id,a.seller_id,b.s_level,a.pay_cnt_90d 
from 
(
select buyer_id ,seller_id,pay_cnt_90d 
from table_A 
) a 
join 
(
select b0.seller id,s_level 
from table_B b0
    join
    (select seller_id from table_A group by seller_id) a0
    on b0.seller_id=a0.seller_id
) b 
on a.seller_id=b.seller_id 
) m 
group by m.buyer_id

此方案在一些情况下可以起作用，但很多时候还是无法解决上述问题，因为大部分卖家尽管 90 买家不多，但还是有一些的，过滤后的 B
表仍然很大。

方案 2：join 时用 case when 语句

应用场景为: 倾斜的值是明确的而且数量很少，比如null值引起的倾斜。

将这些引起倾斜的值随机分发到Reduce，其主要核心逻辑在于 join 时对这些特殊值concat 随机数,从而达到随机分发的目的。核心逻辑如下：

Select a.user_id,a.order_id,b.user_id 
From table_a a 
Join table_b b 
On (case when a.user_id is null then concat ('hive' ,rand()) else a.user_id end)=b.user_id

Hive已对此进行了优化，不需要修改SQL，只需要设置参数；比如 table_B 的值 “0” 和 “1” 引起倾斜，只需要如下设置：

set hive.optimize.skewinfo=table_B:(seller_id)[("0")("1")];
set hive.optimize.skewjoin=true;

但是方案二还是不能解决上述问题，因为倾斜的卖家大量存在而且动态变化。

方案 3：倍数B表，再取模join

通用方案

是建立一个numbers表,其值只有一列int行，比如从1到10(具体根据倾斜程度确定)，然后放大B表10倍，再取模join。

select 
m,buer_id
,sum(pay_cnt_90d) as pay_cnt_90d 
,sum(case when m.s_level=O then pay_cnt_90d end) as pay cnt 90d so 
,sum(case when m.s_level=l then pay cnt 90d end) as pay cnt 90d_sl 
,sum(case when m.s_level=2 then pay_cnt_90d end) as pay_cnt_90d s2 
,sum(case when m.s_level=3 then pay_cnt_90d end) as pay_cnt_90d_s3 
,sum(case when m.s_level=4 then pay_cnt_90d end) as pay cnt 90d s4 
,sum(case when m.s level=S then pay cnt 90d end) as pay cnt 90d s5 
from 
(
select 
a.buyer_id,a.seller_id,b.s_level,a.pay_cnt_90d 
from
(
select buyer_id,seller_id,pay_cnt_90d 
from table_A 
) a 
JOin 
(
select /\*+mapjoin(members)\*/ 
seller_id,s_level,member 
from table_B 
join 
numbers 
) b 
on a.seller_id=b.seller_id 
and mod(a.pay_cnt_90d,10)+1=b.number 
) m 
group by m.buyer_id