hive大表优化

大表join大表优化

如果Hive优化实战2中mapjoin中小表dim_seller很大呢？比如超过了1GB大小？这种就是大表join大表的问题。首先引入一个具体的问题场景，然后基于此介绍各自优化方案。

5.1、问题场景

问题场景如下：

A表为一个汇总表，汇总的是卖家买家最近N天交易汇总信息，即对于每个卖家最近N天，其每个买家共成交了多少单，总金额是多少，假设N取90天，汇总值仅取成交单数。

A表的字段有：buyer_id、seller_id、pay_cnt_90day。

B表为卖家基本信息表，其字段有seller_id、sale_level，其中sale_levels是卖家的一个分层评级信息，比如吧卖家分为6个级别：S0、S1、S2、S3、S4和S5。

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

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

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

select

m.buyer_id,

sum(pay_cnt_90day) as pay_cnt_90day,

sum(case when m.sale_level = 0 then pay_cnt_90day end) as pay_cnt_90day_s0,

sum(case when m.sale_level = 1 then pay_cnt_90day end) as pay_cnt_90day_s1,

sum(case when m.sale_level = 2 then pay_cnt_90day end) as pay_cnt_90day_s2,

sum(case when m.sale_level = 3 then pay_cnt_90day end) as pay_cnt_90day_s3,

sum(case when m.sale_level = 4 then pay_cnt_90day end) as pay_cnt_90day_s4,

sum(case when m.sale_level = 5 then pay_cnt_90day end) as pay_cnt_90day_s5

from (

select a.buer_id, a.seller_id, b.sale_level, a.pay_cnt_90day

from ( select buyer_id, seller_id, pay_cnt_90day from table_A) a

join

(select seller_id, sale_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，超过了1GB的限制。

5.2、优化方案1

一个很正常的想法是，尽管B表无法直接mapjoin, 但是是否可以间接mapjoin它呢？

实际上此思路有两种途径：限制行和限制列。

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

限制列的思路是只取需要的字段。

加上如上的限制后，检查过滤后的B表是否满足了Hive mapjoin的条件，如果能满足，那么添加过滤条件生成一个临时B表，然后mapjoin该表即可。采用此思路的语句如下：

select

m.buyer_id,

sum(pay_cnt_90day) as pay_cnt_90day,

sum(case when m.sale_level = 0 then pay_cnt_90day end) as pay_cnt_90day_s0,

sum(case when m.sale_level = 1 then pay_cnt_90day end) as pay_cnt_90day_s1,

sum(case when m.sale_level = 2 then pay_cnt_90day end) as pay_cnt_90day_s2,

sum(case when m.sale_level = 3 then pay_cnt_90day end) as pay_cnt_90day_s3,

sum(case when m.sale_level = 4 then pay_cnt_90day end) as pay_cnt_90day_s4,

sum(case when m.sale_level = 5 then pa