Hive内容分享(十五)：Hive面试题分享

1、下述SQL在Hive、SparkSql两种引擎中，执行流程分别是什么，区别是什么

Hive on Mapreduce

hive的特性：

1. hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

2. Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。

要理解hive，必须先理解hadoop和mapreduce。

使用hive的命令行接口，感觉很像操作关系数据库，但是hive和关系数据库还是有很大的不同，hive与关系数据库的区别，具体如下：

1. hive和关系数据库存储文件的系统不同，hive使用的是hadoop的HDFS（hadoop的分布式文件系统），关系数据库则是服务器本地的文件系统；

2. hive使用的计算模型是map-reduce，而关系数据库则是自己设计的计算模型；

3. 关系数据库都是为实时查询的业务进行设计的，而hive则是为海量数据做数据挖掘设计的，实时性很差；实时性的区别导致hive的应用场景和关系数据库有很大的不同；

4. Hive很容易扩展自己的存储能力和计算能力，这个是继承hadoop的，而关系数据库在这个方面要比数据库差很多。

以上都是从宏观的角度比较hive和关系数据库的区别，hive和关系数据库的异同还有很多，我在文章的后面会一一描述。

下面来讲讲hive的技术架构，大家先看下面的架构图：

由上图可知，hadoop和mapreduce是hive架构的根基

Hive架构包括如下组件：CLI（command line interface）、JDBC/ODBC、Thrift Server、WEB GUI、metastore和Driver(Complier、Optimizer和Executor)，

这些组件可以分为两大类：服务端组件和客户端组件。

首先讲讲服务端组件：

Driver组件：该组件包括Complier、Optimizer和Executor，它的作用是将我们写的HiveQL（类SQL）语句进行解析、编译优化，生成执行计划，然后调用底层的mapreduce计算框架。

Metastore组件：元数据服务组件，这个组件存储hive的元数据，hive的元数据存储在关系数据库里，hive支持的关系数据库有derby、mysql。元数据对于hive十分重要，因此hive支持把metastore服务独立出来，安装到远程的服务器集群里，从而解耦hive服务和metastore服务，保证hive运行的健壮性，这个方面的知识，我会在后面的metastore小节里做详细的讲解。

Thrift服务：thrift是facebook开发的一个软件框架，它用来进行可扩展且跨语言的服务的开发，hive集成了该服务，能让不同的编程语言调用hive的接口。

客户端组件：

CLI：command line interface，命令行接口。

Thrift客户端：上面的架构图里没有写上Thrift客户端，但是hive架构的许多客户端接口是建立在thrift客户端之上，包括JDBC和ODBC接口。

WEBGUI：hive客户端提供了一种通过网页的方式访问hive所提供的服务。这个接口对应hive的hwi组件（hive web interface），使用前要启动hwi服务。

下面我着重讲讲metastore组件,具体如下：

Hive的metastore组件是hive元数据集中存放地。

Metastore组件包括两个部分：metastore服务和后台数据的存储。

后台数据存储的介质就是关系数据库，例如hive默认的嵌入式磁盘数据库derby，还有mysql数据库。

Metastore服务是建立在后台数据存储介质之上，并且可以和hive服务进行交互的服务组件，默认情况下，metastore服务和hive服务是安装在一起的，运行在同一个进程当中。

也可以把metastore服务从hive服务里剥离出来，metastore独立安装在一个集群里，hive远程调用metastore服务，这样我们可以把元数据这一层放到防火墙之后，客户端访问hive服务，就可以连接到元数据这一层，从而提供了更好的管理性和安全保障。

使用远程的metastore服务，可以让metastore服务和hive服务运行在不同的进程里，这样也保证了hive的稳定性，提升了hive服务的效率。

Hive on Mapreduce执行流程

 

执行流程详细解析

• Step 1：UI(user interface) 调用 executeQuery 接口，发送 HQL 查询语句给 Driver

• Step 2：Driver 为查询语句创建会话句柄，并将查询语句发送给 Compiler， 等待其进行语句解析并生成执行计划

• Step 3 and 4：Compiler 从 metastore 获取相关的元数据

• Step 5：元数据用于对查询树中的表达式进行类型检查，以及基于查询谓词调整分区，生成计划

• Step 6 (6.1，6.2，6.3)：由 Compiler 生成的执行计划是阶段性的 DAG，每个阶段都可能会涉及到 Map/Reduce job、元数据的操作、HDFS 文件的操作，Execution Engine 将各个阶段的 DAG 提交给对应的组件执行。

• Step 7, 8 and 9：在每个任务（mapper / reducer）中，查询结果会以临时文件的方式存储在 HDFS 中。保存查询结果的临时文件由 Execution Engine 直接从 HDFS 读取，作为从 Driver Fetch API 的返回内容。

Hive on Mapreduce特点

1. 关系数据库里，表的加载模式是在数据加载时候强制确定的（表的加载模式是指数据库存储数据的文件格式），如果加载数据时候发现加载的数据不符合模式，关系数据库则会拒绝加载数据，这个就叫“写时模式”，写时模式会在数据加载时候对数据模式进行检查校验的操作。Hive在加载数据时候和关系数据库不同，hive在加载数据时候不会对数据进行检查，也不会更改被加载的数据文件，而检查数据格式的操作是在查询操作时候执行，这种模式叫“读时模式”。在实际应用中，写时模式在加载数据时候会对列进行索引，对数据进行压缩，因此加载数据的速度很慢，但是当数据加载好了，我们去查询数据的时候，速度很快。但是当我们的数据是非结构化，存储模式也是未知时候，关系数据操作这种场景就麻烦多了，这时候hive就会发挥它的优势。

2. 关系数据库一个重要的特点是可以对某一行或某些行的数据进行更新、删除操作，hive不支持对某个具体行的操作，hive对数据的操作只支持覆盖原数据和追加数据。Hive也不支持事务和索引。更新、事务和索引都是关系数据库的特征，这些hive都不支持，也不打算支持，原因是hive的设计是海量数据进行处理，全数据的扫描时常态，针对某些具体数据进行操作的效率是很差的，对于更新操作，hive是通过查询将原表的数据进行转化最后存储在新表里，这和传统数据库的更新操作有很大不同。

3. Hive也可以在hadoop做实时查询上做一份自己的贡献，那就是和hbase集成，hbase可以进行快速查询，但是hbase不支持类SQL的语句，那么此时hive可以给hbase提供sql语法解析的外壳，可以用类sql语句操作hbase数据库。

4. Hive可以认为是MapReduce的一个封装、包装。Hive的意义就是在业务分析中将用户容易编写、会写的Sql语言转换为复杂难写的MapReduce程序，从而大大降低了Hadoop学习的门槛，让更多的用户可以利用Hadoop进行数据挖掘分析。

比较项	SQL	HiveQL
ANSI SQL	支持	不完全支持
更新	UPDATE\INSERT\DELETE	insert OVERWRITE\INTO TABLE
事务	支持	不支持
模式	写模式	读模式
数据保存	块设备、本地文件系统	HDFS
延时	低	高
多表插入	不支持	支持
子查询	完全支持	只能用在From子句中
视图	Updatable	Read-only
可扩展性	低	高
数据规模	小	大
…	…	…

SparkSQL

SparkSQL简介

SparkSQL的前身是Shark，给熟悉RDBMS但又不理解MapReduce的技术人员提供快速上手的工具，hive应运而生，它是当时唯一运行在Hadoop上的SQL-on-hadoop工具。但是MapReduce计算过程中大量的中间磁盘落地过程消耗了大量的I/O，降低的运行效率，为了提高SQL-on-Hadoop的效率，Shark应运而生，但又因为Shark对于Hive的太多依赖（如采用Hive的语法解析器、查询优化器等等),2014年spark团队停止对Shark的开发，将所有资源放SparkSQL项目上

 

其中SparkSQL作为Spark生态的一员继续发展，而不再受限于Hive，只是兼容Hive；而Hive on Spark是一个Hive的发展计划，该计划将Spark作为Hive的底层引擎之一，也就是说，Hive将不再受限于一个引擎，可以采用Map-Reduce、Tez、Spark等引擎。

• SparkSQL的两个组件

1. SQLContext：Spark SQL提供SQLContext封装Spark中的所有关系型功能。可以用之前的示例中的现有SparkContext创建SQLContext。

2. DataFrame：DataFrame是一个分布式的，按照命名列的形式组织的数据集合。DataFrame基于R语言中的data frame概念，与关系型数据库中的数据库表类似。通过调用将DataFrame的内容作为行RDD（RDD of Rows）返回的rdd方法，可以将DataFrame转换成RDD。可以通过如下数据源创建DataFrame：已有的RDD、结构化数据文件、JSON数据集、Hive表、外部数据库。

SparkSQL运行架构

类似于关系型数据库，SparkSQL也是语句也是由Projection（a1，a2，a3）、Data Source（tableA）、Filter（condition）组成，分别对应sql查询过程中的Result、Data Source、Operation，也就是说SQL语句按Operation–>Data Source–>Result的次序来描述的。

 

当执行SparkSQL语句的顺序：

1. 对读入的SQL语句进行解析（Parse），分辨出SQL语句中哪些词是关键词（如SELECT、FROM、WHERE），哪些是表达式、哪些是Projection、哪些是Data Source等，从而判断SQL语句是否规范；

• Projection：简单说就是select选择的列的集合，参考：SQL Projection(http://blog.csdn.net/chncaesar/article/details/17319589)

1. 将SQL语句和数据库的数据字典（列、表、视图等等）进行绑定（Bind），如果相关的Projection、Data Source等都是存在的话，就表示这个SQL语句是可以执行的；

2. 一般的数据库会提供几个执行计划，这些计划一般都有运行统计数据，数据库会在这些计划中选择一个最优计划（Optimize）；

3. 计划执行（Execute），按Operation–>Data Source–>Result的次序来进行的，在执行过程有时候甚至不需要读取物理表就可以返回结果，比如重新运行刚运行过的SQL语句，可能直接从数据库的缓冲池中获取返回结果。

解析方式如下：

 

Hive on Spark

hive on Spark是由Cloudera发起，由Intel、MapR等公司共同参与的开源项目，其目的是把Spark作为Hive的一个计算引擎，将Hive的查询作为Spark的任务提交到Spark集群上进行计算。通过该项目，可以提高Hive查询的性能，同时为已经部署了Hive或者Spark的用户提供了更加灵活的选择，从而进一步提高Hive和Spark的普及率。

在hive中使用以下语句开启；当然引擎还可以使用tez，一样的方式hive> set hive.execution.engine=spark;

解析方式如下：

 

Hive on Spark与SparkSql的区别

hive on spark大体与SparkSQL结构类似，只是SQL解析器不同，但是计算引擎都是spark！（比如sparksql的解析过程中多了很多优化，cbo这类的）敲黑板！这才是重点！

我们来看下，在pyspark中使用Hive on Spark是中怎么样的体验

#初始化Spark SQL
#导入Spark SQL
from pyspark.sql import HiveContext,Row
# 当不能引入Hive依赖时
# from pyspark.sql import SQLContext,Row
# 注意，上面那一点才是关键的，他两来自于同一个包，你们区别能有多大


hiveCtx = HiveContext(sc) #创建SQL上下文环境
input = hiveCtx.jsonFile(inputFile)   #基本查询示例
input.registerTempTable("tweets") #注册输入的SchemaRDD（SchemaRDD在Spark 1.3版本后已经改为DataFrame）
#依据retweetCount(转发计数)选出推文
topTweets = hiveCtx.sql("SELECT text,retweetCount FROM tweets ORDER BY retweetCount LIMIT 10")

SparkSQL和Hive On Spark都是在Spark上实现SQL的解决方案。Spark早先有Shark项目用来实现SQL层，不过后来推翻重做了，就变成了SparkSQL。这是Spark官方Databricks的项目，Spark项目本身主推的SQL实现。Hive On Spark比SparkSQL稍晚。Hive原本是没有很好支持MapReduce之外的引擎的，而Hive On Tez项目让Hive得以支持和Spark近似的Planning结构（非MapReduce的DAG）。所以在此基础上，Cloudera主导启动了Hive On Spark。这个项目得到了IBM，Intel和MapR的支持（但是没有Databricks）。—From: SparkSQL与Hive on Spark的比较(http://blog.csdn.net/yeruby/article/details/51448188)

Hive on Mapreduce和SparkSQL使用场景

Hive on Mapreduce场景

• Hive的出现可以让那些精通SQL技能、但是不熟悉MapReduce 、编程能力较弱与不擅长Java语言的用户能够在HDFS大规模数据集上很方便地利用SQL 语言查询、汇总、分析数据，毕竟精通SQL语言的人要比精通Java语言的多得多

• Hive适合处理离线非实时数据

SparkSQL场景

• Spark既可以运行本地local模式，也可以以Standalone、cluster等多种模式运行在Yarn、Mesos上，还可以运行在云端例如EC2。此外，Spark的数据来源非常广泛，可以处理来自HDFS、HBase、 Hive、Cassandra、Tachyon上的各种类型的数据。

• 实时性要求或者速度要求较高的场所

Hive on Mapreduce和SparkSQL性能对比

结论：sparksql和hive on spark时间差不多，但都比hive on mapreduce快很多，官方数据认为spark会被传统mapreduce快10-100倍

Hive vs Hive on Spark vs Sparksql vs RDD

对比试验详见：https://hivevssparksql.wordpress.com/

2、Hive有哪些保存元数据的方式

• 内存数据库derby，安装小，数据存在内存中，不稳定

• MySql数据库，数据存储模式可以自己设置，持久化好，查看方便

3、简单描述一下Hive的功能，用Hive创建表有几种方式，Hive表有几种

Hive主要做离线分析

Hive建表有三种方式：

• 直接建表

• 查询建表：将自查询的结果存在新表里，一般用于中间表

• Like建表法：常见结构完全相同的表，但无数据

hive表有两种：外部表与内部表

4、Hive内部表和外部表的区别

默认创建内部表，创建外部表，需要加上external关键字修饰，还可通过location指定Hive仓库的路径

默认仓库路径：

内部表：内部表的默认创建路径

在：/user/hive/warehouse/database.db/xm_testA

外部表：外部表的默认创建路径

在：/user/hive/warehouse/database.db/xm_testB

drop表

内部表：内部表删除后会将元数据和路径下的文件都删除

外部表：外部表只删除元数据，不删除路径下的文件

load加载数据：

内部表：会把数据移动到自己指定的路径下

外部表：不会把数据移动到自己的数据仓库目录下，也因此证明外部表的数据不是由自己管理的

• 做etl处理时，通常会选择内部表做中间表，因为清理时，会将HDFS上的文件同时删除

• 如果怕误删数据，可以选择外部表，因为不会删除文件，方便恢复数据

• 如果对数据的处理都是通过hql语句完成，选择内部表，如果有其他工具一同处理，选择外部表

在没有其他限制的情况下，优先使用外部表，因为：

• 不会加载数据到hive，减少数据传输，还能共享

• 不会对HDFS中的数据修改，不用担心数据损坏，删除表时只删除表结构，不删除数据

5、生产环境中为什么建议使用外部表

答案见问题四

6、大表Join小表产生的问题，如何解决

大表Join小表，必然使用MapJoin

Join因为空值导致长尾（key为空值是用随机值代替）

Join因为热点值导致长尾，也可以将热点数据和非热点数据分开处理，最后合并

7、UDF、UDAF、UDTF的区别

UDF：用户定义函数

UDAF：用户定义聚集函数

UDTF：用户定义表生成函数

8、insert into和override write区别

insert into：将数据写入表中

override write：覆盖之前内容

9、Hive的判断函数有哪些？

-- if (boolean testCondition, T valueTrue, T valueFalseOrNull)
select sal,if(sal < 1500,if(sal < 3000 , 2 , 3 ) ) from emp

-- CASE WHEN a THEN b [wHEN c THEN d] * [ELSE e] END
-- 将emp表的员工工资等级分类：0-1500，1500-3000，3000以上
select sal,case when sal < = 1500 then 1
        when sal < = 3000 then 2 
        else 3 end salleve
from emp

-- COALESCE(T v1, T v2, ···) 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select sal,coalesce(comm, 0) from emp;

-- isnull(a) isnotnull(a)
select * from emp where isnull(comm);
select * from emp where isnotnull(comm);

-- nvl(t value, T default_value)
select empno,ename,job,sal + nvl(comm,0) sumsal from emp;

-- nullif(x,y)相等为空，否则为a
select nullif("b", "b"),nullif("b", "a"); 

10、若Hive中建立分区仍不能优化查询效率，建表时如何优化

如果在Hive中建立分区表仍然无法优化查询效率，你可以考虑通过建立分区分桶表来进一步优化。分区分桶表是将数据同时按照分区和分桶的方式进行组织的表结构。

分区分桶表的创建过程如下：

1. 创建表时指定分区字段：在创建表的DDL语句中，使用PARTITIONED BY子句指定分区字段。例如，如果你有一个日期字段作为分区字段，可以这样指定：PARTITIONED BY (date_col string)

2. 创建表时指定分桶字段和数量：在创建表的DDL语句中，使用CLUSTERED BY子句指定分桶字段，并使用INTO子句指定分桶的数量。例如，如果你有一个用户ID字段作为分桶字段，可以这样指定：CLUSTERED BY (user_id) INTO 10 BUCKETS

3. 插入数据：将数据插入到分区分桶表中时，确保按照分区字段和分桶字段的值进行插入。这样可以确保数据被正确地分布到各个分区和分桶中。

使用分区分桶表可以带来以下优势：

1. 更快的查询速度：分区分桶表可以将数据进一步分割成更小的块，使得查询只需要处理特定的分区和分桶，减少了数据的扫描范围，从而提高查询效率。

2. 更好的数据聚集性：分桶操作会将具有相同分桶键的数据存储在一起，提高了数据的聚集性。这对于某些查询操作（如连接操作）可以减少数据的传输量，提高查询性能。

需要注意的是，分区分桶表的创建和维护需要一定的成本和复杂性。你需要根据实际情况选择合适的分桶字段和数量，并确保数据插入的正确性。同时，分区分桶表在某些特定场景下才会带来明显的性能提升，需要综合考虑数据量、查询模式等因素来决定是否使用分区分桶表。

11、UNION和UNION ALL的区别

UNION和UNION ALL是用于合并多个查询结果集的SQL操作，它们的主要区别是：UNION去重，UNION ALL不去重。

1. UNION操作：

• UNION操作会合并多个查询结果集，并去除重复的行。如果多个查询结果中存在相同的行，UNION操作只会返回一次。

• UNION操作会对结果集进行排序，以确保去重后的结果是有序的。

• 由于需要进行去重和排序的操作，UNION操作的性能相对较低，尤其是在处理大量数据时。

1. UNION ALL操作：

• UNION ALL操作也会合并多个查询结果集，但是不进行去重操作。它会将所有的行都包含在结果集中，不管是否存在重复。

• UNION ALL操作不需要进行排序，因此性能相对较高，尤其是在不需要去重的情况下。

需要根据具体的业务需求来选择使用UNION还是UNION ALL：

• 如果你希望合并多个查询结果并且去除重复的行，可以使用UNION操作。

• 如果你希望合并多个查询结果但不需要去重，或者你已经确保多个查询结果中没有重复的行，可以使用UNION ALL操作，它的性能会更好。

需要注意的是，UNION和UNION ALL操作要求多个查询结果的列数和数据类型必须一致，否则会导致操作失败。在使用这两个操作时，建议仔细检查查询结果的结构和数据类型，确保它们匹配一致。

12、如何解决Hive数据倾斜的问题

1. 大表与小表Join

MapJoin（hint指定小表、参数配置自动做MapJoin）

2. 大表与大表Join

空值单独提取出来分析后合并

给空值在join时使用随机数打散

3. Group By

1）开启Map端聚合配置

2）配置有GroupBy操作时数据倾斜进行负载均衡

4. COUNT(DISTINCT)

将count(distinct)替换为先Group By，再Count的方式

13、Hive性能优化常用的方法

1. 前言

毫不夸张的说，有没有掌握hive调优，是判断一个数据工程师是否合格的重要指标

hive调优涉及到压缩和存储调优，参数调优，sql的调优，数据倾斜调优，小文件问题的调优等

2. 数据的压缩与存储格式

1）map阶段输出数据压缩 ，在这个阶段，优先选择一个低CPU开销的算法。

set hive.exec.compress.intermediate=true
set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec
set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;

2）对最终输出结果压缩

set hive.exec.compress.output=true 
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
 
## 当然，也可以在hive建表时指定表的文件格式和压缩编码

结论，一般选择orcfile/parquet + snappy 方式

3. 合理利用分区分桶

分区是将表的数据在物理上分成不同的文件夹，以便于在查询时可以精准指定所要读取的分区目录，从来降低读取的数据量

分桶是将表数据按指定列的hash散列后分在了不同的文件中，将来查询时，hive可以根据分桶结构，快速定位到一行数据所在的分桶文件，从来提高读取效率

4. hive参数优化

// 让可以不走mapreduce任务的，就不走mapreduce任务
hive> set hive.fetch.task.conversion=more;
 
// 开启任务并行执行
 set hive.exec.parallel=true;
// 解释：当一个sql中有多个job时候，且这多个job之间没有依赖，则可以让顺序执行变为并行执行（一般为用到union all的时候）
 
 // 同一个sql允许并行任务的最大线程数 
set hive.exec.parallel.thread.number=8;
 
// 设置jvm重用
// JVM重用对hive的性能具有非常大的 影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。
set mapred.job.reuse.jvm.num.tasks=10; 
 
// 合理设置reduce的数目
// 方法1：调整每个reduce所接受的数据量大小
set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
// 方法2：直接设置reduce数量
set mapred.reduce.tasks = 20

// map端聚合，降低传给reduce的数据量


set hive.map.aggr=true  
// 开启hive内置的数倾优化机制

set hive.groupby.skewindata=true

5. sql优化

1）where条件优化

优化前（关系数据库不用考虑会自动优化）

select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808';

优化后(where条件在map端执行而不是在reduce端执行）

select m.cid,u.id from （select * from order where dt='20180818'） m join customer u on( m.cid =u.id);

2）union优化

尽量不要使用union （union 去掉重复的记录）而是使用 union all 然后在用group by 去重

3）count distinct优化

不要使用count (distinct cloumn) ,使用子查询

select count(1) from (select id from tablename group by id) tmp;

4）用in 来代替join

如果需要根据一个表的字段来约束另为一个表，尽量用in来代替join . in 要比join 快

select id,name from tb1  a join tb2 b on(a.id = b.id);
 
select id,name from tb1 where id in(select id from tb2);

5）优化子查询

消灭子查询内的 group by 、 COUNT(DISTINCT)，MAX，MIN。可以减少job的数量。

6）join 优化

Common/shuffle/Reduce JOIN 连接发生的阶段，发生在reduce 阶段， 适用于大表 连接 大表(默认的方式)

Map join ：

连接发生在map阶段 ， 适用于小表 连接 大表 大表的数据从文件中读取 小表的数据存放在内存中（hive中已经自动进行了优化，自动判断小表，然后进行缓存）

set hive.auto.convert.join=true;

SMB join Sort -Merge -Bucket Join  对大表连接大表的优化，用桶表的概念来进行优化。在一个桶内发生笛卡尔积连接（需要是两个桶表进行join）

set hive.auto.convert.sortmerge.join=true;  
set hive.optimize.bucketmapjoin = true;  
set hive.optimize.bucketmapjoin.sortedmerge = true;  
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

 

6. 数据倾斜

表现：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。

原因：某个reduce的数据输入量远远大于其他reduce数据的输入量

1）sql本身导致的倾斜

（1）group by

如果是在group by中产生了数据倾斜，是否可以讲group by的维度变得更细，如果没法变得更细，就可以在原分组key上添加随机数后分组聚合一次，然后对结果去掉随机数后再分组聚合

在join时，有大量为null的join key，则可以将null转成随机值，避免聚集

（2）count(distinct)

情形：某特殊值过多

后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务

解决方式：count distinct 时，将值为空的情况单独处理，比如可以直接过滤空值的行，

在最后结果中加 1。如果还有其他计算，需要进行 group by，可以先将值为空的记录单独处理，再和其他计算结果进行 union。

（3）不同数据类型关联产生数据倾斜

情形：比如用户表中 user_id 字段为 int，log 表中 user_id 字段既有 string 类型也有 int 类型。当按照 user_id 进行两个表的 Join 操作时。

后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务

默认的 Hash 操作会按 int 型的 id 来进行分配，这样会导致所有 string 类型 id 的记录都分配

到一个 Reducer 中。

解决方式：把数字类型转换成字符串类型

select * from users a
left outer join logs b
on a.usr_id = cast(b.user_id as string)

（4）mapjoin

2）业务数据本身的特性(存在热点key)

join的每路输入都比较大，且长尾是热点值导致的，可以对热点值和非热点值分别进行处理，再合并数据

3）key本身分布不均

可以在key上加随机数，或者增加reduceTask数量

开启数据倾斜时负载均衡

set hive.groupby.skewindata=true;

思想：就是先随机分发并处理，再按照 key group by 来分发处理。

操作：当选项设定为 true，生成的查询计划会有两个 MRJob。

第一个 MRJob 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 GroupBy Key 有可能被分发到不同的Reduce 中，从而达到负载均衡的目的；

第二个 MRJob 再根据预处理的数据结果按照 GroupBy Key 分布到 Reduce 中（这个过程可以保证相同的原始 GroupBy Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

4）控制空值分布

将为空的 key 转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分不到多个 Reducer。

注：对于异常值如果不需要的话，最好是提前在 where 条件里过滤掉，这样可以使计算量大大减少

7. 合并小文件

小文件的产生有三个地方，map输入，map输出，reduce输出，小文件过多也会影响hive的分析效率：

设置map输入的小文件合并

set mapred.max.split.size=256000000;  
//一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000;
//一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)  
set mapred.min.split.size.per.rack=100000000;
//执行Map前进行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

设置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
//当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge。
set hive.merge.smallfiles.avgsize=16000000

 

8. 查看sql的执行计划

explain sql 

学会查看sql的执行计划，优化业务逻辑 ，减少job的数据量。对调优也非常重要

14、简述Delete、Drop、Trancate的区别

delete：删除数据

drop：删除表

truncate：摧毁表结构并重建

15、order by , sort by , distribute by , cluster by 的区别

• order by：会对输入做全局排序，因此只有一个 reducer(多个 reducer无法保证全局有序)，然而只有一个 Reducer会导致当输入规模较大时，消耗较长的计算时间

• sort by：不是全局排序，其在数据进入 reducer前完成排序，因此，如果用 sort by进行排序并且设置 mapped. reduce. tasks >1，则 sort by只会保证每个 reducer的输出有序，并不保证全局有序。(全排序实现:先用 sortby保证每个 reducer输出有序，然后在进行 order by归并下前面所有的 reducer输出进行单个 reducer排序，实现全局有序。)

• distribute by：控制在map端如何拆分数据给 reduce端的。hive会根据 distribute by后面列，对应 reduce的个数进行分发，默认是采用hash算法。sort by为每个 reduce产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个 reducer，这通常是为了进行后续的聚集操作。distribute by刚好可以做这件事。因此， distribute by经常和 sort by配合使用。并且hive规定distribute by 语句要写在sort by语句之前

• cluster by：当distribute by 和 sort by 所指定的字段相同时，即可以使用cluster by

  • 注意:cluster by指定的列只能是升序，不能指定asc和desc

 

16、Hive 里边字段的分隔符用的什么?为什么用\t?有遇到过字段里 边有\t的情况吗，怎么处理的?为什么不用 Hive 默认的分隔符，默认的分隔符是什么?

Hive默认的字段分隔符为ASCII码控制符\001(^A)，建表的时候用fields terminated by '\001'

遇到过字段里面有\t的情况，自定义InputFormat，替换为其他分隔符再做后续处理

17、分区分桶的区别，为什么要分区

• 分区表：原来的一个大表存储的时候分成不同的数据目录进行存储。如果说是单分区表，那么在表的目录下就只有一级子目录，如果说是多分区表，那么在表的目录下有多少分区就有多少级子目录。不管是单分区表，还是多分区表，在表的目录下，和非最终分区目录下是不能直接存储数据文件的

• 分桶表：原理和HashPartitioner一样，将Hive中的一张表的数据进行归纳分类的时候，归纳分类规则就是HashPartitioner（需要指定分桶字段，指定分成多少桶）

区别

除了存储的格式不同之外，主要是作用：

• 分区表：细化数据管理，缩小MR程序需要扫描的数据量

• 分桶表：提高Join查询的效率，在一份数据会被经常用来做连接查询的时候建立分桶表，分桶字段就是连接字段；提高采样的效率

有了分区表为什么还要分桶

1. 获得更高的查询处理效率。桶为表加上了额外的结构，Hive在处理有些查询时可以利用这个结构

2. 使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多的方便

   分桶是相对分区进行更细粒度的划分。分桶将表或者分区的某列值进行Hash值进行分区

   与分区不同的是，分区依据的不是真实数据表文件中的列，而是我们指定的伪列，但是分桶是依据数据表中真实的列而不是伪列

18、MapJoin的原理

MapJoin通常用于一个很小的表和一个大表进行join的场景，具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，该参数表示小表的总大小，默认值为25000000字节，即25M。

Hive0.7之前，需要使用hint提示 /*+ mapjoin(table) */才会执行MapJoin,否则执行Common Join，但在0.7版本之后，默认自动会转换Map Join，由参数hive.auto.convert.join来控制，默认为true

MapJoin简单说就是在Map阶段将小表数据从 HDFS 上读取到内存中的哈希表中，读完后将内存中的哈希表序列化为哈希表文件，在下一阶段，当 MapReduce 任务启动时，会将这个哈希表文件上传到 Hadoop 分布式缓存中，该缓存会将这些文件发送到每个 Mapper 的本地磁盘上。因此，所有 Mapper 都可以将此持久化的哈希表文件加载回内存，并像之前一样进行 Join。顺序扫描大表完成Join。减少昂贵的shuffle操作及reduce操作

MapJoin分为两个阶段：

• 通过MapReduce Local Task，将小表读入内存，生成HashTableFiles上传至Distributed Cache中，这里会HashTableFiles进行压缩。

• MapReduce Job在Map阶段，每个Mapper从Distributed Cache读取HashTableFiles到内存中，顺序扫描大表，在Map阶段直接进行Join，将数据传递给下一个MapReduce任务

19、在 hive 的 row_number 中 distribute by 和 partition by 的区别

row_number() over( partition by 分组的字段 order by 排序的字段) as rank(rank 可随意定义 表示排序的标识)

row_number() over( distribute by 分组的字段 sort by 排序的字段) as rank(rank 可随意定义 表示排序的标识)

partition by 只能和 order by 组合使用

distribute by 只能和 sort by 使用

20、Hive开发中遇到的问题

SQL优化：where条件在map端执行而不是在reduce端执行

21、什么时候使用内部表,什么时候使用外部表

hive内部表和外部表的区别

内部表：加载数据到hive所在的hdfs目录，删除时，元数据和数据文件都删除

外部表：不加载数据到hive所在的hdfs目录，删除时，只删除表结构。

这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据

什么时候使用内部表,什么时候使用外部表

• 每天采集的ng日志和埋点日志,在存储的时候建议使用外部表，因为日志数据是采集程序实时采集进来的，一旦被误删，恢复起来非常麻烦。而且外部表方便数据的共享。

• 抽取过来的业务数据，其实用外部表或者内部表问题都不大，就算被误删，恢复起来也是很快的，如果需要对数据内容和元数据进行紧凑的管理, 那还是建议使用内部表

• 在做统计分析时候用到的中间表，结果表可以使用内部表，因为这些数据不需要共享，使用内部表更为合适。并且很多时候结果分区表我们只需要保留最近3天的数据，用外部表的时候删除分区时无法删除数据

22、hive 都有哪些函数，你平常工作中用到哪些

1.数学函数

- round(double d)
- round(double d,int n)
- floor(double d)
- ceil(double d)
- ceiling(double d)
- rand() 
- rand(int seed)
- exp(double d)
- ln(double d)
- log10(double d)
- log2(double d)
- log(double base,double d)
- pow(double d,double p)
- power(double d,double p)
- sqrt(double d)
- hex(bigint i)
- hex(string str)
- abs(double d)
- PI()

2.集合函数

- size(Map<K.V>)
- map_keys(Map<K.V>)
- map_values(Map<K.V>)
- array_contains(Array<T>, value)
- sort_array(Array<T>)

3.类型转换函数

- cast(expr as <type>)

4.日期函数

- date_add
- date_sub
- next_day
- last_day
- from_unixtime(bigint unixtime, string format)
- to_date(string timestamp)
- year(string date)
- month(string date)
- hour(string date)
- weekofyear(string date)
- datediff(string enddate, string startdate)
- add_months(string start_date, int num_months)
- date_format(date/timestamp/string ts, string fmt)

5.条件函数

- if(boolean testCondition, T valueTrue, T valueFalseOrNull)
- nvl(T value, T default_value)
- COALESCE(T v1, T v2, ...)
- CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
- isnull( a )
- isnotnull ( a )

6.字符函数

- concat(string|binary A, string|binary B...)
- concat_ws(string SEP, string A, string B...)
- length(string A)
- lower(string A) lcase(string A)
- parse_url(string urlString, string partToExtract [, string keyToExtract])
- regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
- reverse(string A)
- split(string str, string pat)
- substr(string|binary A, int start) substring(string|binary A, int start)

7.聚合函数

- count(*)
- count(expr)
- count(distinct expr[,expr_.])
- sum(col)
- sum(distinct col)
- avg(col)
- avg(distinct col)
- min(col)
- max(col)

8.表生成函数

- explode(ARRAY array)
- explode(MAP map)
- explode(ARRAY<TYPE> a)
- json_tuple(STRING jsonStr,p1p2,…,pn)-
- parse_url_tuple(url,partname1,partname2,…,partnameN)

 

23、手写 sql，连续活跃用户

• 先把数据按照用户ID分组，根据登录日期排序

select user_id
       ,login_date
       ,row_number() over(partition by user_id order by login_date asc) as rn 
from
       wedw_dw.t_login_info

+----------+-------------+-----+--+
| user_id  | login_date  | rn  |
+----------+-------------+-----+--+
| user01   | 2018-02-28  | 1   |
| user01   | 2018-03-01  | 2   |
| user01   | 2018-03-02  | 3   |
| user01   | 2018-03-04  | 4   |
| user01   | 2018-03-05  | 5   |
| user01   | 2018-03-06  | 6   |
| user01   | 2018-03-07  | 7   |
| user02   | 2018-03-01  | 1   |
| user02   | 2018-03-02  | 2   |
| user02   | 2018-03-03  | 3   |
| user02   | 2018-03-06  | 4   |
+----------+-------------+-----+--+

• 用登录用户日期减去排序数字rn，得到的差值日期如果是相等的，则说明这几天是连续的

select
     t1.user_id
    ,t1.login_date
    ,date_sub(t1.login_date,rn) as date_diff
from 
(
  select
         user_id
         ,login_date
         ,row_number() over(partition by user_id order by login_date asc) as rn 
  from
       wedw_dw.t_login_info
) t1;

+----------+-------------+-------------+--+
| user_id  | login_date  |  date_diff  |
+----------+-------------+-------------+--+
| user01   | 2018-02-28  | 2018-02-27  |
| user01   | 2018-03-01  | 2018-02-27  |
| user01   | 2018-03-02  | 2018-02-27  |
| user01   | 2018-03-04  | 2018-02-28  |
| user01   | 2018-03-05  | 2018-02-28  |
| user01   | 2018-03-06  | 2018-02-28  |
| user01   | 2018-03-07  | 2018-02-28  |
| user02   | 2018-03-01  | 2018-02-28  |
| user02   | 2018-03-02  | 2018-02-28  |
| user02   | 2018-03-03  | 2018-02-28  |
| user02   | 2018-03-06  | 2018-03-02  |
+----------+-------------+-------------+--+

• 根据user_id和日期差date_diff分组，最小登录日期即为连续登录的开始日期start_date，最大登录日期即为结束日期end_date，登录次数即为分组后的count(1)

select
       t2.user_id         as user_id
      ,count(1)           as times
      ,min(t2.login_date) as start_date
      ,max(t2.login_date) as end_date
from
(
    select
           t1.user_id
          ,t1.login_date
          ,date_sub(t1.login_date,rn) as date_diff
    from
    (
        select
               user_id
              ,login_date
              ,row_number() over(partition by user_id order by login_date asc) as rn 
         from
              wedw_dw.t_login_info
    ) t1
) t2
group by 
 t2.user_id
,t2.date_diff
having times >= 3
;

+----------+--------+-------------+-------------+--+
| user_id  | times  | start_date  |  end_date   |
+----------+--------+-------------+-------------+--+
| user01   | 3      | 2018-02-28   | 2018-03-02  |
| user01    | 4      | 2018-03-04  | 2018-03-07  |
| user02   | 3      | 2018-03-01   | 2018-03-03  |
+----------+--------+-------------+-------------+--+

 

24、left semi join 和 left join 区别

left join：两个表的全部字段均会展示出来

left semi join：仅展示A表字段，因为left semi join只传递表的join key给Map阶段

in：效果与left semi join一致

inner join：仅展示A表数据，但不会对结果去重

25、group by 为什么要排序

在Hive中，当使用GROUP BY语句对数据进行分组时，数据会按照GROUP BY的字段进行排序。这是因为排序是为了确保相同的键值（GROUP BY字段）的数据被连续地放在一起，以便进行聚合操作。

以下是GROUP BY需要排序的几个原因：

1. 分组聚合：GROUP BY语句通常与聚合函数（如SUM、COUNT、AVG等）一起使用，用于对数据进行分组并计算聚合结果。为了正确计算每个组的结果，数据需要按照GROUP BY字段进行排序，以确保相同的键值的数据在一起。

2. 归并操作：在Hive中，GROUP BY操作通常是通过MapReduce或Tez任务来执行的。在这些任务中，数据需要在不同的节点之间进行归并操作，以将相同键值的数据合并在一起。为了有效地进行归并操作，数据需要按照GROUP BY字段进行排序。

3. 优化查询性能：排序可以提高查询性能。当数据按照GROUP BY字段排序后，相同的键值的数据会被连续地存储在磁盘上，这样可以减少磁盘I/O的次数，提高查询的效率。

需要注意的是，Hive中的GROUP BY默认是全局排序，即所有的数据都会被收集到一个节点上进行排序。这可能会导致性能问题，特别是当数据量非常大时。为了解决这个问题，可以使用DISTRIBUTE BY和SORT BY语句来实现分布式排序，将排序的负载分散到不同的节点上，以提高性能。

总而言之，GROUP BY需要排序是为了进行分组聚合操作、归并操作和优化查询性能。排序确保相同的键值的数据被连续地放在一起，以便进行聚合操作和归并操作，并提高查询性能。

26、说说印象最深的一次优化场景，hive 常见的优化思路

印象最深的一次优化场景是在一个大型数据仓库项目中，需要对Hive中的表进行优化以提高查询性能。以下是一些常见的Hive优化思路：

1. 压缩数据：使用压缩算法（如Snappy、Gzip、LZO等）可以减小数据的存储空间，从而减少磁盘IO操作，提高查询性能。

2. 使用列式存储格式：Hive支持多种列式存储格式（如ORC、Parquet），这些格式可以提供更高的压缩比和更快的查询速度，因为它们只读取所需的列，而不是整个行。

3. 分桶：将表按照某个列的哈希值进行分桶，可以将数据均匀地分布在多个文件中，从而提高查询的并行度和性能。

4. 使用索引：Hive支持基于B树的索引，通过在关键列上创建索引可以加快查询速度。但需要注意的是，索引会增加写入操作的开销，因此需要权衡索引对查询性能的提升和写入性能的影响。

5. 合理设置分区：将表按照某个列的值进行分区可以减少查询的数据量，提高查询性能。同时，可以将常用的查询条件作为分区键，以进一步提高查询效率。

6. 数据倾斜处理：当某个列的值分布不均匀时，可能会导致查询性能下降。可以通过对数据进行重新分区、使用随机前缀等方式来解决数据倾斜的问题。

7. 使用适当的硬件资源：合理配置Hive的资源参数，如内存、CPU等，以及选择性能较好的硬件设备，可以提高查询的执行效率。

这些只是一些常见的Hive优化思路，实际的优化策略还需要根据具体的场景和需求来确定。在实际应用中，可以通过不断的实验和调整来找到最适合的优化方案。

27、Hive的执行引擎，Spark和MR的区别

引擎是MR，基于磁盘进行计算，速度比较慢

引擎是Spark，基于内存进行计算，速度比较快

对于超大数据量的话，HiveOnSpark可能会有内存溢出的情况

28、Hive的Join底层MR是如何实现的

Hive中的Join可分为两种情况

• Common Join（Reduce阶段完成join）

• Map Join（Map阶段完成join）

第一种情况：Common Join（Reduce阶段完成join）

如果没开启hive.auto.convert.join=true或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join,在Reduce阶段完成join。并且整个过程包含Map、Shuffle、Reduce阶段。

Map阶段

读取表的数据，Map输出时候以 Join on 条件中的列为key，如果Join有多个关联键，则以这些关联键的组合作为key; Map输出的 value 为 join 之后需要输出或者作为条件的列；同时在value中还会包含表的 Tag 信息，用于标明此value对应的表；按照key进行排序

Shuffle阶段

根据key取哈希值,并将key/value按照哈希值分发到不同的reduce中

Reduce阶段

根据key的值完成join操作，并且通过Tag来识别不同表中的数据。在合并过程中，把表编号扔掉

举例

drop table if exists wedw_dwd.user_info_df;
 CREATE TABLE wedw_dwd.user_info_df(
  user_id    string  COMMENT '用户id',
  user_name  string  COMMENT '用户姓名'
 )
row format delimited fields terminated by '\t'
 STORED AS textfile
 ;
 
 +----------+------------+--+
| user_id  | user_name  |
+----------+------------+--+
| 1        | 小红         |
| 2        | 小明         |
| 3        | 小花         |
+----------+------------+--+

 drop table if exists wedw_dwd.order_info_df;
 CREATE TABLE wedw_dwd.order_info_df(
  user_id      string  COMMENT '用户id',
  course_name  string  COMMENT '课程名称'
 )
row format delimited fields terminated by '\t'
 STORED AS textfile
 ;
 
 +----------+--------------+--+
| user_id  | course_name  |
+----------+--------------+--+
| 1        | spark        |
| 2        | flink        |
| 3        | java         |
+----------+--------------+--+

select
 t1.user_id
,t1.user_name
,t2.course_name
from
wedw_dwd.user_info_df t1
join wedw_dwd.order_info_df t2
on t1.user_id = t2.user_id
;
+----------+------------+--------------+--+
| user_id  | user_name  | course_name  |
+----------+------------+--------------+--+
| 1        | 小红         | spark        |
| 2        | 小明         | flink        |
| 3        | 小花         | java         |
+----------+------------+--------------+--+

图解：(在合并过程中，把表编号扔掉)

 

第二种情况：Map Join（Map阶段完成join）

在Hive调优里面，经常会问到一个很小的表和一个大表进行join，如何优化。

Shuffle 阶段代价非常昂贵，因为它需要排序和合并。减少 Shuffle 和 Reduce 阶段的代价可以提高任务性能。

MapJoin通常用于一个很小的表和一个大表进行join的场景，具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，该参数表示小表的总大小，默认值为25000000字节，即25M。

Hive0.7之前，需要使用hint提示 /*+ mapjoin(table) */才会执行MapJoin,否则执行Common Join，但在0.7版本之后，默认自动会转换Map Join，由参数hive.auto.convert.join来控制，默认为true.

假设a表为一张大表，b为小表，并且hive.auto.convert.join=true,那么Hive在执行时候会自动转化为MapJoin。

MapJoin简单说就是在Map阶段将小表数据从 HDFS 上读取到内存中的哈希表中，读完后将内存中的哈希表序列化为哈希表文件，在下一阶段，当 MapReduce 任务启动时，会将这个哈希表文件上传到 Hadoop 分布式缓存中，该缓存会将这些文件发送到每个 Mapper 的本地磁盘上。

因此，所有 Mapper 都可以将此持久化的哈希表文件加载回内存，并像之前一样进行 Join。顺序扫描大表完成Join。减少昂贵的shuffle操作及reduce操作

MapJoin分为两个阶段：

• 通过MapReduce Local Task，将小表读入内存，生成HashTableFiles上传至Distributed Cache中，这里会HashTableFiles进行压缩。

• MapReduce Job在Map阶段，每个Mapper从Distributed Cache读取HashTableFiles到内存中，顺序扫描大表，在Map阶段直接进行Join，将数据传递给下一个MapReduce任务

 

 

29、建好了外部表，用什么语句把数据文件加载到表中

• 从本地导入：load data lacal inpath /home/data.log into table ods.test

• 从HDFS导入：load data inpath /user/hive/warehouse/a.txt into ods.test

 

30、Hive的执行流程

1. 用户提交查询等任务给Driver

2. 编译器获得该用户的任务Plan

3. 编译器Compiler根据用户任务去MetStore中获取需要的Hive的元数据信息

4. 编译器Compiler得到元数据信息，对任务进行编译，先将HiveQL转换为抽象语法树，然后将抽象语法树转换成查询块，将查询块转化为逻辑的查询计划，重写逻辑查询计划，将逻辑计划转化为物理的计划（MapReduce），最后选择最佳的策略

5. 将最终的计划提交给Driver

6. Driver将计划Plan转交给ExecutionEngine去执行，获取元数据信息，提交给JobTracker或者SourceManager执行该任务，任务会直接读区HDFS中文件进行相应的操作

7. 获取执行的结果

8. 取得并返回执行结果

 

31、SQL语句的执行顺序

FROM—Where—Group By—Having—Select—Order By—Limit

32、On和Where的区别

• 不考虑where条件下,left join 会把左表所有数据查询出来，on及其后面的条件仅仅会影响右表的数据(符合就显示,不符合全部为null)

• 在匹配阶段，where子句的条件都不会被使用，仅在匹配阶段完成以后，where子句条件才会被使用，它将从匹配阶段产生的数据中检索过滤

• 所以左连接关注的是左边的主表数据，不应该把on后面的从表中的条件加到where后，这样会影响原有主表中的数据

• where后面：是先连接然生成临时查询结果，然后再筛选

  on后面：先根据条件过滤筛选，再连接生成临时查询结果

有谓词下推的情况下查询结果没有区别。没有谓词下推的情况下，在执行计划中会对on的条件内的子查询先进行过滤，最后再将结果根据where条件过滤，对于这种情况，应当尽可能的将子查询以及管理表的数据量减少以提升查询性能，避免笛卡尔积等情况。

从功能上来区分，on的结果是临时表，where是对on的临时结果做过滤

33、Hive中导入数据的4中方式

• 从本地导入：load data local inpath /home/data.log into table ods.test

• 从HDFS导入：load data inpath /user/hive/warehouse/a.txt into ods.test

• 查询导入：create table tmp_test as select * from ods.test

• 查询结果导入：insert into table tmp.test select * from ods.test

 

35、Hive的执行计划（explain）

HIVE提供了EXPLAIN命令来展示一个查询的执行计划,这个执行计划对于我们了解底层原理，hive 调优，排查数据倾斜等很有帮助

语法如下：

EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

explain 后面可以跟以下可选参数，注意：这几个可选参数不是 hive 每个版本都支持的

1. EXTENDED：加上 extended 可以输出有关计划的额外信息。这通常是物理信息，例如文件名。这些额外信息对我们用处不大

2. CBO：输出由Calcite优化器生成的计划。CBO 从 hive 4.0.0 版本开始支持

3. AST：输出查询的抽象语法树。AST 在hive 2.1.0 版本删除了，存在bug，转储AST可能会导致OOM错误，将在4.0.0版本修复

4. DEPENDENCY：dependency在EXPLAIN语句中使用会产生有关计划中输入的额外信息。它显示了输入的各种属性

5. AUTHORIZATION：显示所有的实体需要被授权执行（如果存在）的查询和授权失败

6. LOCKS：这对于了解系统将获得哪些锁以运行指定的查询很有用。LOCKS 从 hive 3.2.0 开始支持

7. VECTORIZATION：将详细信息添加到EXPLAIN输出中，以显示为什么未对Map和Reduce进行矢量化。从 Hive 2.3.0 开始支持

8. ANALYZE：用实际的行数注释计划。从 Hive 2.2.0 开始支持

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

explain select sum(id) from test1;

得到如下结果：

STAGE DEPENDENCIES: --各个Stage之间的依赖性
  Stage-1 is a root stage       --Stage-1 是根stage，即开始的stage
  Stage-0 depends on stages: Stage-1 --Stage-0 依赖Stage-1，Stage-1 执行完成之后执行Stage-0

STAGE PLANS: --各个Stage的执行计划
  Stage: Stage-1
    Map Reduce
      Map Operator Tree: --Map端的执行计划树
          TableScan    --表扫描操作
            alias: test1 --alias: 表名称
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE --Statistics: 表统计信息，包含表中数据条数，数据大小等
            Select Operator --选取操作
              expressions: id (type: int)  --expressions:需要的字段名称及字段类型
              outputColumnNames: id     --outputColumnNames: 输出的列名称
              Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE --Statistics: 表统计信息，包含表中数据条数，数据大小等
              Group By Operator --分组聚合操作
                aggregations: sum(id) -- aggregations: 显示聚合函数信息
                mode: hash      -- mode: 聚合模式，值有hash（随机聚合，即分区方式为hash）；partitial（局部集合）；final（最终聚合）
                outputColumnNames: _col0 --outputColumnNames: 聚合之后输出列名
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE --Statistics: 表统计信息，包含分组聚合之后的数据条数，数据大小等
                Reduce Output Operator --输出到Reduce操作
                  sort order:  --sort order: 值为空 不排序；值为“+” 正序排序；值为“-” 倒序排序；值为“+-”  排序的列为两列，第一列为正序，第二列为倒序
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col0 (type: bigint)
      Reduce Operator Tree: --Reduce端的执行计划树
        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  --compressed: 是否压缩
            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 --limit: 值为 -1 表示不限制条数，其他值为限制的条数
      Processor Tree:
        ListSink

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

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

1. stage dependencies：各个stage之间的依赖性

2. stage plan：各个stage的执行计划

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

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

1. Map Operator Tree：MAP端的执行计划树

2. 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 表示不限制条数，其他值为限制的条数

 

实践

1. join 语句会过滤 null 的值吗？

现在，我们在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 是不会自动过滤的，大家可以自行尝试下。

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

看下面这条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)  --按照id进行分组
           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: + --按照in进行正序排序
             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 字段进行正序排序的。

3. 哪条sql执行效率高呢？

观察两条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语句执行效率是一样的

Map Join执行计划

执行计划

在 Map 操作树我们可以看到 Map Side Join Operator 关键字，就说明 join 是 mapjoin

hive> explain select a.* from passwords a,passwords3 b where a.col0=b.col0;
OK
STAGE DEPENDENCIES:
 Stage-4 is a root stage
 Stage-3 depends on stages: Stage-4
 Stage-0 is a root stage
STAGE PLANS:
 Stage: Stage-4
   Map Reduce Local Work
     Alias -> Map Local Tables:
       b
         Fetch Operator
           limit: -1
     Alias -> Map Local Operator Tree:
       b
         TableScan
           alias: b
           Statistics: Num rows: 1 Data size: 31 Basic stats: COMPLETE Column stats: NONE
           HashTable Sink Operator
             condition expressions:
               0 {col0} {col1} {col2} {col3} {col4} {col5} {col6}
               1 {col0}
             keys:
               0 col0 (type: string)
               1 col0 (type: string)
 Stage: Stage-3
   Map Reduce
     Map Operator Tree:
         TableScan
           alias: a
           Statistics: Num rows: 9963904 Data size: 477218560 Basic stats: COMPLETE Column stats: NONE
           Map Join Operator
             condition map:
                  Inner Join 0 to 1
             condition expressions:
               0 {col0} {col1} {col2} {col3} {col4} {col5} {col6}
               1 {col0}
keys:
               0 col0 (type: string)
               1 col0 (type: string)
             outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col9
             Statistics: Num rows: 10960295 Data size: 524940416 Basic stats: COMPLETE Column stats: NONE
             Filter Operator
               predicate: (_col0 = _col9) (type: boolean)
               Statistics: Num rows: 5480147 Data size: 262470184 Basic stats: COMPLETE Column stats: NONE
               Select Operator
                 expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string)
                 outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
                 Statistics: Num rows: 5480147 Data size: 262470184 Basic stats: COMPLETE Column stats: NONE
                 File Output Operator
                   compressed: false
                   Statistics: Num rows: 5480147 Data size: 262470184 Basic stats: COMPLETE Column stats: NONE
                   table:
                       input format: org.apache.hadoop.mapred.TextInputFormat
                       output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                       serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
     Local Work:
       Map Reduce Local Work
 Stage: Stage-0
   Fetch Operator
     limit: -1

上述执行计划中:

1. stage5 有一个Map Local Tables 和 HashTable Sink Operator

2. stage3 有一个Map Join Operator

两个stage结合起来完成了mapjoin 这样一个过程

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

1. 使用explain查看执行计划；

2. 查看YARN提供的日志

 

36、Hive SQL优化

首先介绍下什么是HIve？

1.基于Hadoop的开源的数据仓库工具，用于处理海量海量结构化数据。2.Hive把HDFS中的结构化的数据文件映射成数据表。3.Hive通过HiveSQL进行解析和转换，最终映射成一系列在hadoop上运行的mapreduce任务，通过执行这些任务完成分析和处理。4.HiveSql和Mysql一样，都遵循着SQL的标准，因此它们很多语句都是一样的。

一、先复习下SQL的语句的结构

SELECT  *     （必须）
FROM      表（数据源）
WHERE     条件
GROUP BY       字段
HAVING     条件
ORDER BY       字段(排序 默认 ASC)
LIMIT    限制数据条数

注意：sql对大小写一般不敏感，一般命令用大写，表名，字段用小写

二、 SQL语句的执行顺序

第一步：确定数据源
FROM
JOIN
ON 
第二步：过滤数据
WHERE 
GROUP BY (开始使用SELECT 中的别名，后面的语句中都可以使用)
avg，sum.......
HAVING
第三步：查询数据
SELECT
第四步：显示数据
DISTINCT
ORDER BY 
LIMIT

 

三、优化技巧

技巧一：列裁剪和分区裁剪

1.列裁剪：

列裁剪就是在查询时只读取需要的列。当列很多或者数据量很大时，如果select 所有的列或者不指定分区，导致的全表扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的配置项是hive.optimize.cp，默认是true

2.分区裁剪：

分区裁剪就是在查询时只读需要的分区。Hive中与分区裁剪优化相关的则是hive.optimize.pruner，默认是true。

技巧二：排序技巧 sort by 代替order by

HiveSQL中的order by与其他SQL语言中的功能一样，就是将结果按某个字段全局排序，这会导致所有map端数据都进入一个reduce中，在数据量大时可能会长时间计算不完。

如果使用sort by，那么就会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reduce的key，往往还要配合distribute by一同使用。如果不加distribute by的话，map端数据就会随机分配给reducer。

-- 未优化写法
select a,b,c
from table
where xxx
order by a
limit 10;
-- 优化写法
select a,b,c
from table
where xxx
distribute by a
sort by a
limit 10;

技巧三：去重技巧 --用group by 来代替distinct

-- 取出user_trade表中全部支付用户
-- 原有写法
SELECT distinct user_name
FROM user_trade
WHERE dt>'0';
--测试时长 43 s
-- 优化写法
SELECT user_name
FROM user_trade
WHERE dt>'0'
GROUP BY user_name;
--测试时长 29 s

注意：在极大的数据量(且很多重复值)时，可以先group by去重，再count()计数，效率高于直接count(distinct **)

技巧四：聚合技巧–grouping sets 、cube、rollup

1.grouping sets

想知道用户的性别分布、城市分布、等级分布？通常写法：

性别分布
select sex,
count(distinct user_id)
from user_info
group by sex;

城市分布
select city,
count(distinct user_id)
from user_info
group by city;

等级分布
select level,
count(distinct user_id)
from user_info
group by level;

通常要写三词sql语句 优化之后

select sex,city,level
         count(distinct user_id)
from user_info
group by sex,city,level
grouping sets (sex,city,level)

注意：grouping sets 指定分组的维度 聚合结果均在同一列，分类字段用不同列来区分

2.cube ：根据group by维度的所有组合进行聚合。

-- 性别、城市、等级的各种组合的用户分布
SELECT sex,
   city,
   level,
   count(distinct user_id)
FROM user_info
GROUP BY sex,city,level
GROUPING SETS (sex,city,level,(sex,city),
(sex,level),(city,level),(sex,city,level));

优化之后：

select sex
    city,
       level,
       count(distinct user_id)
FROM user_info
GROUP BY sex,city,level
with cube;

3.rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。

计算出，每个月的支付金额，以及每年的总支付金额

一般写法：

SELECT a.dt,
   sum(a.year_amount),
   sum(a.month_amount)
FROM
  (SELECT substr(dt,1,4) as dt,
      sum(pay_amount) year_amount,
      0 as month_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY substr(dt,1,4)
   UNION ALL
   SELECT substr(dt,1,7) as dt,
      0 as year_amount,
      sum(pay_amount) as month_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY substr(dt,1,7)
  )a
GROUP BY a.dt;

优化写法

SELECT year(dt) as year,
   month(dt) as month,
   sum(pay_amount)
FROM user_trade
WHERE dt>'0'
GROUP BY year(dt),
    month(dt)
with rollup;

技巧五：换个思路解题

条条大路通罗马，写SQL亦是如此，能达到同样效果的SQL有很多种，要学会思路转换，灵活应用。

--在2017年和2018年都购买的用户--
SELECT a.user_name 
FROM
  (SELECT distinct user_name
   FROM user_trade
   WHERE year(dt)=2017)a
  JOIN
  (SELECT distinct user_name
   FROM user_trade
   WHERE year(dt)=2018)b on
a.user_name=b.user_name;

– 方式一

SELECT a.user_name
FROM
 (SELECT user_name,
      count(distinct year(dt)) as
year_num
  FROM user_trade
  WHERE year(dt) in (2017,2018)
  GROUP BY user_name)a
  WHERE a.year_num=2;

– 方式二

SELECT user_name,
   count(distinct year(dt)) as year_num
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY user_name
having count(distinct year(dt))=2;

技巧六：union all时可以开启并发执行

Hive中互相没有依赖关系的job间是可以并行执行的，最典型的就是多个子查询union all。在集群资源相对充足的情况下，可以开启并行执行。

参数设置：set hive.exec.parallel=true;

– 每个用户的支付和退款金额汇总

SELECT a.user_name,
   sum(a.pay_amount),
   sum(a.refund_amount)
FROM
 ( SELECT user_name,
      sum(pay_amount) as pay_amount,
      0 as refund_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY user_name
  UNION ALL
   SELECT user_name,
      0 as pay_amount,
      sum(refund_amount) as
refund_amount
   FROM user_refund
   WHERE dt>'0'
   GROUP BY user_name
 )a
GROUP BY a.user_name;

时间对比：未开并发执行 103 s 开启并发执行 64 s

技巧七：表连接优化

1.小表在前，大表在后

Hive假定查询中最后的一个表是大表，它会将其它表缓存起来，然后扫描最后那个表。

2.使用相同的连接键

当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

3.尽早的过滤数据

减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的字段。

技巧八：遵循严格模式

所谓严格模式，就是强制不允许用户执行3种有风险的HiveSQL语句，一旦执行会直接报错。1.查询分区表时不限定分区列的语句。2.两表join产生了笛卡尔积的语句。3.要order by来排序但没有指定limit的语句。

要开启严格模式，需要将参数hive.mapred.mode设为strict。

37、小文件过多，如何处理？

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

使用方法：

-- 对于非分区表
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

2. 调整参数减少Map数量

• 设置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;

 

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 中，防止出现有的文件过大或过小

 

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

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. 使用Hive自带的contatenate命令，自动合并小文件

使用方法：

-- 对于非分区表
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

2. 调整参数减少Map数量

• 设置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;

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 中，防止出现有的文件过大或过小

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

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

38、说说hive内部表和外部表的区别

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

区别:

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

内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse）

外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上       的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存    放在这里）；删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除。

39、Hive有索引吗

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

索引适用的场景：

适用于不更新的静态字段。以免总是重建索引数据。

每次建立、更新数据后，都要重建索， 用于构建索引表。

40、运维如何对hive进行调度

将hive的sql定义在脚本当中；使用azkaban或者oozie进行任务的调度；监控任务调度页面。

41、ORC、Parquet等列式存储的优点

• ORC:ORC文件是自描述的，它的元数据使用Protocol Buffers序列化，文件中的数据尽可能的压缩以降低存储空间的消耗；以二进制方式存储，不可以直接读取；自解析，包含许多元数据，这些元数据都是同构ProtoBuffer进行序列化的；会尽可能合并多个离散的区间尽可能的减少I/O次数；在新版本的ORC中也加入了对Bloom Filter的支持，它可以进一 步提升谓词下推的效率，在Hive 1.2.0版本以后也加入了对此的支 持。

• Parquet:Parquet支持嵌套的数据模型，类似于Protocol Buffers，每一个数据模型的schema包含多个字段，每一个字段有三个属性：重复次数、数据类型和字段名；Parquet中没有Map、Array这样的复杂数据结构，但是可以通过repeated和group组合来实现；通过Striping/Assembly算法，parquet可以使用较少的存储空间表示复杂的嵌套格式，并且通常Repetition level和Definition level都是较小的整数值，可以通过RLE算法对其进行压缩，进一步降低存储空间；Parquet文件以二进制方式存储，不可以直接读取和修改，Parquet文件是自解析的，文件中包括该文件的数据和元数据。

42、为什么要对数据仓库分层

用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会   存在大量冗余的数据。如果不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。通过数据分层管理可以简化数据清洗的过程，因为把原来一步的工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误的时候，往往我们只需要局部调整某个步骤即可。

43、使用过Hive解析JSON串吗

Hive处理json数据总体来说有两个方向的路走：a.将json以字符串的方式整个入Hive表，然后通过使用UDF函数解析已经导入到hive中的数据，比如使用LATERAL VIEW json_tuple的方法，获取所需要的列名。b.在导入之前将json拆成各个字段，导入Hive表的数据是已经解析过的。这将需要使用第三方的 SerDe。

44、sort by 和 order by 的区别

order by 会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

sort by不是全局排序，其在数据进入reducer前完成排序. 因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1， 则sort by只保证每个reducer的输出有序，不保证全局有序。

45、数据倾斜怎么解决

• 空值引发的数据倾斜

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

SELECT *
FROM log a
 JOIN users b
 ON a.user_id IS NOT NULL
   AND a.user_id = b.user_id
UNION ALL
SELECT *
FROM log a
WHERE auser_id IS NULL;

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

SELECT *
FROM log a
  LEFT JOIN users b ON CASE
    WHEN a.user_id IS NOT NULL THEN concat('hive_', rand())
    ELSE a.user_id
  END = b.user_id;

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

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

SELECT *
FROM log a
  LEFT JOIN logs b ON a.usr_id = CAST(b.user_id AS string); 

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

  解决方案：这种数据倾斜问题没有什么好的解决方案，只能将使用GZIP压缩等不支持文件分割的文件转为bzip和zip等支持文件分割的压缩方式。所以，我们在对文件进行压缩时，为避免因不可拆分大文件而引发数据读取的倾斜，在数据压缩的时候可以采用bzip2和Zip等支持文件分割的压缩算法。

• 数据膨胀引发的数据倾斜

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

• 表连接时引发的数据倾斜

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

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

  解决方案：这类问题最直接的方式就是调整reduce所执行的内存大小。调整reduce的内存大小使用mapreduce.reduce.memory.mb这个配置。

46、Hive 小文件过多怎么解决

使用 hive 自带的 concatenate 命令，自动合并小文件 调整参数减少Map数量 减少Reduce的数量 使用hadoop的archive将小文件归档

47、Hive优化有哪些

数据存储及压缩 通过调参优化 有效地减小数据集将大表拆分成子表；结合使用外部表和分区表 SQL优化

48、Hive的两张表关联，使用MapReduce怎么实现？

如果其中有一张表为小表，直接使用map端join的方式（map端加载小表）进行聚合。

如果两张都是大表，那么采用联合key，联合key的第一个组成部分是join on中的公共字段，第二部分是一个flag，0代表表A，1代表表B，由此让Reduce区分客户信息和订单信息；在Mapper中同时处理两张表的信息，将join on公共字段相同的数据划分到同一个分区中，进而传递到一个Reduce中，然后在Reduce中实现聚合。

49、请谈一下Hive的特点，Hive和RDBMS有什么异同？

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析，但是Hive不支持实时查询。

50、请说明hive中 Sort By，Order By，Cluster By，Distrbute By各代表什么意思？

Order by：会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）。只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

Sort by：不是全局排序，其在数据进入reducer前完成排序。1

Distribute by：按照指定的字段对数据进行划分输出到不同的reduce中。

Cluster by：除了具有 distribute by 的功能外还兼具 sort by 的功能。

51、写出Hive中split、coalesce及collect_list函数的用法（可举例）？

split将字符串转化为数组，即：split('a,b,c,d' , ',') ==> ["a","b","c","d"]。

coalesce(T v1, T v2, …) 返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL。

collect_list列出该字段所有的值，不去重 => select collect_list(id) from table。

52、 Hive有哪些方式保存元数据，各有哪些特点？

Hive支持三种不同的元存储服务器，分别为：内嵌式元存储服务器、本地元存储服务器、远程元存储服务器，每种存储方式使用不同的配置参数。

内嵌式元存储主要用于单元测试，在该模式下每次只有一个进程可以连接到元存储，Derby是内嵌式元存储的默认数据库。

在本地模式下，每个Hive客户端都会打开到数据存储的连接并在该连接上请求SQL查询。

在远程模式下，所有的Hive客户端都将打开一个到元数据服务器的连接，该服务器依次查询元数据，元数据服务器和客户端之间使用Thrift协议通信。

53、Hive内部表和外部表的区别？

创建表时：创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。

删除表时：在删除表的时候，内部表的元数据和数据会被一起删除， 而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。

54、Hive的函数：UDF、UDAF、UDTF的区别？

UDF：单行进入，单行输出

UDAF：多行进入，单行输出

UDTF：单行输入，多行输出

55、所有的Hive任务都会有MapReduce的执行吗？

不是，从Hive0.10.0版本开始，对于简单的不需要聚合的类似SELECT from

LIMIT n语句，不需要起MapReduce job，直接通过Fetch task获取数据。

56、说说对Hive桶表的理解？

桶表是对数据某个字段进行哈希取值，然后放到不同文件中存储。

数据加载到桶表时，会对字段取hash值，然后与桶的数量取模。把数据放到对应的文件中。物理上，每个桶就是表(或分区）目录里的一个文件，一个作业产生的桶(输出文件)和reduce任务个数相同。

桶表专门用于抽样查询，是很专业性的，不是日常用来存储数据的表，需要抽样查询时，才创建和使用桶表。

57、Hive底层与数据库交互原理？

Hive 的查询功能是由 HDFS 和 MapReduce结合起来实现的，对于大规模数据查询还是不建议在 hive 中，因为过大数据量会造成查询十分缓慢。Hive 与 MySQL的关系：只是借用 MySQL来存储 hive 中的表的元数据信息，称为 metastore（元数据信息）。

58、Hive本地模式

大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务时消耗可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。