Hive性能调优的多样性之SQL对性能的影响

前言：谈及一项技术的优化，必然是一项综合性的工作，它是多门技术的结合。在这里我将会用代码来演示各类优化技巧，目的在于演示Hive调优的多样性，如改写SQL、调整数据存储的文件块、改变数据的存储格式、Hive表的设计等方面。

SQL对性能的影响

1. 1.1 数据准备

首先我们在Hive上建两张内部表

student_tb_txt

student_sc_tb_txt

1. 1.2union案例
在该案例中查询student_tb_txt 表，每个年龄段最晚出生和最早出生的人的出生日期，并将其存入表 student_stat中。

--创建一张新的统计表 
create table student_stat(a bigint, b bigint) partitioned by (tp string) STORED AS TEXTFILE;
--开启动态分区 
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict;
--找出各个年龄段最早和最晚出生的信息，并将这两部分信息使用union进行合并写入 student_stat中 
 insert into table student_stat partition(tp) 
 select s_age,max(s_birth) stat,'max' tp 
 from student_tb_txt 
 group by s_age 
 union all 
 select s_age,min(s_birth) stat, 'min' tp 
 from student_tb_txt 
 group by s_age;

--------------执行后计算结果如下-----------------------
Query ID = hdfs_20180928153333_6e96651d-e0d3-41a3-a3d9-121210094aca Total jobs = 5 Launching Job 1 out of 5
…省略大部分的打印信息
MapReduce Jobs Launched:
Stage-Stage-1: Map: 84 Reduce: 328 Cumulative CPU: 1456.95 sec HDFS
Read: 21956521358 HDFS Write: 31719 SUCCESS
Stage-Stage-9: Map: 84 Reduce: 328 Cumulative CPU: 1444.21 sec HDFS
Read: 21956522014 HDFS Write: 31719 SUCCESS
Stage-Stage-2: Map: 6 Cumulative CPU: 34.47 sec HDFS
Read: 258198 HDFS Write: 582 SUCCESS
Stage-Stage-4: Map: 2 Cumulative CPU: 3.83 sec HDFS
Read: 6098 HDFS Write: 84 SUCCESS
Total MapReduce CPU Time Spent: 48 minutes 59 seconds 460 msec OK
Time taken: 336.306 seconds

从上面的打印结果我们可以看到一共有5个Job对应4个MR的任务。从Total MapReduce CPU Time我们可以看到系统的实际耗时是48分钟59 秒，用户的等待时间是336秒。
接下来我们看一个不使用union all的案例对比。

1. 1.3 改写SQL实现union的优化
在上面的案例中，我们只是完成了一个简单的求最大值/最小值的统 计，但却用了4个MR任务。

在大数据领域，分布式计算和分布式存储会消耗大量的磁盘I/O和网络I/O资源，这部分资源往往成为了大数据作业的瓶颈。在运行案例1.2时观察集群资源的运行情况，将会发现CPU使用率很少，磁盘和网络读写会变得很高，所以优化的焦点就集中在如何降低作业对I/O资源的消耗上。MR任务有一个缺点，即启动一次作业需要多次读写磁盘，因为MR会将中间结果写磁盘，而且为了保障磁盘的读写效率和网络传输效率，会进行多次排序。
如果一个SQL包含多个作业，作业和作业之间的中间结果也会先写到磁盘上。减少中间结果的产生，也就能够达到降低I/O资源消耗，提升程序效率。针对案例1.2，我们调优的关键就是减少或者避免中间结果集的产生，基于这样的想法，改造后的数据流程图：

观察图1.2，会发现有一处冗余的地方：step1和step2都是对 student_tb_txt进行计算，但在计算时要查询两次表，这一步其实是冗余的。 如果student_tb_txt是一个基数特别大的表，从表中取数（读取磁盘中的数 据）的时间将变得很长，也浪费了集群宝贵的I/O资源。可以将其进行优 化，变成只需读取一次表，如图1.3所示。

图1.3中优化了从源表student_tb_txt取数的次数，但是计算max值和min 值却要分成两个MR作业，并将计算结果分别插入到student_stat中。如果能 够在一个任务当中完成max和min值计算，那就可以减少启动一个作业的时 间，以及MR任务对磁盘I/O和网络I/O的消耗。改造后的数据流如图1.4所 示。

【案例1.4】去掉union计算max和min。

DROP TABLE if EXISTS student_stat;
 --创建student_stat 
 create table student_stat(a bigint, b bigint) partitioned by (tp string) STORED AS TEXTFILE; 
 --开启动态分区 
 set hive.exec.dynamic.partition=true; 
 set hive.exec.dynamic.partition.mode=nonstrict; 
 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;

-------------------执行结果-----------------------------------
Query ID = hdfs_20190225095959_25418167-9bbe-4c3d-aae0-0511f52ca683 Total jobs = 1
…//省略部分的打印信息
Hadoop job information for Stage-2: number of mappers: 84; number of reducers: 328
2019-02-25 10:00:02,395 Stage-2 map = 0%, reduce = 0%
2019-02-25 10:00:19,973 Stage-2 map = 3%, reduce = 0%, Cumulative CPU 18.06 sec
…//省略部分的打印信息
2019-02-25 10:02:44,432 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 2133.75 sec
MapReduce Total cumulative CPU time: 35 minutes 33 seconds 750 msec Ended Job = job_1550390190029_0218
…
MapReduce Jobs Launched:
Stage-Stage-2: Map: 84 Reduce: 328 Cumulative CPU: 2133.75 sec HDFS
Read: 21957309270 HDFS Write: 2530 SUCCESS
Total MapReduce CPU Time Spent: 35 minutes 33 seconds 750 msec OK
Time taken: 171.342 second

从以上结果中可以看到案例1.4的执行结果： ·Total MapReduce CPU Time：35分33秒；
·用户等待时间：171秒。
相比于案例1.3，在案例1.4中通过改写SQL，即实现MULTI-TABLE- INSERT写法，Total MapReduce CPU Time Spent的总耗时从48分59秒减少到 了35分33秒，减少了13分钟26秒，用户实际等待耗时从336秒减少到了171 秒，节省了近1/2的时间。案例1.4中，整个优化的过程都集中在对磁盘I/O和 网络I/O的优化上，在硬件资源保持不变的情况下，随着数据量的增加，整 个集群的磁盘和网络压力会更大，相比于案例1.3的写法，其节省的时间会 更加明显。
拓展：细心的读者会发现，案例1.4只启动了1个Job，而案例1.3却启动了5个，每启动一个job，就说明集群多执行了一MR作业，MR作业越多则代表数据就要经历更多次的磁盘读写和网络通信。随着数据量增多，磁盘和网络的负载会越来越大，消耗在每个MR过程的时间延迟也会越来越长。

1.1.4 失败的union调优
将会采用如下 案例来对其优化，即把一个union语句拆解成多个简单SQL，
【案例1.5】将一个含有union的SQL改写为两个简单的SQL。

drop table if exists student_stat; 
create table student_stat(a bigint, b bigint) partitioned by (tp string) STORED AS TEXTFILE; 
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict;
 --计算max值 
 insert into table student_stat partition(tp)
  select s_age,max(s_birth) stat, 'max' tp 
  from student_tb_txt 
  group by s_age; 
  --计算min值 
  insert into table student_stat partition(tp) 
  select s_age,min(s_birth) stat,'min' tp 
  from student_tb_txt 
  group by s_age;

案例1.5将案例1.2的代码拆分成了两段代码，这样可以省略union的MR作业，计算max和min值的两个作业可直接将数据放到student_stat目录下， 少了一次MR作业，并节省了一个MR所额外消耗的资源。看似很合理的方 案，我们来看下两个程序的执行结果。
计算max值的执行结果：

Query ID = hdfs_20190219142020_2462a9ff-98d0-4fe0-afd8-24f78eebf46b Total jobs = 1 Launching Job 1 out of 1 …//省略非必要信息 Hadoop job information for Stage-1: number of mappers: 84; number of reducers: 328 2019-02-19 14:20:16,031 Stage-1 map = 0%, reduce = 0% 2019-02-19 14:20:26,350 Stage-1 map = 6%, reduce = 0%, Cumulative CPU 28.57 sec …//省略部分信息 2019-02-19 14:22:35,773 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2028.24 sec MapReduce Total cumulative CPU time: 33 minutes 48 seconds 240 msec Ended Job = job_1550390190029_0057 Loading data to table default.student_stat partition (tp=null) Time taken for load dynamic partitions : 153 Loading partition {tp=max} Time taken for adding to write entity : 0 Partition default.student_stat{tp=max} stats: [numFiles=7, numRows=7, totalSize=42, rawDataSize=35] MapReduce Jobs Launched:
Stage-Stage-1: Map: 84 Reduce: 328 Cumulative CPU: 2028.24 sec HDFS Read: 21956949222 HDFS Write: 1265 SUCCESS Total MapReduce CPU Time Spent: 33 minutes 48 seconds 240 msec OKTime taken: 147.503 seconds

计算min值的执行结果：

Query ID = hdfs_20190219142222_7babcc53-6ceb-4ca6-a1c1-970d7caa43ab Total jobs = 1
Launching Job 1 out of 1
…//省略部分信息
Hadoop job information for Stage-1: number of mappers: 84; number of reducers: 328
2019-02-19 14:22:48,191 Stage-1 map = 0%, reduce = 0%
2019-02-19 14:22:56,453 Stage-1 map = 6%, reduce = 0%, Cumulative CPU 25.13 sec
…//省略部分信息
2019-02-19 14:25:11,062 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2014.8 sec
MapReduce Total cumulative CPU time: 33 minutes 34 seconds 800 msec Ended Job = job_1550390190029_0058 Loading data to table default.student_stat partition (tp=null) Time taken for load dynamic partitions : 104
Loading partition {tp=min} Time taken for adding to write entity : 0 Partition default.student_stat{tp=min} stats: [numFiles=7, numRows=7, totalSize=42, rawDataSize=35]
MapReduce Jobs Launched:
Stage-Stage-1: Map: 84 Reduce: 328 Cumulative CPU: 2014.8 sec HDFS
Read: 21956949222 HDFS Write: 1265 SUCCESS
Total MapReduce CPU Time Spent: 33 minutes 34 seconds 800 msec OK
Time taken: 152.628 seconds

从案例1.5的执行结果中可以得到计算最大值和最小值的Total MapReduce CPU Time总和为66分钟，用户等待时间为303秒。 案例1.5相比于union的写法，即案例1.2，系统CPU耗时多了近16分钟， 这样的结果让人感到意外。难道分析过程有问题？其实对于Hive的早期版 本，用案例1.5的代码确实是对案例1.2的代码进行了调优，但在Hive版本迭 代中对union命令进行了优化，导致拆分后的代码令整个程序跑得慢了。从 这里我们可以知道，某些调优方式随着版本的迭代，也逐渐变得不再适用。