Hive性能优化

Hive性能优化
一、Hive表设计优化

1.1 分区表

1.1.1 Hive查询基本原理

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

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

step1：创建表
```
-- 创建数据库
create database tb_part;

-- 创建表
create table tb_login(
  userid string,
  logindate string
) row format delimited fields terminated by '\t';
```
HDFS中自动在Hive数据仓库的目录下和对应的数据库目录下，创建表的目录

step2：关联数据
```
load data local inpath '/export/data/login.log' into table tb_login;
```
数据会被自动放入HDFS中对应的表的目录下

数据在表中可以被正常读取

step3：查询数据
```
-- 统计3月24号的登录人数
select
  logindate,
  count(*) as cnt
from tb_login
where logindate = '2021-03-24'
group by logindate;
```
当执行查询计划时，Hive会使用表的最后一级目录作为底层处理数据的输入

先根据表名在元数据中进行查询表对应的HDFS目录

然后将整个HDFS中表的目录作为底层查询的输入，可以通过explain命令查看执行计划依赖的数据
```
explain extended
select
  logindate,
  count(*) as cnt
from tb_login
where logindate = '2021-03-24'
group by logindate;
```
1.1.2 普通表结构问题

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

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

1.1.3 分区表设计思想

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

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

1.1.4 分区表测试

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

创建分区表：按照登陆日期分区
```
-- 创建表
create table tb_login_part(
  userid string
) 
partitioned by (logindate string)
row format delimited fields terminated by '\t';
```
将所有登陆数据写入分区表，分区存储
```
-- 开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
-- 按登录日期分区
insert into table tb_login_part partition(logindate)
select * from tb_login;
```
HDFS中会自动在表的目录下，为每个分区创建一个分区目录

查询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目录

加载对应分区的目录作为计算程序的输入

查看执行计划

如果不做分区表
```
explain extended
select
  logindate,
  count(*) as cnt
from tb_login
where logindate = '2021-03-23' or logindate = '2021-03-24'
group by logindate;
```
如果做了分区表
```
explain extended
select
  logindate,
  count(*) as cnt
from tb_login_part
where logindate = '2021-03-23' or logindate = '2021-03-24'
group by logindate;
```
1.2 分桶表

1.2.1 Hive中Join的问题

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

1.2.2 分桶表设计思想

针对以上的问题，Hive中提供了另外一种表的结构——分桶表结构。分桶表的设计有别于分区表的设计，分区表是将数据划分不同的目录进行存储，而分桶表是将数据划分不同的文件进行存储。分桶表的设计是按照一定的规则【通过MapReduce中的多个Reduce来实现】将数据划分到不同的文件中进行存储，构建分桶表。

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

不用进行全局join，只要进行桶与桶直接的Join就可以了。

例如：当前有两张表，订单表有1000万条，用户表有10万条，两张表的关联字段是userid，现在要实现两张表的Join。

我们将订单表按照userid划分为3个桶，1000万条数据按照userid的hash取余存储在对应的Bucket中。

同理，我们再将用户表按照相同的规则，存储在3个桶中。

在Join时，只需要将两张表的Bucket0与Bucket0进行Join，Bucket1与Bucket1进行Join，Bucket2与Bucket2进行Join即可，不用让所有的数据挨个比较，降低了比较次数，提高了Join的性能。

1.2.3 分桶表测试

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

构建普通emp表
```
create database if not exists db_emp;
use db_emp;
-- 创建普通表
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 '/export/data/emp01.txt' into table tb_emp01;
```
构建分桶emp表（分桶排序表）
```
use db_emp;
-- 创建分桶表
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;
```
构建普通dept表
```
use db_emp;
-- 创建部门表
create table tb_dept01(
    deptno string,
    dname string,
    loc string
)
row format delimited fields terminated by ',';
-- 加载数据
load data local inpath '/export/data/dept01.txt' into table tb_dept01;
```
构建分桶dept表
```
use db_emp;
-- 创建分桶表
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;
```
普通的Join执行计划
```
explain
select
  a.empno,
  a.ename,
  a.salary,
  b.deptno,
  b.dname
from tb_emp01 a join tb_dept01 b on a.deptno = b.deptno;
```
分桶的Join执行计划
```
-- 开启分桶SMB join Sorted Merge Bucket
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
-- 查看执行计划
explain
select
  a.empno,
  a.ename,
  a.salary,
  b.deptno,
  b.dname
from tb_emp02 a join tb_dept02 b on a.deptno = b.deptno;
```
1.3 索引设计

1.3.1 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不再支持。

1.3.2 索引的原理及使用

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

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

构建数据时，Hive会根据索引字段的值构建索引信息，将索引信息存储在索引表中

查询数据时，Hive会根据索引字段查询索引表，根据索引表的位置信息读取对应的文件数据。

下面我们来实现索引的构建，例如：

当前有一张分区表tb_login_part，默认查询数据时，是没有索引的，当查询登陆日期时可以通过分区过滤来提高效率，但是如果想按照用户ID进行查询，就无法使用分区进行过滤，只能全表扫描数据。

如果我们需要经常按照用户ID查询，那么性能就会相对较差，我们可以基于用户ID构建索引来加快查询效率。

可以使用Hive3.0以下版本测试

创建索引
```
-- 为表中的userid构建索引
create index idx_user_id_login on table tb_login_part(userid)
-- 索引类型为Compact，Hive支持Compact和Bitmap类型，存储的索引内容不同
as 'COMPACT'
-- 延迟构建索引
with deferred rebuild;
```
构建索引
```
alter index idx_user_id_login ON tb_login_part rebuild;
```
通过运行一个MapReduce程序来构建索引（效率？？）

查看索引
```
desc default__tb_login_part_idx_user_id_login__;
select * from default__tb_login_part_idx_user_id_login__;
```
索引中记录了每个用户ID对应的文件以及在文件中的位置

删除索引
```
DROP INDEX idx_user_id_login ON tb_login_part;
```
1.3.3 索引的问题与应用

问题

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

表中数据发生新增或者修改

索引表没有更新

手动更新索引表
```
alter index idx_user_id_login ON tb_login_part rebuild;
```
应用

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

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

二、Hive表数据优化

2.1 文件格式

2.1.1 概述

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

下面为大家介绍常用的几种文件格式以及各自使用的场景。

2.1.2 TextFile

TextFIle是Hive中默认的文件格式，存储形式为按行存储。工作中最常见的数据文件格式就是TextFile文件，几乎所有的原始数据生成都是TextFile格式，所以Hive设计时考虑到为了避免各种编码及数据错乱的问题，选用了TextFile作为默认的格式。建表时不指定存储格式即为textfile，导入数据时把数据文件拷贝至hdfs不进行处理。

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

创建原始数据表
```
-- 创建数据库
create database if not exists db_fileformat;
use db_fileformat;

-- 创建原始数据表
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;
```
创建TextFile数据表
```
-- 创建textfile数据表
create table tb_sogou_text(
  stime string,
  userid string,
  keyword string,
  clickorder string,
  url string
)
row format delimited fields terminated by '\t'
-- 指定 textfile 不指定也是默认
stored as textfile;
```
写入TextFile数据表
```
insert into table tb_sogou_text
select * from tb_sogou_source;
```
2.1.3 SequenceFile

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

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

创建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;
```
不能用load加载（load只能做单纯的复制移动操作），应采用insert select，底层走MR程序，把TextFile写入SequenceFile数据表。
```
insert into table tb_sogou_seq
select * from tb_sogou_source;
```
查看数据

在Hadoop中可以看懂，下载下来就是二进制文件，看不懂。

2.1.4 Parquet

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

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

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

Parquet的优点
- 更高效的压缩和编码
- 可用于多种数据处理框架
Parquet的缺点
- 不支持update, insert, delete, ACID
Parquet的应用
- 适用于字段数非常多，无更新，只取部分列的查询。
Parquet的使用

创建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.1.5 ORC

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

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

ORC文件：保存在文件系统上的普通二进制文件，一个ORC文件中可以包含多个stripe，每一个stripe包含多条记录，这些记录按照列进行独立存储，对应到Parquet中的row group的概念。

文件级元数据：包括文件的描述信息PostScript、文件meta信息（包括整个文件的统计信息）、所有stripe的信息和文件schema信息。

stripe：一组行形成一个stripe，每次读取文件是以行组为单位的，一般为HDFS的块大小，保存了每一列的索引和数据。

stripe元数据：保存stripe的位置、每一个列的在该stripe的统计信息以及所有的stream类型和位置。

row group：索引的最小单位，一个stripe中包含多个row group，默认为10000个值组成。

stream：一个stream表示文件中一段有效的数据，包括索引和数据两类。索引stream保存每一个row group的位置和统计信息，数据stream包括多种类型的数据，具体需要哪几种是由该列类型和编码方式决定。

ORC文件中保存了三个层级的统计信息，分别为文件级别、stripe级别和row group级别的，他们都可以用来根据Search ARGuments（谓词下推条件）判断是否可以跳过某些数据，在统计信息中都包含成员数和是否有null值，并且对于不同类型的数据设置一些特定的统计信息。

性能测试：
- 原始Text格式，未压缩 : 38.1 G
- ORC格式，默认压缩（ZLIB）: 11.5 G
- Parquet格式，默认压缩（Snappy）：14.8 G
- 测试对比：复杂数据Join关联测试
ORC的优点
- 列式存储，存储效率非常高
- 可压缩，高效的列存取
- 查询效率较高，支持索引
- 支持矢量化查询
ORC的缺点
- 加载时性能消耗较大
- 需要通过text文件转化生成
- 读取全量数据时性能较差
ORC的应用
- 适用于Hive中大型的存储、查询
ORC的使用

创建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;
```
写入ORC数据表
```
insert into table tb_sogou_orc
select * from tb_sogou_source;
```
查询数据

二进制序列化存储，大大压缩了存储空间。

2.2 数据压缩

2.2.1 压缩概述

Hive底层转换HQL运行MapReduce程序时，磁盘I/O操作、网络数据传输、shuffle和merge要花大量的时间，尤其是数据规模很大和工作负载密集的情况下，鉴于磁盘I/O和网络带宽是Hadoop的宝贵资源，数据压缩对于节省资源、最小化磁盘I/O和网络传输非常有帮助。如果磁盘I/O和网络带宽影响了MapReduce作业性能，在任意MapReduce阶段启用压缩都可以改善端到端处理时间并减少I/O和网络流量。

压缩的优点
- 减小文件存储所占空间
- 加快文件传输效率，从而提高系统的处理速度
- 降低IO读写的次数
压缩的缺点
- 使用数据时需要先对文件解压，加重CPU负荷，压缩算法越复杂，解压时间越长（利用算法对数据进行重新编制，用算力换空间）
Hadoop中各种压缩算法对比

2.2.2 Hive中压缩配置

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

Hadoop中支持的压缩算法：

要想在Hive中使用压缩，需要对MapReduce和Hive进行相应的配置

临时配置
- 配置MapReduce开启输出压缩及配置压缩类型
```
-- 开启输出压缩
set mapreduce.output.fileoutputformat.compress=true;
-- 配置压缩类型为Snappy
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
```
- 配置Hive开启中间结果压缩和输出压缩及配置压缩类型
```
-- 中间结果压缩
set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
-- 输出结果压缩
set hive.exec.compress.output=true;
```
永久配置
- 将以上MapReduce的配置写入mapred-site.xml中，重启Hadoop
- 将以上Hive的配置写入hive-site.xml中，重启Hive
1.2.3 Hive中压缩测试

创建表，指定为textfile格式，并使用snappy压缩
```
create table tb_sogou_snappy
stored as textfile
as select * from tb_sogou_source;
```
查看数据

创建表，指定为orc格式，并使用snappy压缩
```
create table tb_sogou_orc_snappy
stored as orc tblproperties ("orc.compress"="SNAPPY")
as select * from tb_sogou_source;
```
查看数据

2.3 存储优化

2.3.1 避免小文件生成

Hive的存储本质还是HDFS，HDFS是不利于小文件存储的，因为每个小文件会产生一条元数据信息，并且不利用MapReduce的处理（小文件过多，虽然磁盘空间所占不多，但元数据可能占很多，导致HDFS的namenode被撑爆），MapReduce中每个小文件会启动一个MapTask计算处理，导致资源的浪费，所以在使用Hive进行处理分析时，要尽量避免小文件的生成。

那么在使用Hive时，如何能避免小文件的生成呢？当我们使用多个Reduce进行聚合计算时，我们并不清楚每个Reduce最终会生成的结果的数据大小，无法控制用几个Reduce来处理。Hive中为我们提供了一个特殊的机制，可以自动的判断是否是小文件，如果是小文件可以自动将小文件进行合并。

配置
```
-- 如果hive的程序，只有maptask，将MapTask产生的所有小文件进行合并
set hive.merge.mapfiles=true;
-- 如果hive的程序，有Map和ReduceTask,将ReduceTask产生的所有小文件进行合并
set hive.merge.mapredfiles=true;
-- 每一个合并的文件的大小
set hive.merge.size.per.task=256000000;
-- 平均每个文件的大小，如果小于这个值就会进行合并
set hive.merge.smallfiles.avgsize=16000000;
```
2.3.2 读取小文件

尽管我们通过配置避免了多个小文件的同时产生，但是我们总会遇到数据处理的中间结果是小文件的情况，例如每个小时的分区数据中，大多数小时的数据都比较多，但是个别几个小时，如凌晨的2点~6点等等，数据量比较小，下一步进行处理时就必须对多个小文件进行处理，那么这种场景下怎么解决呢？

类似于MapReduce中的解决方案，Hive中也提供一种输入类CombineHiveInputFormat，用于将小文件合并以后，再进行处理。

配置
```
-- 设置Hive中底层MapReduce读取数据的输入类：将所有文件合并为一个大文件作为输入
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
```
2.3.3 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会失去意义。另外，这种索引主要用于数值型字段的范围查询过滤优化上。
- 开启索引配置
```
set hive.optimize.index.filter=true;
```
永久生效，请配置在hive-site.xml中
- 创建表，并指定构建索引
```
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;
```
- 进行范围或者等值查询（<,>,=）时就可以基于构建的索引进行查询
```
select count(*) from tb_sogou_orc_index where stime > '12:00:00' and stime < '18:00:00';
```
Bloom Filter Index

建表时候，通过表参数”orc.bloom.filter.columns”=”columnName……”来指定为哪些字段建立BloomFilter索引，这样，在生成数据的时候，会在每个stripe中，为该字段建立BloomFilter的数据结构。

当查询条件中包含对该字段的=号过滤时候，先从BloomFilter中获取以下是否包含该值，如果不包含，则跳过该stripe。

主要是用来判断数据在不在的，当布隆过滤器认为数据不存在，那么一定不存在；认为存在时，可能存在。
- 创建表，并指定构建索引
```
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;
```
- 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' ;
```
2.3.4 ORC矢量化查询

Hive的默认查询执行引擎一次处理一行，而矢量化查询执行是一种Hive针对ORC文件操作的特性，目的是按照每批1024行读取数据，并且一次性对整个记录整合（而不是对单条记录）应用操作，提升了像过滤, 联合, 聚合等等操作的性能。

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

配置
```
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
```
三、计算Job执行优化

3.1 Explain

3.1.1 功能

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

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

官网：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain

3.1.2 语法

常用语法命令如下：

EXPLAIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION|] query
- FORMATTED：对执行计划进行格式化，返回JSON格式的执行计划
- EXTENDED：提供一些额外的信息，比如文件的路径信息
- DEPENDENCY：以JSON格式返回查询所依赖的表和分区的列表
- AUTHORIZATION：列出需要被授权的条目，包括输入与输出
3.1.3 组成

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

The Abstract Syntax Tree for the query
- 抽象语法树AST：Hive使用Antlr解析生成器，可以自动地将HQL生成为抽象语法树
The dependencies between the different stages of the plan
- Stage依赖关系：会列出运行查询所有的依赖以及stage的数量
The description of each of the stages
- Stage内容：包含了非常重要的信息，比如运行时的operator和sort orders等具体的信息
3.1.4 示例1：过滤
```
explain select count(*) as cnt from tb_emp where deptno = '10';
```
- 组成
- 解释
3.1.5 示例2：分组排序
```
explain
select
   deptno,count(*) as cnt
from tb_emp where salary > 2000
group by deptno order by cnt desc;
```
3.2 MapReduce属性优化

3.2.1 本地模式

使用Hive的过程中，有一些数据量不大的表也会转换为MapReduce处理，提交到集群时，需要申请资源，等待资源分配，启动JVM进程，再运行Task，一系列的过程比较繁琐，本身数据量并不大，提交到YARN运行返回会导致性能较差的问题。

Hive为了解决这个问题，延用了MapReduce中的设计，提供本地计算模式，允许程序不提交给YARN，直接在本地运行，以便于提高小数据量程序的性能。
- 配置
```
-- 开启本地模式
set hive.exec.mode.local.auto = true;
```
- 限制条件
Hive为了避免大数据量的计算也使用本地模式导致性能差的问题，所以对本地模式做了以下限制，如果以下任意一个条件不满足，那么即使开启了本地模式，将依旧会提交给YARN集群运行。
- 处理的数据量不超过128M
- MapTask的个数不超过4个
- ReduceTask的个数不超过1个
3.2.2 JVM重用

JVM正常指代一个Java进程，Hadoop默认使用派生的JVM来执行map-reducer，如果一个MapReduce程序中有100个Map，10个Reduce，Hadoop默认会为每个Task启动一个JVM来运行，那么就会启动100个JVM来运行MapTask，在JVM启动时内存开销大，尤其是Job大数据量情况，如果单个Task数据量比较小，也会申请JVM资源，这就导致了资源紧张及浪费的情况。

为了解决上述问题，MapReduce中提供了JVM重用机制来解决，JVM重用可以使得JVM实例在同一个job中重新使用N次，当一个Task运行结束以后，JVM不会进行释放，而是继续供下一个Task运行，直到运行了N个Task以后，就会释放，N的值可以在Hadoop的mapred-site.xml文件中进行配置，通常在10-20之间。
- 配置
```
-- Hadoop3之前的配置，在mapred-site.xml中添加以下参数
-- Hadoop3中已不再支持该选项
mapreduce.job.jvm.numtasks=10 
```
3.2.3 并行执行

Hive在实现HQL计算运行时，会解析为多个Stage，有时候Stage彼此之间有依赖关系，只能挨个执行，但是在一些别的场景下，很多的Stage之间是没有依赖关系的，例如Union语句，Join语句等等，这些Stage没有依赖关系，但是Hive依旧默认挨个执行每个Stage，这样会导致性能非常差，我们可以通过修改参数，开启并行执行，当多个Stage之间没有依赖关系时，允许多个Stage并行执行，提高性能。
- 配置
```
-- 开启Stage并行化，默认为false
SET hive.exec.parallel=true;
-- 指定并行化线程数，默认为8
SET hive.exec.parallel.thread.number=16; 
```
注意：线程数越多，程序运行速度越快，但同样更消耗CPU资源

3.3 Join优化

3.3.1 Hive中的Join方案

表的Join是数据分析处理过程中必不可少的操作，Hive同样支持Join的语法，Hive Join的底层还是通过MapReduce来实现的，Hive实现Join时，为了提高MapReduce的性能，提供了多种Join方案来实现，例如适合小表Join大表的Map Join，大表Join大表的Reduce Join，以及大表Join的优化方案Bucket Join等。

3.3.2 Map Join

应用场景

适合于小表join大表或者小表Join小表

没有了Reduce阶段，没有了shuffle操作，将小表数据进行分布式缓存，发送到各台机器上，启动Mapper Task读大表数据，并与缓存中的小表数据关联，关联结果直接输出

原理
- 将小的那份数据给每个MapTask的内存都放一份完整的数据，大的数据每个部分都可以与小数据的完整数据进行join
- 底层不需要经过shuffle，需要占用内存空间存放小的数据文件
使用
- 尽量使用Map Join来实现Join过程
- Hive中默认自动开启了Map Join
```
-- 默认已经开启了Map Join
hive.auto.convert.join=true
```
- Hive中判断哪张表是小表及限制
  - LEFT OUTER JOIN的左表必须是大表
  - RIGHT OUTER JOIN的右表必须是大表
  - INNER JOIN左表或右表均可以作为大表
  - FULL OUTER JOIN不能使用MAPJOIN
  - MAPJOIN支持小表为子查询
  - 使用MAPJOIN时需要引用小表或是子查询时，需要引用别名
  - 在MAPJOIN中，可以使用不等值连接或者使用OR连接多个条件
  - 在MAPJOIN中最多支持指定6张小表，否则报语法错误
- Hive中小表的大小限制
```
-- 2.0版本之前的控制属性
hive.mapjoin.smalltable.filesize=25M
-- 2.0版本开始由以下参数控制
hive.auto.convert.join.noconditionaltask.size=512000000
```
3.3.3 Reduce Join

应用场景

适合于大表Join大表

原理
- 将两张表的数据在shuffle阶段利用shuffle的分组来将数据按照关联字段进行合并
- 必须经过shuffle，利用Shuffle过程中的分组来实现关联
使用
- Hive会自动判断是否满足Map Join，如果不满足Map Join，则自动执行Reduce Join
3.3.4 Bucket Join

桶之间jion，减少笛卡儿积

应用场景

适合于大表Join大表

原理
- 将两张表按照相同的规则将数据划分，根据对应的规则的数据进行join，减少了比较次数，提高了性能
使用
- Bucket Join
  - 语法：clustered by colName
  - 参数
    
    -- 开启分桶join set hive.optimize.bucketmapjoin = true;
  - 要求
    
    分桶字段 = Join字段，桶的个数相等或者成倍数
- Sort Merge Bucket Join（SMB）：基于有序的数据Join
  - 语法：clustered by colName sorted by (colName)
  - 参数
    
    -- 开启分桶SMB join set hive.optimize.bucketmapjoin = true; set hive.auto.convert.sortmerge.join=true; set hive.optimize.bucketmapjoin.sortedmerge = true; set hive.auto.convert.sortmerge.join.noconditionaltask=true;
  - 要求
    
    分桶字段 = Join字段 = 排序字段，桶的个数相等或者成倍数
3.4 优化器

3.4.1 关联优化

在使用Hive的过程中经常会遇到一些特殊的问题，例如当一个程序中如果有一些操作彼此之间有关联性，是可以放在一个MapReduce中实现的，但是Hive会不智能的选择，Hive会使用两个MapReduce来完成这两个操作。

例如：当我们执行以下SQL语句：
```
select …… from table group by id order by id desc;
```
该SQL语句转换为MapReduce时，我们可以有两种方案来实现：

方案一
- 第一个MapReduce做group by，经过shuffle阶段对id做分组
- 第二个MapReduce对第一个MapReduce的结果做order by，经过shuffle阶段对id进行排序
方案二
- 因为都是对id处理，可以使用一个MapReduce的shuffle既可以做分组也可以排序
在这种场景下，Hive会默认选择用第一种方案来实现，这样会导致性能相对较差，我们可以在Hive中开启关联优化，对有关联关系的操作进行解析时，可以尽量放在同一个MapReduce中实现。

配置
```
set hive.optimize.correlation=true;
```
3.4.2 CBO优化器引擎

在使用MySQL或者Hive等工具时，我们经常会遇到一个问题，默认的优化器在底层解析一些聚合统计类的处理的时候，底层解析的方案有时候不是最佳的方案。

例如：当前有一张表【共1000条数据】，id构建了索引，id =100值有900条，我们现在的需求是查询所有id = 100的数据，所以SQL语句为：select * from table where id = 100;

方案一
- 由于id这一列构建了索引，索引默认的优化器引擎RBO，会选择先从索引中查询id = 100的值所在的位置，再根据索引记录位置去读取对应的数据，但是这并不是最佳的执行方案。
方案二
- 有id=100的值有900条，占了总数据的90%，这时候是没有必要检索索引以后再检索数据的，可以直接检索数据返回，这样的效率会更高，更节省资源，这种方式就是CBO优化器引擎会选择的方案。
RBO
- rule basic optimise：基于规则的优化器（比较死板）
- 根据设定好的规则来对程序进行优化
CBO
- cost basic optimise：基于代价的优化器
- 根据不同场景所需要付出的代价来合适选择优化的方案
- 对数据的分布的信息【数值出现的次数，条数，分布】来综合判断用哪种处理的方案是最佳方案
很明显CBO引擎更加智能，所以在使用Hive时，我们可以配置底层的优化器引擎为CBO引擎。

配置
```
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
```
CBO引擎是基于代价的优化引擎，那么CBO如何知道每种方案的计算代价的呢？

要求
- 要想使用CBO引擎，必须构建数据的元数据【表行数、列的信息、分区的信息……】
- 提前获取这些信息，CBO才能基于代价选择合适的处理计划
- 所以CBO引擎一般搭配analyze分析优化器一起使用
3.4.3 Analyze分析优化器

功能

用于提前运行一个MapReduce程序将表或者分区的信息构建一些元数据【表的信息、分区信息、列的信息】，搭配CBO引擎一起使用

语法
```
-- 构建分区信息元数据
ANALYZE TABLE tablename
  [PARTITION(partcol1[=val1], partcol2[=val2], ...)]
  COMPUTE STATISTICS [noscan];

-- 构建列的元数据
ANALYZE TABLE tablename
  [PARTITION(partcol1[=val1], partcol2[=val2], ...)]
  COMPUTE STATISTICS FOR COLUMNS ( columns name1, columns name2...) [noscan];

-- 查看元数据
DESC FORMATTED [tablename] [columnname];
```
举例
- 构建表中分区数据的元数据信息
```
ANALYZE TABLE tb_login_part PARTITION(logindate) COMPUTE STATISTICS;
```
- 构建表中列的数据的元数据信息
```
ANALYZE TABLE tb_login_part  COMPUTE STATISTICS FOR COLUMNS  userid;
```
- 查看构建的列的元数据
```
desc formatted tb_login_part userid;
```
3.5 谓词下推（PPD）

3.5.1 基本思想

谓词下推 Predicate Pushdown（PPD）的思想简单点说就是在不影响最终结果的情况下，尽量将过滤条件提前执行。谓词下推后，过滤条件在map端执行，减少了map端的输出，降低了数据在集群上传输的量，降低了Reduce端的数据负载，节约了集群的资源，也提升了任务的性能。
```
-- 方式一
select a.id,a.value1,b.value2 
from table1 a
join (select b.* from table2 b where b.ds>='20181201' and b.ds<'20190101') c
on (a.id=c.id)

-- 方式二
select a.id,a.value1,b.value2 
from table1 a
join table2 b on a.id=b.id
where b.ds>='20181201' and b.ds<'20190101'
```
推荐方式一，先过滤再join。

3.5.2 基本规则
- 开启参数
```
-- 默认自动开启谓词下推
hive.optimize.ppd=true;
```
- 不同Join场景下的Where谓词下推测试
- 试验结论
- Inner Join和Full outer Join，条件写在on后面，还是where后面，性能上面没有区别
- Left outer Join时，右侧的表写在on后面，左侧的表写在where后面，性能上有提高
- Right outer Join时，左侧的表写在on后面、右侧的表写在where后面，性能上有提高
- 如果SQL语句中出现不确定结果的函数，也无法实现下推
3.6 数据倾斜

3.6.1 数据倾斜的现象

分布式计算中最常见的，最容易遇到的问题就是数据倾斜，数据倾斜的现象是，当我们提交运行一个程序时，我们通过监控发现，这个程序的大多数的Task都已经运行结束了，只有某一个Task一直在运行，迟迟不能结束，导致整体的进度卡在99%或者100%，这时候我们就可以判定程序出现了数据倾斜的问题。

3.6.2 数据倾斜的原因

表面上看，发生数据倾斜的原因在于这个Task运行过慢，但是仔细分析我们会发现，这个Task运行过慢的原因在于这个Task的负载要比其他Task的负载要高，所以发生数据倾斜的直观原因在于Task的数据分配不均衡。

那为什么会出现多个Task数据分配不均衡的情况呢？

从两方面考虑，第一：数据本身就是倾斜的，数据中某种数据出现的次数过多。第二：分区规则导致这些相同的数据都分配给了同一个Task，导致这个Task拿到了大量的数据，而其他Task拿到的数据比较少，所以运行起来相比较于其他Task就比较慢一些。

综上所述，产生数据倾斜的根本原因在于分区规则。

3.6.3 group By的数据倾斜

当程序中出现group by或者count（distinct）等分组聚合的场景时，如果数据本身是倾斜的根据MapReduce的Hash分区规则，肯定会出现数据倾斜的现象。根本原因是因为分区规则导致的，所以我们可以通过以下几种方案来解决group by导致的数据倾斜的问题。

方案一：开启Map端聚合

尽量将一些聚合操作在Map端进行
```
-- 开启Map端聚合：Combiner
hive.map.aggr=true;
```
- 通过减少Reduce的输入量，避免每个Task数据差异过大导致数据倾斜
方案二：实现随机分区
```
-- SQL中避免数据倾斜，构建随机分区
select * from table distribute by rand();
```
- distribute by用于指定底层的MapReduce按照哪个字段作为Key实现分区、分组等
- 默认由Hive自己选择，我们可以通过distribute by自己指定，通过rank函数随机值实现随机分区，避免数据倾斜
方案三：自动构建随机分区并自动聚合

空间换时间
```
-- 开启随机分区，走两个MapReduce 
hive.groupby.skewindata=true;
```
- 开启该参数以后，当前程序会自动通过两个MapReduce程序来运行
- 第一个MapReduce自动进行随机分区，然后实现聚合(Key相同的数据会被打散，需要第二个MapReduce程序)
- 第二个MapReduce将聚合的结果再按照业务进行处理，得到结果
3.6.4 Join的数据倾斜

实际业务需求中往往需要构建两张表的Join实现，如果两张表比较大，无法实现Map Join，只能走Reduce Join，那么当关联字段中某一种值过多的时候依旧会导致数据倾斜的问题；

面对Join产生的数据倾斜，我们核心的思想是尽量避免Reduce Join的产生，优先使用Map Join来实现

但往往很多的Join场景不满足Map Join的需求，那么我们可以以下几种方案来解决Join产生的数据倾斜问题：

方案一：提前过滤，将大数据变成小数据，实现Map Join

实现两张表的Join时，我们要尽量考虑是否可以使用Map Join来实现Join过程。有些场景下看起来是大表Join大表，但是我们可以通过转换将大表Join大表变成大表Join小表，来实现Map Join。

例如：现在有两张表订单表A与用户表B，需要实现查询今天所有订单的用户信息，关联字段为userid。

A表：今天的订单，1000万条，字段：orderId,userId,produceId,price等

B表：用户信息表，100万条，字段：userid,username,age,phone等
- 需求：两张表关联得到今天每个订单的用户信息
- 实现1：直接关联，实现大表Join大表
```
select a.*,b.* from A join B on a.userid = b.userid;
```
由于两张表比较大，无法走Map Join，只能走Reduce Join，容易产生数据倾斜。
- 实现2：将下了订单的用户的数据过滤出来，再Join
```
select a.*,d.*
from (
  -- 获取所有下订单的用户信息
  select
    b.*
  from
  -- 获取所有下订单的userid
  ( select distinct a.userid from A a ) c join B b on c.userid = b.userid ) d 
join
  A a on d.userid = a.userid;
```
- 100万个用户中，在今天下订单的人数可能只有一小部分，大量数据是不会Join成功的
- 可以提前将订单表中的userid去重，获取所有下订单的用户id
- 再使用所有下订单的用户id关联用户表，得到所有下订单的用户的信息
- 最后再使用下订单的用户信息关联订单表
- 通过多次Map Join来代替Reduce Join，性能更好也可以避免数据倾斜
方案二：使用Bucket Join
- 如果使用方案一来避免Reduce Join ，有些场景下依旧无法满足，例如过滤后的数据依旧是一张大表，那么最后的Join依旧是一个Reduce Join
- 这种场景下，我们可以将两张表的数据构建为桶表，实现Bucket Map Join，避免数据倾斜
方案三：使用Skew Join

Skew Join是Hive中一种专门为了避免数据倾斜而设计的特殊的Join过程，这种Join的原理是将Map Join和Reduce Join进行合并，如果某个值出现了数据倾斜，就会将产生数据倾斜的数据单独使用Map Join来实现，其他没有产生数据倾斜的数据由Reduce Join来实现，这样就避免了Reduce Join中产生数据倾斜的问题，最终将Map Join的结果和Reduce Join的结果进行Union合并

配置
```
-- 开启运行过程中skewjoin
set hive.optimize.skewjoin=true;
-- 如果这个key的出现的次数超过这个范围
set hive.skewjoin.key=100000;
-- 在编译时判断是否会产生数据倾斜
set hive.optimize.skewjoin.compiletime=true;
-- 不合并，提升性能
set hive.optimize.union.remove=true;
-- 如果Hive的底层走的是MapReduce，必须开启这个属性，才能实现不合并
set mapreduce.input.fileinputformat.input.dir.recursive=true;
```
示例图
相关阅读:
获取到某一方法的调用者的类名、方法名、命名空间(转)
JQ插件收集
 输出KnownColor枚举颜色值
 SQL 优化之该走索引却不走索引的分析
 oracle日期处理（一）
ORACLE如何使用DBMS_METADATA.GET_DDL获取DDL语句
 案例分析：ora04031与ora04030错误分析与解决
 分区表、分区索引和全局索引部分总结
 Oracle性能调整指导纲要
 ORACLE常用SQL优化hint语句
原文地址：https://www.cnblogs.com/wkfvawl/p/16884206.html

一、Hive表设计优化

1.1 分区表

1.1.1 Hive查询基本原理

1.1.2 普通表结构问题

1.1.3 分区表设计思想

1.1.4 分区表测试

1.2 分桶表

1.2.1 Hive中Join的问题

1.2.2 分桶表设计思想

1.2.3 分桶表测试

1.3 索引设计

1.3.1 Hive中的索引

1.3.2 索引的原理及使用

1.3.3 索引的问题与应用

二、Hive表数据优化

2.1 文件格式

2.1.1 概述

2.1.2 TextFile

2.1.3 SequenceFile

2.1.4 Parquet

2.1.5 ORC

2.2 数据压缩

2.2.1 压缩概述

2.2.2 Hive中压缩配置

1.2.3 Hive中压缩测试

2.3 存储优化

2.3.1 避免小文件生成

2.3.2 读取小文件

2.3.3 ORC文件索引

2.3.4 ORC矢量化查询

三、计算Job执行优化

3.1 Explain

3.1.1 功能

3.1.2 语法

3.1.3 组成

3.1.4 示例1：过滤

3.1.5 示例2：分组排序

3.2 MapReduce属性优化

3.2.1 本地模式

3.2.2 JVM重用

3.2.3 并行执行

3.3 Join优化

3.3.1 Hive中的Join方案

3.3.2 Map Join

3.3.3 Reduce Join

3.3.4 Bucket Join

3.4 优化器

3.4.1 关联优化

3.4.2 CBO优化器引擎

3.4.3 Analyze分析优化器

3.5 谓词下推（PPD）

3.5.1 基本思想

3.5.2 基本规则

3.6 数据倾斜

3.6.1 数据倾斜的现象

3.6.2 数据倾斜的原因

3.6.3 group By的数据倾斜

3.6.4 Join的数据倾斜