Hive优化思路

在做Shuffle阶段的优化过程中，遇到了数据倾斜的问题，造成了对一些情况下优化效果不明显。主要是因为在Job完成后的所得到的Counters是整个Job的总和，优化是基于这些Counters得出的平均值，而由于数据倾斜的原因造成map处理数据量的差异过大，使得这些平均值能代表的价值降低。Hive的执行是分阶段的，map处理数据量的差异取决于上一个stage的reduce输出，所以如何将数据均匀的分配到各个reduce中，就是解决数据倾斜的根本所在。规避错误来更好的运行比解决错误更高效。在查看了一些资料后，总结如下。

1数据倾斜的原因
1.1操作：
关键词

情形

后果

Join

其中一个表较小，

但是key集中

分发到某一个或几个Reduce上的数据远高于平均值

大表与大表，但是分桶的判断字段0值或空值过多

这些空值都由一个reduce处理，灰常慢

group by

group by 维度过小，

某值的数量过多

处理某值的reduce灰常耗时

Count Distinct

某特殊值过多

处理此特殊值的reduce耗时

1.2原因：
1)、key分布不均匀

2)、业务数据本身的特性

3)、建表时考虑不周

4)、某些SQL语句本身就有数据倾斜

1.3表现：
任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。

单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最长时长远大于平均时长。

2数据倾斜的解决方案
2.1参数调节：
hive.map.aggr=true

Map 端部分聚合，相当于Combiner

hive.groupby.skewindata=true

有数据倾斜的时候进行负载均衡，当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

2.2 SQL语句调节：
如何Join：

关于驱动表的选取，选用join key分布最均匀的表作为驱动表

做好列裁剪和filter操作，以达到两表做join的时候，数据量相对变小的效果。

大小表Join：

使用map join让小的维度表（1000条以下的记录条数） 先进内存。在map端完成reduce.

大表Join大表：

把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。

count distinct大量相同特殊值

count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union。

group by维度过小：

采用sum() group by的方式来替换count(distinct)完成计算。

特殊情况特殊处理：

在业务逻辑优化效果的不大情况下，有些时候是可以将倾斜的数据单独拿出来处理。最后union回去。

3典型的业务场景
3.1空值产生的数据倾斜
场景：如日志中，常会有信息丢失的问题，比如日志中的 user_id，如果取其中的 user_id 和 用户表中的user_id 关联，会碰到数据倾斜的问题。

解决方法1： user_id为空的不参与关联（红色字体为修改后）

select * from log a
join users b
on a.user_id is not null
and a.user_id = b.user_id
union all
select * from log a
where a.user_id is null;
解决方法2 ：赋与空值分新的key值

select *
from log a
left outer join users b
on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;
结论：方法2比方法1效率更好，不但io少了，而且作业数也少了。解决方法1中 log读取两次，jobs是2。解决方法2 job数是1 。这个优化适合无效 id (比如 -99 , ’’, null 等) 产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。

3.2不同数据类型关联产生数据倾斜
场景：用户表中user_id字段为int，log表中user_id字段既有string类型也有int类型。当按照user_id进行两个表的Join操作时，默认的Hash操作会按int型的id来进行分配，这样会导致所有string类型id的记录都分配到一个Reducer中。

解决方法：把数字类型转换成字符串类型

select * from users a
left outer join logs b
on a.usr_id = cast(b.user_id as string)
3.3小表不小不大，怎么用 map join 解决倾斜问题
使用 map join 解决小表(记录数少)关联大表的数据倾斜问题，这个方法使用的频率非常高，但如果小表很大，大到map join会出现bug或异常，这时就需要特别的处理。 以下例子:

select * from log a
left outer join users b
on a.user_id = b.user_id;
users 表有 600w+ 的记录，把 users 分发到所有的 map 上也是个不小的开销，而且 map join 不支持这么大的小表。如果用普通的 join，又会碰到数据倾斜的问题。

解决方法：

select /+mapjoin(x)/* from log a
left outer join (
select /+mapjoin©/d.*
from ( select distinct user_id from log ) c
join users d
on c.user_id = d.user_id
) x
on a.user_id = b.user_id;
假如，log里user_id有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。

3.4大表join大表，业务场景如何优化
用户轨迹工程的性能瓶颈一直是etract_track_info，其中耗时大户主要在于trackinfo与pm_info进行左关联的环节，trackinfo与pm_info两张表均为GB级别，左关联代码块如下：
from trackinfo a
left outer join pm_info b
on (a.ext_field7 = b.id)
使用以上代码块需要耗时1.5小时。

优化流程
第一次优化
考虑到pm_info表的id是bigint类型，trackinfo表的ext_field7是string类型，其关联时数据类型不一致，默认的hash操作会按bigint型的id进行分配，这样会导致所有string类型的ext_field7集中到一个reduce里面，因此，改为如下：
from trackinfo a
left outer join pm_info b
on (cast(a.ext_field7as bigint) = b.id)
改动为上面代码后，效果仍然不理想，耗时为1.5小时。
第二次优化
考虑到trackinfo表的ext_field7字段缺失率很高（为空、字段长度为零、字段填充了非整数）情况，做进行左关联时空字段的关联操作实际上没有意义，因此，如果左表关联字段ext_field7为无效字段，则不需要关联，因此，改为如下：
from trackinfo a
left outer join pm_info b
on (a.ext_field7 is not null
and length(a.ext_field7) > 0
and a.ext_field7 rlike’1+′ a n d a . e x t f i e l d 7 = b . i d ) 上 面 代 码 块 的 作 用 是 ， 如 果 左 表 关 联 字 段 e x t f i e l d 7 为 无 效 字 段 时 （ 为 空 、 字 段 长 度 为 零 、 字 段 填 充 了 非 整 数 ） ， 不 去 关 联 右 表 ， 由 于 空 字 段 左 关 联 以 后 取 到 的 右 表 字 段 仍 然 为 n u l l ， 所 以 不 会 影 响 结 果 。 改 动 为 上 面 代 码 后 ， 效 果 仍 然 不 理 想 ， 耗 时 为 50 分 钟 。 第 三 次 优 化 想 了 很 久 ， 第 二 次 优 化 效 果 效 果 不 理 想 的 原 因 ， 其 实 是 在 左 关 联 中 ， 虽 然 设 置 了 左 表 关 联 字 段 为 空 不 去 关 联 右 表 ， 但 是 这 样 做 ， 左 表 中 未 关 联 的 记 录 （ e x t f i e l d 7 为 空 ） 将 会 全 部 聚 集 在 一 个 r e d u c e 中 进 行 处 理 ， 体 现 为 r e d u c e 进 度 长 时 间 处 在 99 换 一 种 思 路 ， 解 决 办 法 的 突 破 点 就 在 于 如 何 把 左 表 的 未 关 联 记 录 的 k e y 尽 可 能 打 散 ， 因 此 可 以 这 么 做 ： 若 左 表 关 联 字 段 无 效 （ 为 空 、 字 段 长 度 为 零 、 字 段 填 充 了 非 整 数 ） ， 则 在 关 联 前 将 左 表 关 联 字 段 设 置 为 一 个 随 机 数 ， 再 去 关 联 右 表 ， 这 么 做 的 目 的 是 即 使 是 左 表 的 未 关 联 记 录 ， 它 的 k e y 也 分 布 得 十 分 均 匀 f r o m t r a c k i n f o a l e f t o u t e r j o i n p m i n f o b o n ( c a s e w h e n ( a . e x t f i e l d 7 i s n o t n u l l a n d l e n g t h ( a . e x t f i e l d 7 ) > 0 a n d a . e x t f i e l d 7 r l i k e ′ [ 0 − 9 ] + ' and a.ext_field7 = b.id) 上面代码块的作用是，如果左表关联字段ext_field7为无效字段时（为空、字段长度为零、字段填充了非整数），不去关联右表，由于空字段左关联以后取到的右表字段仍然为null，所以不会影响结果。 改动为上面代码后，效果仍然不理想，耗时为50分钟。 第三次优化 想了很久，第二次优化效果效果不理想的原因，其实是在左关联中，虽然设置了左表关联字段为空不去关联右表，但是这样做，左表中未关联的记录（ext_field7为空）将会全部聚集在一个reduce中进行处理，体现为reduce进度长时间处在99%。 换一种思路，解决办法的突破点就在于如何把左表的未关联记录的key尽可能打散，因此可以这么做：若左表关联字段无效（为空、字段长度为零、字段填充了非整数），则在关联前将左表关联字段设置为一个随机数，再去关联右表，这么做的目的是即使是左表的未关联记录，它的key也分布得十分均匀 from trackinfo a left outer join pm_info b on ( casewhen (a.ext_field7 is not null andlength(a.ext_field7) > 0 anda.ext_field7 rlike '^[0-9]+′anda.extf​ield7=b.id)上面代码块的作用是，如果左表关联字段extf​ield7为无效字段时（为空、字段长度为零、字段填充了非整数），不去关联右表，由于空字段左关联以后取到的右表字段仍然为null，所以不会影响结果。改动为上面代码后，效果仍然不理想，耗时为50分钟。第三次优化想了很久，第二次优化效果效果不理想的原因，其实是在左关联中，虽然设置了左表关联字段为空不去关联右表，但是这样做，左表中未关联的记录（extf​ield7为空）将会全部聚集在一个reduce中进行处理，体现为reduce进度长时间处在99换一种思路，解决办法的突破点就在于如何把左表的未关联记录的key尽可能打散，因此可以这么做：若左表关联字段无效（为空、字段长度为零、字段填充了非整数），则在关联前将左表关联字段设置为一个随机数，再去关联右表，这么做的目的是即使是左表的未关联记录，它的key也分布得十分均匀fromtrackinfoaleftouterjoinpmi​nfobon(casewhen(a.extf​ield7isnotnullandlength(a.extf​ield7)>0anda.extf​ield7rlike′[0−9]+’)
then
cast(a.ext_field7as bigint)
else
cast(ceiling(rand() * -65535)as bigint)
end= b.id
)
第三次改动后，耗时从50分钟降为了1分钟32秒，效果显著！
4总结
使map的输出数据更均匀的分布到reduce中去，是我们的最终目标。由于Hash算法的局限性，按key Hash会或多或少的造成数据倾斜。大量经验表明数据倾斜的原因是人为的建表疏忽或业务逻辑可以规避的。在此给出较为通用的步骤：

1、采样log表，哪些user_id比较倾斜，得到一个结果表tmp1。由于对计算框架来说，所有的数据过来，他都是不知道数据分布情况的，所以采样是并不可少的。

2、数据的分布符合社会学统计规则，贫富不均。倾斜的key不会太多，就像一个社会的富人不多，奇特的人不多一样。所以tmp1记录数会很少。把tmp1和users做map join生成tmp2,把tmp2读到distribute file cache。这是一个map过程。

3、map读入users和log，假如记录来自log,则检查user_id是否在tmp2里，如果是，输出到本地文件a,否则生成<user_id,value>的key,value对，假如记录来自member,生成<user_id,value>的key,value对，进入reduce阶段。

4、最终把a文件，把Stage3 reduce阶段输出的文件合并起写到hdfs。

如果确认业务需要这样倾斜的逻辑，考虑以下的优化方案：

1、对于join，在判断小表不大于1G的情况下，使用map join

2、对于group by或distinct，设定 hive.groupby.skewindata=true

3、尽量使用上述的SQL语句调节进行优化

四，hive数据倾斜的简单处理
? 数据倾斜症状：
任务长时间维持在99%（或100%）;
查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成；
本地读写数据量很大。
? 原因：
key分布不均匀；
业务数据本身特点。
? 导致数据倾斜的操作：
GROUP BY, COUNT DISTINCT()，join

这里列出几个常用解决办法：

参数hive.groupby.skewindata = true
似乎是解决数据倾斜的万能钥匙，查询计划会有两个 MR Job，第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

使用COUNT DISTINCT造成的数据倾斜

如果某一个值的记录特别多，可以先把该值过滤掉，在最后单独处理。比如某一天的IMEI值为’lxw1234’的特别多，当我要统计总的IMEI数，可以先统计不为’lxw1234’的，之后再加1。
SELECT CAST ( COUNT ( DISTINCT imei)+1 AS bigint )
FROM lxw1234 where pt = ‘2012-05-28’
AND imei <> ‘lxw1234’ ;
数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：

SELECT day , COUNT ( DISTINCT id) AS uv
FROM lxw1234
GROUP BY day ;
–可以转换成：
SELECT day , COUNT (id) AS uv
FROM ( SELECT day ,id FROM lxw1234 GROUP BY day ,id) a
GROUP BY day ;
虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。

使用JOIN引起的数据倾斜

表连接顺序优化：
多表连接时，尽量小表在前，大表在后。因为JOIN前一阶段生成的数据会存在于Reducer的buffer中，小表数据量小，内存开销就小，与后面的大表进行连接时，只需要从buffer中读取缓存的Key，与大表中的指定Key进行连接，速度会更快，也可能避免内存缓冲区溢出。

where优化：
Join出现在WHERR子句之前。因此如果想在join前进行过滤，限制条件应该出现在JOIN子句中，而不是where中：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b
ON (a. key =b. key )
WHERE a.ds= ‘2009-07-07’ AND b.ds= ‘2009-07-07’ ;
//当该左外连接在a中发现 key 而在b中没有发现 key 时，b中的列将为 null ，包括分区列ds，后边的 where 就没有用了。
//下面的语句将会提前根据条件过滤：
SELECT a.val, b.val FROM a LEFT OUTER JOIN b
ON (a. key =b. key AND b.ds= ‘2009-07-07’ AND a.ds= ‘2009-07-07’ );
关联键存在大量空值：
在SQL标准中，任何对NULL的操作(如数值比较，字符串操作等)结果都为NULL。Hive对NULL值的处理与其基本一致，除了JOIN时的特殊逻辑：Hive的JOIN中作为JOIN Key的字段比较，NULL=NULL是有意义的，且返回值为True。
所以需要改写查询手动过滤NULL值的情况，操作如下：

SELECT user .uid, count ( user .uid) FROM class JOIN user
ON (class.uid = user .uid and class.uid IS NOT NULL and user .uid IS NOT NULL )
GROUP BY user .uid;
不同数据类型的字段关联：
转换为同一数据类型之后再做关联。