HIVE优化操作总结

hive数据倾斜优化策略

在做Shuffle阶段的优化过程中，遇到了数据倾斜的问题 ,
优化主要是因为在Job完成后的所得到的Counters是整个Job的总和，优化是基于这些 Counters得出的平均值，
而由于数据倾斜的原因造成map处理数据量的差异过大，使得这些平均值能代表的价值降低。Hive的执行是分阶段 的，
map处理数据量的差异取决于上一个stage的reduce输出，所以如何将数据均匀的分配到各个reduce中，
就是解决数据倾斜的根本所在。

一.数据倾斜

1、常见操作

2、原因：

1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜

3、表现：

任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最长时长远大于平均时长。

二、数据倾斜的解决方案

1、参数调节

1.1 map端聚合或者map-join

1) Map端部分聚合 （mapjoin） 相当于Combiner,

  set hive.map.aggr=true 默认是true。# 提高HiveQL聚合的执行性能。

例子：
select gender,count(1) from user group by gender;

适合场景：groupby_key是不散列，首先对map端进行汇总是有意义的。
不适合场景：
不是所有的聚合都需要这个优化。 groupby_key是用户ID（散列）,一般用户ID没有重复的，
因此map聚合没有太大意义，并且浪费资源。
select user_id,sum(info_fee) from user group by user_id;

补充：
hive.groupby.mapaggr.checkinterval = 100000
Hive.map.aggr.hash.min.reduction=0.5
上面这两个参数控制关掉map聚合的策略

2) Map-side Join：Map端连结

SELECT /*+ MAPJOIN(d) */ s.ymd, s.symbol, s.price_close, d.dividend
FROM stocks s JOIN dividends d ON s.ymd = d.ymd AND s.symbol =d.symbol
WHERE s.symbol ='AAPL';

在Hive v0.7之前，MAPJOIN()会将指定的表，一般是较小的表，加载到内存中，这样整个连结过程会在Map段完成。这样可以避免产生冗余的中间数据（连结产生的中间表）同时也可以免除相应的Reduce操作，进而提高整体性能。

在Hive v0.7之后，需要设置hive.auto.convert.join=true，(默认设置为true) 开启MapJoin功能。
注意：使用默认启动该优化的方式如果出现默名奇妙的BUG(比如MAPJOIN并不起作用),就将以下两个属性置为fase手动使用MAPJOIN标记来启动该优化。

hive.auto.convert.join=false(关闭自动MAPJOIN转换操作)
hive.ignore.mapjoin.hint=false(不忽略MAPJOIN标记)

1.2、distinct 数据去重

set hive.groupby.skewindata =true  默认是false。

场景分析：
一般是在有distinct出现的时候，由于map需要保存所有的user.id，
map聚合开关会自动关掉，导致出现计算不均衡的现象，只有2个reduce做聚合，每个reduce处理100亿条记录。
参数分析：
有数据倾斜的时候进行负载均衡，当选项设定为 true，生成的查询计划会有两个 MR Job。
1）第一个MR的reduce_key是gender+id。因为id是一个随机散列的值，
因此这个MR的reduce计算是很均匀的，reduce完成局部聚合的工作
2）MR1第二个MR完成最终的聚合，统计男女的distinct id值，每个Map只输出两条记录，绝大部分计算量已经在第一个MR完成。

例子1： select gender,count(distinct user_id) from user group by gender;
这个参数设置是true的作用相当于下面的语句：
select gender,count(1) from (select gender,user_id from user group by gender,user_id) t group by gender;
适合场景：
只有在groupby_key不散列，而distinct_key散列的情况下才需要打开这个开关。

所以通常对distinct的数据都会内部做去重，然后外层做count统计，这个和参数的意义是一样的。

1.3、小文件的合并

大量的小文件导致文件数目过多，给HDFS带来压力，对hive处理的效率影响比较大，可以合并map和reduce产生的文件。

set hive.merge.mapfiles = true;是否和并 Map 输出文件，默认为 True
set hive.merge.mapredfiles = true ;是否合并 Reduce 输出文件，默认为 False
set hive.merge.size.per.task = 256*1000*1000;      --合并文件的大小

2、SQL语句调节：

2.1.如何Join：

关于驱动表的选取，选用join key分布最均匀的表作为驱动表。
where的条件写在join里面，使得减少join的数量（经过map端过滤，只输出复合条件的）。

2.2.大小表Join：

select from 小表 join 大表 on…
场景分析：
写sql的时候要注意把小表放在join的左边，原因是在 Join 操作的 Reduce 阶段，
位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生 out of memory 错误的几率。
适合场景：
这种大表和小表通常采用mapjoin的方式来解决这种不均衡的现象。
目前hive是采用/*+ MAPJOIN(gender_config) */提示的方式告诉翻译器把sql翻译成mapjoin

2.3.大表Join大表：

把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。
关联条件：
on (case when nvl(trim(t1.program_id),’’)=’’ or trim(t1.program_id)=’-998’ then concat(‘XX’,rand()) ELSE t1.program_id END) = t2.program_id

2.4.count distinct大量相同特殊值：

select count(distinct（字段名) from 表 //计算不重复的结果条数
等价于
select count(1) from (select 字段1，字段2 from group by 字段1，字段2 ) t group by 字段1

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

2.5、排序

order by 排序，只存在一个reduce，这样效率比较低。
可以用sort by操作,通常结合distribute by使用做reduce分区键
举个栗子：
select id,money,name from table distribute by id sort by money desc;

cluster by 相当于 distribute by id sort by money desc, 但是是默认升序操作

等效于：
select id,money,name from table cluster by id

2.6、hive 的 left semi join代替in操作

Hive 当前没有实现 IN/EXISTS 子查询，可以用 LEFT SEMI JOIN 重写你的子查询语句。
举个栗子：
select emp.id,emp.name from emp where emp.id in (select dept.empid from dept)
可以改写为：
select emp.id,emp.name from emp left semi join dept on (emp.id = dept.id)

1、left semi join 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

2、因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。left semi join有去重功能。

2.7.特殊情况特殊处理：

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

三、典型的业务场景

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(c)*/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不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。

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中的视图

好处1：上面就是减少数据的冗余，方便对数据操作
好处2：数据的安全和保密

举例理解视图：
1、数据库虽然可以存储海量数据，但是在数据表设计上却不可能每种关系创建数据表，例如，对于学生表，存储了学生信息，学生的属性包括学号、姓名、年龄、家庭地址等信息；而学生成绩表只存储了学生学号、科目、成绩等信息。现获得学生姓名和成绩信息，那么就需要创建一个关系，该关系需要包含学生的姓名、科目、成绩。但是为了该关系创建一个新的数据表，并利用实际信息进行填充，以备查询使用，是不合适的，这样会造成了数据库中数据的大量冗余。

视图就是解决这个问题的最佳策略，因此视图可以存储查询定义，一旦使用视图存储了查询定义，就如同存储了一个新的关系，用户就可以直接对视图中所存储的关系进行各种操作，就如同面对的是真实的数据表。

2、一个数据表可能包含很多列，但是这些列的信息，对于不同的角色来说，肯定不是全部公开的，对于员工表来说吧，一个普通的员工只能看见这个员工表中的姓名和年龄这些信息，但是对于高层来说，他们要看见员工表中更多信息，不仅仅是上面的两列还有其他的信息，包括员工的住址和员工的薪资待遇，这个时候都是同一张表，怎么办？视图可以解决呀，首先建立一个视图只有员工的姓名和年龄，再建一个视图包含地址和薪资待遇的信息。这样就可以根据不同的角色分配两个视图的查询权限，与实际表隔离开来。这样就可以提高数据访问的安全性了。

3、创建视图
create view tmp_view as
select * from a join b