面试 hive常见面试题1

hive常见面试题1

Hive有索引吗？

1
Hive本身不支持传统意义上的索引，但可以通过分区来实现类似的效果。分区可以按照某个字段对数据进行分组，从而提高查询效率。

在Map端和Reduce端进行join的不同场景是什么？

1
2
在Map端进行join适用于一张表非常小的情况，因为此时可以将小表完全加载到内存中进行处理。
在Reduce端进行join是最常用的join方式，适用于两表都较大的情况，通过MapReduce框架并行处理以提高效率

Hive的存储格式有哪些？各自的优缺点是什么？

1
2
3
ORC（OrcolLECTION Format）：压缩率高，读写速度快，适合实时分析。
Parquet：列式存储，压缩率高，查询性能好，但写入速度较慢。
sequenceFile：简单易用，但压缩率低，性能较差，支持切分

如何优化Hive查询性能？

1
2
3
4
使用合适的存储格式（如ORC或Parquet）。
进行SQL优化，如合理使用索引（分区）、避免全表扫描。
调整配置参数，如设置合理的副本数和副本位置。
利用内置优化器和统计信息来提升查询效率

Hive中的Sort By、Order By、Cluster By、Distribute By各代表什么意思？

1
课程解释....
1
2
3
4
Sort By：对结果集进行排序。
Order By：对结果集进行排序，通常用于查询结果的排序。
Cluster By：对内部节点进行聚类，减少跨节点的数据传输。
Distribute By：对数据进行分片，均匀分布到各个节点上。
1
2
3
4
5
6
7
8
ORDER BY是对数据进行全局排序，SORT BY是对每个Reducer中的数据进行单独排序。当Reducer的数量为1时，ORDER BY和SORT BY都是全局排序。
ORDER BY的排序列可以不为SELECT中出现的指定列，SORT BY的排序列必须为SELECT中出现的指定列。

ORDER BY适用于小数据集，性能较差;SORT BY适用于大数据集。
对大数据集进行部分排序，则用DISTRIBUTE BY+SORT BY;对大数据集进行全局排序，则用(DISTRIBUTE BY+SORT BY|CLUSTER BY)+ORDER BY.

DISTRIBUTE BY和CLUSTER BY的区别？
DISTRIBUTE BY是确定数据被分配到哪个Reducer中，CLUSTER BY相当于对同一个字段进行DISTRIBUTE BY+SORT BY。

Hive的两种模式是什么？分别适用于什么场景？

1
2
Local模式：仅在单个节点上运行，用于开发和调试。
-远端模式：将任务提交到Yarn或其他资源管理系统上执行，适用于生产环境。

数仓为什么要进行分层？

1
2
3
4
用空间换时间，通过大量的预处理来提升应用系统的用户体验(效率)，因此数据仓库会存在大量冗余的数据。
如果不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。
(在实际业务环境中，源业务系统的数据结构和业务规则经常会发生变化。)
通过数据分层管理能够简化数据清洗的过程。

关于Hive有哪些常见的优化？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Hive MapJoin

配置：
set hive.auto.convert.join = true(默认开启) -- 开启mapjoin操作
set hive.mapjoin.smalltable.filesize=25000000; -- 重新定义小表边界，如果内存空闲，则可以调大
set hive.optimize.bucketmapjoin=false; -- 对分桶表不做MapJoin

其他:
可以设置为不等值连接

不适用的情况：
a."联合"(除UNION ALL)之前，"分组排序"之后
在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面
在UNION, JOIN 以及其他 MAPJOIN之前
b.仅适用于"大表+小表"的情况，不适用于多张表或复杂排序的情况
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Hive Reducer优化
设置特定MapReduce作业的reducer数量
默认为-1，表示Hive将自动决定Reducer的数量
set mapreduce.job.reduces=; ✔
set mapred.reduce.tasks=; 单个Reducer最大处理的字节数
默认为256000000，为256兆 ≈ 2Block
set hive.exec.reducers.bytes.per.reducer=;
* 限制任何Hive查询可能使用的reducer的最大数量
默认为1009
Reducer数量距离最大机器数1024还有一定余量的原因：如果集群的机器数>1000台，那么每台机器不一定都有DN，则有一些机器单独跑NN，那么这些机器就没有NM，也就不会分配容器跑Reducer.
set hive.exec.reducers.max=; 面试：Reducer的数量为什么不是越多越好？
1.资源浪费，本应分配给其他任务和节点的资源被分配给Reducer。
2.会花费过多的时间在上下文切换上而非任务处理。
3.系统需要管理更多的Reducer，增加了调度和通信的开销。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
简化排序列的表示
set hive.groupby.orderby.position.alias=true|false
当该属性设置为true时，允许在GROUP BY存在时在ORDER BY子句使用排序列编号替代字段。

Fetch抓取
Hive中对某些情况的查询可以不必使用MapReduce计算，例如：SELECT * FROM employees;
将hive.fetch.task.conversion设置为more即可实现不走MapReduce，走Fetch。

本地模式
对于小数据集，采取本地模式在单台机器上处理所有的任务即可，不必分配给多台机器进行处理，可以明显缩短执行时间。
set hive.exec.mode.local.auto=true// 开启本地mr
set hive.exec.mode.local.auto.inputbytes.max=50000000;// 设置local mr的最大输入数据量
set hive.exec.mode.local.auto.input.files.max=10;//设置local mr的最大输入文件个数

GROUP BY优化
默认情况下，Map阶段同一Key数据分发给一个Reduce，当一个Key数据过大时就倾斜了。
并不是所有的聚合操作都需要在Reducer端完成，很多聚合操作都可以现在Map端完成部分聚合，最终再Reducer端得出最终结果。
set hive.map.aggr=true;//开启Map端聚合
set hive.groupby.mapaggr.checkinterval=100000;//设置在Map端进行聚合操作的数据条目数目
set hive.groupby.skewindata=true;//有数据倾斜时进行负载均衡

行列过滤
列处理：只查询需要的列
行处理：表连接时，先过滤数据再进行表连接

动态分区
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
开启并行执行(并行的前提是系统资源比较空闲)
Hive会将一个查询转化成一个或者多个阶段，这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段...或者其他在Hive执行中可能需要的阶段。
Hive默认一次只会执行一个阶段，但是对于可以并行执行的、非相互依赖的阶段，可以设置并行执行。
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=16;//同一个SQL允许最大并行度，默认为8。

开启严格模式：防止用户执行一些查询方式。
严格模式：
set hive.mapred.mode=strict
严格模式可以禁止的3类查询：
1.对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。
2.对于使用了order by语句的查询，要求必须使用limit语句。rder by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。
3.限制笛卡尔积的查询。

选择合适的文件格式
使用列式存储格式(如Parquet,ORC)可以显著提高查询性能。

优化数据存储和加载
使用数据存储：
可以使用Snappy压缩格式
合理选择数据分隔符
1
2
3
4
5
6
7
8
9
开启Vetorization
启用Vectorization可以使Hive在执行查询时一次处理一批数据，而不是逐行处理，从而显著提高性能。
set hive.vectorized.execution.enabled=true

EXPLAIN HQL
执行计划分析，进行CBO，选择最优的执行计划。

Hive可以配置为使用Tez执行引擎替代传统的MapReduce
set hive.execution.engine=tez

如何处理数据倾斜问题？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
数据倾斜问题主要有以下几种：

空值引发的数据倾斜

不同数据类型引发的数据倾斜

不可拆分大文件引发的数据倾斜

数据膨胀引发的数据倾斜

表连接时引发的数据倾斜

确实无法减少数据量引发的数据倾斜

以上倾斜问题的具体解决方案可查看：https://xie.infoq.cn/link?target=https%3A%2F%2Fmp.weixin.qq.com%2Fs%2Fhz_6io_ZybbOlmBQE4KSBQ

Hive中排序函数的区别？

1
略

Hive如何实现分区？

Hive如何进行数据的导入和导出？

自定义函数 UDF UDTF

image-20240729215409564

计算字符串长度

image-20240729215702152

image-20240729215743506

image-20240729215818679

image-20240729215939200

1
上传jar包到hive\lib下，再重启

image-20240729220133515

image-20240729220155087

image-20240729220223772

列转行 单列

image-20240729220307194

image-20240729220341169

列转行 多列

image-20240729215508618

image-20240729220421704

image-20240729220443113

image-20240729220526482

通过自定义函数(UDF)实现在SQL中将单词首字母转换成大写

1
略

36、【学习任务】项目任务-使用Hive加载指定格式数据

1

1
不知道怎么做
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
中建表，加载这份数据，最终可以使用SQL查询这份数据中的各科成绩。

效果：
希望最终查询出来的效果是这样的：

hive (default)> select * from stu;
stu.name       stu.scores
zs      [{"subject":"语文","score":81},{"subject":"数学","score":80}]
ls      [{"subject":"语文","score":90}]
hive (default)> select scores[0].score from stu;
score
81
90

任务要求:

1：针对数据源中的数据进行数据清洗，清洗成需要的格式，使用MapReduce代码进行数据清洗

2：针对清洗之后的数据在Hive中创建外部表

3：针对数据中的第二列内容需要使用数组存储

任务提示、思路分析：

1：考虑使用Hive中的复杂数据类型实现

2：注意，数据中的第二列内容使用array<string>这种格式是错误的！！！

2 一个复杂的案例

image-20240729233130927

image-20240729233401098

运维如何对 hive 进行调度

1
2
3
4
将 hive 的 sql 定义在脚本当中；

使用 azkaban 或者 oozie 进行任务的调度；
监控任务调度页面。

使用过Hive解析JSON串吗

1
2
3
4
5
6
7
Hive 处理 json 数据总体来说有两个方向的路走：

将 json 以字符串的方式整个入 Hive 表，然后通过使用 UDF 函数解析已经导入到 hive 中的数据，比如使用LATERAL VIEW json_tuple的方法，获取所需要的列名。

在导入之前将 json 拆成各个字段，导入 Hive 表的数据是已经解析过的。这将需要使用第三方的 SerDe。

详细介绍可查看：https://mp.weixin.qq.com/s/awCvlb9BzCRX-Da1_l1FYg

get_json_object 自带

json_tuple 自带

Hive解析json数组

1
2
3
4
5
6
7
8
9
如果有一个hive表，表中 json_str 字段的内容如下：

json_str
[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]
我们想把这个字段解析出来，形成如下的结构：

website	    name
baidu.com	百度
google.com	谷歌

嵌套子查询解析json数组

1
2
3
4
select 
json_tuple(explode(split(
regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) 
, 'website', 'name') ;
1
2
3
4
5
6
执行上述语句，结果报错了：
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

意思是UDTF函数不能写在别的函数内，也就是这里的explode函数不能写在json_tuple里面。

既然explode函数不能写在别的json_tuple里面，那我们可以用子查询方式
1
2
3
4
5
6
7
8
select json_tuple(json, 'website', 'name') 
from (
select explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) 
as json) t;
执行上述语句，没有报错，执行结果如下：

www.baidu.com   百度
google.com      谷歌
explode函数
regexp_replace函数
json_tuple

使用 lateral view 解析json数组

1
2
3
4
5
hive表中 goods_id 和 json_str 字段的内容如下：

goods_id	json_str
1,2,3	[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
目的：把 goods_id 字段和 json_str 字段中的monthSales解析出来。
1
2
3
4
5
6
7
8
9
10
11
12
下面我们就开始解析：

拆分goods_id字段及将json数组转化成多个json字符串：
select 
explode(split(goods_id,',')) as good_id,
explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) 
as sale_info 
from tableName;
执行上述语句，结果报错：
FAILED: SemanticException 3:0 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'sale_info'

意思是用UDTF的时候，SELECT 只支持一个字段。而上述语句select中有两个字段，所以报错了。
1
2
那怎么办呢，要解决这个问题，还得再介绍一个hive语法：
lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
1
2
3
4
5
6
7
假设我们有一张用户兴趣爱好表 hobbies_table，它有两列数据，第一列是name，第二列是用户兴趣爱好的id_list，是一个数组，存储兴趣爱好的id值：

name	id_list
zhangsan	[1,2,3]
lisi	[3,4,5]

我们要统计所有兴趣id在所有用户中出现的次数：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
对兴趣id进行解析：
SELECT name, hobby_id 
FROM hobbies_table 
LATERAL VIEW explode(id_list) tmp_table AS hobby_id;

上述sql执行结果：
name	hobby_id
zhangsan	1
zhangsan	2
zhangsan	3
lisi	3
lisi	4
lisi	5

2. 按照hobby_id进行分组聚合即可：
SELECT hobby_id ,count(name) client_num
FROM hobbies_table 
LATERAL VIEW explode(id_list) tmp_table AS hobby_id
GROUP BY hobby_id;

结果：
hobby_id	client_num
1			1
2			1
3			2
4			1
5			1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
介绍完 lateral view 之后，我们再来解决上面遇到的用UDTF的时候，SELECT 只支持一个字段的问题：

select good_id,get_json_object(sale_json,'$.monthSales') as monthSales
from tableName 
LATERAL VIEW explode(split(goods_id,','))goods as good_id 
LATERAL VIEW explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) sales as sale_json;
注意：上述语句是三个表笛卡尔积的结果，所以此方式适用于数据量不是很大的情况。

上述语句执行结果如下：

goods_id	monthSales
1	4900
1	2090
1	6987
2	4900
2	2090
2	6987
3	4900
3	2090
3	6987
1
2
3
如果表中还有其他字段，我们可以根据其他字段筛选出符合结果的数据。

总结：lateral view通常和UDTF一起出现，为了解决UDTF不允许在select存在多个字段的问题。

Hive 小文件过多怎么解决

小文件产生原因

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
hive 中的小文件肯定是向 hive 表中导入数据时产生，所以先看下向 hive 中导入数据的几种方式

直接向表中插入数据
insert into table A values (1,'zhangsan',88),(2,'lisi',61);
这种方式每次插入时都会产生一个文件，多次插入少量数据就会出现多个小文件，但是这种方式生产环境很少使用，可以说基本没有使用的

通过load方式加载数据
load data local inpath '/export/score.csv' overwrite into table A  -- 导入文件
load data local inpath '/export/score' overwrite into table A   -- 导入文件夹
使用 load 方式可以导入文件或文件夹，当导入一个文件时，hive表就有一个文件，当导入文件夹时，hive表的文件数量为文件夹下所有文件的数量

通过查询方式加载数据
insert overwrite table A  select s_id,c_name,s_score from B;
这种方式是生产环境中常用的，也是最容易产生小文件的方式

insert 导入数据时会启动 MR 任务，MR中 reduce 有多少个就输出多少个文件
所以， 文件数量=ReduceTask数量*分区数
也有很多简单任务没有reduce，只有map阶段，则
文件数量=MapTask数量*分区数

每执行一次 insert 时hive中至少产生一个文件，因为 insert 导入时至少会有一个MapTask。
像有的业务需要每10分钟就要把数据同步到 hive 中，这样产生的文件就会很多。

小文件过多产生的影响

1
2
3
首先对底层存储HDFS来说，HDFS本身就不适合存储大量小文件，小文件过多会导致namenode元数据特别大, 占用太多内存，严重影响HDFS的性能

对 hive 来说，在进行查询时，每个小文件都会当成一个块，启动一个Map任务来完成，而一个Map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的Map数量是受限的。

怎么解决小文件过多

使用 hive 自带的 concatenate 命令，自动合并小文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
使用方法：
#对于非分区表
alter table A concatenate;

#对于分区表
alter table B partition(day=20201224) concatenate;

举例：
#向 A 表中插入数据
hive (default)> insert into table A values (1,'aa',67),(2,'bb',87);
hive (default)> insert into table A values (3,'cc',67),(4,'dd',87);
hive (default)> insert into table A values (5,'ee',67),(6,'ff',87);

#执行以上三条语句，则A表下就会有三个小文件,在hive命令行执行如下语句
#查看A表下文件数量
hive (default)> dfs -ls /user/hive/warehouse/A;
Found 3 items
-rwxr-xr-x   3 root supergroup        378 2020-12-24 14:46 /user/hive/warehouse/A/000000_0
-rwxr-xr-x   3 root supergroup        378 2020-12-24 14:47 /user/hive/warehouse/A/000000_0_copy_1
-rwxr-xr-x   3 root supergroup        378 2020-12-24 14:48 /user/hive/warehouse/A/000000_0_copy_2
#可以看到有三个小文件，然后使用 concatenate 进行合并

hive (default)> alter table A concatenate;
#再次查看A表下文件数量
hive (default)> dfs -ls /user/hive/warehouse/A;
Found 1 items
-rwxr-xr-x   3 root supergroup        778 2020-12-24 14:59 /user/hive/warehouse/A/000000_0
#已合并成一个文件

注意： 
1、concatenate 命令只支持RCFILE和ORC文件类型。 
2、使用concatenate命令合并小文件时不能指定合并后的文件数量，但可以多次执行该命令。 
3、当多次使用concatenate后文件数量不在变化，这个跟参数mapreduce.input.fileinputformat.split.minsize=256mb的设置有关，可设定每个文件的最小size。

调整参数减少Map数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
设置map输入合并小文件的相关参数：
#执行Map前进行小文件合并
#CombineHiveInputFormat底层是 Hadoop的 CombineFileInputFormat 方法
#此方法是在mapper中将多个文件合成一个split作为输入
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- 默认
#每个Map最大输入大小(这个值决定了合并后文件的数量)
set mapred.max.split.size=256000000;   -- 256M
#一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000;  -- 100M
#一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)
set mapred.min.split.size.per.rack=100000000;  -- 100M

设置map输出和reduce输出进行合并的相关参数:
#设置map端输出进行合并，默认为true
set hive.merge.mapfiles = true;
#设置reduce端输出进行合并，默认为false
set hive.merge.mapredfiles = true;
#设置合并文件的大小
set hive.merge.size.per.task = 256*1000*1000;   -- 256M
#当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge
set hive.merge.smallfiles.avgsize=16000000;   -- 16M 

启用压缩
# hive的查询结果输出是否进行压缩
set hive.exec.compress.output=true;
# MapReduce Job的结果输出是否使用压缩
set mapreduce.output.fileoutputformat.compress=true;

减少Reduce的数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
3. 减少Reduce的数量
#reduce 的个数决定了输出的文件的个数，所以可以调整reduce的个数控制hive表的文件数量，
#hive中的分区函数 distribute by 正好是控制MR中partition分区的，
#然后通过设置reduce的数量，结合分区函数让数据均衡的进入每个reduce即可。

#设置reduce的数量有两种方式，第一种是直接设置reduce个数
set mapreduce.job.reduces=10;

#第二种是设置每个reduce的大小，Hive会根据数据总大小猜测确定一个reduce个数
set hive.exec.reducers.bytes.per.reducer=5120000000; -- 默认是1G，设置为5G

#执行以下语句，将数据均衡的分配到reduce中
set mapreduce.job.reduces=10;
insert overwrite table A partition(dt)
select * from B
distribute by rand();

解释：如设置reduce数量为10，则使用 rand()， 随机生成一个数 x % 10 ，
这样数据就会随机进入 reduce 中，防止出现有的文件过大或过小

使用hadoop的archive将小文件归档

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Hadoop Archive简称HAR，是一个高效地将小文件放入HDFS块中的文件存档工具，它能够将多个小文件打包成一个HAR文件，这样在减少namenode内存使用的同时，仍然允许对文件进行透明的访问

#用来控制归档是否可用
set hive.archive.enabled=true;
#通知Hive在创建归档时是否可以设置父目录
set hive.archive.har.parentdir.settable=true;
#控制需要归档文件的大小
set har.partfile.size=1099511627776;

#使用以下命令进行归档
ALTER TABLE A ARCHIVE PARTITION(dt='2020-12-24', hr='12');

#对已归档的分区恢复为原文件
ALTER TABLE A UNARCHIVE PARTITION(dt='2020-12-24', hr='12');

注意:  
归档的分区可以查看不能 insert overwrite，必须先unarchive
1
2
3
最后
如果是新集群，没有历史遗留问题的话，建议hive使用orc文件格式，以及启用 lzo 压缩。
这样小文件过多可以使用hive自带命令concatenate快速合并

Hive 优化有哪些

Hive性能问题排查方式

1
2
3
经常使用关系型数据库的同学可能知道关系型数据库的优化的诀窍-看执行计划。如Oracle数据库，它有多种类型的执行计划，通过多种执行计划的配合使用，可以看到根据统计信息推演的执行计划，即Oracle推断出来的未真正运行的执行计划；能够观察到从数据读取到最终呈现的主要过程和中间的量化数据。可以说，在Oracle开发领域，掌握合适的环节，选用不同的执行计划，SQL调优就不是一件难事。

Hive中也有执行计划，但是Hive的执行计划都是预测的，这点不像Oracle和SQL Server有真实的计划，可以看到每个阶段的处理数据、消耗的资源和处理的时间等量化数据。Hive提供的执行计划没有这些数据，这意味着虽然Hive的使用者知道整个SQL的执行逻辑，但是各阶段耗用的资源状况和整个SQL的执行瓶颈在哪里是不清楚的。
1
想要知道HiveSQL所有阶段的运行信息，可以查看YARN提供的日志。查看日志的链接，可以在每个作业执行后，在控制台打印的信息中找到。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Hive提供的执行计划目前可以查看的信息有以下几种：
查看执行计划的基本信息，即explain；
查看执行计划的扩展信息，即explain extended；
查看SQL数据输入依赖的信息，即explain dependency；
查看SQL操作相关权限的信息，即explain authorization；
查看SQL的向量化描述信息，即explain vectorization。
在查询语句的SQL前面加上关键字explain是查看执行计划的基本方法。用explain打开的执行计划包含以下两部分：

作业的依赖关系图，即STAGE DEPENDENCIES；
每个作业的详细信息，即STAGE PLANS。

注：使用explain查看执行计划是Hive性能调优中非常重要的一种方式，请务必掌握！

总结：Hive对SQL语句性能问题排查的方式：
使用explain查看执行计划；
查看YARN提供的日志。


Hive中的explain执行计划详解可看我之前写的这篇文章：https://mp.weixin.qq.com/s?__biz=Mzg2MzU2MDYzOA==&mid=2247484152&idx=1&sn=7e48aa4a9650481f960c6cac234977a4&scene=21#wechat_redirect

实践

1
不懂hive中的explain，说明hive还没入门，学会explain，能够给我们工作中使用hive带来极大的便利！
1
2
3
4
HIVE提供了EXPLAIN命令来展示一个查询的执行计划,这个执行计划对于我们了解底层原理，hive 调优，排查数据倾斜等很有帮助

使用语法如下：
EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query
1
2
3
4
5
6
7
8
9
10
explain 后面可以跟以下可选参数，注意：这几个可选参数不是 hive 每个版本都支持的

EXTENDED：加上 extended 可以输出有关计划的额外信息。这通常是物理信息，例如文件名。这些额外信息对我们用处不大
CBO：输出由Calcite优化器生成的计划。CBO 从 hive 4.0.0 版本开始支持
AST：输出查询的抽象语法树。AST 在hive 2.1.0 版本删除了，存在bug，转储AST可能会导致OOM错误，将在4.0.0版本修复
DEPENDENCY：dependency在EXPLAIN语句中使用会产生有关计划中输入的额外信息。它显示了输入的各种属性
AUTHORIZATION：显示所有的实体需要被授权执行（如果存在）的查询和授权失败
LOCKS：这对于了解系统将获得哪些锁以运行指定的查询很有用。LOCKS 从 hive 3.2.0 开始支持
VECTORIZATION：将详细信息添加到EXPLAIN输出中，以显示为什么未对Map和Reduce进行矢量化。从 Hive 2.3.0 开始支持
ANALYZE：用实际的行数注释计划。从 Hive 2.2.0 开始支持
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
在 hive cli 中输入以下命令(hive 2.3.7)：

explain select sum(id) from test1;
得到结果（请逐行看完，即使看不懂也要每行都看）：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: test1
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: int)
              outputColumnNames: id
              Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: sum(id)
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  sort order:
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col0 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: sum(VALUE._col0)
          mode: mergepartial
          outputColumnNames: _col0
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

看完以上内容有什么感受，是不是感觉都看不懂，不要着急，下面将会详细讲解每个参数，相信你学完下面的内容之后再看 explain 的查询结果将游刃有余。
1
一个HIVE查询被转换为一个由一个或多个stage组成的序列（有向无环图DAG）。这些stage可以是MapReduce stage，也可以是负责元数据存储的stage，也可以是负责文件系统的操作（比如移动和重命名）的stage。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
我们将上述结果拆分看，先从最外层开始，包含两个大的部分：
stage dependencies： 各个stage之间的依赖性
stage plan： 各个stage的执行计划

先看第一部分 stage dependencies ，包含两个 stage，Stage-1 是根stage，说明这是开始的stage，Stage-0 依赖 Stage-1，Stage-1执行完成后执行Stage-0。

再看第二部分 stage plan，里面有一个 Map Reduce，一个MR的执行计划分为两个部分：
Map Operator Tree： MAP端的执行计划树
Reduce Operator Tree： Reduce端的执行计划树

这两个执行计划树里面包含这条sql语句的operator：
map端第一个操作肯定是加载表，所以就是 TableScan 表扫描操作，常见的属性：
alias： 表名称
Statistics： 表统计信息，包含表中数据条数，数据大小等
Select Operator： 选取操作，常见的属性 ：
expressions：需要的字段名称及字段类型
outputColumnNames：输出的列名称
Statistics：表统计信息，包含表中数据条数，数据大小等
Group By Operator：分组聚合操作，常见的属性：
aggregations：显示聚合函数信息
mode：聚合模式，值有 hash：随机聚合，就是hash partition；partial：局部聚合；final：最终聚合
keys：分组的字段，如果没有分组，则没有此字段
outputColumnNames：聚合之后输出列名
Statistics： 表统计信息，包含分组聚合之后的数据条数，数据大小等
Reduce Output Operator：输出到reduce操作，常见属性：
sort order：值为空 不排序；值为 + 正序排序，值为 - 倒序排序；值为 +-  排序的列为两列，第一列为正序，第二列为倒序
Filter Operator：过滤操作，常见的属性：
predicate：过滤条件，如sql语句中的where id>=1，则此处显示(id >= 1)
Map Join Operator：join 操作，常见的属性：
condition map：join方式 ，如Inner Join 0 to 1 Left Outer Join0 to 2
keys: join 的条件字段
outputColumnNames： join 完成之后输出的字段
Statistics： join 完成之后生成的数据条数，大小等
File Output Operator：文件输出操作，常见的属性
compressed：是否压缩
table：表的信息，包含输入输出文件格式化方式，序列化方式等
Fetch Operator 客户端获取数据操作，常见的属性：
limit，值为 -1 表示不限制条数，其他值为限制的条数

好，学到这里再翻到上面 explain 的查询结果，是不是感觉基本都能看懂了。

join 语句会过滤 null 的值吗？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
现在，我们在hive cli 输入以下查询计划语句

select a.id,b.user_name from test1 a join test2 b on a.id=b.id;
问：上面这条 join 语句会过滤 id 为 null 的值吗

执行下面语句：
explain select a.id,b.user_name from test1 a join test2 b on a.id=b.id;

我们来看结果 (为了适应页面展示，仅截取了部分输出信息)：
TableScan
 alias: a
 Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
 Filter Operator
    predicate: id is not null (type: boolean)
    Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
    Select Operator
        expressions: id (type: int)
        outputColumnNames: _col0
        Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
        HashTable Sink Operator
           keys:
             0 _col0 (type: int)
             1 _col0 (type: int)
 ...

从上述结果可以看到 predicate: id is not null 这样一行，说明 join 时会自动过滤掉关联字段为 null 值的情况，但 left join 或 full join 是不会自动过滤的，大家可以自行尝试下。

group by 分组语句会进行排序吗？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
看下面这条sql
select id,max(user_name) from test1 group by id;

问：group by 分组语句会进行排序吗
直接来看 explain 之后结果 (为了适应页面展示，仅截取了部分输出信息)
 TableScan
    alias: test1
    Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE
    Select Operator
        expressions: id (type: int), user_name (type: string)
        outputColumnNames: id, user_name
        Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE
        Group By Operator
           aggregations: max(user_name)
           keys: id (type: int)
           mode: hash
           outputColumnNames: _col0, _col1
           Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE
           Reduce Output Operator
             key expressions: _col0 (type: int)
             sort order: +
             Map-reduce partition columns: _col0 (type: int)
             Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE
             value expressions: _col1 (type: string)
 ...

我们看 Group By Operator，里面有 keys: id (type: int) 说明按照 id 进行分组的，再往下看还有 sort order: + ，说明是按照 id 字段进行正序排序的。

哪条sql执行效率高呢？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
观察两条sql语句
SELECT
    a.id,
    b.user_name
FROM
    test1 a
JOIN test2 b ON a.id = b.id
WHERE
    a.id > 2;

SELECT
    a.id,
    b.user_name
FROM
    (SELECT * FROM test1 WHERE id > 2) a
JOIN test2 b ON a.id = b.id;

这两条sql语句输出的结果是一样的，但是哪条sql执行效率高呢  
有人说第一条sql执行效率高，因为第二条sql有子查询，子查询会影响性能  
有人说第二条sql执行效率高，因为先过滤之后，在进行join时的条数减少了，所以执行效率就高了

到底哪条sql效率高呢，我们直接在sql语句前面加上 explain，看下执行计划不就知道了嘛
在第一条sql语句前加上 explain，得到如下结果
hive (default)> explain select a.id,b.user_name from test1 a join test2 b on a.id=b.id where a.id >2;
OK
Explain
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_0:a
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_0:a
          TableScan
            alias: a
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id > 2) (type: boolean)
              Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: int)
                outputColumnNames: _col0
                Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
                HashTable Sink Operator
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id > 2) (type: boolean)
              Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: int), user_name (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Inner Join 0 to 1
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)
                  outputColumnNames: _col0, _col2
                  Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col0 (type: int), _col2 (type: string)
                    outputColumnNames: _col0, _col1
                    Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                      table:
                          input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                          output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

在第二条sql语句前加上 explain，得到如下结果
hive (default)> explain select a.id,b.user_name from(select * from  test1 where id>2 ) a join test2 b on a.id=b.id;
OK
Explain
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_0:test1
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_0:test1
          TableScan
            alias: test1
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id > 2) (type: boolean)
              Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: int)
                outputColumnNames: _col0
                Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
                HashTable Sink Operator
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id > 2) (type: boolean)
              Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: int), user_name (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Inner Join 0 to 1
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)
                  outputColumnNames: _col0, _col2
                  Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col0 (type: int), _col2 (type: string)
                    outputColumnNames: _col0, _col1
                    Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                      table:
                          input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                          output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

大家有什么发现，除了表别名不一样，其他的执行计划完全一样，都是先进行 where 条件过滤，在进行 join 条件关联。说明 hive 底层会自动帮我们进行优化，所以这两条sql语句执行效率是一样的。
1
以上仅列举了3个我们生产中既熟悉又有点迷糊的例子，explain 还有很多其他的用途，如查看stage的依赖情况、排查数据倾斜、hive 调优等，小伙伴们可以自行尝试。

Hive性能调优的方式

1
2
3
为什么都说性能优化这项工作是比较难的，因为一项技术的优化，必然是一项综合性的工作，它是多门技术的结合。我们如果只局限于一种技术，那么肯定做不好优化的。

下面将从多个完全不同的角度来介绍Hive优化的多样性，我们先来一起感受下。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1. 数据存储及压缩：
针对 hive 中表的存储格式通常有 orc 和 parquet，压缩格式一般使用 snappy。相比与 textfile 格式表，orc 占有更少的存储。因为 hive 底层使用 MR 计算架构，数据流是 hdfs 到磁盘再到 hdfs，而且会有很多次，所以使用 orc 数据格式和 snappy 压缩策略可以降低 IO 读写，还能降低网络传输量，这样在一定程度上可以节省存储，还能提升 hql 任务执行效率；

2. 通过调参优化：
并行执行，调节 parallel 参数；

调节 jvm 参数，重用 jvm；

设置 map、reduce 的参数；开启 strict mode 模式；

关闭推测执行设置。

3. 有效地减小数据集将大表拆分成子表；结合使用外部表和分区表。
4. SQL 优化
大表对大表：尽量减少数据集，可以通过分区表，避免扫描全表或者全字段；

大表对小表：设置自动识别小表，将小表放入内存中去执行。

Hive 优化详细剖析可查看：https://mp.weixin.qq.com/s/0YL0skTG9448Os3Md7CIzg

1. SQL语句优化

union all
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
insert into table stu partition(tp) 
select s_age,max(s_birth) stat,'max' tp 
from stu_ori
group by s_age

union all

insert into table stu partition(tp) 
select s_age,min(s_birth) stat,'min' tp 
from stu_ori
group by s_age;

我们简单分析上面的SQL语句，就是将每个年龄段的最大和最小的生日获取出来放到同一张表中，union all 前后的两个语句都是对同一张表按照s_age进行分组，然后分别取最大值和最小值。


上面的SQL对同一张表的相同字段进行两次分组，这显然造成了极大浪费，我们能不能改造下呢，当然是可以的，为大家介绍一个语法： from ... insert into ... ，这个语法将from前置，作用就是使用一张表，可以进行多次插入操作：

--开启动态分区 
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict; 

from stu_ori 

insert into table stu partition(tp) 
select s_age,max(s_birth) stat,'max' tp 
group by s_age

insert into table stu partition(tp) 
select s_age,min(s_birth) stat,'min' tp 
group by s_age;

上面的SQL就可以对stu_ori表的s_age字段分组一次而进行两次不同的插入操作。

这个例子告诉我们一定要多了解SQL语句，如果我们不知道这种语法，一定不会想到这种方式的。
distinct
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
先看一个SQL，去重计数：
select count(1) 
from( 
  select s_age 
  from stu 
  group by s_age 
) b;

这是简单统计年龄的枚举值个数，为什么不用distinct？
select count(distinct s_age) 
from stu;

有人说因为在数据量特别大的情况下使用第一种方式(group by)能够有效避免Reduce端的数据倾斜，但事实如此吗？
我们先不管数据量特别大这个问题，就当前的业务和环境下使用distinct一定会比上面那种子查询的方式效率高。原因有以下几点：
上面进行去重的字段是年龄字段，要知道年龄的枚举值是非常有限的，就算计算1岁到100岁之间的年龄，s_age的最大枚举值才100，如果转化成MapReduce来解释的话，在Map阶段，每个Map会对s_age去重。由于s_age枚举值有限，因而每个Map得到的s_age也有限，最终得到reduce的数据量也就是map数量*s_age枚举值的个数。这个数量是很小的。

distinct的命令会在内存中构建一个hashtable，查找去重的时间复杂度是O(1)；group by在不同版本间变动比较大，有的版本会用构建hashtable的形式去重，有的版本会通过排序的方式， 排序最优时间复杂度无法到O(1)。另外，第一种方式(group by)去重会转化为两个任务，会消耗更多的磁盘网络I/O资源。

最新的Hive 3.0中新增了 count(distinct) 优化，通过配置 hive.optimize.countdistinct，即使真的出现数据倾斜也可以自动优化，自动改变SQL执行的逻辑。

第二种方式(distinct)比第一种方式(group by)代码简洁，表达的意思简单明了，如果没有特殊的问题，代码简洁就是优！

这个例子告诉我们，有时候我们不要过度优化，调优讲究适时调优，过早进行调优有可能做的是无用功甚至产生负效应，在调优上投入的工作成本和回报不成正比。调优需要遵循一定的原则。

2. 数据格式优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Hive提供了多种数据存储组织格式，不同格式对程序的运行效率也会有极大的影响。
Hive提供的格式有TEXT、SequenceFile、RCFile、ORC和Parquet等。
SequenceFile是一个二进制key/value对结构的平面文件，在早期的Hadoop平台上被广泛用于MapReduce输出/输出格式，以及作为数据存储格式。
Parquet是一种列式数据存储格式，可以兼容多种计算引擎，如MapRedcue和Spark等，对多层嵌套的数据结构提供了良好的性能支持，是目前Hive生产环境中数据存储的主流选择之一。
ORC优化是对RCFile的一种优化，它提供了一种高效的方式来存储Hive数据，同时也能够提高Hive的读取、写入和处理数据的性能，能够兼容多种计算引擎。事实上，在实际的生产环境中，ORC已经成为了Hive在数据存储上的主流选择之一。

我们执行同样的SQL语句及同样的数据，只是数据存储格式不同，得到如下执行时长：
数据格式		CPU时间	用户等待耗时
TextFile		33分		171秒
SequenceFile	38分		162秒
Parquet			2分22秒	50秒
ORC				1分52秒	56秒

注：CPU时间：表示运行程序所占用服务器CPU资源的时间。
用户等待耗时：记录的是用户从提交作业到返回结果期间用户等待的所有时间。

查询TextFile类型的数据表耗时33分钟， 查询ORC类型的表耗时1分52秒，时间得以极大缩短，可见不同的数据存储格式也能给HiveSQL性能带来极大的影响。

3. 小文件过多优化

1
2
3
小文件如果过多，对 hive 来说，在进行查询时，每个小文件都会当成一个块，启动一个Map任务来完成，而一个Map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的Map数量是受限的。

所以我们有必要对小文件过多进行优化，关于小文件过多的解决的办法，我之前专门写了一篇文章讲解，具体可查看：https://mp.weixin.qq.com/s?__biz=Mzg2MzU2MDYzOA==&mid=2247483683&idx=1&sn=14b25010032bdf0d375080e48de36d7f&scene=21#wechat_redirect

4. 并行执行优化

1
2
3
4
5
6
7
Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。如果有更多的阶段可以并行执行，那么job可能就越快完成。

通过设置参数hive.exec.parallel值为true，就可以开启并发执行。在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。
set hive.exec.parallel=true; //打开任务并行执行
set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度，默认为8。

当然得是在系统资源比较空闲的时候才有优势，否则没资源，并行也起不来。

5. JVM优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
JVM重用是Hadoop调优参数的内容，其对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短。

Hadoop的默认配置通常是使用派生JVM来执行map和Reduce任务的。这时JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含有成百上千task任务的情况。JVM重用可以使得JVM实例在同一个job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中进行配置。通常在10-20之间，具体多少需要根据具体业务场景测试得出。

<property>
  <name>mapreduce.job.jvm.numtasks</name>
  <value>10</value>
  <description>How many tasks to run per jvm. If set to -1, there is
  no limit. 
  </description>
</property>
我们也可以在hive中设置

set  mapred.job.reuse.jvm.num.tasks=10; //这个设置来设置我们的jvm重用
这个功能的缺点是，开启JVM重用将一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。如果某个“不平衡的”job中有某几个reduce task执行的时间要比其他Reduce task消耗的时间多的多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放。

6. 推测执行优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
在分布式集群环境下，因为程序bug（包括Hadoop本身的bug），负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。为了避免这种情况发生，Hadoop采用了推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。

设置开启推测执行参数：Hadoop的mapred-site.xml文件中进行配置：

<property>
  <name>mapreduce.map.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some map tasks 
               may be executed in parallel.</description>
</property>

<property>
  <name>mapreduce.reduce.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some reduce tasks 
               may be executed in parallel.</description>
</property>
hive本身也提供了配置项来控制reduce-side的推测执行:

set hive.mapred.reduce.tasks.speculative.execution=true
关于调优这些推测执行变量，还很难给一个具体的建议。如果用户因为输入数据量很大而需要执行长时间的map或者reduce task的话，那么启动推测执行造成的浪费是非常巨大的。
1
2
3
4
5
6
7
最后
代码优化原则：

理透需求原则，这是优化的根本；
把握数据全链路原则，这是优化的脉络；
坚持代码的简洁原则，这让优化更加简单；
没有瓶颈时谈论优化，这是自寻烦恼