Hive知识体系保姆级教程-22l2-技术成就未来

Hive涉及的知识点如下图所示，本文将逐一讲解：

正文开始：

一. Hive概览

1.1 hive的简介

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

其本质是将SQL转换为MapReduce/Spark的任务进行运算，底层由HDFS来提供数据的存储，说白了hive可以理解为一个将SQL转换为MapReduce/Spark的任务的工具，甚至更进一步可以说hive就是一个MapReduce/Spark Sql的客户端

为什么要使用hive ?

主要的原因有以下几点:

hive的特点:

1.2 hive的架构

基本组成:

用户接口

元数据存储

解释器、编译器、优化器、执行器

1.3 hive与hadoop的关系

Hive利用HDFS存储数据，利用MapReduce查询分析数据

1.4 hive与传统数据库对比

hive主要是用于海量数据的离线数据分析

总结：hive具有sql数据库的外表，但应用场景完全不同，hive只适合用来做批量数据统计分析。

1.5 hive的数据存储

SequenceFile是hadoop中的一种文件格式：文件内容是以序列化的kv对象来组织的

只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。

Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket。

db：在hdfs中表现为hive.metastore.warehouse.dir目录下一个文件夹。

table：在hdfs中表现所属db目录下一个文件夹。

external table：与table类似，不过其数据存放位置可以在任意指定路径。

partition：在hdfs中表现为table目录下的子目录。

bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件。

二、Hive表类型

2.1 Hive 数据类型

Hive的基本数据类型有：TINYINT，SAMLLINT，INT，BIGINT，BOOLEAN，FLOAT，DOUBLE，STRING，TIMESTAMP(V0.8.0+)和BINARY(V0.8.0+)。

Hive的集合类型有：STRUCT，MAP和ARRAY。

Hive主要有四种数据模型(即表)：内部表、外部表、分区表和桶表。

当前hive只支持Derby和MySQL数据库

2.2 Hive 内部表

Hive中的内部表和传统数据库中的表在概念上是类似的，Hive的每个表都有自己的存储目录，除了外部表外，所有的表数据都存放在配置在hive-site.xml文件的${hive.metastore.warehouse.dir}/table_name目录下。

创建内部表：

2.3 Hive 外部表

被external修饰的为外部表（external table），外部表指向已经存在在Hadoop HDFS上的数据，除了在删除外部表时只删除元数据而不会删除表数据外，其他和内部表很像。

创建外部表：

2.4 Hive 分区表

分区表的每一个分区都对应数据库中相应分区列的一个索引，但是其组织方式和传统的关系型数据库不同。在Hive中，分区表的每一个分区都对应表下的一个目录，所有的分区的数据都存储在对应的目录中。

比如说，分区表partitinTable有包含nation(国家)、ds(日期)和city(城市)3个分区，其中nation = china，ds = 20130506，city = Shanghai则对应HDFS上的目录为：

/datawarehouse/partitinTable/nation=china/city=Shanghai/ds=20130506/。

分区中定义的变量名不能和表中的列相同

创建分区表：

2.5 Hive 分桶表

桶表就是对指定列进行哈希(hash)计算，然后会根据hash值进行切分数据，将具有不同hash值的数据写到每个桶对应的文件中。

多个文件

创建分桶表：

2.6 Hive 视图

在 Hive 中，视图是逻辑数据结构，可以通过隐藏复杂数据操作（Joins, 子查询, 过滤,数据扁平化）来于简化查询操作。

对于经常变化的表应慎重使用视图

创建视图：

创建视图的时候是不会触发 MapReduce 的 Job，因为只存在元数据的改变。

但是，当对视图进行查询的时候依然会触发一个 MapReduce Job 进程：SHOW CREATE TABLE 或者 DESC FORMATTED TABLE 语句来显示通过 CREATE VIEW 语句创建的视图。以下是对Hive 视图的 DDL操作：

更改视图的属性：

重新定义视图：

删除视图：

三、Hive数据抽样

模式 & 趋势识别

3.1 随机抽样

rand()函数

使用rand()函数进行随机抽样，limit关键字限制抽样返回的数据，其中rand函数前的distribute和sort关键字可以保证数据在mapper和reducer阶段是随机分布的。

案例如下：

使用order 关键词:

案例如下：

经测试对比，千万级数据中进行随机抽样 order by方式耗时更长，大约多30秒左右。

3.2 块抽样

tablesample()函数

注意：测试过程中发现，select语句不能带where条件且不支持子查询，可通过新建中间表或使用随机抽样解决。

3.3 桶表抽样

tablesample (bucket x out of y [on colname])

其中x是要抽样的桶编号，桶编号从1开始，colname表示抽样的列，y表示桶的数量。

hive中分桶其实就是根据某一个字段Hash取模，放入指定数据的桶中，比如将表table_1按照ID分成100个桶，其算法是hash(id) % 100，这样，hash(id) % 100 = 0的数据被放到第一个桶中，hash(id) % 100 = 1的记录被放到第二个桶中。创建分桶表的关键语句为：CLUSTER BY语句。

例如：将表随机分成10组，抽取其中的第一个桶的数据：

四、Hive计算引擎

目前Hive支持MapReduce、Tez和Spark 三种计算引擎。

4.1 MR计算引擎

MR运行的完整过程：

Map在读取数据时，先将数据拆分成若干数据，并读取到Map方法中被处理。数据在输出的时候，被分成若干分区并写入内存缓存（buffer）中，内存缓存被数据填充到一定程度会溢出到磁盘并排序，当Map执行完后会将一个机器上输出的临时文件进行归并存入到HDFS中。

当Reduce启动时，会启动一个线程去读取Map输出的数据，并写入到启动Reduce机器的内存中，在数据溢出到磁盘时会对数据进行再次排序。当读取数据完成后会将临时文件进行合并，作为Reduce函数的数据源。

4.2 Tez计算引擎

Apache Tez是进行大规模数据处理且支持DAG作业的计算框架，它直接源于MapReduce框架，除了能够支持MapReduce特性，还支持新的作业形式，并允许不同类型的作业能够在一个集群中运行。

Tez将原有的Map和Reduce两个操作简化为一个概念——Vertex，并将原有的计算处理节点拆分成多个组成部分：Vertex Input、Vertex Output、Sorting、Shuffling和Merging。计算节点之间的数据通信被统称为Edge，这些分解后的元操作可以任意灵活组合，产生新的操作，这些操作经过一些控制程序组装后，可形成一个大的DAG作业。

通过允许Apache Hive运行复杂的DAG任务，Tez可以用来处理数据，之前需要多个MR jobs，现在一个Tez任务中。

Tez和MapReduce作业的比较

Tez绕过了MapReduce很多不必要的中间的数据存储和读取的过程，直接在一个作业中表达了MapReduce需要多个作业共同协作才能完成的事情。

Tez和MapReduce一样都运行使用YARN作为资源调度和管理。但与MapReduce on YARN不同，Tez on YARN并不是将作业提交到ResourceManager，而是提交到AMPoolServer的服务上，AMPoolServer存放着若干已经预先启动ApplicationMaster的服务。

当用户提交一个作业上来后，AMPoolServer从中选择一个ApplicationMaster用于管理用户提交上来的作业，这样既可以节省ResourceManager创建ApplicationMaster的时间，而又能够重用每个ApplicationMaster的资源，节省了资源释放和创建时间。

Tez相比于MapReduce有几点重大改进

当查询需要有多个reduce逻辑时，Hive的MapReduce引擎会将计划分解，每个Redcue提交一个MR作业。这个链中的所有MR作业都需要逐个调度，每个作业都必须从HDFS中重新读取上一个作业的输出并重新洗牌。而在Tez中，几个reduce接收器可以直接连接，数据可以流水线传输，而不需要临时HDFS文件，这种模式称为MRR（Map-reduce-reduce*）。

Tez还允许一次发送整个查询计划，实现应用程序动态规划，从而使框架能够更智能地分配资源，并通过各个阶段流水线传输数据。对于更复杂的查询来说，这是一个巨大的改进，因为它消除了IO/sync障碍和各个阶段之间的调度开销。

在MapReduce计算引擎中，无论数据大小，在洗牌阶段都以相同的方式执行，将数据序列化到磁盘，再由下游的程序去拉取，并反序列化。Tez可以允许小数据集完全在内存中处理，而MapReduce中没有这样的优化。仓库查询经常需要在处理完大量的数据后对小型数据集进行排序或聚合，Tez的优化也能极大地提升效率。

4.3 Spark计算引擎

Apache Spark是专为大规模数据处理而设计的快速、通用支持DAG（有向无环图）作业的计算引擎，类似于Hadoop MapReduce的通用并行框架，可用来构建大型的、低延迟的数据分析应用程序。

大规模数据处理高容错性高可伸缩性

Spark运行流程

Spark具有以下几个特性。

高效性

Spark会将作业构成一个DAG，优化了大型作业一些重复且浪费资源的操作，对查询进行了优化，重新编写了物理执行引擎，如可以实现MRR模式。

易用性

Spark不同于MapReducer只提供两种简单的编程接口，它提供了多种编程接口去操作数据，这些操作接口如果使用MapReduce去实现，需要更多的代码。Spark的操作接口可以分为两类：transformation（转换）和action（执行）。Transformation包含map、flatmap、distinct、reduceByKey和join等转换操作；Action包含reduce、collect、count和first等操作。

通用性

Spark针对实时计算、批处理、交互式查询，提供了统一的解决方案。但在批处理方面相比于MapReduce处理同样的数据，Spark所要求的硬件设施更高，MapReduce在相同的设备下所能处理的数据量会比Spark多。所以在实际工作中，Spark在批处理方面只能算是MapReduce的一种补充。

兼容性

Spark和MapReduce一样有丰富的产品生态做支撑。例如Spark可以使用YARN作为资源管理器，Spark也可以处理Hbase和HDFS上的数据。

五、存储与压缩

5.1 Hive存储格式

Hive支持的存储数的格式主要有：TEXTFILE（行式存储）、SEQUENCEFILE(行式存储)、ORC（列式存储）、PARQUET（列式存储）。

5.1.1 行式存储和列式存储

上图左边为逻辑表，右边第一个为行式存储，第二个为列式存储。

行存储的特点：

列存储的特点：

5.1.2 TEXTFILE

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

5.1.3 ORC格式

Orc (Optimized Row Columnar)是hive 0.11版里引入的新的存储格式。

可以看到每个Orc文件由1个或多个stripe组成，每个stripe250MB大小，这个Stripe实际相当于RowGroup概念，不过大小由4MB->250MB，这样能提升顺序读的吞吐率。每个Stripe里有三部分组成，分别是Index Data,Row Data,Stripe Footer：

Index Data：一个轻量级的index，默认是每隔1W行做一个索引。这里做的索引只是记录某行的各字段在Row Data中的offset。

Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。

Stripe Footer：存的是各个stripe的元数据信息

每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。

5.1.4 PARQUET格式

Parquet是面向分析型业务的列式存储格式，由Twitter和Cloudera合作开发，2015年5月从Apache的孵化器里毕业成为Apache顶级项目。

Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。

通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。Parquet文件的格式如下图所示。

上图展示了一个Parquet文件的内容，一个文件中可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验它是否是一个Parquet文件，Footer length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据，在Parquet中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前Parquet中还不支持索引页。

5.2 Hive压缩格式

在实际工作当中，hive当中处理的数据，一般都需要经过压缩，前期我们在学习hadoop的时候，已经配置过hadoop的压缩，我们这里的hive也是一样的可以使用压缩来节省我们的MR处理的网络带宽

mr支持的压缩格式:

hadoop支持的解压缩的类：

压缩性能的比较：

Snappy生成的压缩文件要大20%到100%。在64位模式下的core i7处理器的单内核上，Snappy以250 MB/秒或更多的速度压缩，并以500 MB/秒或更多的速度解压。

实现压缩hadoop需要配置的压缩参数:

hive配置压缩的方式:

5.3 存储和压缩相结合

ORC存储方式的压缩：

创建一个非压缩的ORC存储方式：

创建一个SNAPPY压缩的ORC存储方式：

5.4 主流存储文件性能对比

从存储文件的压缩比和查询速度两个角度对比。

压缩比比较：

数据压缩比结论:

ORC > Parquet > textFile

存储文件的查询效率测试

存储文件的查询效率比较:

ORC > TextFile > Parquet

六、Hive Sql 大全

本节基本涵盖了Hive日常使用的所有SQL，因为SQL太多，所以将SQL进行了如下分类：一、DDL语句（数据定义语句）：对数据库的操作：包含创建、修改数据库对数据表的操作：分为内部表及外部表，分区表和分桶表二、DQL语句（数据查询语句）：单表查询、关联查询hive函数：包含聚合函数，条件函数，日期函数，字符串函数等行转列及列转行：lateral view 与 explode 以及 reflect窗口函数与分析函数其他一些窗口函数

hive的DDL语法

对数据库的操作

说明：可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

对数据表的操作

对管理表(内部表)的操作:

对decimal类型简单解释下

row format delimited fields terminated by ‘t’ 指定字段分隔符，默认分隔符为 ’01’stored as 指定存储格式location 指定存储位置

对外部表操作

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉，只会删除表的元数据

对分区表的操作

注意：hive表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时，hive会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错当表是分区表时，比如 partitioned by (day string)，则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123这种格式，然后使用：msck repair table score; 修复表结构，成功之后即可看到数据已经全部加载到表当中去了

注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

对分桶表操作

将数据按照指定的字段进行分成多个桶中去，就是按照分桶字段进行哈希划分到多个文件当中去分区就是分文件夹，分桶就是分文件

分桶优点：1. 提高join查询效率2. 提高抽样效率

桶表的数据加载：由于桶表的数据加载通过hdfs dfs -put文件或者通过load data均不可以，只能通过insert overwrite 进行加载所以把文件加载到桶表中，需要先创建普通表，并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

修改表和删除表

注意：truncate 和 drop：如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表结构恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复truncate清空的表所以 truncate 一定慎用，一旦清空将无力回天

向hive表中加载数据

hive表中数据导出

hive的DQL查询语法

单表查询

注意：1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by

注意：小于某个值是不包含null的，如上查询结果是把 s_score 为 null 的行剔除的

注意：如果使用 group by 分组，则 select 后面只能写分组的字段或者聚合函数where和having区别：1 having是在 group by 分完组之后再对数据进行筛选，所以having 要筛选的字段只能是分组字段或者聚合函数2 where 是从数据表中的字段直接进行的筛选的，所以不能跟在gruopby后面，也不能使用聚合函数

注：1. hive2版本已经支持不等值连接，就是 join on条件后面可以使用大于小于符号了;并且也支持 join on 条件后跟or (早前版本 on 后只支持 = 和 and，不支持 > < 和 or)2.如hive执行引擎使用MapReduce，一个join就会启动一个job，一条sql语句中如有多个join，则会启动多个job

注意：表之间用逗号(,)连接和 inner join 是一样的select * from table_a,table_b where table_a.id=table_b.id;它们的执行效率没有区别，只是书写方式不同，用逗号是sql 89标准，join 是sql 92标准。用逗号连接后面过滤条件用 where ，用 join 连接后面过滤条件是 on。

注意：order by 是全局排序，所以最后只有一个reduce，也就是在一个节点执行，如果数据量太大，就会耗费较长时间

注意：Hive要求 distribute by 语句要写在 sort by 语句之前

Hive函数

聚合函数

注意：聚合操作时要注意null值count(*) 包含null值，统计所有行数count(id) 不包含null值min 求最小值是不包含null，除非所有值都是nullavg 求平均值也是不包含null

关系运算

数学运算

逻辑运算

数值运算

此外还有：以2为底对数函数: log2()、对数函数: log()

十六进制函数: hex()、将十六进制转化为字符串函数: unhex()进制转换函数: conv(bigint num, int from_base, int to_base) 说明: 将数值num从from_base进制转化到to_base进制

此外还有很多数学函数：绝对值函数: abs()、正取余函数: pmod()、正弦函数: sin()、反正弦函数: asin()、余弦函数: cos()、反余弦函数: acos()、positive函数: positive()、negative函数: negative()

条件函数

日期函数

注：以下SQL语句中的 from tableName 可去掉，不影响查询结果

字符串函数

复合类型构建操作

复杂类型访问操作

复杂类型长度统计函数

hive当中的lateral view 与 explode以及reflect和窗口函数

使用explode函数将hive表中的Map和Array字段数据进行拆分

lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。

其中explode还可以用于将hive一列中复杂的array或者map结构拆分成多行

需求：现在有数据格式如下

字段之间使用t分割，需求将所有的child进行拆开成为一列

将map的key和value也进行拆开，成为如下结果

使用explode拆分json字符串

需求: 需求：现在有一些数据格式如下：

其中字段与字段之间的分隔符是 |

我们要解析得到所有的monthSales对应的值为以下这一列（行转列）

4900

2090

6987

配合LATERAL VIEW使用

配合lateral view查询多个字段

也可以多重使用

最终，我们可以通过下面的句子，把这个json格式的一行数据，完全转换成二维表的方式展现

总结：

Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select字段的问题。Multiple Lateral View可以实现类似笛卡尔乘积。Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。

行转列

列转行

所需函数:

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

数据准备:

需求: 将电影分类中的数组数据展开。结果如下：

实现步骤:

reflect函数

reflect函数可以支持在sql中调用java中的自带函数，秒杀一切udf函数。

需求1: 使用java.lang.Math当中的Max求两列中最大值

实现步骤:

需求2: 文件中不同的记录来执行不同的java的内置函数

实现步骤:

需求3: 判断是否为数字

实现方式:

使用apache commons中的函数，commons下的jar已经包含在hadoop的classpath中，所以可以直接使用。

窗口函数与分析函数

在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能。

partition byorder by。

over (partition by xxx order by xxx)

sum、avg、min、max

准备数据

SUM函数和窗口函数的配合使用：结果和ORDER BY相关,默认为升序。

如果不指定rows between,默认为从起点到当前行;

如果不指定order by，则将分组内所有值累加;

关键是理解rows between含义,也叫做window子句：

preceding：往前

following：往后

current row：当前行

unbounded：起点

unbounded preceding 表示从前面的起点

unbounded following：表示到后面的终点

AVG，MIN，MAX，和SUM用法一样。

row_number、rank、dense_rank、ntile

准备数据

ROW_NUMBER()使用

ROW_NUMBER()从1开始，按照顺序，生成分组内记录的序列。

RANK 和 DENSE_RANK使用

RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位。

DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。

NTILE

有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。

ntile可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

其他一些窗口函数

lag,lead,first_value,last_value

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

FIRST_VALUE

取分组内排序后，截止到当前行，第一个值

取分组内排序后，截止到当前行，最后一个值

如果想要取分组内排序后最后一个值，则需要变通一下：

特别注意order by

如果不指定ORDER BY，则进行排序混乱，会出现错误的结果

cume_dist,percent_rank

注意：序列函数不支持WINDOW子句

CUME_DIST 和order byd的排序顺序有关系

CUME_DIST 小于等于当前值的行数/分组内总行数 order 默认顺序正序升序比如，统计小于等于当前薪水的人数，所占总人数的比例

PERCENT_RANK

PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1

经调研该函数显示现实意义不明朗有待于继续考证

grouping sets,grouping__id,cube,rollup

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

等价于将不同维度的GROUP BY结果集进行UNION ALL。

GROUPING__ID

再如：

根据GROUP BY的维度的所有组合进行聚合。

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

七、Hive执行计划

可以说执行计划是打开SQL优化大门的一把钥匙

要想学SQL执行计划，就需要学习查看执行计划的命令：explain，在查询语句的SQL前面加上关键字explain是查看执行计划的基本方法。

学会explain，能够给我们工作中使用hive带来极大的便利！

查看SQL的执行计划

Hive提供的执行计划目前可以查看的信息有以下几种：

explain

explain dependency

explain authorization

explain vectorization

explain analyze

explain cbo

explain locks

explain ast

explain extended

1. explain 的用法

Hive提供了explain命令来展示一个查询的执行计划

使用语法如下：

在 hive cli 中输入以下命令(hive 2.3.7)：

得到结果：

看完以上内容有什么感受，是不是感觉都看不懂，不要着急，下面将会详细讲解每个参数，相信你学完下面的内容之后再看 explain 的查询结果将游刃有余。

一个HIVE查询被转换为一个由一个或多个stage组成的序列（有向无环图DAG）。这些stage可以是MapReduce stage，也可以是负责元数据存储的stage，也可以是负责文件系统的操作（比如移动和重命名）的stage

我们将上述结果拆分看，先从最外层开始，包含两个大的部分：

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

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

这两个执行计划树里面包含这条sql语句的 operator：

2. explain 的使用场景

本节介绍 explain 能够为我们在生产实践中带来哪些便利及解决我们哪些迷惑

案例一：join 语句会过滤 null 的值吗？

现在，我们在hive cli 输入以下查询计划语句

上面这条 join 语句会过滤 id 为 null 的值吗

执行下面语句：

我们来看结果 (为了适应页面展示，仅截取了部分输出信息)：

predicate: id is not null说明 join 时会自动过滤掉关联字段为 null值的情况，但 left join 或 full join 是不会自动过滤null值的

案例二：group by 分组语句会进行排序吗？

看下面这条sql

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

直接来看 explain 之后结果 (为了适应页面展示，仅截取了部分输出信息)

说明是按照 id 字段进行正序排序的

案例三：哪条sql执行效率高呢？

观察两条sql语句

这两条sql语句输出的结果是一样的，但是哪条sql执行效率高呢

有人说第一条sql执行效率高，因为第二条sql有子查询，子查询会影响性能；

有人说第二条sql执行效率高，因为先过滤之后，在进行join时的条数减少了，所以执行效率就高了。

到底哪条sql效率高呢，我们直接在sql语句前面加上 explain，看下执行计划不就知道了嘛！

在第一条sql语句前加上 explain，得到如下结果

在第二条sql语句前加上 explain，得到如下结果

说明 hive 底层会自动帮我们进行优化，所以这两条sql语句执行效率是一样的

以上仅列举了3个我们生产中既熟悉又有点迷糊的例子，explain 还有很多其他的用途，如查看stage的依赖情况、排查数据倾斜、hive 调优等，小伙伴们可以自行尝试。

2. explain dependency的用法

explain dependency用于描述一段SQL需要的数据来源，输出是一个json格式的数据，里面包含以下两个部分的内容：

input_partitions

input_tables

使用explain dependency查看SQL查询非分区普通表

得到结果：

使用explain dependency查看SQL查询分区表

得到结果：

explain dependency的使用场景有两个：

场景一

场景二

下面通过两个案例来看explain dependency的实际运用：

案例一：识别看似等价的代码

对于刚接触SQL的程序员，很容易将

select * from a inner join b on a.no=b.no and a.f>1 and a.f<3;

等价于

select * from a inner join b on a.no=b.no where a.f>1 and a.f<3;

我们可以通过案例来查看下它们的区别：

代码1：

代码2：

我们看下上述两段代码explain dependency的输出结果：

代码1的explain dependency结果

代码2的explain dependency结果

通过上面的输出结果可以看到，其实上述的两个SQL并不等价，代码1在内连接（inner join）中的连接条件（on）中加入非等值的过滤条件后，并没有将内连接的左右两个表按照过滤条件进行过滤，内连接在执行时会多读取part=0的分区数据。而在代码2中，会过滤掉不符合条件的分区。

案例二：识别SQL读取数据范围的差别

代码1：

代码2：

以上两个代码的数据读取范围是一样的吗？答案是不一样，我们通过explain dependency来看下：

代码1的explain dependency结果

代码2的explain dependency结果

如果过滤条件是作用于右表（b表）有起到过滤的效果，则右表只要扫描两个分区即可，但是左表（a表）会进行全表扫描。如果过滤条件是针对左表，则完全没有起到过滤的作用，那么两个表将进行全表扫描

在使用过程中，容易认为代码片段2可以像代码片段1一样进行数据过滤，通过查看explain dependency的输出结果，可以知道不是如此。

3. explain authorization 的用法

通过explain authorization可以知道当前SQL访问的数据来源（INPUTS）和数据输出（OUTPUTS），以及当前Hive的访问用户（CURRENT_USER）和操作（OPERATION）。

在 hive cli 中输入以下命令：

结果如下：

从上面的信息可知：

上面案例的数据来源是defalut数据库中的 student_tb_orc表；

数据的输出路径是hdfs://node01:8020/tmp/hive/hdfs/cbf182a5-8258-4157-9194-90f1475a3ed5/-mr-10000；

当前的操作用户是hdfs，操作是查询；

Hive在默认不配置权限管理的情况下不进行权限验证，所有的用户在Hive里面都是超级管理员，即使不对特定的用户进行赋权，也能够正常查询

最后

通过上面对explain的介绍，可以发现explain中有很多值得我们去研究的内容，读懂 explain 的执行计划有利于我们优化Hive SQL，同时也能提升我们对SQL的掌控力。

八、Hive SQL底层执行原理

本节结构采用宏观着眼，微观入手，从整体到细节的方式剖析 Hive SQL 底层原理。第一节先介绍 Hive 底层的整体执行流程，然后第二节介绍执行流程中的 SQL 编译成 MapReduce 的过程，第三节剖析 SQL 编译成 MapReduce 的具体实现原理。

Hive 底层执行架构

我们先来看下 Hive 的底层执行架构图， Hive 的主要组件与 Hadoop 交互的过程：

在 Hive 这一侧，总共有五个组件：

UI：用户界面。可看作我们提交SQL语句的命令行界面。

DRIVER：驱动程序。接收查询的组件。该组件实现了会话句柄的概念。

COMPILER：编译器。负责将 SQL 转化为平台可执行的执行计划。对不同的查询块和查询表达式进行语义分析，并最终借助表和从 metastore 查找的分区元数据来生成执行计划。

METASTORE：元数据库。存储 Hive 中各种表和分区的所有结构信息。

EXECUTION ENGINE：执行引擎。负责提交 COMPILER 阶段编译好的执行计划到不同的平台上。

上图的基本流程是：

步骤1

步骤2

步骤3和4

步骤5

如果是 map/reduce 作业，该计划包括 map operator trees 和一个 reduce operator tree，执行引擎将会把这些作业发送给 MapReduce ：

步骤6、6.1、6.2和6.3

步骤7、8和9

Hive SQL 编译成 MapReduce 过程

编译 SQL 的任务是在上节中介绍的 COMPILER（编译器组件）中完成的。Hive将SQL转化为MapReduce任务，整个编译过程分为六个阶段：

Antlr是一种语言识别的工具，可以用来构造领域语言。使用Antlr构造特定的语言只需要编写一个语法文件，定义词法和语法替换规则即可，Antlr完成了词法分析、语法分析、语义分析、中间代码生成的过程。

语义解析

生成逻辑执行计划

优化逻辑执行计划

生成物理执行计划

优化物理执行计划

下面对这六个阶段详细解析：

为便于理解，我们拿一个简单的查询语句进行展示，对5月23号的地区维表进行查询：

阶段一

根据Antlr定义的sql语法规则，将相关sql进行词法、语法解析，转化为抽象语法树AST Tree：

阶段二

遍历AST Tree，抽象出查询的基本组成单元QueryBlock：

AST Tree生成后由于其复杂度依旧较高，不便于翻译为mapreduce程序，需要进行进一步抽象和结构化，形成QueryBlock。

QueryBlock是一条SQL最基本的组成单元，包括三个部分：输入源，计算过程，输出。简单来讲一个QueryBlock就是一个子查询。

QueryBlock的生成过程为一个递归过程，先序遍历 AST Tree ，遇到不同的 Token 节点(理解为特殊标记)，保存到相应的属性中。

阶段三

遍历QueryBlock，翻译为执行操作树OperatorTree：

Hive最终生成的MapReduce任务，Map阶段和Reduce阶段均由OperatorTree组成。

基本的操作符包括：

Operator在Map Reduce阶段之间的数据传递都是一个流式的过程。每一个Operator对一行数据完成操作后之后将数据传递给childOperator计算。

由于Join/GroupBy/OrderBy均需要在Reduce阶段完成，所以在生成相应操作的Operator之前都会先生成一个ReduceSinkOperator，将字段组合并序列化为Reduce Key/value, Partition Key。

阶段四

Hive中的逻辑查询优化可以大致分为以下几类：

阶段五

生成物理执行计划即是将逻辑执行计划生成的OperatorTree转化为MapReduce Job的过程，主要分为下面几个阶段：

阶段六

Hive中的物理优化可以大致分为以下几类：

经过以上六个阶段，SQL 就被解析映射成了集群上的 MapReduce 任务。

SQL编译成MapReduce具体原理

在阶段五-生成物理执行计划，即遍历 OperatorTree，翻译为 MapReduce 任务，这个过程具体是怎么转化的呢

我们接下来举几个常用 SQL 语句转化为 MapReduce 的具体步骤：

Join的实现原理

以下面这个SQL为例，讲解 join 的实现：

在map的输出value中为不同表的数据打上tag标记，在reduce阶段根据tag判断数据来源。MapReduce的过程如下：

Group By的实现原理

以下面这个SQL为例，讲解 group by 的实现：

将GroupBy的字段组合为map的输出key值，利用MapReduce的排序，在reduce阶段保存LastKey区分不同的key。MapReduce的过程如下:

Distinct的实现原理

以下面这个SQL为例，讲解 distinct 的实现：

当只有一个distinct字段时，如果不考虑Map阶段的Hash GroupBy，只需要将GroupBy字段和Distinct字段组合为map输出key，利用mapreduce的排序，同时将GroupBy字段作为reduce的key，在reduce阶段保存LastKey即可完成去重:

九、Hive千亿级数据倾斜

数据倾斜问题剖析

为什么说千亿级

所以就需要我们对数据倾斜的问题进行优化，尽量避免或减轻数据倾斜带来的影响。

在解决数据倾斜问题之前，还要再提一句：没有瓶颈时谈论优化，都是自寻烦恼。

如果相同的key过多，那么hash的结果就是大量相同的key进入到同一个reduce中

那么有没有可能在map阶段就发生数据倾斜呢，是有这种可能的。

当对文件使用GZIP压缩等不支持文件分割操作的压缩方式

发生数据倾斜的原因有两种：一是任务中需要处理大量相同的key的数据。二是任务读取不可分割的大文件

数据倾斜解决方案

MapReduce和Spark中的数据倾斜解决方案原理都是类似的，以下讨论Hive使用MapReduce引擎引发的数据倾斜，Spark数据倾斜也可以此为参照。

1. 空值引发的数据倾斜

实际业务中有些大量的null值或者一些无意义的数据参与到计算作业中，表中有大量的null值，如果表之间进行join操作，就会有shuffle产生，这样所有的null值都会被分配到一个reduce中，必然产生数据倾斜。

之前有小伙伴问，如果A、B两表join操作，假如A表中需要join的字段为null，但是B表中需要join的字段不为null，这两个字段根本就join不上啊，为什么还会放到一个reduce中呢？

这里我们需要明确一个概念，数据放到同一个reduce中的原因不是因为字段能不能join上，而是因为shuffle阶段的hash操作，只要key的hash结果是一样的，它们就会被拉到同一个reduce中。

解决方案

第一种：可以直接不让null值参与join操作，即不让null值有shuffle阶段

第二种：因为null值参与shuffle时的hash结果是一样的，那么我们可以给null值随机赋值，这样它们的hash结果就不一样，就会进到不同的reduce中：

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

对于两个表join，表a中需要join的字段key为int，表b中key字段既有string类型也有int类型。当按照key进行两个表的join操作时，默认的Hash操作会按int型的id来进行分配，这样所有的string类型都被分配成同一个id，结果就是所有的string类型的字段进入到一个reduce中，引发数据倾斜。

解决方案

如果key字段既有string类型也有int类型，默认的hash就都会按int类型来分配，那我们直接把int类型都转为string就好了，这样key字段都为string，hash时就按照string类型分配了：

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

当对文件使用GZIP压缩等不支持文件分割操作的压缩方式，在日后有作业涉及读取压缩后的文件时，该压缩文件只会被一个任务所读取

解决方案：

这种数据倾斜问题没有什么好的解决方案，只能将使用GZIP压缩等不支持文件分割的文件转为bzip和zip等支持文件分割的压缩方式。

我们在对文件进行压缩时，为避免因不可拆分大文件而引发数据读取的倾斜，在数据压缩的时候可以采用bzip2和Zip等支持文件分割的压缩算法

4. 数据膨胀引发的数据倾斜

在多维聚合计算时，如果进行分组聚合的字段过多，如下：

select a，b，c，count（1）from log group by a，b，c with rollup;

注：对于最后的with rollup关键字不知道大家用过没，with rollup是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息。

如果上面的log表的数据量很大，并且Map端的聚合不能很好地起到数据压缩的情况下，会导致Map端产出的数据急速膨胀，这种情况容易导致作业内存溢出的异常。如果log表含有数据倾斜key，会加剧Shuffle过程的数据倾斜。

解决方案

可以拆分上面的sql，将with rollup拆分成如下几个sql：

但是，上面这种方式不太好，因为现在是对3个字段进行分组聚合，那如果是5个或者10个字段呢，那么需要拆解的SQL语句会更多。

在Hive中可以通过参数 hive.new.job.grouping.set.cardinality 配置的方式自动控制作业的拆解，该参数默认值是30。表示针对grouping sets/rollups/cubes这类多维聚合的操作，如果最后拆解的键组合大于该值，会启用新的任务去处理大于该值之外的组合。如果在处理数据时，某个分组聚合的列有较大的倾斜，可以适当调小该值。

5. 表连接时引发的数据倾斜

两表进行普通的repartition join时，如果表连接的键存在倾斜，那么在 Shuffle 阶段必然会引起数据倾斜。

解决方案

通常做法是将倾斜的数据存到分布式缓存中，分发到各个 Map任务所在节点。在Map阶段完成join操作，即MapJoin，这避免了 Shuffle，从而避免了数据倾斜。

其适用于小表JOIN大表的场景

由于其需要将小表加载进内存所以要注意小表的大小

如将a表放到Map端内存中执行，在Hive 0.11版本之前需要这样写：

如果想将多个表放到Map端内存中，只需在mapjoin()中写多个表名称即可，用逗号分隔，如将a表和c表放到Map端内存中，则 /* +mapjoin(a,c) */ 。

在Hive 0.11版本及之后，Hive默认启动该优化，也就是不在需要显示的使用MAPJOIN标记，其会在必要的时候触发该优化操作将普通JOIN转换成MapJoin，可以通过以下两个属性来设置该优化的触发时机：

hive.auto.convert.join=true 默认值为true，自动开启MAPJOIN优化。

hive.mapjoin.smalltable.filesize=2500000 默认值为2500000(25M)，通过配置该属性来确定使用该优化的表的大小，如果表的大小小于此值就会被加载进内存中。

注意

hive.auto.convert.join=false (关闭自动MAPJOIN转换操作)

hive.ignore.mapjoin.hint=false (不忽略MAPJOIN标记)

再提一句：将表放到Map端内存时，如果节点的内存很大，但还是出现内存溢出的情况，我们可以通过这个参数 mapreduce.map.memory.mb 调节Map端内存的大小。

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

在一些操作中，我们没有办法减少数据量，如在使用 collect_list 函数时：

collect_list：将分组中的某列转为一个数组返回。

在上述sql中，s_age有数据倾斜，但如果数据量大到一定的数量，会导致处理倾斜的Reduce任务产生内存溢出的异常。

collect_list输出一个数组，中间结果会放到内存中，所以如果collect_list聚合太多数据，会导致内存溢出。

有小伙伴说这是 group by 分组引起的数据倾斜，可以开启hive.groupby.skewindata参数来优化。我们接下来分析下：

开启该配置会将作业拆解成两个作业，第一个作业会尽可能将Map的数据平均分配到Reduce阶段，并在这个阶段实现数据的预聚合，以减少第二个作业处理的数据量；第二个作业在第一个作业处理的数据基础上进行结果的聚合。

hive.groupby.skewindata的核心作用在于生成的第一个作业能够有效减少数量。但是对于collect_list这类要求全量操作所有数据的中间结果的函数来说，明显起不到作用，反而因为引入新的作业增加了磁盘和网络I/O的负担，而导致性能变得更为低下。

解决方案

这类问题最直接的方式就是调整reduce所执行的内存大小。

调整reduce的内存大小使用mapreduce.reduce.memory.mb这个配置。

总结

shuffle阶段堪称性能的杀手

所以围绕shuffle和数据倾斜有很多的调优点：

十、Hive企业级性能优化

Hive性能问题排查的方式

当我们发现一条SQL语句执行时间过长或者不合理时，我们就要考虑对SQL进行优化，优化首先得进行问题排查，那么我们可以通过哪些方式进行排查呢。

看执行计划

Hive中也有执行计划，但是Hive的执行计划都是预测的，这点不像Oracle和SQL Server有真实的计划，可以看到每个阶段的处理数据、消耗的资源和处理的时间等量化数据。Hive提供的执行计划没有这些数据，这意味着虽然Hive的使用者知道整个SQL的执行逻辑，但是各阶段耗用的资源状况和整个SQL的执行瓶颈在哪里是不清楚的。

YARN提供的日志

Hive提供的执行计划目前可以查看的信息有以下几种

在查询语句的SQL前面加上关键字explain是查看执行计划的基本方法。用explain打开的执行计划包含以下两部分：

Hive中的explain执行计划详解可看我之前写的这篇文章

Hive底层原理：explain执行计划详解

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

总结：Hive对SQL语句性能问题排查的方式

Hive性能调优的方式

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

下面将从多个完全不同的角度来介绍Hive优化的多样性，我们先来一起感受下。

1. SQL语句优化

SQL语句优化涉及到的内容太多，因篇幅有限，不能一一介绍到，所以就拿几个典型举例，让大家学到这种思想，以后遇到类似调优问题可以往这几个方面多思考下。

1. union all

我们简单分析上面的SQl语句，就是将每个年龄的最大和最小的生日获取出来放到同一张表中，union all 前后的两个语句都是对同一张表按照s_age进行分组，然后分别取最大值和最小值。对同一张表相同的字段进行两次分组，这造成了极大浪费，我们能不能改造下呢，当然是可以的，为大家介绍一个语法：from … insert into … ，这个语法将from前置，作用就是使用一张表，可以进行多次插入操作：

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

这个例子告诉我们一定要多了解SQL语句，如果我们不知道这种语法，一定不会想到这种方式的

2. distinct

先看一个SQL，去重计数：

这是简单统计年龄的枚举值个数，为什么不用distinct？

有人说因为在数据量特别大的情况下使用第一种方式能够有效避免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. 数据格式优化

Hive提供了多种数据存储组织格式，不同格式对程序的运行效率也会有极大的影响。

Hive提供的格式有TEXT、SequenceFile、RCFile、ORC和Parquet等。

SequenceFile是一个二进制key/value对结构的平面文件，在早期的Hadoop平台上被广泛用于MapReduce输出/输出格式，以及作为数据存储格式。

Parquet是一种列式数据存储格式，可以兼容多种计算引擎，如MapRedcue和Spark等，对多层嵌套的数据结构提供了良好的性能支持，是目前Hive生产环境中数据存储的主流选择之一。

ORC优化是对RCFile的一种优化，它提供了一种高效的方式来存储Hive数据，同时也能够提高Hive的读取、写入和处理数据的性能，能够兼容多种计算引擎。事实上，在实际的生产环境中，ORC已经成为了Hive在数据存储上的主流选择之一。

我们使用同样数据及SQL语句，只是数据存储格式不同，得到如下执行时长：

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

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

3. 小文件过多优化

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

所以我们有必要对小文件过多进行优化，关于小文件过多的解决的办法，我之前专门写了一篇文章讲解，具体可查看：

解决hive小文件过多问题

4. 并行执行优化

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

通过设置参数hive.exec.parallel值为true，就可以开启并发执行。在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。

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

5. JVM优化

JVM重用是Hadoop调优参数的内容，其对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短。

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

我们也可以在hive中设置

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

6. 推测执行优化

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

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

hive本身也提供了配置项来控制reduce-side的推测执行:

关于调优这些推测执行变量，还很难给一个具体的建议。如果用户对于运行时的偏差非常敏感的话，那么可以将这些功能关闭掉。如果用户因为输入数据量很大而需要执行长时间的map或者Reduce task的话，那么启动推测执行造成的浪费是非常巨大大。

最后

代码优化原则：

十一、Hive大厂面试真题

1. hive内部表和外部表的区别

未被external修饰的是内部表，被external修饰的为外部表。

本文首发于公众号【五分钟学大数据】，关注公众号，获取最新大数据技术文章

区别

内部表数据由Hive自身管理，外部表数据由HDFS管理；

内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）；

删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除

本文首发于公众号【五分钟学大数据】

2. Hive有索引吗

Hive支持索引（3.0版本之前），但是Hive的索引与关系型数据库中的索引并不相同，比如，Hive不支持主键或者外键。并且Hive索引提供的功能很有限，效率也并不高，因此Hive索引很少使用。

适用于不更新的静态字段。以免总是重建索引数据。每次建立、更新数据后，都要重建索引以构建索引表。

hive在指定列上建立索引，会产生一张索引表（Hive的一张物理表），里面的字段包括：索引列的值、该值对应的HDFS文件路径、该值在文件中的偏移量。

Hive 0.8版本后引入bitmap索引处理器，这个处理器适用于去重后，值较少的列（例如，某字段的取值只可能是几个枚举值）因为索引是用空间换时间，索引列的取值过多会导致建立bitmap索引表过大。

注意Hive官方文档已经明确表示Hive的索引不推荐被使用，在新版本的Hive中已经被废弃了

扩展

3. 运维如何对hive进行调度

将hive的sql定义在脚本当中；

使用azkaban或者oozie进行任务的调度；

监控任务调度页面。

4. ORC、Parquet等列式存储的优点

ORC和Parquet都是高性能的存储方式，这两种存储格式总会带来存储和性能上的提升。

Parquet

Parquet支持嵌套的数据模型，类似于Protocol Buffers，每一个数据模型的schema包含多个字段，每一个字段有三个属性：重复次数、数据类型和字段名。重复次数可以是以下三种：required(只出现1次)，repeated(出现0次或多次)，optional(出现0次或1次)。每一个字段的数据类型可以分成两种：group(复杂类型)和primitive(基本类型)。

Parquet中没有Map、Array这样的复杂数据结构，但是可以通过repeated和group组合来实现的。

由于Parquet支持的数据模型比较松散，可能一条记录中存在比较深的嵌套关系，如果为每一条记录都维护一个类似的树状结可能会占用较大的存储空间，因此Dremel论文中提出了一种高效的对于嵌套数据格式的压缩算法：Striping/Assembly算法。通过Striping/Assembly算法，parquet可以使用较少的存储空间表示复杂的嵌套格式，并且通常Repetition level和Definition level都是较小的整数值，可以通过RLE算法对其进行压缩，进一步降低存储空间。

Parquet文件是以二进制方式存储的，是不可以直接读取和修改的，Parquet文件是自解析的，文件中包括该文件的数据和元数据。

ORC

ORC文件是自描述的，它的元数据使用Protocol Buffers序列化，并且文件中的数据尽可能的压缩以降低存储空间的消耗。

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

ORC会尽可能合并多个离散的区间尽可能的减少I/O次数。

ORC中使用了更加精确的索引信息，使得在读取数据时可以指定从任意一行开始读取，更细粒度的统计信息使得读取ORC文件跳过整个row group，ORC默认会对任何一块数据和索引信息使用ZLIB压缩，因此ORC文件占用的存储空间也更小。

在新版本的ORC中也加入了对Bloom Filter的支持，它可以进一步提升谓词下推的效率，在Hive 1.2.0版本以后也加入了对此的支持。

5. 数据建模用的哪些模型？

1. 星型模型

星形模式(Star Schema)是最常用的维度建模方式。星型模式是以事实表为中心，所有的维度表直接连接在事实表上，像星星一样。星形模式的维度建模由一个事实表和一组维表成，且具有以下特点：

a. 维表只和事实表关联，维表之间没有关联；

b. 每个维表主键为单列，且该主键放置在事实表中，作为两边连接的外键；

c. 以事实表为核心，维表围绕核心呈星形分布。

2. 雪花模型

雪花模式的维度表可以拥有其他维度表的

3. 星座模型

星座模式是基于多张事实表的，而且共享维度信息

数仓建模详细介绍可查看：通俗易懂数仓建模

6. 为什么要对数据仓库分层？

用空间换时间

如果不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。

通过数据分层管理可以简化数据清洗的过程

数据仓库详细介绍可查看：万字详解整个数据仓库建设体系

7. 使用过Hive解析JSON串吗

Hive处理json数据总体来说有两个方向的路走

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

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

详细介绍可查看：Hive解析Json数组超全讲解

8. sort by 和 order by 的区别

order by 会对输入做全局排序，因此只有一个reducer

sort by只保证每个reducer的输出有序，不保证全局有序

9. 数据倾斜怎么解决

数据倾斜问题主要有以下几种：

空值引发的数据倾斜

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

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

数据膨胀引发的数据倾斜

表连接时引发的数据倾斜

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

以上倾斜问题的具体解决方案可查看：Hive千亿级数据倾斜解决方案

注意

小伙伴们在阅读时注意下，在上面的文章（Hive千亿级数据倾斜解决方案）中，有一处sql出现了上述问题（举例的时候原本是想使用left join的，结果手误写成了join）。此问题由公众号读者发现，感谢这位读者指正。

10. Hive 小文件过多怎么解决

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

使用方法：

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

2. 调整参数减少Map数量

设置map输入合并小文件的相关参数（执行Map前进行小文件合并）：

在mapper中将多个文件合成一个split作为输入（CombineHiveInputFormat底层是Hadoop的CombineFileInputFormat方法）：

每个Map最大输入大小（这个值决定了合并后文件的数量）：

一个节点上split的至少大小（这个值决定了多个DataNode上的文件是否需要合并）：

一个交换机下split的至少大小(这个值决定了多个交换机上的文件是否需要合并)：

3. 减少Reduce的数量

reduce 的个数决定了输出的文件的个数，所以可以调整reduce的个数控制hive表的文件数量。

hive中的分区函数 distribute by 正好是控制MR中partition分区的，可以通过设置reduce的数量，结合分区函数让数据均衡的进入每个reduce即可：

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

4. 使用hadoop的archive将小文件归档

Hadoop Archive简称HAR，是一个高效地将小文件放入HDFS块中的文件存档工具，它能够将多个小文件打包成一个HAR文件，这样在减少namenode内存使用的同时，仍然允许对文件进行透明的访问。

归档的分区可以查看不能 insert overwrite，必须先 unarchive

Hive 小文件问题具体可查看：解决hive小文件过多问题

11. Hive优化有哪些

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优化详细剖析可查看：Hive企业级性能优化

附：九个最易出错的SQL讲解

阅读本节小建议：本文适合细嚼慢咽，不要一目十行，不然会错过很多有价值的细节。

在进行数仓搭建和数据分析时最常用的就是 sql，其语法简洁明了，易于理解，目前大数据领域的几大主流框架全部都支持sql语法，包括 hive，spark，flink等，所以sql在大数据领域有着不可替代的作用，需要我们重点掌握。

在使用sql时如果不熟悉或不仔细，那么在进行查询分析时极容易出错，接下来我们就来看下几个容易出错的sql语句及使用注意事项。

1. decimal

hive 除了支持 int,double,string等常用类型，也支持 decimal 类型，用于在数据库中存储精确的数值，常用在表示金额的字段上

注意事项：

整数部分超过9位，则这个字段就会变成null，如果整数部分不超过9位，则原字段显示小数部分不足2位，则后面用0补齐两位，如果小数部分超过两位，则超出部分四舍五入

2. location

注意事项：

指定文件夹时，hive会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错。msck repair table score

3. load data 和 load data local

注意事项：

4. drop 和 truncate

注意事项：

hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的truncate 清空的表是不进回收站的，所以无法恢复truncate清空的表。

5. join 连接

注意事项：

注意

它们的执行效率没有区别，只是书写方式不同

6. left semi join

注意事项：

7. 聚合函数中 null 值

注意事项：

聚合操作时要注意 null 值

求最小值是不包含 null求平均值也是不包含 null

以上需要特别注意，null 值最容易导致算出错误的结果

8. 运算符中 null 值

注意事项：

各字段含义： id (商品id)、price (价格)、dis_amount (优惠金额)

每个商品优惠后实际的价格

得到结果如下：

id=2的商品价格为 null，结果是错误的。

对 null 值进行处理

小于是不包含 null 值

not in 是不包含 null 值的

9. and 和 or

and 的优先级高于 or

注意事项：

例：还是一张商品表（product）

我想要统计下电器或者家具这两类中价格大于100的商品，sql如下：

得到结果

结果是错误的，把所有的电器类型都查询出来了，原因就是 and 优先级高于 or，上面的sql语句实际执行的是，先找出 classify = ‘家具’ and price>100 的，然后在找出 classify = ‘电器’ 的

正确的 sql 就是加个括号，先计算括号里面的：

最后

文章版权归作者所有，未经允许请勿转载。

THE END

大数据分享
# bi