1、join无关的优化
Hive SQL的性能问题大部分都和join相关,对于和join无关的问题主要有group by 相关的倾斜和count distinct相关的优化
-
group by引起的倾斜优化:
-
group by引起的倾斜主要是输入数据行按照group by列分布不均匀引起的,因此导致部分Reduce Task分布的数据过多,从而导致数据倾斜。
-
对于group by引起的倾斜,优化方法是开启负载均衡,只需设置下面两个参数即可:
set hive.map.aggr = true set hive.groupby.skewindata=true
此时生成的查询计划会有两个MapReduce Job。第一个MapReduce Job中,Map输出的结果集合会随机分配到Reduce中,每个Reduce做部分聚合操作并输出结果,这样处理的结果是相同的GroupBy Key有可能被分布到不同的Reduce中,从而达到负载均衡的目的。第二个MapReduce Job再根据预处理的数据结果按照GroupBy Key分布到Reduce中,这个过程可以保证相同的GroupBy Key被分布到同一个Reduce中,最后完成最终的聚合操作。
-
-
count distinct优化:
-
在Hive开发过程中,应该小心使用count distinct,因为很容易引起性能问题,比如下面的SQL:
select count(distinct user) from some_table;
由于必须去重,因此Hive会将Map阶段的输出全部分布到一个Reduce Task上,此时很容易引起性能问题。对于这种情况,可以通过先group by再count的方式来优化,优化后的SQL如下:
select count(*) from ( select user from some table group by user ) tmp;
其原理为:利用group by去重,再统计group by的行数目。
-
2、大表join小表优化
大表join小表的优化可以通过mapjoin来解决。
假如供应商会进行评级,比如(五星、四星、 两星、 三星、两星、一星),此时业务人员希望能够分析各供应商星级的每天销售情况及其占比。开发人员一般会写出如下SQL:
select
Seller star
,count(order id) as order 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;
通常来说,供应商是有限的,而销售明细事实表比较大,这就是典型的大表join小表的问题,可以通过mapjoin的方式来优化,只需要添加mapjoin hint即可,优化后的SQL如下:
select /*+mapjoin(b)*/
Seller star
,count(order id) as order 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 hint,如果需要mapjoin多个表,则格式为/*+mapjoin(b, c, d)*/。Hive对于mapjoin是开启的,设置参数为:
set hive.auto.convert.join=ture;
mapjoin优化是在Map阶段进行join,而不是像通常那样在Reduce阶段按照join列进行分发后在每个Reduce任务节点上进行join,不需要分发也就没有倾斜的问题了,但是Hive会将小表sql指定的列全量复制到每个Map任务节点,然后每个Map任务节点执行lookup小表即可,因此小表不能特别大,否则全量复制分发得不偿失。实际上Hive根据参数hive.mapjoin.smalltable.filesize
( ( 0.11.0 版本后是 hive.auto.convert.join.noconditionaltask.size
)来确定表的大小是否满足条件(默认25MB),实际中此参数值可以修改,但是一般最大不能超过1GB,太大的话Map任务所在的节点内存会撑爆,Hive会报错。而且HDFS显示的文件大小是压缩后的大小,当实际加载到内存的时候,容量会增大很多,很多场景下可能会膨胀10倍。
3、大表join大表优化
如果小表超过了1GB的限制,就无法直接使用mapjoin来解决了,例如下面的SQL:
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的时候会按照Seller_id进行分发,table_A的大卖家引起了数据倾斜。但是本数据倾斜问题无法用mapjoin table_B解决,因为卖家有超过千万条,文件大小有几个GB,超过了mapjoin表最大1GB的限制。
方案1:转化为map join
尽管B表无法直接map join,但是可以通过限制行和限制列的方法来间接对B表进行mapjoin。
- 限制行:限制行的思路是不需要join B全表,而只需要join其在A表中存在的数据,对于本问题而言,就是过滤掉90天内没有成交的卖家。
- 限制列:限制列的思路是只取需要的字段。
加上行列限制后,检查过滤后的B表是否满足了Hive mapjoin的条件(即表的大小小于1GB),如果能够满足,那么添加过滤条件生成一个临时B表,然后mapjoin该表即可,其SQL如下:
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 bO.seller_id,bO.s_level
from table_B bO
join
(select seller_id from table_A group by seller_id) aO
on bO.seller_id=aO.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对此已经进行了优化,只需要设置参数skewinfo和skew join参数,不需要修改SQL代码,例如,由于table_b的值“0”和“1”引起了倾斜,只需作如下设置:
set hive .optimize. skewinfo=table B: (seller id) [("0")("1")];
set hive.optimize.skewjoin=true;
但是方案2也无法解决本问题场景的倾斜问题,因为倾斜的卖家大量存在而且动态变化。
方案3:倍数B表,再取模join
-
通用方案:
这种方案的思路是建立一个numbers表,其值只有一列int行,比如从1到10(具体值可根据倾斜程度确定),然后放大B表10倍,再取模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_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 members ) b on a.seller_id=b.seller_id and mod(a.pay_cnt_ 90d,10)+l=b.number ) m group by m.buyer_id
此思路的核心在于:既然按照seller_id分发会倾斜,那么再人工增加一列进行分发,这样之前倾斜的值的倾斜程度会减少为原来的1/10。可以通过配置numbers表修改放大倍数来降低倾斜程度,但这样做的一个弊端是B表也会膨胀N倍。
-
专用方案:
通用方案的思路把B表的每条数据都放大了相同的倍数,实际上这是不需要的,只需要把大卖家放大倍数即可:
- 首先需要知道大卖家的名单,即先建立一个临时表动态存放每日最新的大卖家(比如dim_big_seller),同时此表的大卖家要膨胀预先设定的倍数(比如1000倍)。
- 然后在A表和B表中分别新建一个join列,其逻辑为:如果是大卖家,那么concat一个随机分配正整数(0到预定义的倍数之间,本例为0~1000);如果不是,则保持不变。
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_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 /*mapjoin(big)*/ buyer_id,seller_id,pay_cnt_90d, if(big.seller_id is not null,concat(table_A.seller_id,'rnd',cast (rand() *1000 as bigint),table_A.seller_id) as seller_id_joinkey from table_A left outer join -- big seller 有重复,请注意一定要 group by 后再 join,保证 table_A 的行数保持不变 (select seller_id from dim_big_seller group by seller_ id) big on table_A.seller_ id=big.seller_id ) a join ( select /*+mapjoin(big))*/ seller_id,s_level, --big 表的 seller id_joinkey 生成逻辑和上面的生成逻辑一样 coalesce(seller_id_ joinkey,table_B.seller_id) as seller_ id_ joinkey from table_B left outer join --table_B join 大卖家表后大卖家行数放大 1000 ,其他卖家行数保持不变 (select seller_id, seller_ id_joinkey frαm dim_big_ seller) big on table_B.seller_ id=big.seller_ id ) b on a.seller_id_joinkey=b.seller_id_joinkey ) m group by m.buyer_id
相比通用方案,专用方案的运行效率明显好了很多,因为只是将B表中大卖家的行数放大了1000倍,其他卖家的行数保持不变,但同时也可以看到代码也复杂了很多,而且必须首先建立大卖家表。
方案4:动态一分为二
- 对于mapjoin不能解决的问题,终极解决方案就是动态一分为二,即对倾斜的键值和不倾斜的键值分开处理,不倾斜的正常join即可,倾斜的把它们找出来然后做mapjoin,最后union all其结果即可。
- 但是此中解决方案比较麻烦,代码会变得复杂而且需要一个临时表存放倾斜的键值。
--由于数据倾斜,先找出近 90 天买家数超过 10000 的卖家
insert overwrite table tmp_table_B
select
m.seller_id
n.s_level,
from(
select
seller_id
from(
select
seller_id,
count(buyer_id) as byr_cnt
from
table_A
group by
seller_id
) a
where
a.byr_cnt>10000
) m
left outer join(
select
user_id,
s_level,
from table_B
) n
on m.seller_id=n.user_id;
--对于 90 天买家数超过 10000 的卖家直接 map join ,对于其他卖家正常 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_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 seller_id,a.s_level
from table_A a
left outer join tmp_table_B b
on a.user_id=b.seller_id
where b.seller_id is null
) b
on a.seller_id=b.seller_id
union all
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 seller_id,s_level
from table_B
) b
on a.seller_id=b.seller_id
) m group by m.buyer_id
) m
group by m.buyer_id
总结:
- 方案1、2以及方案3中的通用方案不能保证解决大表join大表问题,因为它们都存在种种不同的限制和特定的使用场景。
- 方案3的专用方案和方案4是较为推荐的优化方案,但是它们都需要新建一个临时表来存放每日动态变化的大卖家。这两种方案的具体区别如下
- 方案3的专用方案:不需要对代码框架进行修改,但是B表会被放大,所以一定要是维度表,不然统计结果会是错误的。
- 方案4:这种解决方案最通用,自由度最高,但是对代码的更改也最大,甚至需要更改代码框架,可最为终极方案来使用。