hive高级操作（优化，数据倾斜优化）

2019/2/21 星期四

hive高级操作（优化，数据倾斜优化）

专注于为中小企业提供成都网站设计、做网站、成都外贸网站建设公司服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业济水街道免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了上1000家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

分区表/桶表应用，skew，map-join //见hive的基本语法
行列转换

hive 优化
hive 优化思想
Explain 的使用
经典案例(distinct count)

数据倾斜的原因
操作：
关键词 情形 后果
1、Join 其中一个表较小，但是key 集中分发到某一个或几个Reduce 上的数据远高于平均值 ；
2、大表与大表，但是分桶的判断字段0 值或空值过多这些空值都由一个reduce 处理，非常慢；
3、group by group by 维度过小，某值的数量过多处理某值的reduce 非常耗时
4、Count Distinct 某特殊值过多处理此特殊值的reduce 耗时。
原因小结：
1)、key 分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL 语句本身就有数据倾斜

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

数据倾斜的解决方案
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、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 回去。

典型的业务场景 //生产中遇到的问题解决方法总结
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_idunion allselect 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 上,解决数据倾斜问题。

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、小表不小不大，怎么用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 不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。

总结：
//*****使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,否则生成的key,value 对，假如记录来自member,生成的key,value 对，进入reduce 阶段。
4、最终把a 文件，把Stage3 reduce 阶段输出的文件合并起写到hdfs。

如果确认业务需要这样倾斜的逻辑，考虑以下的优化方案：
1、对于join，在判断小表不大于1G 的情况下，使用map join
2、对于group by 或distinct，设定hive.groupby.skewindata=true
3、尽量使用上述的SQL 语句调节进行优化

提示
1、大表与小表JOIN 时，需采用MapJoin。执行MapJoin 时须重点关注JOIN的顺序和过滤后小表的行数。过滤后小表的行数不能太大，通常允许的小表上限为20 万条（仅提供参考），如果超过此界限不建议使用MapJoin。特别的，如果JOIN 的大表极大，Shuffle 的数据极多，考虑到大表与小表的大小的相对性，允许将可接受的小表行数上限提升至100 万条。
2、重点关注Shuffle 和JOIN Stage 的数据量是否过大而使磁盘溢出，如果有请增加Reduce Task数目。必须尽量避免大表与大表直接JOIN，所以执行之前要检查分析一下SQL，如果有小表，先用小表或是过滤率较高的表过滤大表，即尽可能先做与小表有关的JOIN，再使大表参与进来。
3、实在要进行大表Join 大表：把空值的key 变成一个字符串加上随机数，把倾斜的数据分到不同的reduce 上，由于null 值关联不上，处理后并不影响最终结果。

正常的在业务上处理的就是表的join
总结：
1、首先需要对表做好列裁剪，经量让两个表的数据量相对相等，处理后的数据量也变小
2、大小表join： 在map端 join 可以考虑让小的维度表1000条数据先进内存，也可以让小表或者过滤率较高的表过滤大表，即尽可能先做与小表有关的JOIN，再使大表参与进来。
3、大表与大表join：可以把空值的key 变成一个字符串加上随机数，把倾斜的数据分到不同的reduce 上，由于null 值关联不上，处理后并不影响最终结果。也可以把大表进行成小表进行join