Hive优化实践

不管是对于流行的分布式数据计算框架（如离线的 MapReduce、流计算 Storm、 迭代内 存计算 Spark），还是分布式计算框架新贵（如 Flink、 Beam），抑或是商业性的大数据解决 方案（如 Teradata 数据库、 EMC Greeplum、 HP Vertica、 Oracle Exadata），“数据量大”从 来都不是问题，因为理论上来说，都可以通过增加并发的节点数来解决。 但是如果数据倾斜或者分布不均了，那么就会是问题。 此时不能简单地通过增加并发 节点数来解决问题，而必须采用针对性的措施和优化方案来解决。 这也正是本章将要讨论的主要内容。 实际上， Hive SQL 的各种优化方法基本都和数据 倾斜密切相关，因此本章首先介绍“数据倾斜”的基本概念，然后在此基础上仔细介绍各 种场景下的 Hive 优化方案。 Hive 的优化分为 join 相关的优化和 join 无关的优化，从项目实际来说， join 相关的优 化占了 Hive 优化的大部分内容，而join 相关的优化又分为 mapjoin 可以解决的 join 优化和 mapjoin 无法解决的 join 优化。

离线数据处理的主要挑战：数据倾斜

在进入具体的 Hive 各个场景优化之前，首先介绍 “数据倾斜”的概念。

实际上，并没有专门针对数据倾斜给出的一个理论定义。 “倾斜”应该来自于统计学里的偏态分布。 所谓偏态分布，即统计数据峰值与平均值不相等的频率分布，根据峰值小于 或大于平均值可分为正偏函数和负偏函数，其偏离的程度可用偏态系数刻画。 数据处理中 的倾斜和此相关，但是含义有着很多不同。 下面着重介绍数据处理中的数据倾斜。

对于分布式数据处理来说，我们希望数据平均分布到每个处理节点。 如果以每个处理 节点为 X轴，每个节点处理的数据为 Y轴，我们希望的柱状图是图 所示的样式。

但是实际上由于业务数据本身的问题或者分布算法的问题，每个节点分配到的数据量 很可能是图 所示的样式。

更极端情况下还可能是图所示的样式。

也就是说，只有待分到最多数据的节点处理完数据，整个数据处理任务才能完成， 此 时分布式的意义就大打折扣。 实际上，即使每个节点分配到的数据量大致相同，数据仍然可能倾斜，比如考虑统计词频的极端问题，如果某个节点分配到的词都是一个词，那么显 然此节点需要的耗时将很长，即使其数据量和其他节点的数据量相同。 Hive 的优化正是采用各种措施和方法对上述场景的倾斜问题进行优化和处理。

Hive 优化

在实际 Hive SQL 开发的过程中， Hive SQL 性能的问题上实际只有一小部分和数据倾 斜相关。 很多时候， Hive SQL 运行得慢是由开发人员对于使用的数据了解不够以及一些不 良的使用习惯引起的。 开发人员需要确定以下几点。
1.需要计算的指标真的需要从数据仓库的公共明细层来自行汇总么？是不是数据公共 层团队开发的公共汇总层已经可以满足自己的需求？对于大众的、 KPI 相关的指标 等通常设计良好的数据仓库公共层肯定已经包含了，直接使用即可。
2.真的需要扫描这么多分区么？比如对于销售明细事务表来说，扫描一年的分区和扫 描一周的分区所带来的计算、 IO 开销完全是两个量级，所耗费的时间肯定也是不同 的。 笔者并不是说不能扫描一年的分区，而是希望开发人员需要仔细考虑业务需求， 尽量不浪费计算和存储资源，毕竟大数据也不是毫无代价的。
3.尽量不要使用 select * from your_table 这样的方式，用到哪些列就指定哪些列，如 select coll, col2 from your_table。 另外， where 条件中也尽量添加过滤条件，以去 掉无关的数据行，从而减少整个 MapReduce 任务中需要处理、 分发的数据量。
4.输入文件不要是大量的小文件。 Hive 的默认 Input Split 是 128MB （可配置），小文件 可先合并成大文件。

在保证了上述几点之后，有的时候发现 Hive SQL 还是要运行很长时间，甚至运行不出 来， 这时就需要真正的 Hive 优化技术了。 下面逐一详细介绍各种场景下的 Hive 优化方法，但是开发人员需要了解自己的 SQL, 并根据执行过程中慢的环节来定位是何种问题，进而采用下述针对性解决方案。

join 无关的优化

Hive SQL 性能问题基本上大部分都和 join 相关，对于和 join 无关的问题主要有 group by 相关的倾斜和 count distinct 相关的优化。

group by 引起的倾斜优化

group by 引起的倾斜主要是输入数据行按照 group by 列分布不均匀引起的，比如， 假设按照供应商对销售明细事实表来统计订单数，那么部分大供应商的订单量显然非常多，而多数供应商的订单量就一般，由于 group by 的时候是按照供应商的 ID 分发到每个 Reduce Task，那么此时分配到大供应商的 Reduce Task 就分配了更多的订单，从而导致数 据倾斜。

对于 group by 引起的倾斜，优化措施非常简单，只需设置下面参数即可：

set hive.map.aggr = true
set hive.groupby.skewindata=true 

此时 Hive 在数据倾斜的时候会进行负载均衡，生成的查询计划会有两个 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 的行数目 。

大表 join 小表优化

和 join 相关的优化主要分为 mapjoin 可以解决的优化 C liP大表 join 小表） 和 mapjoin 无 法解决的优化（即大表 join 大表）。 大表join 小表相对容易解决，大表 join 大表相对复杂和 难以解决，但也不是不可解决的，只是相对比较麻烦而已。 首先介绍大表join 小表优化。 仍以销售明细事实表为例来说明大表join 小表的场景。 假如供应商会进行评级，比如（五星、四星、 三星、 两星、 一星），此时业务人员希望能够分析各供应商星级的每天销售情况及其占比。

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

select 
	Seller_star ,
	count(order_id) as order_cnt from 
(
	Select order_id,seller_id 
	from dwd_sls_act_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 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
会将小表全量复制到每个 Map 任务节点（对于本例是 dim_seller 表，当然仅全量复制 b 表
sql 指定的列），然后每个 Map 任务节点执行 lookup 小表即可 。

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

大表 join 大表优化

如果上述 mapjoin 中小表 dim_seller很大呢？比如超过了 1GB 的大小？这种就是大表
join 大表的问题 。 此类问题相对比较复杂，因此本节首先引人一个具体 的问题场景 ，然后
基于此介绍各种优化方案 。

问题场景

问题场景如下 。
A 表为一个汇总表，汇总的是卖家买家最近 N天交易汇总信息，即对于每个卖家最近
N 天，其每个买家共成交了多少单、总金额是多少，为了专注于本节要解决的问题， N 只取
90 天，汇总值仅取成交单数 。 A 表的字段有 ： buyer_id 、 seller_id 和 pay_cnt_90d 。

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

要获得的结果是每个买家在各个级别卖家的成交比例信息，比如：
某买家 ： 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=0 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 ：转化为 ma时oin

一个很正常的想法是，尽管 B 表无法直接 mapjoin ，但是否可以间接地 mapjoin 它呢？
实际上此思路有两种途径：限制行和限制列 。

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

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

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

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,b0.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 已 对 此进 行了 优化 ， 只 需要 设 置参 数 skewinfo 和 skewjoin 参数，不需要修改
SQL 代码 ， 例如，由 于 table_B 的值 “0”和 “ 1 ”引起了倾斜，只需作如下设置 ：

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

但是方案 2 也无法解决本问题场景的倾斜问题，因为倾斜的卖家大量存在而且动态变化。

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

1. 通用方案

此种方案的思路是建立一个 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)+1=b.number
  ) m
  group by m.buyer_id

此思路的核心在于：既然按照 seller id 分发会倾斜，那么再人工增加一列进行分发，
这样之前倾斜的值的倾斜程度会减为原来的 1/10 。 可以通过配置 nubmers 表修改放大倍数
来降低倾斜程度，但这样做的一个弊端是 B 表也会膨胀 N倍 。

2. 专用方案

通用方案的思路把 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_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=5 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 后再 j oin，保证 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 from 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 ：动态一分为二

实际上方案 2 和 3 都用到了一分为二的思想，但是都不彻底，对于 mapjoin 不能解决的问题，终极解决方案就是动态一分为二 ，即对倾斜的键值和不倾斜的键值分开处理，不倾斜的正常 join 即可，倾斜的把它们找出来然后做 mapjoin ，最后 union all 其结果即可 。

但是此种解决方案比较麻烦，代码会变得复杂而且需要一个临时表存放倾斜的键值。

采用此解决方案的伪代码如下所示：

--由于数据倾斜，先找出近 9 0 天买家数超过 10000 的卖家
insert overwrite table trnp 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>1000
) m
left outer join(
select
    user_id,
    s_level,
from
	table_B
) n
on m.seller i d=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_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=5 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 是本节推荐的优化方案，但是它们都需要新建一个临时
表来存放每日动态变化的大卖家 。 相对方案 4 来说，方案 3 的专用方案不需要对代码框架
进行修改，但是 B 表会被放大，所以一定要是维度表，不然统计结果会是错误的 。

方案 4
的解决方案最通用，自由度最高，但是对代码的更改也最大，甚至需要更改代码框架，可
作为终极方案来使用 。

小节

首先概要介绍了数据倾斜的概念，然
后对 Hive SQL 优化进行了概要性介绍，在此基础上分别介绍了 join 无关的优化场景一－
group by 的倾斜优化和 count distinct 优化，然后重点介绍了 mapjoin 的优化以及 mapjoin 无
法解决的场景的优化。 mapjoin 无法解决的优化共有 5 种方案，实际项目中，用户可以根据
情况选用适合自己的优化方案 。