HiveSQL性能调优

1、选择合适的文件存储格式

Hive支持TEXTFILE, SEQUENCEFILE, AVRO, RCFILE, ORC,以及PARQUET文件格式。
如果数据存储在小于块大小的小文件中,则可以使用SEQUENCE文件格式;如果要以减少存储空间并提高性能的优化方式存储数据,则可以使用ORC文件格式;当列中嵌套的数据过多时,Parquet格式会很有用。

2、减少扫描次数

如果要在某张hive表中执行多个操作,建议使用一次扫描并使用该扫描来执行多个操作。
比如将一张表的数据多次查询出来装载到另外一张表中。如下面的示例,表my_table是一个分区表,分区字段为dt,如果需要在表中查询2个特定的分区日期数据,并将记录装载到2个不同的表中。

1
2
INSERT INTO temp_table_20201115 SELECT * FROM my_table WHERE dt ='2020-11-15'
INSERT INTO temp_table_20201116 SELECT * FROM my_table WHERE dt ='2020-11-16'

在以上查询中,Hive将扫描表2次,为了避免这种情况,我们可以使用下面的方式:

1
2
3
FROM my_table
INSERT INTO temp_table_20201115 SELECT * WHERE dt ='2020-11-15'
INSERT INTO temp_table_20201116 SELECT * WHERE dt ='2020-11-16'

这样可以确保只对my_table表执行一次扫描,从而可以大大减少执行的时间和资源。

3、合理设置分区

对于一张比较大的表,将其设计成分区表可以提升查询的性能,对于一个特定分区的查询,只会加载对应分区路径的文件数据,因此,当用户使用特定分区列值执行选择查询时,将仅针对该特定分区执行查询,由于将针对较少的数据量进行扫描,所以可以提供更好的性能。值得注意的是,分区字段的选择是影响查询性能的重要因素,尽量避免层级较深的分区,这样会造成太多的子文件夹。
现在问题来了,该使用哪些列进行分区呢?一条基本的法则是:选择低基数属性作为”分区键”,比如”地区”或”日期”等。

4、使用分桶提升性能

分区代表了数据的仓库,也就是文件夹目录。每个文件夹下面可以放不同的数据文件。通过文件夹可以查询里面存放的文件。但文件夹本身和数据的内容毫无关系。
与分区不同,桶是以文件的形式存在的。物理上每个桶就是目录里的一个文件,一个作业产生的桶(输出文件)数量和reduce任务个数相同。
具体做法就是按照数据内容的某个值进行分桶,把一个大文件散列称为一个个小文件。这些小文件可以单独排序。
如果另外一个表也按照同样的规则分成了一个个小文件。两个表join的时候,就没必要扫描整个表,只需要匹配相同分桶的数据即可,效率当然大大提升。
同样,对数据抽样的时候,也不需要扫描整个文件。只需要对每个分区按照相同规则抽取一部分数据即可。

5、启用中间数据压缩

复杂的Hive查询通常会转换为一系列多阶段的MapReduce作业,并且这些作业将由Hive引擎链接起来以完成整个查询。因此,此处的”中间输出”是指上一个MapReduce作业的输出,它将用作下一个MapReduce作业的输入数据。
压缩可以显著减少中间数据量,从而在内部减少了Map和Reduce之间的数据传输量。

一些常用的压缩编码器
org.apache.hadoop.io.compress.DefaultCodec
org.apache.hadoop.io.compress.GzipCodec
org.apache.hadoop.io.compress.BZip2Codec
com.hadoop.compression.lzo.LzopCodec
org.apache.hadoop.io.compress.Lz4Codec
org.apache.hadoop.io.compress.SnappyCodec

1
2
3
4
5
6
7
-- 在中间输出上启用压缩
set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set hive.intermediate.compression.type=BLOCK;

-- 将最终输出到HDFS的数据进行压缩
set hive.exec.compress.output=true;

6、启用map端join

一旦开启map端join配置,Hive会自动检查小表是否大于hive.mapjoin.smalltable.filesize配置的大小,如果大于则转为普通的join,如果小于则转为map端join。

1
2
3
4
SET hive.auto.convert.join=true; --  hivev0.11.0之后默认true
SET hive.mapjoin.smalltable.filesize=600000000; -- 默认 25m
SET hive.auto.convert.join.noconditionaltask=true; -- 默认true,所以不需要指定map join hint
SET hive.auto.convert.join.noconditionaltask.size=10000000; -- 控制加载到内存的表的大小

7、启用查询向量化

Hive中的向量化查询执行大大减少了典型查询操作(如扫描,过滤器,聚合和连接)的CPU使用率。
标准查询执行系统一次处理一行,在处理下一行之前,单行数据会被查询中的所有运算符进行处理,导致CPU使用效率非常低。在向量化查询执行中,数据行被批处理在一起(默认=> 1024行),表示为一组列向量。
要使用向量化查询执行,必须以ORC格式(CDH 5)存储数据,并设置以下变量。

1
SET hive.vectorized.execution.enabled=true

在CDH 6中默认启用Hive查询向量化,启用查询向量化后,还可以设置其他属性来调整查询向量化的方式。

8、启用谓词下推

1
2
3
4
SELECT a.*, b.* 
FROM a join b
on (a.col1 = b.col1)
WHERE a.col1 > 15 AND b.col2 > 16

如果没有谓词下推,join将首先发生,并且可能产生更多的行,然后再进行过滤操作。
使用谓词下推,这两个谓词 (a.col1> 15和b.col2> 16) 将在join之前被处理,它可能会从a和b的连接中过滤掉大部分的数据行。因此,建议启用谓词下推。

1
SET hive.optimize.ppd=true

9、启动严格模式

如果要查询分区的Hive表,但不提供分区谓词(分区列条件),则在这种情况下,将针对该表的所有分区发出查询,这可能会非常耗时且占用资源。

1
SET hive.partition.pruning=strict  -- 在分区表上未提供分区谓词的情况下编译器将引发错误
0%