Hive SQL调优,distict去重效率竟然比group by高?union该如何优化?

Hive SQL调优,distict去重效率竟然比group by高?union该如何优化?

大家好,我是后来。
这几天因为做数仓,写完SQL后总觉得自己写的SQL又臭又长,是不是应该好好优化下,于是还专门为此重新2本书,才看了没多少,就打破了自己原来对调优的认知。本文内容大多来自于《Hive 性能调优实战》

SQL优化?到底在优化什么?

理透需求原则,这是优化的根本;

把握数据全链路原则,这是优化的脉络;

坚持代码的简洁原则,这让优化更加简单;

没有瓶颈时谈论优化,是自寻烦恼。

所以接下来通过2个常见的案例来了解下HQL优化

1、distict去重效率比group by低?

之前大家在网上总能看到hive调优中一定有这么一条,要避免使用distinct去重,代替法是group by。
但是 是不是所有的情况下都是如此呢?看下面这个案例

select count(1) from(
    select s_age
    from student_tb_orc
    group by s_age
) b

这里为了从学生表中统计年龄的枚举值个数,但是为什么不用下面的这种distinct呢?

select count(distinct s_age)
from student_tb_orc

我们一般都会想数据量大了第一种能够避免reduce端的数据倾斜,但事实上,不论数据量大小,都是下面的简洁SQL效率更高。
该作者跑的结果为47s 和 28s。我尝试在自己的集群上跑同样的SQL,用Spark 引擎,可能因为数据量小的原因,相差不大,都是4s左右。
这是为什么呢?

  1. 因为去重的是s_age列,实际上业务含义表示年龄,枚举值个数非常有限,在Map阶段会对s_age去重,因此每个Map得到的s_age有限,最后到达Reduce阶段的非常有限,根本不会达到数据倾斜的量。
  2. 另外group by在不同版本间变动比较大,有的版本会用构建hashtable的形式去重,有的版本会通过排序的方式,排序最优时间复杂度无法到O(1) 。另外上面写法转化为两个任务,会消耗更多的磁盘网络I/O资源。
  3. 目前Hive 3种新增了count(distinct) 优化,通过配置hive.optimize.countdistinct,即使真的出现数据倾斜也可以自动优化,自动改变SQL执行的逻辑。

所以,上面的写法有点过度优化。让我们继续看一下他们的执行流程图:

第一种SQL执行流程图如下:

www.zeeklog.com  - Hive SQL调优,distict去重效率竟然比group by高?union该如何优化?


第二种SQL的执行流程图如下:

www.zeeklog.com  - Hive SQL调优,distict去重效率竟然比group by高?union该如何优化?


所以这2个SQL执行流程的对比图如下:

www.zeeklog.com  - Hive SQL调优,distict去重效率竟然比group by高?union该如何优化?


这两个SQL执行出来的时间差主要集中在数据传输和中间任务的创建下,就是上图的虚线框部分,因此通过distinct关键字比子查询的方式效率更高。
当然如果这里采用Spark 引擎,就直接省去了Map1落盘和Reduce再去读取中间数据的时间,2者的运行时间差可能更短。但是从SQL同等复杂程度下,简洁更优的角度来说,还是distinct更优。

那么什么情况下第一种写法的SQL会比第二种写法的SQL效率更高呢?
在有数据倾斜的情况下,第一种写法的SQL方式更优。

当数据大到一定的量级时,第一种写法的SQL有两个作业,可以把处理逻辑分散到两个阶段中,即第一个阶段先处理一部分数据,缩小数据量,第二个阶段在已经缩小的数据集上继续处理。而第二种写法的SQL,经过Map阶段处理的数据还非常多时,所有的数据却都需要交给一个Reduce节点去处理,就好比千军万马过独木桥一样,不仅无法利用到分布式集群的优势,还要浪费大量时间在等待,而这个等待的时间远比第一种写法的SQL多个MapReduce所延长的流程导致额外花费的时间还多。

但是,如前面所说,在Hive 3.0中即使遇到数据倾斜,第二种写法的SQL将hive.optimize.countdistinct设置为true,则整个写法也能达到第一种写法的SQL的效果。

2、改写SQL实现union 的优化

需求:从学生表中找到每个年龄段最晚出生和最早出生的人的生日日期,写入一个表中;
于是SQL如下:

INSERT into table student_stat partition(tp)
select
	s_age,
	min(s_birth) stat,
	'min' tp
from student_tb_txt
group by s_age
union all
select
	s_age,
	max(s_birth) stat,
	'max' tp
from student_tb_txt
group by s_age;

但是这个SQL其实是5个job对应了4个MR任务,效率是比较低的。

www.zeeklog.com  - Hive SQL调优,distict去重效率竟然比group by高?union该如何优化?


那怎么优化呢?
那能不能只读一次表,就能都计算出最小值和最大值,然后依次写入最后的结果表,不需要中间并集。
看如下SQL

from student_tb_txt
INSERT into table student_stat partition(tp)
select s_age,min(s_birth) stat,'min' tp
group by s_age
insert into table  student_stat partition(tp)
select s_age,max(s_birth) stat,'max’ tp
group by s_age;

这种也叫做multi-table-insert语法,多路输出

www.zeeklog.com  - Hive SQL调优,distict去重效率竟然比group by高?union该如何优化?


在如上的SQL执行时,其实也启动了1个Job ,所以效率的提升还是非常显著的。

总结:

调优讲究适时调优,过早进行调优有可能做的是无用功甚至产生负效应,在调优上投入的工作成本和回报不成正比。调优需要遵循一定的原则。

扫码关注