写点什么

大数据开发之 Hive SQL 的优化分享

  • 2022 年 1 月 04 日
  • 本文字数:2411 字

    阅读完需:约 8 分钟

hive 的优化主要分为:配置优化、SQL 语句优化、任务优化等方案。

其中在开发过程中主要涉及到的可能是 SQL 优化这块。

优化的核心思想是:

  • 减少数据量(例如分区、列剪裁);

  • 避免数据倾斜(例如加参数、Key 打散);

  • 避免全表扫描(例如 on 添加加上分区等);

  • 减少 job 数(例如相同的 on 条件的 join 放在一起作为一个任务)。


HQL 语句优化

1、使用分区剪裁、列剪裁

在分区剪裁中,当使用外关联时,如大数据培训果将副表的过滤条件写在 Where 后面,那么就会先全表关联,之后再过滤。

select a.*from test1 aleft join test2 b on  a.uid = b.uidwhere a.ds='2020-08-10'and b.ds='2020-08-10'
复制代码

上面这个 SQL 主要是犯了两个错误:

  1. 副表的过滤条件写在 where 后面,会导致先全表关联在过滤分区;

  2. on 的条件没有过滤 null 值的情况,如果两个数据表存在大批量 null 值的情况,会造成数据倾斜。

select a.*from test1 aleft join test2 b on (d.uid is not null and a.uid = b.uid and b.ds='2020-08-10')where a.ds='2020-08-10'
复制代码

如果 null 值也是需要的,那么需要在条件上转换,或者单独拿出来

select a.*from test1 aleft join test2 b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10')where a.ds='2020-08-10'union allselect a.* from test1 a where a.uid is null
或者
select a.*from test1 aleft join test2 b on case when a.uid is null then concat("test",RAND()) else a.uid end = b.uid and b.ds='2020-08-10'where a.ds='2020-08-10'
或者(子查询)select a.*from test1 aleft join (select uid from test2 where ds = '2020-08-10' and uid is not null) b on a.uid = b.uidwhere a.uid is not nulland a.ds='2020-08-10'
复制代码

2、尽量不要用 COUNT DISTINCT,因为 COUNT DISTINCT 操作需要用一个 Reduce Task 来完成,这一个 Reduce 需要处理的数据量太大,就会导致整个 Job 很难完成,一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换,虽然会多用一个 Job 来完成,但在数据量大的情况下,这个绝对是值得的。

select count(distinct uid)from testwhere ds='2020-08-10' and uid is not null
转换为
select count(a.uid)from (select uid from test where uid is not null and ds = '2020-08-10' group by uid) a
复制代码

3、使用 with as,因为拖慢 hive 查询效率出了 join 产生的 shuffle 以外,还有一个就是子查询,在 SQL 语句里面尽量减少子查询。with as 是将语句中用到的子查询事先提取出来(类似临时表),使整个查询当中的所有模块都可以调用该查询结果。使用 with as 可以避免 Hive 对不同部分的相同子查询进行重复计算。

select a.*from test1 aleft join test2 b on  a.uid = b.uidwhere a.ds='2020-08-10'and b.ds='2020-08-10'
可以转化为
with b as select uidfrom test2where ds = '2020-08-10' and uid is not null

select a.*from test1 aleft join b on a.uid = b.uidwhere a.ds='2020-08-10' and a.uid is not null
复制代码

4、大小表的 join,写有 Join 操作的查询语句时有一条原则:应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段,位于 Join 操作符左边的表的内容会被加载进内存,将条目少的表放在左边,可以有效减少发生 OOM 错误的几率。

但新版的 hive 已经对小表 JOIN 大表和大表 JOIN 小表进行了优化。小表放在左边和右边已经没有明显区别。

不过在做 join 的过程中通过小表在前可以适当的减少数据量,提高效率。

5、数据倾斜,数据倾斜的原理都知道,就是某一个或几个 key 占据了整个数据的 90%,这样整个任务的效率都会被这个 key 的处理拖慢,同时也可能会因为相同的 key 会聚合到一起造成内存溢出。

数据倾斜只会发生在 shuffle 过程中。这里给大家罗列一些常用的并且可能会触发 shuffle 操作的算子:distinct、 groupByKey、reduceByKey、aggregateByKey、join、cogroup、repartition 等。出现数据倾斜时, 可能就是你的代码中使用了这些算子中的某一个所导致的。

hive 的数据倾斜一般的处理方案:

常见的做法,通过参数调优:set hive.map.aggr=true;set hive.groupby.skewindata = ture;当选项设定为true时,生成的查询计划有两个MapReduce任务。在第一个MapReduce中,map的输出结果集合会随机分布到reduce中,每个reduce做部分聚合操作,并输出结果。这样处理的结果是,相同的Group By Key有可能分发到不同的reduce中,从而达到负载均衡的目的;第二个MapReduce任务再根据预处理的数据结果按照Group By Key分布到reduce中(这个过程可以保证相同的Group By Key分布到同一个reduce中),最后完成最终的聚合操作。
但是这个处理方案对于我们来说是个黑盒,无法把控。
一般处理方案是将对应的key值打散即可。
例如:select a.*from test1 aleft join test2 b on a.uid = b.uidwhere a.ds='2020-08-10'and b.ds='2020-08-10'
如果有90%的key都是null,这样不可避免的出现数据倾斜。
select a.uidfrom test1 as ajoin( select case when uid is null then cast(rand(1000000) as int) else uid from test2 where ds='2020-08-10') b on a.uid = b.uidwhere a.ds='2020-08-10'
当然这种只是理论上的处理方案。正常的方案是null进行过滤,但是日常情况下不是这中特殊的key。那么在日常需求的情况下如何处理这种数据倾斜的情况呢:1、sample采样,获取哪些集中的key;2、将集中的key按照一定规则添加随机数;3、进行join,由于打散了,所以数据倾斜避免了;4、在处理结果中对之前的添加的随机数进行切分,变成原始的数据;
复制代码

当然这些优化都是针对 SQL 本身的优化,还有一些是通过参数设置去调整的,这里面就不再详细描述了。

但是优化的核心思想都差不多:

  1. 减少数据量;

  2. 避免数据倾斜;

  3. 减少 JOB 数;

  4. 虚核心点:根据业务逻辑对业务实现的整体进行优化;

  5. 虚解决方案:采用 presto、impala 等专门的查询引擎,采用 spark 计算引擎替换 MR/TEZ;

用户头像

关注尚硅谷,轻松学IT 2021.11.23 加入

还未添加个人简介

评论

发布
暂无评论
大数据开发之Hive SQL的优化分享