hive执行语句优化

70人阅读 评论(0) 收藏 举报
分类:

一、类SQL语句优化

1、跟SQL基本一致的优化原则

1.1 尽量原子化操作

尽量避免一个SQL包含复杂逻辑,可以使用中间表来完成复杂的逻辑。

1.2 尽量尽早地过滤数据

尽量先where筛选后再join,减少每个阶段的数据量,对于分区表要加分区条件,只选择需要使用到的字段。

1.3 表链接时尽量使用相同的连接键

当对3个或者更多个表进行join连接时,如果每个on子句都使用相同的连接键的话,那么只会产生一个MapReduce job。

2、跟SQL不一致的优化原则

2.1 小表联大表

表连接时将大表放后头(即小表联大表,跟传统sql相反)。Hive假定查询中最后的一个表是大表。它会将其它表缓存起来,然后扫描最后那个表。

2.2 用insert into替换union all

如果union all的部分个数大于2,或者每个union部分数据量大,应该拆成多个insert into 语句,实际测试过程中,执行时间能大幅提升。

2.3 尽量将order by替换为sort by

order by : 对查询结果进行全局排序,消耗时间长。需要 set hive.mapred.mode=nostrict
sort by : 局部排序,并非全局有序,提高效率
order by 排序,只存在一个reduce,这样效率比较低。可以用sort by操作,通常结合distribute by使用做reduce分区键

2.4 limit 语句快速出结果优化

一般情况下,Limit语句还是需要执行整个查询语句,然后再返回部分结果。
有一个配置属性可以开启,避免这种情况—对数据源进行抽样
hive.limit.optimize.enable=true — 开启对数据源进行采样的功能
hive.limit.row.max.size — 设置最小的采样容量
hive.limit.optimize.limit.file — 设置最大的采样样本数
缺点:有可能部分数据永远不会被处理到

二、数据倾斜优化

1、现象描述

在hive里经常遇见数据倾斜问题。表现为:任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。
因为单一reduce的处理的数据量和其他reduce差异过大。通常可能达到3倍甚至更多。

2、原因

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

关键词 情形 后果
join 其中一个表较小,但是key集中 分发到某一个或几个Reduce上的数据远高于平均值
join 大表与大表,但是分桶的判断字段0值或空值过多 这些空值都由一个reduce处理,灰常慢
group by group by 维度过小,某值的数量过多 处理某值的reduce灰常耗时
count distinct 某特殊值过多 处理此特殊值reduce耗时

3、解决办法

3.1 常用的处理办法

第一通过hive.groupby.skewindata=true控制生成两个MR Job,第一个MR Job Map的输出结果随机分配到reduce做次预汇总,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的;减少某些key值条数过多某些key条数过小造成的数据倾斜问题。

第二通过hive.map.aggr = true(默认为true)在Map端做combiner,假如map各条数据基本上不一样, 聚合没什么意义,做combiner反而画蛇添足,hive里也考虑的比较周到通过参数hive.groupby.mapaggr.checkinterval = 100000 (默认)hive.map.aggr.hash.min.reduction=0.5(默认),预先取100000条数据聚合,如果聚合后的条数/100000>0.5,则不再聚合

3.2 SQL语句调节

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

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

group by维度过小时:
采用sum() group by的方式来替换count(distinct)完成计算。

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

三、调整mapper和reducer的阶段优化

1、Map阶段优化

map个数的主要的决定因素有: input的文件总个数,input的文件大小,集群设置的文件块大小(默认128M,不可自定义)。
由三个配置决定,

mapred.min.split.size.per.node  一个节点上split的至少的大小
mapred.min.split.size.per.rack  一个交换机下split至少的大小
mapred.max.split.size  一个split最大的大小

例如,若有大量小文件(小于128M),会产生多个map,为减少map数,处理方法是:

set mapred.max.split.size=100000000; 
set mapred.min.split.size.per.node=100000000; 
set mapred.min.split.size.per.rack=100000000;  
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- 执行前进行小文件合并

前面三个参数确定合并文件块的大小,大于文件块大小128m的,按照128m来分隔,小于128m,大于100m的,按照100m来分隔,把那些小于100m的(包括小文件和分隔大文件剩下的)进行合并
同理,增加map数也做相应设置

2、Reduce阶段优化

reduce数量由以下三个参数决定:

mapred.reduce.tasks  强制指定reduce的任务数量
hive.exec.reducers.bytes.per.reducer  每个reduce任务处理的数据量,默认为1000^3=1G
hive.exec.reducers.max  每个任务最大的reduce数,默认为999  

一般根据输入文件的总大小,用它的estimation函数来自动计算reduce的个数:计算reducer数的公式很简单N=min( hive.exec.reducers.max ,总输入数据量/ hive.exec.reducers.bytes.per.reducer )

PS:这一块的优化,个人没什么经验。目前遇到的优化问题,主要还是集中在前两点上。


四、学习参考资料

1、https://www.cnblogs.com/sandbank/p/6408762.html
2、https://www.cnblogs.com/xd502djj/p/3799432.html

查看评论

Hive性能优化

架构层面优化: l  分表 l  合理利用中间结果集,避免查过就丢的资源浪费,减低Hadoop的IO负载 l  常用复杂或低效函数尽量不用或拆分成其他实现方式,如count(distinct) ...
  • jiangshouzhuang
  • jiangshouzhuang
  • 2016-05-24 10:23:10
  • 3872

Hive执行SQL语句

org.apache.hadoop.hive.ql.Driver类 public int execute(boolean deferClose) 方法 1. 从Plan中统计MR/Tez/Spark ...
  • Aegeaner
  • Aegeaner
  • 2016-12-02 22:29:51
  • 741

Hive HiveQL基础知识及常用语句总结

Hive HiveQL基础知识及常用语句总结 基础语句 CREATE DROP 建表删表 ALTER 更改表结构 INSERT 插入 LOAD 载入 其他语句 函数 技巧 方法 调优 Hive里的...
  • u012386109
  • u012386109
  • 2017-10-12 14:54:31
  • 559

hive HQL优化

在有限资源下,提高执行效率 常见问题: 数据倾斜, map数设置 reduce数设置hive执行,HQL>JOB>MAP/REDUCE 查看执行计划 explain 【extended】 ...
  • lovelovelovelovelo
  • lovelovelovelovelo
  • 2016-08-24 22:12:36
  • 1801

【性能优化】Hive优化

1.概述   继续《那些年使用Hive踩过的坑》一文中的剩余部分,本篇博客赘述了在工作中总结Hive的常用优化手段和在工作中使用Hive出现的问题。下面开始本篇文章的优化介绍。 2.介绍   首...
  • yixiantian7
  • yixiantian7
  • 2016-06-06 16:56:50
  • 5058

Hive SQL的优化

本章只是从HQL层面介绍一下,日常开发HQL中需要注意的一些优化点,不涉及Hadoop层面的参数、配置等优化。 1 使用分区剪裁、列剪裁 在SELECT中,只拿需要的列,如果有,尽量使用分...
  • zy_zhengyang
  • zy_zhengyang
  • 2016-12-29 15:09:17
  • 1823

总结:Hive性能优化上的一些总结

注意,本文百分之九十来源于此文:Hive性能优化,很感谢作者的细心整理,其中有些部分我做了补充和追加,要是有什么写的不对的地方,请留言赐教,谢谢前言 今天电话面试突然被涉及到hive上有没有做过什...
  • MrLevo520
  • MrLevo520
  • 2017-07-29 16:25:19
  • 4503

hive sql语句执行原理

大致理解为:hive会对写好的sql语句进行拆解,按执行顺序拆分成树结构(AST)交给mapreduce处理 hive对不同DML/DDL关键字有不同的反应,比较敏感的有select、from、joi...
  • u013122565
  • u013122565
  • 2017-06-29 14:58:08
  • 650

hive入门学习:浅谈hive的常见优化策略

hive入门学习:浅谈hive的常见优化策略                           hive的产生 虽然大幅度的减轻了编写MR代码的代价,但是由于它依然使用的是以mapreduce作为底...
  • liyaohhh
  • liyaohhh
  • 2016-02-19 17:21:54
  • 1590

hive推测执行(speculative execution)

前几天在解决同事一个Hive Bug的时候,需要关闭task推测执行(speculative execution), 在网上查了资料后有人提到需要 set hive.mapred.map.task...
  • lalaguozhe
  • lalaguozhe
  • 2013-06-11 22:02:00
  • 3299
    个人资料
    持之以恒
    等级:
    访问量: 6万+
    积分: 1016
    排名: 5万+
    最新评论