hive调优

优化时，把hive sql当做map reduce程序来读，会有意想不到的惊喜。（当然我们可以在写hive语句的时候，可以看看hive是怎么把sql语句编程mapreduce的）

理解hadoop的核心能力，是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表现：

1.不怕数据多，就怕数据倾斜。

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

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

3.对sum，count来说，不存在数据倾斜问题。

4.对count(distinct ),效率较低，数据量一多，准出问题，如果是多count(distinct )效率更低。

 

2数据倾斜的解决方案

优化可以从几个方面着手：

1. 好的模型设计事半功倍。

2. 解决数据倾斜问题。

3. 减少job数。

4. 设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。

5. 自己动手写sql解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化总是漠视业务，习惯性提供通用的解决方法。 Etl开发人员更了解业务，更了解数据，所以通过业务逻辑解决倾斜的方法往往更精确，更有效。

6. 对count(distinct)采取漠视的方法（就是说不要用了），尤其数据大的时候很容易产生倾斜问题，不抱侥幸心理。自己动手，丰衣足食。

7. 对小文件进行合并，是行至有效的提高调度效率的方法，假如我们的作业设置合理的文件数，对云梯的整体调度效率也会产生积极的影响。

8. 优化时把握整体，单个作业最优不如整体最优。

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典型的业务场景

迁移和优化过程中的案例：

问题1：如日志中，常会有信息丢失的问题，比如全网日志中的user_id，如果取其中的user_id和bmw_users关联，就会碰到数据倾斜的问题。（空值产生的数据倾斜）

方法：解决数据倾斜问题

解决方法1. User_id为空的不参与关联，例如：

Select *

From log a

Join bmw_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个具有相同列及数据类型的 结果 放到一起显示，并且不去重。
select a,b,c from table1

union all
select ca,cb,cc from table2
解决方法2 ：

Select *

from log a

left outer join bmw_users b

on case when a.user_id is null then concat(‘dp_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上 ,解决数据倾斜问题。因为空值不参与关联，即使分到不同的reduce上，也不影响最终的结果。附上hadoop通用关联的实现方法（关联通过二次排序实现的，关联的列为parition key,关联的列c1和表的tag组成排序的group key,根据parition key分配reduce。同一reduce内根据group key排序）。

问题2：不同数据类型id的关联会产生数据倾斜问题。

一张表s8的日志，每个商品一条记录，要和商品表关联。但关联却碰到倾斜的问题。s8的日志中有字符串商品id,也有数字的商品id,类型是string的，但商品中的数字id是bigint的。猜测问题的原因是把s8的商品id转成数字id做hash来分配reduce，所以字符串id的s8日志，都到一个reduce上了，解决的方法验证了这个猜测。

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

Select * from s8_log a

Left outer join r_auction_auctions b

On a.auction_id = cast(b.auction_id as string);

问题3：利用hive 对UNION ALL的优化的特性

hive对union all优化只局限于非嵌套查询。

比如以下的例子：

select * from

(select * from t1

Group by c1,c2,c3

Union all

Select * from t2

Group by c1,c2,c3) t3

Group by c1,c2,c3;

从业务逻辑上说，子查询内的group by 怎么都看显得多余（功能上的多余,除非有count(distinct)），如果不是因为hive bug或者性能上的考量(曾经出现如果不子查询group by ，数据得不到正确的结果的hive bug)。所以这个hive按经验转换成

select * from

(select * from t1

Union all

Select * from t2

) t3

Group by c1,c2,c3;

经过测试，并未出现union all的hive bug,数据是一致的。mr的作业数有3减少到1。

t1相当于一个目录，t2相当于一个目录，那么对map reduce程序来说，t1,t2可以做为map reduce 作业的mutli inputs。那么，这可以通过一个map reduce 来解决这个问题。Hadoop的计算框架，不怕数据多，就怕作业数多。

但如果换成是其他计算平台如oracle，那就不一定了，因为把大的输入拆成两个输入，分别排序汇总后merge(假如两个子排序是并行的话)，是有可能性能更优的（比如希尔排序比冒泡排序的性能更优）。

问题4：比如推广效果表要和商品表关联，效果表中的auction id列既有商品id,也有数字id,和商品表关联得到商品的信息。那么以下的hive sql性能会比较好

Select * from effect a

Join (select auction_id as auction_id from auctions

Union all

Select auction_string_id as auction_id from auctions

) b

On a.auction_id = b.auction_id。

比分别过滤数字id,字符串id然后分别和商品表关联性能要好。

这样写的好处,1个MR作业,商品表只读取一次，推广效果表只读取一次。把这个sql换成MR代码的话，map的时候，把a表的记录打上标签a,商品表记录每读取一条，打上标签b，变成两个<key ,value>对，<b,数字id>，<b,字符串id>。所以商品表的hdfs读只会是一次。

问题5：先join生成临时表，在union all还是写嵌套查询，这是个问题。比如以下例子：

Select *

From (select *

From t1

Uion all

select *

From t4

Union all

Select *

From t2

Join t3

On t2.id = t3.id

) x

Group by c1,c2;

这个会有4个jobs。假如先join生成临时表的话t5,然后union all，会变成2个jobs。

Insert overwrite table t5

Select *

From t2

Join t3

On t2.id = t3.id

;

Select * from (t1 union all t4 union all t5) ;

hive在union all优化上可以做得更智能（把子查询当做临时表），这样可以减少开发人员的负担。出现这个问题的原因应该是union all目前的优化只局限于非嵌套查询。如果写MR程序这一点也不是问题，就是multi inputs。

 

问题6：使用map join解决数据倾斜的常景下小表关联大表的问题，但如果小表很大，怎么解决。这个使用的频率非常高，但如果小表很大，大到map join会出现bug或异常，这时就需要特别的处理。云瑞和玉玑提供了非常给力的解决方案。（小表不小不大，怎么用 map join 解决倾斜问题）以下例子：

Select * from log a

Left outer join members b

On a.memberid = b.memberid.

Members有600w+的记录，把members分发到所有的map上也是个不小的开销，而且map join不支持这么大的小表。如果用普通的join，又会碰到数据倾斜的问题。

问题的意思是：一张表很大，另一张表更大。

解决方法：

Select /*+mapjoin(x)*/* from log a

Left outer join (select /*+mapjoin(c)*/*

From (select distinct memberid from log ) c

Join members d

On c.memberid = d.memberid

)x

On a.memberid = b.memberid。

先根据log取所有的memberid，然后mapjoin 关联members取今天有日志的members的信息，然后在和log做mapjoin。

假如，log里memberid有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。

评价：他解决问题的方法很聪明，先是获取了今天日志中的不重复的会员（这也是根据我们的业务需求的），这样就相当于把我们的表缩小了，这样我们把变得小的表就可以放到内存了。

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语句调节进行优化