hive执行语句优化

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/abc200941410128/article/details/79963031

一、类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

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页