Hive优化方法

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：这种解决方案最通用，自由度最高，但是对代码的更改也最大，甚至需要更改代码框架，可最为终极方案来使用。