23-Hive优化(上)

1. Hive 表设计优化

1.1 分区表

a. 基本查询原理

Hive 的设计思想是通过元数据将 HDFS 上的文件映射成表，基本的查询原理是当用户通过 HQL 语句对 Hive 中的表进行复杂数据处理和计算时，默认将其转换为分布式计算 MapReduce 程序对 HDFS 中的数据进行读取处理的过程。

例如，当我们在 Hive 中创建一张表 tb_login 并关联 HDFS上 的文件，用于存储所有用户的登录信息，当我们对这张表查询数据时，Hive 中的实现过程如下：

use tree;
create table tb_login
(
    userid    string,
    logintime string
) row format delimited fields terminated by '\t';
load data local inpath '/home/liujiaqi/hivedata/window_func/login.log' into table tb_login;

HDFS 中自动在 Hive 数据仓库的目录下和对应的数据库目录下，创建表的目录，并且数据会被自动放入 HDFS 中对应的表的目录下：

当执行查询计划时，Hive 会使用表的最后一级目录作为底层处理数据的输入。先根据表名在元数据中进行查询表对应的HDFS目录，然后将整个 HDFS 中表的目录作为底层查询的输入。

可以通过 explain extended 命令查看执行计划依赖的数据：

b. 分区设计思想

默认的普通表结构中，表的最后一级目录就是表的目录，而底层的计算会使用表的最后一级目录作为 Input 进行计算，这种场景下，我们就会遇到一个问题，如果表的数据很多，而我们需要被处理的数据很少，只是其中一小部分，这样就会导致大量不必要的数据被程序加载，在程序中被过滤，导致大量不必要的计算资源的浪费。

例如，上面的需求中，只需要对 2021-03-24 的数据进行计算，但实际上由于表结构的设计，在底层执行 MapReduce 时，将整张表的数据都进行了加载，MapReduce 程序中必须对所有数据进行过滤，将 3 月 24 日的数据过滤出来，再进行处理。假设每天有 1G 的数据增量，一年就是 365GB 的数据，按照业务需求，我们每次只需要对其中一天的数据进行处理，也就是处理 1GB 的数据，程序会先加载 365GB 的数据，然后将 364GB 的数据过滤掉，只保留一天的数据再进行计算，导致了大量的磁盘和网络的 IO 的损耗。

针对上面的问题，Hive 提供了一种特殊的表结构来解决 — 分区表结构。分区表结构的设计思想是：根据查询的需求，将数据按照查询的条件（一般都以时间）进行划分分区存储，将不同分区的数据单独使用一个 HDFS 目录来进行存储，当底层实现计算时，根据查询的条件，只读取对应分区的数据作为输入，减少不必要的数据加载，提高程序的性能。

例如，上面的需求中，我们可以将每天的用户登录数据，按照登陆日期进行分区存储到 Hive 表中，每一天一个分区，在 HDFS 的底层就可以自动实现将每天的数据存储在不同的目录中，当用户查询某天的数据时，可以直接使用这一天的分区目录进行处理，不需要加载其他数据。

c. 分区表测试

基于分区表的设计实现将所有用户的登录信息进行分区存储：

查询 2021-03-23 或 2021-03-24 的数据进行统计：

select logindate, count(*) as cnt
from tb_login_part
where logindate = '2021-03-23'
   or logindate = '2021-03-24'
group by logindate;

查询先检索元数据，元数据中记录该表为分区表并且查询过滤条件为分区字段，所以找到该分区对应的 HDFS 目录；再加载对应分区的目录作为计算程序的输入。

查询的执行计划：

1.2 分桶表

a. Join 的问题

表的 Join 是数据分析处理过程中必不可少的操作，Hive 同样支持 Join 的语法，Hive Join 的底层还是通过 MapReduce 来实现的，但是 Hive 实现 Join 时面临一个问题：如果有两张非常大的表要进行 Join，两张表的数据量都很大，Hive 底层通过 MapReduce 实现时，无法使用 MapJoin 提高 Join 的性能，只能走默认的 ReduceJoin，而 ReduceJoin 必须经过 Shuffle 过程，相对性能比较差，而且容易产生数据倾斜，如何解决这个问题？

b. 分桶设计思想

针对以上的问题，Hive 中提供了另外一种表的结构 — 分桶表结构。

分桶表的设计有别于分区表的设计，分区表是将数据划分不同的目录进行存储，而分桶表是将数据划分不同的文件进行存储。分桶表的设计是按照一定的规则（通过 MapReduce 中的多个 Reduce 来实现）将数据划分到不同的文件中进行存储，构建分桶表。

如果有两张表按照相同的划分规则（按照 Join 的关联字段）将各自的数据进行划分，在 Join 时就可以实现 Bucket 与 Bucket 的 Join，避免不必要的比较，减少笛卡尔积数量。

例如：当前有两张表，订单表有 1000w 条，用户表有 10w 条，两张表的关联字段是 userid，现在要实现两张表的 Join。我们将订单表按照 userid 划分为 3 个桶，1000w 条数据按照 userid 的 hash 取余存储在对应的 Bucket 中。

c. 分桶表测试

当前有两份较大的数据文件，emp 员工数据和 dept 部门数据，现在要基于 Hive 实现两张表的 Join，我们可以通过分桶实现分桶 Join 提高性能。

-- 1. 创建员工表
create table tb_emp01
(
    empno     string,
    ename     string,
    job       string,
    managerid string,
    hiredate  string,
    salary    double,
    jiangjin  double,
    deptno    string
) row format delimited fields terminated by '\t';
-- 加载数据
load data local inpath '/home/liujiaqi/hivedata/day6/emp01.txt' into table tb_emp01;

-- 2. 创建分桶员工表
create table tb_emp02
(
    empno     string,
    ename     string,
    job       string,
    managerid string,
    hiredate  string,
    salary    double,
    jiangjin  double,
    deptno    string
)
    clustered by (deptno) sorted by (deptno asc) into 3 buckets
    row format delimited fields terminated by '\t';
-- 写入分桶表
insert overwrite table tb_emp02
select *
from tb_emp01;


-- 3. 创建部门表
create table tb_dept01
(
    deptno string,
    dname  string,
    loc    string
)
    row format delimited fields terminated by ',';
-- 加载数据
load data local inpath '/home/liujiaqi/hivedata/day6/dept01.txt' into table tb_dept01;

-- 4. 创建分桶部门表
create table tb_dept02
(
    deptno string,
    dname  string,
    loc    string
)
    clustered by (deptno) sorted by (deptno asc) into 3 buckets
    row format delimited fields terminated by ',';
-- 写入分桶表
insert overwrite table tb_dept02
select *
from tb_dept01;

查看执行计划：

1.3 索引

a. Hive 索引

在传统的关系型数据库例如 MySQL、Oracle 等数据库中，为了提高数据的查询效率，可以为表中的字段单独构建索引，查询时，可以基于字段的索引快速的实现查询、过滤等操作。
Hive 中也同样提供了索引的设计，允许用户为字段构建索引，提高数据的查询效率。但是Hive的索引与关系型数据库中的索引并不相同，比如，Hive 不支持主键或者外键。Hive 索引可以建立在表中的某些列上，以提升一些操作的效率，例如减少 MapReduce 任务中需要读取的数据块的数量。

在可以预见到分区数据非常庞大的情况下，分桶和索引常常是优于分区的。而分桶由于 SMB Join 对关联键要求严格，所以并不是总能生效。

官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create/Drop/AlterIndex

【注意】官方明确表示，索引功能支持是从 Hive0.7 版本开始，到 Hive3.0 不再支持。

b. 索引的原理及使用

Hive 中索引的基本原理：当为某张表的某个字段创建索引时，Hive 中会自动创建一张索引表，该表记录了该字段的每个值与数据实际物理位置之间的关系，例如数据所在的 HDFS 文件地址，以及所在文件中偏移量 offset 等信息。

Hive 的索引目的是提高 Hive 表指定列的查询速度。没有索引时，类似 WHERE tab1.col1 = 10 的查询，Hive 会加载整张表或分区，然后处理所有的 rows，但是如果在字段 col1 上面存在索引时，那么只会加载和处理文件的一部分。

构建数据时，Hive 会根据索引字段的值构建索引信息，将索引信息存储在索引表中；查询数据时，Hive 会根据索引字段查询索引表，根据索引表的位置信息读取对应的文件数据。

下面我们来实现索引的构建，例如：当前有一张分区表 tb_login_part，默认查询数据时是没有索引的，当查询登陆日期时可以通过分区过滤来提高效率，但是如果想按照用户 ID 进行查询，就无法使用分区进行过滤，只能全表扫描数据。如果我们需要经常按照用户 ID 查询，那么性能就会相对较差，我们可以基于用户 ID 构建索引来加快查询效率。

（1）创建索引

-- 为表中的 userid 构建索引
CREATE INDEX idx_user_id_login ON TABLE tb_login_part (userid)
-- 索引类型为 Compact（Hive 支持 Compact/Bitmap 类型，存储的索引内容不同）
as 'COMPACT'
-- 延迟构建索引
with deferred rebuild;

（2）构建索引

-- 通过运行一个 MapReduce 程序来构建索引
ALTER INDEX idx_user_id_login ON tb_login_part rebuild;

（3）查看索引

DESC default__tb_login_part_idx_user_id_login__;
select * from default__tb_login_part_idx_user_id_login__;

（4）删除索引

DROP INDEX idx_user_id_login ON tb_login_part;

c. 索引的问题

Hive 构建索引的过程是通过一个 MapReduce 程序来实现的，这就导致了 Hive 的一个问题，每次 Hive 中原始数据表的数据发生更新时，索引表不会自动更新，必须手动执行一个 ALTER INDEX 命令来实现通过 MapReduce 更新索引表，导致整体性能较差，维护相对繁琐。

由于 Hive 的索引设计过于繁琐，所以从 Hive3.0 版本开始，取消了对 Hive Index 的支持及使用，不过如果使用的是 Hive1.x 或 Hive2.x 在特定的场景下依旧可以使用 Hive Index 来提高性能。

实际工作场景中，一般不推荐使用 Hive Index，推荐使用 ORC 文件格式中的索引来代替 Hive Index 提高查询性能。

2. Hive 表存储优化

2.1 文件格式

Hive 数据存储的本质还是 HDFS，所有的数据读写都基于 HDFS 的文件来实现，为了提高对 HDFS 文件读写的性能，Hive 中提供了多种文件存储格式：TextFile、SequenceFile、RCFile、ORC、Parquet 等。不同的文件存储格式具有不同的存储特点，有的可以降低存储空间，有的可以提高查询性能等，可以用来实现不同场景下的数据存储，以提高对于数据文件的读写效率。

Text File 和 Sequence File 都是基于行存储的，ORC 和 Parquet 是基于列式存储的。

Hive 的文件格式在建表时指定（默认 TextFile）：

a. TextFile

TextFile 是 Hive 中默认的文件格式，存储形式为按行存储。

工作中最常见的数据文件格式就是 TextFile 文件，几乎所有的原始数据生成都是 TextFile 格式，所以 Hive 设计时考虑到为了避免各种编码及数据错乱的问题，选用了 TextFile 作为默认的格式。

建表时不指定存储格式即为 TextFile，导入数据时把数据文件拷贝至 HDFS 不进行处理。

优点	缺点	应用场景
1. 最简单的数据格式，不需要经过处理，可以直接 cat 查看；2. 可以使用任意的分隔符进行分割；3. 便于和其他工具（grep、sed、awk）共享数据；4. 可以搭配 Gzip、Bzip2、Snappy 等压缩一起使用	1. 耗费存储空间，I/O 性能较低；2. 结合压缩时 Hive 不进行数据切分合并，不能进行并行操作，查询效率低；3. 按行存储，读取列的性能差	1. 适合于小量数据的存储查询；2. 一般用于做第一层数据加载和测试使用

（1）创建原始数据表

-- 创建原始数据表
create table tb_sogou_source
(
    stime      string,
    userid     string,
    keyword    string,
    clickorder string,
    url        string
) row format delimited fields terminated by '\t';

-- 加载原始数据
load data local inpath '/export/data/SogouQ.reduced' into table tb_sogou_source;

（2）创建 TextFile 数据表

-- 创建 TextFile 数据表
create table tb_sogou_text
(
    stime      string,
    userid     string,
    keyword    string,
    clickorder string,
    url        string
) row format delimited fields terminated by '\t'
stored as textfile;

-- 写入 TextFile 数据表
insert into table tb_sogou_text
select * from tb_sogou_source;

b. SequenceFile

SequenceFile 是 Hadoop 里用来存储序列化的键值对即二进制的一种文件格式。SequenceFile 文件也可以作为 MapReduce 作业的输入和输出，Hive 也支持这种格式。

优点	缺点	应用场景
1. 以二进制的 KV 形式存储数据，与底层交互更加友好，性能更快；2. 可压缩、可分割，优化磁盘利用率和 I/O；3. 可并行操作数据，查询效率高；4. SequenceFile 也可以用于存储多个小文件。	1. 存储空间消耗最大；2. 与 !Hadoop 生态系统之外的工具不兼容；3. 构建 SequenceFile 需要通过 TextFile 文件转化加载。	适合于小量数据，但是查询列比较多的场景

测试：

-- 创建 SequenceFile 数据表
create table tb_sogou_seq
(
    stime      string,
    userid     string,
    keyword    string,
    clickorder string,
    url        string
)
row format delimited fields terminated by '\t'
stored as sequencefile;

-- 写入 SequenceFile 数据表
insert into table tb_sogou_seq
select * from tb_sogou_source;

c. ORC

ORC（OptimizedRC File）文件格式也是一种 Hadoop 生态圈中的列式存储格式。它的产生早在 2013 年初，最初产生自 Apache Hive，用于降低 Hadoop 数据存储空间和加速 Hive 查询速度。

它并不是一个单纯的列式存储格式，仍然是首先根据行组分割整个表，在每一个行组内进行按列存储。ORC 文件是自描述的，它的元数据使用 Protocol Buffers 序列化，并且文件中的数据尽可能的压缩以降低存储空间的消耗，目前也被 Hive、Spark SQL、Presto 等查询引擎支持。2015 年 ORC 项目被 Apache 项目基金会提升为 Apache 顶级项目。

ORC 文件也是以二进制方式存储的，所以是不可以直接读取，ORC 文件也是自解析的，它包含许多的元数据，这些元数据都是同构 ProtoBuffer 进行序列化的。

优点	缺点	应用场景
1. 列式存储，存储效率非常高；2. 可压缩，高效的列存取；3. 查询效率较高，支持索引；4. 支持矢量化查询。	1. 加载时性能消耗较大；2. 需要通过 TextFile 转化生成；3. 读取全量数据时性能较差。	适用于 Hive 中大型的存储、查询。

ORC 文件结构如下：

每个 ORC 文件由 Header、Body 和 Tail 三部分组成。

（1）Header 内容为 ORC，用于表示文件类型。

（2）Body 由 1 个或多个 Stripe 组成，每个 Stripe 一般为 HDFS 的块大小，每一个 Stripe 包含多条记录，这些记录按照列进行独立存储，每个 Stripe 里有三部分组成，分别是 Index Data、Row Data、Stripe Footer。

• Index Data：一个轻量级的 index，默认是为各列每隔 1w 行做一个索引。每个索引会记录第 N 万行的位置，和最近 1w 行的最大值和最小值等信息；
• Row Data：存的是具体的数据，按列进行存储，并对每个列进行编码，分成多个 Stream 来存储；
• Stripe Footer：存放的是各个 Stream 的位置以及各 column 的编码信息。

（3）Tail 由 File Footer 和 PostScript 组成。

• File Footer 中保存了各 Stripe 的起始位置、索引长度、数据长度等信息，各 column 的统计信息等；
• PostScript 记录了整个文件的压缩类型以及 File Footer 的长度信息等；
• 整个文件的最后一个字节用来记录 PostScript 长度。

在读取 ORC 文件时，会先从最后一个字节读取 PostScript 长度，进而读取到 PostScript，从里面解析到 File Footer 长度，进而读取 FileFooter，从中解析到各个 Stripe 信息，再读各个 Stripe，即从后往前读。

查看一张 ORC 表的建表详细信息：

ORC · tblproperties 支持的参数如下：

参数	默认值	说明
orc.compress	ZLIB	压缩格式，可选项：NONE、ZLIB,、SNAPPY
orc.compress.size	262,144	每个压缩块的大小（ORC 文件是分块压缩的）
orc.stripe.size	67,108,864	每个 Stripe 的大小（结合 HDFS BLOCK）
orc.row.index.stride	10,000	索引步长（每隔多少行数据建一条索引 must be >= 1k）

测试：

-- 创建 ORC 数据表
create table tb_sogou_orc
(
    stime      string,
    userid     string,
    keyword    string,
    clickorder string,
    url        string
)
row format delimited fields terminated by '\t'
stored as orc;
-- tblproperties (property_name=property_value, ...); ｜参数如上｜

-- 写入 ORC 数据表
insert into table tb_sogou_orc
select * from tb_sogou_source;

d. Parquet

Parquet 是一种支持嵌套结构的列式存储文件格式，最早是由 Twitter 和 Cloudera 合作开发，2015 年 5 月从 Apache 孵化器里毕业成为 Apache 顶级项目。是一种支持嵌套数据模型对的列式存储系统，作为大数据系统中 OLAP 查询的优化方案，它已经被多种查询引擎原生支持，并且部分高性能引擎将其作为默认的文件存储格式。通过数据编码和压缩，以及映射下推和谓词下推功能，Parquet 的性能也较之其它文件格式有所提升。

上图展示了一个 Parquet 文件的基本结构：

（1）文件的首尾都是该文件的 Magic Code，用于校验它是否是一个 Parquet 文件。

（2）首尾中间由若干个 Row Group 和一个 Footer（File Meta Data）组成。

+++++ 嵌套组成 +++++	每个 Row Group 包含多个 Column Chunk，每个 Column Chunk 又包含多个 Page。
行组（Row Group）	一个「行组」对应逻辑表中的若干行。在水平方向上将数据划分为行组，默认行组大小与 HDFS Block 块大小对齐，Parquet 保证一个行组会被一个 Mapper 处理。
列块（Column Chunk）	行组中每一列保存在一个「列块」中，一个列块具有相同的数据类型，不同的列块可以使用不同的压缩。
页（Page）	Parquet 是页存储方式，每一个列块包含多个「页」。页是最小的编码的单位，同一列块的不同页可以使用不同的编码方式。

（3）Footer（File Meta Data）中存储了每个行组（Row Group）中的每个列块（Column Chunk）的元数据信息，元数据信息包含了该列的数据类型、编码方式、Data Page 位置等信息。

Parquet 是与语言无关的，而且不与任何一种数据处理框架绑定在一起，适配多种语言和组件，能够与 Parquet 适配的查询引擎包括 Hive、Impala、Pig、Presto、Drill、Tajo、HAWQ、IBM Big SQL 等，计算框架包括 MapReduce、Spark、Cascading、Crunch、Scalding、Kite 等。

Parquet 是 Hadoop 生态圈中主流的列式存储格式，并且行业内流行这样一句话流传：如果说 HDFS 是大数据时代文件系统的事实标准，Parquet 就是大数据时代存储格式的事实标准。Hive 中也同样支持使用 Parquet 格式来实现数据的存储，并且是工作中主要使用的存储格式之一。

优点	缺点	应用场景
1. 更高效的压缩和编码；2. 可用于多种数据处理框架。	不支持 update/insert/delete、ACID	适用于字段数非常多，无更新，只取部分列的查询。

Parquet · tblproperties 支持的参数如下：

参数	默认值	说明
parquet.compression	uncompressed	压缩格式，可选项：uncompressed，snappy，gzip，lzo，brotli，lz4
parquet.block.size	134217728	行组大小，通常与 HDFS BLOCK 大小保持一致
parquet.page.size	1048576	页大小（压缩时是按页压缩）

测试：

-- 创建 Parquet 数据表
create table tb_sogou_parquet
(
    stime      string,
    userid     string,
    keyword    string,
    clickorder string,
    url        string
)
row format delimited fields terminated by '\t'
stored as parquet;


-- 写入 Parquet 数据表
insert into table tb_sogou_parquet
select * from tb_sogou_source;

2.2 数据压缩

在 表存储和计算过程中，保持数据的压缩，对磁盘空间的有效利用和提高查询性能都是十分有益的。

a. Hive 表压缩

在 Hive 中，不同文件类型的表，声明数据压缩的方式是不同的。

（1）TextFile

若一张表的文件类型为 TextFile，若需要对该表中的数据进行压缩，多数情况下，无需在建表语句做出声明。直接将压缩后的文件导入到该表即可，Hive 在查询表中数据时，可自动识别其压缩格式，进行解压。

需要注意的是，在执行往表中导入数据的SQL语句时，用户需设置以下参数，来保证写入表中的数据是被压缩的。

-- SQL 的最终输出结果是否压缩
set hive.exec.compress.output=true;
-- 输出结果的压缩格式（以下示例为Snappy）
set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;

（2）ORC

若一张表的文件类型为 ORC，若需要对该表数据进行压缩，需在建表语句中声明压缩格式如下：

create table orc_table
(column_specs)
stored as orc
tblproperties ("orc.compress"="snappy");

（3）Parquet

若一张表的文件类型为 Parquet，若需要对该表数据进行压缩，需在建表语句中声明压缩格式如下：

create table orc_table
(column_specs)
stored as parquet
tblproperties ("parquet.compression"="snappy");

b. 计算过程中压缩

Hive 底层转换 HQL 运行 MapReduce 程序时，磁盘 I/O 操作、网络数据传输、shuffle 和 merge 要花大量的时间，尤其是数据规模很大和工作负载密集的情况下，鉴于磁盘 I/O 和网络带宽是 Hadoop 的宝贵资源，数据压缩对于节省资源、最小化磁盘 I/O 和网络传输非常有帮助。

如果磁盘 I/O 和网络带宽影响了 MapReduce 作业性能，在任意 MapReduce 阶段启用压缩都可以改善端到端处理时间并减少 I/O 和网络流量。

优点	缺点
1. 减小文件存储所占空间；2. 加快文件传输效率，从而提高系统的处理速度；3. 降低 IO 读写的次数。	用数据时需要先对文件解压，加重 CPU 负荷，压缩算法越复杂，解压时间越长。

（1）单个 MR 的中间结果进行压缩

Mapper 输出的数据，对其进行压缩可降低 shuffle 阶段的网络 IO，可通过以下参数进行配置：

-- 开启MapReduce中间数据压缩功能
set mapreduce.map.output.compress=true;
-- 设置MapReduce中间数据数据的压缩方式（以下示例为Snappy）
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

（2）单条 SQL 语句的中间结果进行压缩

两个 MR（一条 SQL 可能需要通过多个 MR 进行计算）之间的临时数据，可通过以下参数进行配置：

-- 是否对两个MR之间的临时数据进行压缩
set hive.exec.compress.intermediate=true;
-- 压缩格式（以下示例为Snappy）
set hive.intermediate.compression.codec= org.apache.hadoop.io.compress.SnappyCodec;

（3）输出结果压缩

set hive.exec.compress.output=true;

Hive 中的压缩就是使用了 Hadoop 中的压缩实现的，所以 Hadoop 中支持的压缩在 Hive 中都可以直接使用。Hadoop 中支持的压缩算法如下：

上面说的配置都是临时配置，如果想要永久配置，则需要修改配置文件。

• 将以上 MapReduce 的配置写入 mapred-site.xml 中，重启 Hadoop；
• 将以上 Hive 的配置写入 hive-site.xml 中，重启 Hive。

2.3 小文件合并

a. 优化说明

“小文件合并优化”分为两个方面，分别是「Map 端输入的小文件合并」和「Reduce 端输出的小文件合并」。

（1）Map 端输入文件合并

合并 Map 端输入的小文件，是指将多个小文件划分到一个切片中，进而由一个 Map Task 去处理。目的是防止为单个小文件启动一个 Map Task，浪费计算资源。

相关参数如下：

-- 可将多个小文件切片，合并为一个切片，进而由一个map task处理
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

（2）Reduce 端输出文件合并

合并 Reduce 端输出的小文件，是指将多个小文件合并成大文件。目的是减少 HDFS 小文件数量。其原理是根据计算任务输出文件的平均大小进行判断，若符合条件，则单独启动一个额外的任务进行合并。

相关参数如下：

-- 开启合并Map Only任务输出的小文件
set hive.merge.mapfiles=true;

-- 开启合并MR任务输出的小文件
set hive.merge.mapredfiles=true;

-- 触发小文件合并任务的阈值，若某计算任务输出的文件平均大小低于该值，则触发合并
set hive.merge.smallfiles.avgsize=16000000;

-- 合并后的文件大小
set hive.merge.size.per.task=256000000;

b. 优化案例

现有一个需求，计算各省份订单金额总和，下表为结果表。

drop table if exists order_amount_by_province;
create table order_amount_by_province(
    province_id string comment '省份id',
    order_amount decimal(16,2) comment '订单金额'
)
location '/order_amount_by_province';

示例 SQL：

insert overwrite table order_amount_by_province
select
    province_id,
    sum(total_amount)
from order_detail
group by province_id;

优化前，根据任务并行度一节的内容，可分析出，默认情况下该 SQL 的 Reduce 端并行度为 5，故最终输出文件个数也为 5，下图为输出文件，可以看出 5 个均为小文件。

若想避免小文件的产生，可采取方案有两个：

（1）合理设置任务的 Reduce 端并行度

若将上述计算任务的并行度设置为 1，就能保证其输出结果只有一个文件。

（2）启用 Hive 合并小文件优化

set hive.merge.mapredfiles=true;
set hive.merge.smallfiles.avgsize=16000000;
set hive.merge.size.per.task=256000000;

再次执行上述的 insert 语句，观察结果表中的文件，只剩一个了。

2.4 ORC 优化

a. ORC 文件索引

在使用 ORC 文件时，为了加快读取 ORC 文件中的数据内容，ORC 提供了两种索引机制：Row Group Index 和 Bloom Filter Index。

可以帮助提高查询 ORC 文件的性能，当用户写入数据时，可以指定构建索引，当用户查询数据时，可以根据索引提前对数据进行过滤，避免不必要的数据扫描。

Row Group Index

一个 ORC 文件包含一个或多个 stripes（Groups of Row Data），每个 stripe 中包含了每个 column 的 min/max 值的索引数据，当查询中有 <,>,= 的操作时，会根据 min/max 值，跳过扫描不包含的 stripes。而其中为每个 stripe 建立的包含 min/max 值的索引，就称为 Row Group Index 行组索引，也叫 min-max Index 大小对比索引或 Storage Index。

在建立 ORC 格式表时，指定表参数 orc.create.index=true 之后，便会建立 Row Group Index，需要注意的是，为了使 Row Group Index 有效利用，向表中加载数据时，必须对需要使用索引的字段进行排序，否则 min/max 会失去意义。另外，这种索引主要用于数值型字段的范围查询过滤优化上。

-- 1. 开启索引配置（永久生效需配置在 hive-site.xml 中）
set hive.optimize.index.filter=true;

-- 2. 创建表，并指定构建索引
create table tb_sogou_orc_index
    stored as orc tblproperties ("orc.create.index" = "true")
as
select *
from tb_sogou_source
    distribute by stime
    sort by stime;

-- 3. 当进行范围或者等值查询（<,>,=）时就可以基于构建的索引进行查询
select count(*)
from tb_sogou_orc_index
where stime > '12:00:00'
  and stime < '18:00:00';

Bloom Filter Index

建表时候，通过表参数 orc.bloom.filter.columns=... 来指定为哪些字段建立 BloomFilter 索引，这样在生成数据的时候，会在每个 stripe 中，为该字段建立 BloomFilter 的数据结构，当查询条件中包含对该字段的 = 过滤时候，先从 BloomFilter 中获取以下是否包含该值，如果不包含，则跳过该 stripe。

与此同时，在 Hive 中使用布隆过滤器，虽然可以用较少的文件空间快速判定数据是否存表中，但是也存在将不属于这个表的数据误判为属于这张表的情况，这个称之为「假正（False Positive）概率」，开发者可以通过表参数 orc.bloom.filter.fpp=[0.0,1.0] 调整该概率（默认值是 0.05）。

-- 1. 创建表，并指定构建索引
create table tb_sogou_orc_bloom
    stored as orc tblproperties ("orc.create.index" = "true","orc.bloom.filter.columns" = "stime,userid")
as
select *
from tb_sogou_source
    distribute by stime
    sort by stime;

-- 2. stime 的范围过滤可以走 row group index / userid 的过滤可以走 bloom filter index
select count(*)
from tb_sogou_orc_index
where stime > '12:00:00'
  and stime < '18:00:00'
  and userid = '3933365481995287';

b. ORC 矢量化查询

要使用矢量化查询执行，就必须以 ORC 格式存储数据。

Hive 的默认查询执行引擎一次处理一行，而矢量化查询执行是一种 Hive 针对 ORC 文件操作的特性，目的是按照每批 1024 行读取数据，并且一次性对整个记录整合（而不是对单条记录）应用操作，极大的提高一些典型查询场景（例如 scans, filters, aggregates, and joins）下的 CPU 使用效率。

set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;

Hive 的矢量化查询优化，依赖于 CPU 的矢量化计算，CPU 的矢量化计算的基本原理如下图：

若执行计划中出现“Execution mode: vectorized”字样，即表明使用了矢量化计算。

参考链接：https://cwiki.apache.org/confluence/display/Hive/Vectorized+Query+Execution#VectorizedQueryExecution-Limitations

3. Job 执行优化

3.1 Explain 执行计划

HiveQL 是一种类 SQL 的语言，从编程语言规范来说是一种声明式语言，用户会根据查询需求提交声明式的 HQL 查询，而 Hive 会根据底层计算引擎将其转化成 Mapreduce/Tez/Spark 的 Job。大多数情况下，用户不需要了解 Hive 内部是如何工作的，不过，当用户对于 Hive 具有越来越多的经验后，尤其是需要在做性能优化的场景下，就要学习下 Hive 背后的理论知识以及底层的一些实现细节，会让用户更加高效地使用 Hive。

Explain 命令就可以帮助用户了解一条 HQL 语句在底层的实现过程。

Explain 会解析 HQL 语句，将整个 HQL 语句的实现步骤、依赖关系、实现过程都会进行解析返回，可以帮助更好地了解一条 HQL 语句在底层是如何实现数据的查询及处理的过程，这样可以辅助用户对 Hive 进行优化。

Explain 呈现的执行计划，由一系列 Stage 组成，这一系列 Stage 具有依赖关系，每个 Stage 对应一个 MapReduce Job，或者一个文件系统操作等。

若某个 Stage 对应的一个 MapReduce Job，其 Map 端和 Reduce 端的计算逻辑分别由 Map Operator Tree 和 Reduce Operator Tree 进行描述，Operator Tree 由一系列的 Operator 组成，一个 Operator 代表在 Map 或 Reduce 阶段的一个单一的逻辑操作，例如 TableScan Operator、Select Operator、Join Operator 等。

Operator	说明
TableScan	表扫描操作（通常 map 端第一个操作肯定是表扫描操作）
Select Operator	选取操作
Group By Operator	分组聚合操作
Reduce Output Operator	输出到 reduce 端操作
Filter Operator	过滤操作
Join Operator	join 操作
File Output Operator	文件输出操作
Fetch Operator	客户端获取数据操作

语法如下：

EXPLAIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION] <query_statement>
-- FORMATTED            对执行计划进行格式化，返回 JSON 格式的执行计划
-- EXTENDED             提供一些额外的信息，比如文件的路径信息
-- DEPENDENCY           输出执行计划读取的表及分区
-- AUTHORIZATION        列出需要被授权的条目，包括输入与输出

解析后的执行计划一般由三个部分构成，分别是：

1. The Abstract Syntax Tree for the query（抽象语法树）：Hive 使用 Antlr 解析生成器，可以自动地将 HQL 生成为抽象语法树；
2. The dependencies between the different stages of the plan（Stage 依赖关系）：会列出运行查询所有的依赖以及 Stage 的数量；
3. The description of each of the stages（Stage 内容）：包含了非常重要的信息，比如运行时的 Operator 和 Sort Orders 等具体的信息。

3.2 计算资源配置

计算资源的调整主要包括 Yarn 和 MR。

a. YARN

需要调整的 Yarn 参数均与 CPU、内存等资源有关，核心配置参数（$HADOOP_HOME/etc/hadoop/yarn-site.xml）如下：

（1）yarn.nodemanager.resource.memory-mb

该参数的含义是，一个 NodeManager 节点分配给 Container 使用的内存。该参数的配置，取决于 NodeManager 所在节点的总内存容量和该节点运行的其他服务的数量。

<property>
    <name>yarn.nodemanager.resource.memory-mb</name>
    <value>10240</value>
</property>

（2）yarn.nodemanager.resource.cpu-vcores

该参数的含义是，一个 NodeManager 节点分配给 Container 使用的 CPU 核数。该参数的配置，同样取决于 NodeManager 所在节点的总 CPU 核数和该节点运行的其他服务。

<property>
    <name>yarn.nodemanager.resource.cpu-vcores</name>
    <value>4</value>
</property>

（3）yarn.scheduler.maximum-allocation-mb

该参数的含义是，单个 Container 能够使用的最大内存。

<property>
    <name>yarn.scheduler.maximum-allocation-mb</name>
    <value>4096</value>
</property>

（4）yarn.scheduler.minimum-allocation-mb

该参数的含义是，单个 Container 能够使用的最小内存。

<property>
    <name>yarn.scheduler.minimum-allocation-mb</name>
    <value>512</value>
</property>

b. MR

MapReduce 资源配置主要包括 Map Task 的内存和 CPU 核数，以及 Reduce Task 的内存和 CPU 核数。核心配置参数如下：

（1）mapreduce.map.memory.mb｜mapreduce.reduce.memory.mb

该参数的含义是，单个 Map Task 申请的 Container 容器内存大小，其默认值为 1024。该值不能超出 yarn.scheduler.maximum-allocation-mb 和 yarn.scheduler.minimum-allocation-mb 规定的范围。

该参数需要根据不同的计算任务单独进行配置，在 Hive 中可直接使用如下方式为每个 SQL 语句单独进行配置：

set mapreduce.map.memory.mb=4096;
set mapreduce.reduce.memory.mb=4096;

（2）mapreduce.map.cpu.vcores｜mapreduce.reduce.cpu.vcores

该参数的含义是，单个 Map Task 申请的 Container 容器 CPU 核数，其默认值为 1。该值一般无需调整。

3.3 任务并行度优化

a. 优化说明

对于一个分布式的计算任务而言，设置一个合适的并行度十分重要。Hive 的计算任务由 MapReduce 完成，故并行度的调整需要分为 Map 端和 Reduce 端。

一、Map 端并行度

Map 端的并行度，也就是 Map 的个数。是由输入文件的切片数决定的。一般情况下，Map 端的并行度无需手动调整。以下特殊情况可考虑调整 Map 端并行度：

（1）查询的表中存在大量小文件

按照 Hadoop 默认的切片策略，一个小文件会单独启动一个 Map Task 负责计算。若查询的表中存在大量小文件，则会启动大量 Map Task，造成计算资源的浪费。这种情况下，可以使用 Hive 提供的 CombineHiveInputFormat 将多个小文件合并为一个切片，从而控制 Map Task 个数。

相关参数如下：

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

（2）Map 端有复杂的查询逻辑

若 SQL 语句中有正则替换、json 解析等复杂耗时的查询逻辑时，Map 端的计算会相对慢一些。若想加快计算速度，在计算资源充足的情况下，可考虑增大 Map 端的并行度，令 Map Task 多一些，每个 Map Task 计算的数据少一些。

相关参数如下：

set mapreduce.input.fileinputformat.split.maxsize=256000000; -- 一个切片的最大值

二、Reduce 端并行度

Reduce 端的并行度，也就是 Reduce 个数。相对来说，更需要关注。Reduce 端的并行度，可由用户自己指定，也可由 Hive 自行根据该 MR Job 输入的文件大小进行估算。

Reduce 端的并行度的相关参数如下：

-- 指定Reduce端并行度，默认值为-1，表示用户未指定
set mapreduce.job.reduces;
-- Reduce端并行度最大值
set hive.exec.reducers.max;
-- 单个Reduce Task计算的数据量，用于估算Reduce并行度
set hive.exec.reducers.bytes.per.reducer;

Reduce 端并行度的确定逻辑：

若指定参数 mapreduce.job.reduces 的值为一个非负整数，则 Reduce 端并行度为指定值。否则，Hive 自行估算 Reduce 端并行度，估算逻辑如下：

根据上述描述，可以看出，Hive 自行估算 Reduce 端并行度时，是以整个 MR Job 输入的文件大小作为依据的。因此，在某些情况下其估计的并行度很可能并不准确，此时就需要用户根据实际情况来指定 Reduce 端并行度了。

b. 优化案例

示例 SQL：

select
    province_id,
    count(*)
from order_detail
group by province_id;

优化前，上述 SQL 在不指定 Reduce 端并行度时，Hive 自行估算并行度的逻辑如下：

totalInputBytes= 1136009934
bytesPerReducer=256000000
maxReducers=1009
-- 经计算，Reduce端并行度为5 --
numReducers = min{ceil(1136009934/256000000), 1009} = 5

优化思路：

上述 SQL 在默认情况下，是会进行 Map-Side 聚合的，也就是 Reduce 端接收的数据，实际上是 Map 端完成聚合之后的结果。观察任务的执行过程，会发现每个 Map 端输出的数据只有 34 条记录，共有 5 个 Map Task。

也就是说 Reduce 端实际只会接收 170（34*5）条记录，故理论上 Reduce 端并行度设置为 1 就足够了。这种情况下，用户可通过以下参数，自行设置 Reduce 端并行度为 1。

set mapreduce.job.reduces=1;