hive优化，

和通数据库htsjk.Com2019-09-05 23:03 来源:未知阅读:1602 评论 476 热度5

标签：

hive优化，

hive性能低下的根源
● 表存在很多小文件，造成jobs数比较多，运行效率相对比较低；
● COUNT(DISTINCT)在数据量大的情况下，效率较低；
● 数据倾斜是导致效率大幅降低的主要原因，可以多写几层嵌套，避免倾斜；
● join字段的数据类型不一致，效率低下
优化策略
1. 列裁剪
Hive 在读数据的时候，可以只读取查询中所需要用到的列，而忽略其它列。例如，若有以下查询：
SELECT a,b FROM q WHERE e<10;
在实施此项查询中，Q 表有 5 列（a，b，c，d，e），Hive 只读取查询逻辑中真实需要的 3 列 a、b、e，而忽略列 c，d；这样做节省了读取开销，中间表存储开销和数据整合开销。
2. 分区裁剪
可以在查询的过程中减少不必要的分区。例如，若有以下查询：
SELECT * FROM (SELECTT a1,COUNT(1) FROM T GROUP BY a1) subq WHERE subq.prtn=100; #（多余分区） SELECT * FROM T1 JOIN (SELECT * FROM T2) subq ON (T1.a1=subq.a2) WHERE subq.prtn=100;
查询语句若将“subq.prtn=100”条件放入子查询中更为高效，可以减少读入的分区数目。 Hive 自动执行这种裁剪优化。
3. join优化
在使用写有 Join 操作的查询语句时有一条原则：应该将条目少的表/子查询放在 Join 操作符的左边。
对于一条语句中有多个 Join 的情况，如果 Join 的条件相同，比如查询：
INSERT OVERWRITE TABLE pv_users SELECT pv.pageid, u.age FROM page_view p JOIN user u ON (pv.userid = u.userid) JOIN newuser x ON (u.userid = x.userid);

● 如果 Join 的 key 相同，不管有多少个表，都会则会合并为一个 Map-Reduce
● 一个 Map-Reduce 任务，而不是 ‘n’ 个
● 在做 OUTER JOIN 的时候也是一样
如果 Join 的条件不相同，比如：
INSERT OVERWRITE TABLE pv_users SELECT pv.pageid, u.age FROM page_view p JOIN user u ON (pv.userid = u.userid) JOIN newuser x on (u.age = x.age);
Map-Reduce 的任务数目和 Join 操作的数目是对应的，上述查询和以下查询是等价的：
INSERT OVERWRITE TABLE tmptable SELECT * FROM page_view p JOIN user u ON (pv.userid = u.userid); INSERT OVERWRITE TABLE pv_users SELECT x.pageid, x.age FROM tmptable x JOIN newuser y ON (x.age = y.age);
4. map join
Join 操作在 Map 阶段完成，不再需要Reduce，前提条件是需要的数据在 Map 的过程中可以访问到。
先过滤，再join
源sql：select t1.from( select from table1)t1join( select * from table2)t2 on t1.id = t2.idwhere t1.age > ‘10’ 建议写成： select t1.from( select from table1 where age > ‘10’)t1join( select * from table2)t2 on t1.id = t2.id
5. group by操作
进行GROUP BY操作时需要注意一下几点：
● Map端部分聚合
　　事实上并不是所有的聚合操作都需要在reduce部分进行，很多聚合操作都可以先在Map端进行部分聚合，然后reduce端得出最终结果。
　　这里需要修改的参数为：
　　hive.map.aggr=true（用于设定是否在 map 端进行聚合，默认值为真） hive.groupby.mapaggr.checkinterval=100000（用于设定 map 端进行聚合操作的条目数）
● 有数据倾斜时进行负载均衡
　　此处需要设定 hive.groupby.skewindata，当选项设定为 true 是，生成的查询计划有两个 MapReduce 任务。在第一个 MapReduce 中，map 的输出结果集合会随机分布到 reduce 中，每个 reduce 做部分聚合操作，并输出结果。这样处理的结果是，相同的 Group By Key 有可能分发到不同的 reduce 中，从而达到负载均衡的目的；第二个 MapReduce 任务再根据预处理的数据结果按照 Group By Key 分布到 reduce 中（这个过程可以保证相同的 Group By Key 分布到同一个 reduce 中），最后完成最终的聚合操作。
合并小文件
我们知道文件数目小，容易在文件存储端造成瓶颈，给 HDFS 带来压力，影响处理效率。对此，可以通过合并Map和Reduce的结果文件来消除这样的影响。
　　用于设置合并属性的参数有：
● 是否合并Map输出文件：hive.merge.mapfiles=true（默认值为真）
● 是否合并Reduce 端输出文件：hive.merge.mapredfiles=false（默认值为假）
● 合并文件的大小：hive.merge.size.per.task=256*1000*1000（默认值为 256000000）
无效ID在关联时的数据倾斜问题
问题：日志中常会出现信息丢失，比如每日约为 20 亿的全网日志，其中的 user_id 为主键，在日志收集过程中会丢失，出现主键为 null 的情况，如果取其中的 user_id 和 bmw_users 关联，就会碰到数据倾斜的问题。原因是 Hive 中，主键为 null 值的项会被当做相同的 Key 而分配进同一个计算 Map。
解决方法 1：user_id 为空的不参与关联，子查询过滤 null
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 如下所示：函数过滤 null

SELECT * FROM log a LEFT OUTERJOIN bmw_users b ONCASE WHEN a.user_id IS NULL THEN CONCAT(‘dp_hive’,RAND()) ELSE a.user_id END =b.user_id;

调优结果：原先由于数据倾斜导致运行时长超过 1 小时，解决方法 1 运行每日平均时长 25 分钟，解决方法 2 运行的每日平均时长在 20 分钟左右。优化效果很明显。
　　我们在工作中总结出：解决方法2比解决方法1效果更好，不但IO少了，而且作业数也少了。解决方法1中log读取两次，job 数为2。解决方法2中 job 数是1。这个优化适合无效 id（比如-99、 ‘’，null 等）产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的数据分到不同的Reduce上，从而解决数据倾斜问题。因为空值不参与关联，即使分到不同的 Reduce 上，也不会影响最终的结果。附上 Hadoop 通用关联的实现方法是：关联通过二次排序实现的，关联的列为 partion key，关联的列和表的 tag 组成排序的 group key，根据 pariton key分配Reduce。同一Reduce内根据group key排序。
不同数据类型关联产生的倾斜问题

问题：不同数据类型 id 的关联会产生数据倾斜问题。
　　一张表 s8 的日志，每个商品一条记录，要和商品表关联。但关联却碰到倾斜的问题。 s8 的日志中有 32 为字符串商品 id，也有数值商品 id，日志中类型是 string 的，但商品中的数值 id 是 bigint 的。猜想问题的原因是把 s8 的商品 id 转成数值 id 做 hash 来分配 Reduce，所以字符串 id 的 s8 日志，都到一个 Reduce 上了，解决的方法验证了这个猜测。
　　解决方法：把数据类型转换成字符串类型
调优结果显示：数据表处理由 1 小时 30 分钟经代码调整后可以在 20 分钟内完成。
GROUP BY替代COUNT(DISTINCT)达到优化效果
计算 uv 的时候，经常会用到 COUNT(DISTINCT)，但在数据比较倾斜的时候 COUNT(DISTINCT) 会比较慢。这时可以尝试用 GROUP BY 改写代码计算 uv。
● 原有代码

NSERT OVERWRITE TABLE s_dw_tanx_adzone_uv PARTITION (ds=20120329) SELECT 20120329 AS thedate,adzoneid,COUNT(DISTINCT acookie) AS uv FROM s_ods_log_tanx_pv t WHERE t.ds=20120329 GROUP BY adzoneid

关于COUNT(DISTINCT)的数据倾斜问题不能一概而论，要依数据量情况而定，如果数据量不大的话distinct效率会高，因为用group by后语句有2个SELECT，多了一个job。
优化总结
1. 不怕数据多，就怕数据倾斜。
2. 对jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，没半小时是跑不完的。map reduce作业初始化的时间是比较长的。
3. 对sum，count来说，不存在数据倾斜问题。
4. 对count(distinct ),效率较低，数据量一多，准出问题，如果是多count(distinct )效率更低(时间换空间，空间换时间)。

优化可以从几个方面着手