hive中用临时表的好处 hive临时表储存位置

hive

• 1. hive 有哪些方式保存元数据，各有哪些特点？
• 2. hive内部表和外部表的区别
• 3.生产环境中为什么建议使用外部表？什么时候使用内部表？什么时候使用外部表？
• 4.你们数据库怎么导入hive 的,有没有出现问题
• 5.简述Hive中的虚拟列作用是什么，使用它的注意事项

• 扩展

• 6.hive partition分区
• 7. hive partition什么时候使用手动分区
• 8.hive partition怎么手动分区
• 9.hive partition什么时候使用自动分区
• 10.hive partition怎么自动分区
• 11.如何查看分区
• 12.分桶结构表clustered
• 13.分桶表怎么构建
• 14.分区表和分桶表的区别

• 分区表
• 分桶表

• 15.insert into 和 insert overwrite区别？
• 16.假如一个分区的数据主部错误怎么通过hivesql删除hdfs
• 17.Hive 表关联查询，如何解决数据倾斜的问题？

• 1）倾斜原因：
• 2）解决⽅案

• 18.请谈⼀下 Hive 的特点，Hive 和 RDBMS 有什么异同？（重新整理）
• 19.请说明 hive 中 Sort By，Order By，Cluster By，Distrbute By 各代表什么意思？
• 20.简要描述数据库中的 null，说出 null 在 hive 底层如何存储，并 解释 select a.* from t1 a left outer join t2 b on a.id=b.id where b.id is null; 语句的含义？
• 21.写出 hive 中 split、coalesce 及 collect_list 函数的⽤法（可举 例）？
• 22.Hive 有哪些⽅式保存元数据，各有哪些特点？
• 23.Hive 内部表和外部表的区别？
• 24.Hive 的 HSQL 转换为 MapReduce 的过程？（重新整理）
• 25.Hive 底层与数据库交互原理？
• 26.请把下⾯语句⽤ Hive 实现
• 27.写出将 text.txt ⽂件放⼊ hive 中 test 表‘2016-10-10’ 分区 的语句，test 的分区字段是 l_date
• 28.Hive 如何进⾏权限控制？
• 29.对于 hive，你写过哪些 udf 函数，作⽤是什么？

• 开发过程

• 30.Hive 中的压缩格式 TextFile、SequenceFile、RCfile 、ORCfile 各有什么区别？
• 31.Hive join 过程中⼤表⼩表的放置顺序？
• 32.Hive 的两张表关联，使⽤ MapReduce 怎么实现？
• 33.Hive 中使⽤什么代替 in 查询？
• 34.所有的 Hive 任务都会有 MapReduce 的执⾏吗？
• 35.Hive 的函数：UDF、UDAF、UDTF 的区别？
• 36.说说对 Hive 桶表的理解？
• 37.Hive ⾃定义 UDF 函数的流程?
• 38.Hive 可以像关系型数据库那样建⽴多个库吗？
• 39.Hive 实现统计的查询语句是什么？
• 40.Hive 优化措施

• 1.Fetch 抓取
• 2.本地模式
• 3.表的优化
• 优化措施4.数据倾斜
• 优化措施5.`并⾏执⾏`
• 优化措施6.`严格模式`
• 优化措施7.`JVM 重⽤`
• 化措施8.`推测执⾏`
• 优化措施9.`压缩`
• 优化措施10.EXPLAIN（执⾏计划）

• 41.Hive 数据分析⾯试题

• 1 情景题：分组 TOPN
• 2 情景题：where 与 having
• 3 情景题：数据倾斜
• 4 情景题：分区表

• 42.hive导入导出

• 导入
• 导出

• 43.Hive的存储格式及压缩算法

• 存储格式
• 压缩算法

• 44.Hive中小文件问题

• 产生:
• 影响
• 解决
• map/reduce端的相关参数的设置
• 配置Hive结果合并

• 45.Hive中Join的类型和用法

• 概览:
• left semi join和left join区别
• 实例

• 46.Hive严格模式
• 47.sparkSQL一定比hive快吗，能否想出一种场景，sparkSQL比hive慢(重点)

• sparkSQL之所以快

1. hive 有哪些方式保存元数据，各有哪些特点？

• 解答：

• 1、默认的存储位置:内存数据库derby，安装小，但是数据存在内存，不稳定
• 2、mysql数据库，数据存储模式可以自己设置，持久化好，查看方便。

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

• 解答：
• 主要体现在删除时
• 内部表：加载数据到hive所在的hdfs目录

• 元数据中表的信息会被删除，HDFS中表的目录以及数据也会被删除
• 适用于中间表、结果表（数据恢复起来比较快的）

• 外部表(external关键字)：不加载数据到hive所在的hdfs目录

• 元数据中表的信息会被删除，但是HDFS中表的目录以及数据依旧存在
• 只是删除了HDFS与Hive的表的关联
• 适合应用于多张表共用一份数据的情况下，即共享源数据的时候

3.生产环境中为什么建议使用外部表？什么时候使用内部表？什么时候使用外部表？

• 解答：

• 1、因为外部表不会加载数据到hive，减少数据传输、数据还能共享。
• 2、hive不会修改数据，所以无需担心数据的损坏
• 3、删除表时，只删除表结构、不删除数据。
• 4、ods层的埋点日志和业务抽取数据可以使用外部表，因为被误删除恢复会比较麻烦（实时采集）
• 5、抽取过来处理后的数据恢复起来很快，建议使用内部表（中间表&结果表）

4.你们数据库怎么导入hive 的,有没有出现问题

• 解答：

• 在导入hive的时候，如果数据库中有blob或者text字段，会报错。有个参数limit

5.简述Hive中的虚拟列作用是什么，使用它的注意事项

• 解答：
• 解释:此列在表中并未真正存在
• Hive提供了三个虚拟列：

• INPUT__FILE__NAME

• 每一个map任务对应 输入文件的目录及文件名

• BLOCK__OFFSET__INSIDE__FILE

• 当前map任务处理的数据所对应的偏移量,文件中的块内偏移量

• ROW__OFFSET__INSIDE__BLOCK

• 默认不开启,需要设置参数,文件的行偏移量

• 但ROW__OFFSET__INSIDE__BLOCK默认是不可用的，需要设置hive.exec.rowoffset为true才可以。- 可以用来排查有问题的输入数据。

• INPUT__FILE__NAME, mapper任务的输出文件名。
• BLOCK__OFFSET__INSIDE__FILE, 当前全局文件的偏移量。对于块压缩文件，就是当前块的文件偏移量，即当前块的第一个字节在文件中的偏移量。

hive> SELECT INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, line
> FROM hive_text WHERE line LIKE '%hive%' LIMIT 2;
har://file/user/hive/warehouse/hive_text/folder=docs/
data.har/user/hive/warehouse/hive_text/folder=docs/README.txt  2243
har://file/user/hive/warehouse/hive_text/folder=docs/
data.har/user/hive/warehouse/hive_text/folder=docs/README.txt  3646

扩展

• 文件的行偏移量平时作用不大,但这三个参数在找错误方面很有用
• 已知clickcube_mid表中有一个字段 regioncode , regioncode 描述了 一个ip对应的region信息，这个regioncode 目前使用的是原始值，为日志中直接获取。
• 某一天，由于regioncode 异常，导致spark 进程中断，查找得知是 regioncode 不合理导致，此时我们需要找到错误的regioncode, 可以进行如下的查询：

SELECT 
    INPUT__FILE__NAME,
    BLOCK__OFFSET__INSIDE__FILE, 
    ROW__OFFSET__INSIDE__BLOCK,
     substr(regioncode,0,20) 
FROM clickcube_mid 
WHERE length(regioncode) > 100;

6.hive partition分区

• 功能：将数据按照数据本身分区的规则来存储
• 本质:实现了底层程序的输入的优化

• 通过分区，先对分区过滤，然后再作为输入
• 比先读取所有数据，再过滤，性能要高的多

• 不做分区表：表的最后一级目录就是表的目录
• 做了分区表：表的最后一级目录是分区的目录
• 分区的方式：

• 手动分区
• 自动分区

7. hive partition什么时候使用手动分区

• 如果数据文件是已经按照分区分好的每个文件，就用手动分区

• 每天一个文件
• 每个部门一个文件

8.hive partition怎么手动分区

• step1:创建分区表

create table tb_emp_part1(
empno string,
ename string,
job string,
managerno string,
hiredate string,
salary double,
jiangjin double,
deptno string
) 
partitioned by (department int)
row format delimited fields terminated by '\t';

• setp2:加载数据

load data local inpath '/export/datas/emp10.txt' into table tb_emp_part1 partition(department = 10);
load data local inpath '/export/datas/emp20.txt' into table tb_emp_part1 partition(department = 20);
load data local inpath '/export/datas/emp30.txt' into table tb_emp_part1 partition(department = 30);

9.hive partition什么时候使用自动分区

• 如果数据是一个整体，没有按照分区规则变成多个文件

10.hive partition怎么自动分区

• step1:先创建一个张原始数据表，放所有数据：tb_emp
• step2:将所有数据通过程序来实现分区，写入新的分区表
• step3:开启自动分区

• set hive.exec.dynamic.partition.mode=nonstrict;

• step4:创建分区表

create table tb_emp_part2(
empno string,
ename string,
job string,
managerno string,
hiredate string,
salary double,
jiangjin double
) 
partitioned by (dept string)
row format delimited fields terminated by '\t';

• step5:从原始数据表加载到分区表

insert into table tb_emp_part2 partition(dept)
select * from tb_emp;

• 实现：判断tb_emp中的部门编号：deptno有哪几种值，第一种值作为第一个分区，第二种值作为第二个分区，将对应的数据放入对应的分区

• 如何知道我是按照tb_emp中的deptno做的分区？
• 按照查询语句的最后一个字段做分区，写入新表
• 如果分区的字段不是最后一个字段怎么办？
• 例如：我想按照job进行分区，那么select的时候将其放到最后

create table tb_emp_part2(
empno string,
ename string,
managerno string,
hiredate string,
salary double,
jiangjin double，
deptno string
) 
partitioned by (job string)
row format delimited fields terminated by '\t';

insert into table tb_emp_part2 partition(job)
select 
empno,ename,managerno,hiredate
,salary,jiangjin,deptno
,job  
from tb_emp;

11.如何查看分区

show partitions 表名;

12.分桶结构表clustered

• 本质:就是底层MapReduce的分区，分桶的规则按照分桶字段的HASH取余
• 应用场景:
• SMB Join：适合于大表 join 大表的应用场景，是一种优化以后 join

• sort merge bulket join
• 如果两张表要进行SMB Join，要求两张表必须都为桶表

• Reduce join：适合于`大表join大表，但是比较慢

• A表：1亿条
• B表：1亿条
• reducejoin时：A表的每一条都需要与B表的每一条进行比较，然后关联

• 比较过程类似于笛卡尔积

13.分桶表怎么构建

• 注意:分桶的数据是不能直接load进去的，必须通过MapReduce进行分区处理才能分桶
• step1:开启分桶

set hive.enforce.bucketing=true;

• step2:创建分桶表

create table tb_emp_bucket(
empno string,
ename string,
job string,
managerno string,
hiredate string,
salary double,
jiangjin double,
deptno string
) 
clustered by (deptno) into 3 BUCKETS
row format delimited fields terminated by '\t';

• step3:从原始数据表加载到分桶表

insert overwrite table tb_emp_bucket
select * from tb_emp cluster by (deptno);

14.分区表和分桶表的区别

• 共同点：大数据的处理思想：大而化小，分而治之
• 字段：分区的字段是假的，分桶的字段是真的
• 目录：分区是目录级别，分桶是文件级别
• 目的：

• 分区是为了加快输入的性能，会构建元数据，表的最后一级目录是分区的目录
• 分桶是为了SMB Join而构建的，表到最后一级目录还是表的目录

分区表

• 本质减少mr的工作量，比如我们要查询某一字段的全部数据，可以只对目录下的分区文件进行一个mr作业就可以了
• 对数据进行水平切分，每个分区即为一个物理文件夹。
• 按照某列或某些列分为多个分区,容易数据倾斜
• 物理表现为：目录到分区,对应不同文件夹
• 分区字段为假
• 应用场景：比如国家，时间

分桶表

• 本质减少shuffle花费的时间，表文件会被物理分桶为不同的文件，被分桶的数据被统一放到了一个或几个切片，这样可以让相同分桶的字段join，只需要把这两个分桶shuffer到一个reducer中就可以了
• 对数据进行垂直切分，每个分桶即为一个文件。
• 按照某列hash值%桶个数分割
• 物理表现为：目录到表,对应不同文件,细粒度
• 分区字段为真
• 应用场景：比如班级，某些有固定规定的字段

15.insert into 和 insert overwrite区别？

• 解答：

• insert into：

• 将某一张表中的数据写到另一张表中

• insert overwrite：

• 覆盖之前的内容。
• 如果有分区,只会重写当前分区数据

16.假如一个分区的数据主部错误怎么通过hivesql删除hdfs

• 解答：

alter table ptable drop partition (daytime='20140911',city='bj');

• 元数据，数据文件都删除，但目录daytime= 20140911还在

17.Hive 表关联查询，如何解决数据倾斜的问题？

• 可以参考

1）倾斜原因：

• map 输出数据按 key Hash 的分配到 reduce 中，由于 key 分布不均匀、业务数据本身的特、 建表时考虑不周、等原因造成的 reduce 上的数据量差异过⼤。

• （1）key 分布不均匀;
• （2）业务数据本身的特性;
• （3）建表时考虑不周;
• （4）某些 SQL 语句本身就有数据倾斜;
• 如何避免：对于 key 为空产⽣的数据倾斜，可以对其赋予⼀个随机值

2）解决⽅案

• （1）参数调节：

• hive.map.aggr = true
• hive.groupby.skewindata=true

• 有数据倾斜的时候进⾏负载均衡，当选项设定位 true,⽣成的查询计划会有两个 MR Job。 第⼀个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合 操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从⽽达到负载均衡的⽬的；第⼆个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同⼀个 Reduce 中）， 最后完成最终的聚合操作。
• （2）SQL 语句调节：

• ① 选⽤ join key 分布最均匀的表作为驱动表。做好列裁剪和 filter 操作，以达到两表做 join 的时候，数据量相对变⼩的效果。
• ② ⼤⼩表 Join： 使⽤ map join 让⼩的维度表（1000 条以下的记录条数）先进内存。在 map 端完成 reduce.
• ③⼤表 Join ⼤表： 把空值的 key 变成⼀个字符串加上随机数，把倾斜的数据分到不同的 reduce 上，由于 null 值关联不上，处理后并不影响最终结果。
• ④ count distinct ⼤量相同特殊值: count distinct 时，将值为空的情况单独处理，如果是计算 count distinct，可以不⽤处理， 直接过滤，在最后结果中加 1。如果还有其他计算，需要进⾏ group by，可以先将值为空的 记录单独处理，再和其他计算结果进⾏ union。

18.请谈⼀下 Hive 的特点，Hive 和 RDBMS 有什么异同？（重新整理）

• hive 是基于 Hadoop 的⼀个数据仓库⼯具，可以将结构化的数据⽂件映射为⼀张数据库 表，并提供完整的 sql 查询功能，可以将 sql 语句转换为 MapReduce 任务进⾏运⾏。

• 其优点 是学习成本低，可以通过类 SQL 语句快速实现简单的 MapReduce 统计，不必开发专⻔的 MapReduce 应⽤，⼗分适合数据仓库的统计分析，但是 Hive 不⽀持实时查询。

• Hive 与关系型数据库的区别：

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

• order by：会对输⼊做全局排序，因此只有⼀个 reduce（r 多个 reducer ⽆法保证全局有序）。 只有⼀个 reducer，会导致当输⼊规模较⼤时，需要较⻓的计算时间。
• sort by：分区内排序，不是全局排序，其在数据进⼊ reducer 前完成排序，每个reduce内有序。
• distribute by：按照指定的字段对数据进⾏划分输出到不同的 reduce 中，一般结合sort by，类似MR中Partition，按照指定的key进行分区。
• cluster by：除了具有 distribute by 的功能外还兼具 sort by 的功能，指定的列只能是降序，不能指定asc和desc。

20.简要描述数据库中的 null，说出 null 在 hive 底层如何存储，并 解释 select a.* from t1 a left outer join t2 b on a.id=b.id where b.id is null; 语句的含义？

• null 与任何值运算的结果都是 null, 可以使⽤ is null、is not null 函数指定在其值为 null 情况下的取值。
• null 在 hive 底 层 默 认 是 ⽤'\N'来 存 储 的 ， 可 以 通 过 alter table test SET SERDEPROPERTIES(‘serialization.null.format’ = ‘a’);来修改。
• 查询出 ID在t1表存在在t2不存在的数据

21.写出 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。

22.Hive 有哪些⽅式保存元数据，各有哪些特点？

• Hive ⽀持三种不同的元存储服务器，分别为：内嵌式元存储服务器、本地元存储服务器、 远程元存储服务器，每种存储⽅式使⽤不同的配置参数。
• 内嵌式元存储主要⽤于单元测试，在该模式下每次只有⼀个进程可以连接到元存储， Derby 是内嵌式元存储的默认数据库。
• 在本地模式下，每个 Hive 客户端都会打开到数据存储的连接并在该连接上请求 SQL 查 询。
• 在远程模式下，所有的 Hive 客户端都将打开⼀个到元数据服务器的连接，该服务器依 次查询元数据，元数据服务器和客户端之间使⽤ Thrift 协议通信。

23.Hive 内部表和外部表的区别？

• 创建表时：

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

• 删除表时：

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

24.Hive 的 HSQL 转换为 MapReduce 的过程？（重新整理）

• HiveSQL ->AST(抽象语法树) -> QB(查询块) ->OperatorTree（操作树）->优化后的操作 树->mapreduce 任务树->优化后的 mapreduce 任务树



• 
  



• 
  
• 过程描述如下：

• SQL Parser：Antlr 定义 SQL 的语法规则，完成 SQL 词法，语法解析，将 SQL 转化为抽 象 语法树 AST Tree；
• Semantic Analyzer：遍历 AST Tree，抽象出查询的基本组成单元 QueryBlock；
• Logical plan：遍历 QueryBlock，翻译为执⾏操作树 OperatorTree； Logical plan optimizer: 逻 辑 层 优 化 器 进 ⾏ OperatorTree 变 换 ，合 并 不 必 要 的 ReduceSinkOperator，减少 shuffle 数据量；
• Physical plan：遍历 OperatorTree，翻译为 MapReduce 任务；
• Logical plan optimizer：物理层优化器进⾏ MapReduce 任务的变换，⽣成最终的执⾏计 划；

25.Hive 底层与数据库交互原理？

• 由于 Hive 的元数据可能要⾯临不断地更新、修改和读取操作，所以它显然不适合使⽤ Hadoop ⽂件系统进⾏存储。⽬前 Hive 将元数据存储在 RDBMS 中，⽐如存储在 MySQL、 Derby 中。元数据信息包括：存在的表、表的列、权限和更多的其他信息。
  

26.请把下⾯语句⽤ Hive 实现

select a.key,a.value 
from a where a.key not exists (select b.key from b)

27.写出将 text.txt ⽂件放⼊ hive 中 test 表‘2016-10-10’ 分区 的语句，test 的分区字段是 l_date

LOAD DATA LOCAL INPATH '/your/path/test.txt' 
OVERWRITE INTO TABLE test PARTITION (l_date='2016-10-10')

28.Hive 如何进⾏权限控制？

• ⽬前 hive ⽀持简单的权限管理，默认情况下是不开启，这样所有的⽤户都具有相同的权 限，同时也是超级管理员，也就对 hive 中的所有表都有查看和改动的权利，这样是不符合 ⼀般数据仓库的安全原则的。Hive 可以是基于元数据的权限管理，也可以基于⽂件存储级 别的权限管理。
• 为了使⽤ Hive 的授权机制，有两个参数必须在 hive-site.xml 中设置：

<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
<description>enable or disable the hive client authorization</description>
</property>
<property>
<name>hive.security.authorization.createtable.owner.grants</name>
<value>ALL</value>
<description>the privileges automatically granted to the owner whenever a table gets created.
An example like "select,drop" will grant select and drop privilege to the owner of the
table</description>
</property>

• Hive ⽀持以下权限：
• Hive 授权的核⼼就是⽤户（user）、组（group）、⻆⾊（role）。
• Hive 中的⻆⾊和平常我们认知的⻆⾊是有区别的，Hive 中的⻆⾊可以理解为⼀部分有 ⼀些相同“属性”的⽤户或组或⻆⾊的集合。这⾥有个递归的概念，就是⼀个⻆⾊可以是⼀些⻆⾊的集合。
• 下⾯举例进⾏说明：

⽤户 组
张三 G_db1
李四 G_db2
王五 G_bothdb

• 如上有三个⽤户分别属于 G_db1、G_db2、G_alldb。G_db1、G_db2、G_ bothdb 分别表示该组⽤户可以访问数据库 1、数据库 2和可以访问 1、2两个数据库。现在可以创建 role_db1和 role_db2，分别并授予访问数据库 1和数据库 2的权限。这样只要将 role_eb1赋给 G_db1（或者该组的所有⽤户），将 role_eb2赋给 G_db2，就可以是实现指定⽤户访问指定数据库。最后创建role_bothdb 指向 role_db1、role_db2（role_bothdb 不需要指定访问那个数据库），然后 role_bothdb 授予 G_bothdb，则 G_bothdb 中的⽤户可以访问两个数据库。
• Hive 的⽤户和组使⽤的是 Linux 机器上的⽤户和组，⽽⻆⾊必须⾃⼰创建。

⻆⾊管理：
--创建和删除⻆⾊
create role role_name;
drop role role_name;
--展示所有 roles
show roles
--赋予⻆⾊权限
grant select on database db_name to role role_name;
grant select on [table] t_name to role role_name;
--查看⻆⾊权限
show grant role role_name on database db_name;
show grant role role_name on [table] t_name;
--⻆⾊赋予⽤户
grant role role_name to user user_name
--回收⻆⾊权限
revoke select on database db_name from role role_name;
revoke select on [table] t_name from role role_name;
--查看某个⽤户所有⻆⾊
show role grant user user_name;

29.对于 hive，你写过哪些 udf 函数，作⽤是什么？

• ⽇期处理 UDF 函数。

开发过程

• 1-开发udf程序：继承UDF类，实现一个或者多个evaluate方法
• 2-打成jar包
• 3-上传jar包到集群中，并添加到hive的环境变量中，在hive中执行

add jar /export/datas/udf.jar;

• 4-创建临时函数：

create temporary function  transDate as 'bigdata.hanjiaxiaozhi.cn.hive.udf.UserUDF';

• 5-测试函数：

select transDate("18/Aug/2019:12:30:05");

• 6-删除临时函数

DROP TEMPORARY FUNCTION transDate;

30.Hive 中的压缩格式 TextFile、SequenceFile、RCfile 、ORCfile 各有什么区别？

• TextFile

• 默认格式，存储⽅式为⾏存储，数据不做压缩，磁盘开销⼤，数据解析开销⼤。 可结合Gzip、Bzip2使⽤(系统⾃动检查，执⾏查询时⾃动解压)，但使⽤这种⽅式，压缩后的⽂件不⽀持 split，Hive 不会对数据进⾏切分，从⽽⽆法对数据进⾏并⾏操作。并且在反序列化过程中，必须逐个字符判断是不是分隔符和⾏结束符，因此反序列化开销会⽐ SequenceFile ⾼⼏⼗倍。

• SequenceFile

• SequenceFile 是 Hadoop API 提供的⼀种⼆进制⽂件⽀持，，存储⽅式为⾏存储，其具有使⽤⽅便、可分割、可压缩的特点。
• SequenceFile ⽀持三种压缩选择：NONE，RECORD，BLOCK。Record 压缩率低，⼀般建议使⽤ BLOCK 压缩。
• 优势是⽂件和 hadoop api 中的 MapFile 是相互兼容的

• RCFile

• 存储⽅式：数据按⾏分块，每块按列存储。结合了⾏存储和列存储的优点：

• ⾸先，RCFile 保证同⼀⾏的数据位于同⼀节点，因此元组重构的开销很低；
• 其次，像列存储⼀样，RCFile 能够利⽤列维度的数据压缩，并且能跳过不必要的列读取；

• RCFile 的⼀个⾏组包括三个部分：

• 第⼀部分是⾏组头部的【同步标识】，主要⽤于分隔 hdfs 块中的两个连续⾏组
• 第⼆部分是⾏组的【元数据头部】，⽤于存储⾏组单元的信息，包括⾏组中的记录数、每个列的字节数、列中每个域的字节数
• 第三部分是【表格数据段】，即实际的列存储数据。在该部分中，同⼀列的所有域顺序存储。
• 从图可以看出，⾸先存储了列 A 的所有域，然后存储列 B 的所有域等。
• 数据追加：RCFile 不⽀持任意⽅式的数据写操作，仅提供⼀种追加接⼝，这是因为底层的 HDFS 当前仅仅⽀持数据追加写⽂件尾部。
• ⾏组⼤⼩：⾏组变⼤有助于提⾼数据压缩的效率，但是可能会损害数据的读取性能，因为这样增加了 Lazy 解压性能的消耗。⽽且⾏组变⼤会占⽤更多的内存，这会影响并发执⾏的其他 MR 作业。考虑到存储空间和查询效率两个⽅⾯，Facebook 选择 4MB 作为默认的⾏组⼤⼩，当然也允许⽤户⾃⾏选择参数进⾏配置。

• ORCFile

• 存储⽅式：数据按⾏分块 每块按照列存储。
• 压缩快 快速列存取。
• 效率⽐ rcfile ⾼,是 rcfile 的改良版本。
• 以下为 RCFile、TextFile、SequenceFile 三种⽂件的存储情况：

[hadoop@master ~]$ hadoop dfs -dus /user/Hive/warehouse/*
hdfs://master :9000/user/Hive/warehouse/hbase_table_1 0
hdfs://master :9000/user/Hive/warehouse/hbase_table_2 0
hdfs://master :9000/user/Hive/warehouse/orcfile_table 0
hdfs://master :9000/user/Hive/warehouse/rcfile_table 102638073
hdfs://master :9000/user/Hive/warehouse/seqfile_table 112497695
hdfs://master :9000/user/Hive/warehouse/testfile_table 536799616
hdfs://master :9000/user/Hive/warehouse/textfile_table 107308067
[hadoop@singlehadoop ~]$ hadoop dfs -ls /user/Hive/warehouse/*/
-rw-r--r-- 2 hadoop supergroup 51328177 2014-03-20 00:42
/user/Hive/warehouse/rcfile_table/000000_0
-rw-r--r-- 2 hadoop supergroup 51309896 2014-03-20 00:43
/user/Hive/warehouse/rcfile_table/000001_0
-rw-r--r-- 2 hadoop supergroup 56263711 2014-03-20 01:20
/user/Hive/warehouse/seqfile_table/000000_0
-rw-r--r-- 2 hadoop supergroup 56233984 2014-03-20 01:21
/user/Hive/warehouse/seqfile_table/000001_0
-rw-r--r-- 2 hadoop supergroup 536799616 2014-03-19 23:15
/user/Hive/warehouse/testfile_table/weibo.txt
-rw-r--r-- 2 hadoop supergroup 53659758 2014-03-19 23:24
/user/Hive/warehouse/textfile_table/000000_0.gz
-rw-r--r-- 2 hadoop supergroup 53648309 2014-03-19 23:26
/user/Hive/warehouse/textfile_table/000001_1.gz

• 总结:(重要)

• 相⽐ TEXTFILE 和 SEQUENCEFILE，RCFILE 由于列式存储⽅式，数据加载时性能消耗较⼤，但是具有较好的压缩⽐和查询响应。
• 数据仓库的特点是⼀次写⼊、多次读取，因此，整体来看，RCFILE 相⽐其余两种格式具有较明显的优势。

31.Hive join 过程中⼤表⼩表的放置顺序？

• 解答:

• 个人感觉这个解释不太满意，不过小表放前，养成习惯也好
• 1.将最⼤的表放置在 JOIN 语句的最右边，或者直接使⽤/*+ streamtable(table_name) */指出。
• 2.在编写带有 join 操作的代码语句时，应该将条⽬少的表/⼦查询放在 Join 操作符的左边。因为在 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，载⼊条⽬较少的表可以有效减少 OOM（out of memory）即内存溢出。所以对于同⼀个 key 来说，对应的 value 值⼩的放前，⼤的放后，这便是“⼩表放前”原则。若⼀条语句中有多个 Join，依据Join 的条件相同与否，有不同的处理⽅法。

32.Hive 的两张表关联，使⽤ MapReduce 怎么实现？

• 解答:

• 如果其中有⼀张表为⼩表，直接使⽤ map 端 join 的⽅式（map 端加载⼩表）进⾏聚合。
• 如果两张都是⼤表，那么采⽤联合 key，联合 key 的第⼀个组成部分是 join on 中的公共字段，第⼆部分是⼀个 flag，0 代表表 A，1 代表表 B，由此让 Reduce 区分客户信息和订单信息；在 Mapper 中同时处理两张表的信息，将 join on 公共字段相同的数据划分到同⼀个分区中，进⽽传递到⼀个 Reduce 中，然后在 Reduce 中实现聚合。

33.Hive 中使⽤什么代替 in 查询？

• 解答:

• 在 Hive 0.13 版本之前，通过 left outer join 实现 SQL 中的 in 查询
• 0.13 版本之后，Hive已经⽀持 in 查询。

34.所有的 Hive 任务都会有 MapReduce 的执⾏吗？

• 解答:
• 不是，从 Hive0.10.0 版本开始
• 对于简单的不需要聚合的类似 SELECT <col> from <table>LIMIT n语句，不需要起 MapReduce job，直接通过 Fetch task 获取数据。

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

• 解答:
• UDF: 单⾏进⼊，单⾏输出 →一对一
• UDAF: 多⾏进⼊，单⾏输出 →多对一
• UDTF: 单⾏输⼊，多⾏输出 →一对多

36.说说对 Hive 桶表的理解？

• 解答:

• 桶表是对数据进⾏哈希取值，然后放到不同⽂件中存储。
• 数据加载到桶表时，会对字段取 hash 值，然后与桶的数量取模 。把数据放到对应的⽂件中。物理上，每个桶就是表(或分区⽬录⾥的⼀个⽂件，⼀个作业产⽣的桶(输出⽂件)和reduce 任务个数相同。
• 桶表专⻔⽤于抽样查询，是很专业性的，不是⽇常⽤来存储数据的表，需要抽样查询时，才创建和使⽤桶表。

37.Hive ⾃定义 UDF 函数的流程?

• 解答:

• 1）写⼀个类继承（org.apache.hadoop.hive.ql.）UDF 类；
• 2）覆盖⽅法 evaluate()；
• 3）打 JAR 包；
• 4）通过 hive 命令将 JAR 添加到 Hive 的类路径：

• hive> add jar /home/ubuntu/ToDate.jar;

• 5）注册函数：

• hive> create temporary function xxx as ‘XXX’;

• 6）使⽤函数；
• 7）[可选] drop 临时函数；

38.Hive 可以像关系型数据库那样建⽴多个库吗？

• 可以建⽴多个库

39.Hive 实现统计的查询语句是什么？

• count 等语句。

40.Hive 优化措施

• 可以参考

1.Fetch 抓取

• Hive 中对某些情况的查询可以不必使⽤ MapReduce 计算。

• hive-default.xml.template ⽂件中 hive.fetch.task.conversion 默认是 more,老版本hive是minimal（修改为more）
• more时全局查找、字段查找、limit 查找等都不⾛ mapreduce。

2.本地模式

• 单台机器上处理所有的任务,适合于小数据集,执行时间会大大缩短
• Hive 的输⼊数据量⾮常⼩的情况下
• set hive.exec.mode.local.auto=true; （开启本地模式）
• set hive.exec.mode.local.auto.inputbytes.max=134217728;（小于128mb采用本地模式，默认134217728）
• set hive.exec.mode.local.auto.input.files.max=10;（输入文件个数小于10个采用本地模式，默认为 4）

场景:
我想要验证一个函数的执行结果
我先建一张表xxx,
往表里插入一行数据
select (1+2) from xxx
结果需要好久???
name我可以开启本地模式set hive.exec.mode.local.auto=true;(默认为false)
但是你会发现job确实是以本地模式运行了（看job名字就能看出来,中间有local字样）,但是还是会报错，各种找不到jar包。
这里还要运行一个语句：set fs.defaultFS=file:///
在执行sql发现速度大大提高了!
当一个job满足下面条件的时候才能真正使用本地模式
输入数据小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
map数小于参数：hive.exec.mode.local.auto.tasks.max(默认4)
reduce为0或1

3.表的优化

• 表的优化1.⼩表、⼤表 Join（新版的 hive 已经对⼩表 JOIN ⼤表和⼤表 JOIN ⼩表进⾏了优化。⼩表 放在左边和右边已经没有明显区别。）

• 将 key 相对分散，并且数据量⼩的表放在 join 的左边
• 优点：

• 1.有效减少内存溢出错误发⽣的⼏率
• 2.可以使⽤ Group 让⼩的维度表（1000条以下的记录条数）先进内存
• 3.在 map 端完成 reduce。

• 表的优化2.⼤表 Join ⼤表

• 空 KEY 过滤（is not null）

• 有时 join 超时是因为某些 key 对应的数据太多，⽽相同 key 对应的数据都会发送到相同的 reducer 上，从⽽导致内存不够。此时我们应该仔细分析这些异常的 key，很多情况下，这些 key 对应的数据是异常数据，我们需要在 SQL 语句中进⾏过滤。例如 key 对应的字段为空

• 空 key 转换（concat（‘含空列’,rand()））

• 有时虽然某个 key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join 的结果中，此时我们可以表 a 中 key 为空的字段赋⼀个随机的值，使得数据随机均匀地分不到不同的 reducer 上。

• 表的优化3.MapJoin
• 表的优化4.Group By
• 表的优化5.Count(Distinct) 去重统计
• 表的优化6.避免笛卡尔积

• 尽量避免笛卡尔积（join 的时候不加 on 条件，或者⽆效的 on 条件）

• 表的优化7.⾏列过滤

• 列处理：在 SELECT 中，只拿需要的列，如果有，尽量使⽤分区过滤，少⽤ SELECT *。
• ⾏处理：在分区剪裁中，当使⽤外关联时，如果将副表的过滤条件写在 Where 后⾯，那么就会先全表关联，之后再过滤

• 表的优化8.动态分区调整

• 关系型数据库中，对分区表 Insert 数据时候，数据库⾃动会根据分区字段的值，将数据插⼊到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使⽤ Hive 的动态分区，需要进⾏相应的配置。

• 表的优化9.分桶
• 表的优化10.分区

优化措施4.数据倾斜

• Map 数
• ⼩⽂件进⾏合并
• 复杂⽂件增加 Map 数
• Reduce 数

优化措施5.并⾏执⾏

优化措施6.严格模式

优化措施7.JVM 重⽤

化措施8.推测执⾏

优化措施9.压缩

优化措施10.EXPLAIN（执⾏计划）

41.Hive 数据分析⾯试题

• 场景举例.北京市学⽣成绩分析.
• 成绩的数据格式:时间,学校,年纪,姓名,科⽬,成绩
• 样例数据如下:

2013,北⼤,1,裘容絮,语⽂,97
2013,北⼤,1,庆眠拔,语⽂,52
2013,北⼤,1,乌洒筹,语⽂,85
2012,清华,0,钦尧,英语,61
2015,北理⼯,3,冼殿,物理,81
2016,北科,4,况飘索,化学,92
2014,北航,2,孔须,数学,70
2012,清华,0,王脊,英语,59
2014,北航,2,⽅部盾,数学,49
2014,北航,2,东⻔雹,数学,77

1 情景题：分组 TOPN

-- 1.分组 TOPN 选出 今年每个学校,每个年级,分数前三的科⽬.
hive -e"set mapreduce.job.queuename=low;
select t.*
from(select
		school,
		class,
		subjects,
		score,
		row_number() over (partition by school,class,subjects order by score desc) rank_code
	from spark_test_wx
	where partition_id = "2017"
) t
where t.rank_code <= 3;"
-- 今年,北航,每个班级,每科的分数,及分数上下浮动 2分的总和

select school,class,subjects,score,
sum(score) over(order by score range between 2 preceding and 2 following) sscore
from spark_test_wx
where partition_id = "2017" and school="北航"
提问,上述 sql 有没有可优化的点.
-- row_number() over (distribute by school,class,subjects sort by score desc) rank_code

2 情景题：where 与 having

-- 今年 清华 1 年级 总成绩⼤于 200 分的学⽣ 以及学⽣数
hive -e "
set mapreduce.job.queuename=low;
select school,class,name,sum(score) as total_score,
count(1) over (partition by school,class) nct
from spark_test_wx
where partition_id = "2017" and school="清华" and class = 1
group by school,class,name
having total_score>200;
"
having 是分组（group by）后的筛选条件，分组后的数据组内再筛选，也就是说 HAVING⼦句可以让我们筛选成组后的各组数据。
where则是在分组,聚合前先筛选记录。也就是说作⽤在GROUP BY⼦句和HAVING⼦句前。

3 情景题：数据倾斜

今年加⼊进来了 10 个学校,学校数据差异很⼤计算每个学校的平均分。
该题主要是考察数据倾斜的处理⽅式。
Group by ⽅式很容易产⽣数据倾斜。需要注意⼀下⼏点
1）Map 端部分聚合
	hive.map.aggr=true（⽤于设定是否在 map 端进⾏聚合，默认值为真，相当于combine）
	hive.groupby.mapaggr.checkinterval=100000（⽤于设定 map 端进⾏聚合操作的条数）
2）有数据倾斜时进⾏负载均衡
	设定 hive.groupby.skewindata，当选项设定为 true 是，⽣成的查询计划有两个MapReduce 任务。
	在第⼀个MapReduce 中，map 的输出结果集合会随机分布到reduce 中，每个reduce做部分聚合操作，并输出结果。这样处理的结果是，相同的 Group By Key 有可能分发到不同的 reduce 中，从⽽达到负载均衡的⽬的；
	第⼆个 MapReduce 任务再根据预处理的数据结果按照 Group By Key 分布到 reduce中（这个过程可以保证相同的 Group By Key 分布到同⼀个 reduce 中），最后完成最终的聚合操作。

4 情景题：分区表

假设我创建了⼀张表，其中包含了 2016 年客户完成的所有交易的详细信息：CREATE TABLE transaction_details (cust_id INT, amount FLOAT, month STRING, country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
	现在我插⼊了 100 万条数据，我想知道每个⽉的总收⼊。
	问：如何⾼效的统计出结果。写出步骤即可。
解答:
1）⾸先分析这个需求，其实并不难，但是由于题⽬说了，要⾼效。⽽且数据量也不⼩,直接写 sql 查询估计肯定会挂。
2）分析：
（1）我们可以通过根据每个⽉对表进⾏分区来解决查询慢的问题。 因此，对于每个⽉我们将只扫描分区的数据，⽽不是整个数据集。
（2）但是我们不能直接对现有的⾮分区表进⾏分区。 所以我们会采取以下步骤来解决这个问题：
（3）创建⼀个分区表，partitioned_transaction：
	create table partitioned_transaction (cust_id int, amount float, country string) partitioned by(month string) row format delimited fields terminated by ‘,’;
（4）在 Hive 中启⽤动态分区：
	SET hive.exec.dynamic.partition = true;
	SET hive.exec.dynamic.partition.mode = nonstrict;
（5）将数据从⾮分区表导⼊到新创建的分区表中：
	insert overwrite table partitioned_transaction partition (month) select cust_id, amount,country, month from transaction_details;
（6）使⽤新建的分区表实现需求。

42.hive导入导出

导入

• 本地文件导入到Hive表–load data local inpath '路径' into/overwrite table 表名
• HDFS文件导入到Hive表–load data inpath 'hdfs路径' into/overwrite table 表名
• Hive表导入到Hive表(select 或者insert )

• 创建表的过程中从其他表导入–create table ... as select xxx from 其他表
• 从其他表导入–insert into/overwrite table 表名 select xxx from 其他表

• 指定数据库目录在HDFS上的地址–location 'hdfs路径'

导出

• 导出到本地文件系统；

• insert overwrite local directory ‘/home/wyp/wyp’ select * from wyp；
• 和导入数据到Hive不一样，不能用insert into来将数据导出
• 还可以用hive的-e和-f参数来导出数据。其中-e 表示后面直接接带双引号的sql语句；而-f是接一个文件，文件的内容为一个sql语句

• hive -e “select * from wyp” >> local/wyp.txt

• 导出到HDFS中；

• insert overwrite directory ‘/home/wyp/wyp’ select * from wyp；

• 导出到Hive的另一个表中。

• insert into/overwrite table 表名 select xxx from 其他表

43.Hive的存储格式及压缩算法

存储格式

• TextFile

• 行式存储:查询一整行的时候,查询快
• 默认格式
• 数据不做压缩,磁盘开销大,数据解析开销大
• 可使用Gzip、Bzip2，但压缩后的文件不支持split
• 在反序列化的时候，必须逐个字段判断是不是分隔符和行结束符，因此反序列化开销高

• SequenceFile

• 行式存储

• ORC

• 列式存储:查询只有少数几个字段的时候,大大减少读取的数据量
• 每个ORC文件由3部分组成：Index Data（1w行做一个索引）、Row Data（具体数据）、Stripe Footer（类型，长度）

• Parquent

• 列式存储
• 二进制存储
• 会按照Block大小设置行组的大小

压缩算法

• Bzip2

• 可切分
• 压缩率高,高于Gzip,压缩速度很慢
• 适合:对速度要求不高,但需要较大压缩率

• Gzip

• 不可切分

• LZO

• 不可切分
• 压缩率低于Gzip
• 需要建索引,并指定输入格式

• LZ4

• 不可切分

• Snappy

• 不可切分
• 高效的压缩速度和合理的压缩率
• 压缩率低于Gzip

44.Hive中小文件问题

产生:

• 动态分区插入
• reduce数量过多
• 数据源本身包含大量小文件

影响

• 会开很多map,一个map开一个jvm执行,这些任务的初始化和启动和回收会占用大量资源 会占用大量元数据内存(每个小文件对象150byte左右)

解决

• 不使用textfile
• 减少reduce数量
• 少用动态分区,用的话加上distribute by分区
• 对于已存在的数据

• hadoop archive命令把小文件进行归档
• 重建表,建表时减少reduce数量
• 参数进行调节,设置map/reduce相关参数

• map端/reduce端输出进行合并
• 设置合并文件大小
• 当输出文件的平均大小小于某值时,启动一个独立的MR任务进行文件merge

map/reduce端的相关参数的设置

• 设置map输入合并小文件的相关参数：

//每个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;

配置Hive结果合并

• 我们可以通过一些配置项来使Hive在执行结束后对结果文件进行合并：

//设置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

• 注意：对于输出结果为压缩文件形式存储的情况，要解决小文件问题，如果在Map输入前合并，对输出的文件存储格式并没有限制。但是如果使用输出合并，则必须配合SequenceFile来存储，否则无法进行合并。

45.Hive中Join的类型和用法

概览:

• 内关联（JOIN）

• 只返回能关联上的结果。

• 左外关联（LEFT [OUTER] JOIN）

• 以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。
• 是否指定OUTER关键字，貌似对查询结果无影响。

• 右外关联（RIGHT [OUTER] JOIN）

• 和左外关联相反，以RIGTH [OUTER] JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。
• 是否指定OUTER关键字，貌似对查询结果无影响。

• 全外关联（FULL [OUTER] JOIN）

• 以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。
• 是否指定OUTER关键字，貌似对查询结果无影响。
• 注意：FULL JOIN时候，Hive不会使用MapJoin来优化。

• LEFT SEMI JOIN

• 以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY 也在副表中的记录。

• 笛卡尔积关联（CROSS JOIN）

• 返回两个表的笛卡尔积结果，不需要指定关联键。

left semi join和left join区别

• LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。
• LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方都不行。
• 因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。
• left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。因为右表只有 join key 参与关联计算了，而left join on 默认是整个关系模型都参与计算了

实例

• Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持LEFT SEMI JOIN和CROSS JOIN，但这两种JOIN类型也可以用前面的代替。
• 注意：Hive中Join的关联键必须在ON ()中指定，不能在Where中指定，否则就会先做笛卡尔积，再过滤。
• 数据准备

hive> desc lxw1234_a;
OK
id                      string                                      
name                    string                                      
Time taken: 0.094 seconds, Fetched: 2 row(s)
hive> select * from lxw1234_a;
OK
1       zhangsan
2       lisi
3       wangwu
Time taken: 0.116 seconds, Fetched: 3 row(s)
hive> desc lxw1234_b;
OK
id                      string                                      
age                     int                                         
Time taken: 0.159 seconds, Fetched: 2 row(s)
hive> select * from lxw1234_b;
OK
1       30
2       29
4       21
Time taken: 0.09 seconds, Fetched: 3 row(s)

• 内关联（JOIN）

• 只返回能关联上的结果。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
join lxw1234_b b 
ON (a.id = b.id);

--执行结果

1       zhangsan    30
2       lisi        29

• 左外关联（LEFT [OUTER] JOIN）

• 以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。
• 是否指定OUTER关键字，貌似对查询结果无影响。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
left join lxw1234_b b 
ON (a.id = b.id);

--执行结果：

1   zhangsan    30
2   lisi        29
3   wangwu      NULL

• 右外关联（RIGHT [OUTER] JOIN）

• 和左外关联相反，以RIGTH [OUTER] JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。
• 是否指定OUTER关键字，貌似对查询结果无影响。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
RIGHT OUTER JOIN lxw1234_b b 
ON (a.id = b.id);

--执行结果：

1          zhangsan    30
2          lisi        29
NULL       NULL        21

• 全外关联（FULL [OUTER] JOIN）

• 以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。
  是否指定OUTER关键字，貌似对查询结果无影响。
• 注意：FULL JOIN时候，Hive不会使用MapJoin来优化。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
FULL OUTER JOIN lxw1234_b b 
ON (a.id = b.id);

--执行结果：

1       zhangsan        30
2       lisi            29
3       wangwu          NULL
NULL    NULL            21

• LEFT SEMI JOIN

• 以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录。

SELECT a.id,
a.name 
FROM lxw1234_a a 
LEFT SEMI JOIN lxw1234_b b 
ON (a.id = b.id);

--执行结果：

1       zhangsan
2       lisi

--等价于：

SELECT a.id,
a.name 
FROM lxw1234_a a 
WHERE a.id IN (SELECT id FROM lxw1234_b);

--也等价于：

SELECT a.id,
a.name 
FROM lxw1234_a a 
join lxw1234_b b 
ON (a.id = b.id);

--也等价于：

SELECT a.id,
a.name 
FROM lxw1234_a a 
WHERE EXISTS (SELECT 1 FROM lxw1234_b b WHERE a.id = b.id);

• 笛卡尔积关联（CROSS JOIN）

• 返回两个表的笛卡尔积结果，不需要指定关联键。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
CROSS JOIN lxw1234_b b;

--执行结果：

1       zhangsan        30
1       zhangsan        29
1       zhangsan        21
2       lisi            30
2       lisi            29
2       lisi            21
3       wangwu          30
3       wangwu          29
3       wangwu          21

• 除非特殊需求，并且数据量不是特别大的情况下，才可以慎用CROSS JOIN，否则，很难跑出正确的结果，或者JOB压根不能执行完。
• Hive中只要是涉及到两个表关联，首先得了解一下数据，看是否存在多对多的关联。

46.Hive严格模式

• 开启后禁止3种类型查询

• 分区表没有指定分区字段为过滤条件的查询
• order by不加limit的查询
• 笛卡尔积的查询

• Hive严格模式

• Hive提供了一个严格模式，可以防止用户执行那些可能产生意向不到的不好的效果的查询。说通俗一点就是这种模式可以阻止某些查询的执行。通过如下语句设置严格模式：

hive> set hive.mapred.mode=strict;

• 设置为严格模式后，可以禁止3种类型的查询：
• (1)：带有分区的表的查询

• 如果在一个分区表执行hive，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行。换句话说就是在严格模式下不允许用户扫描所有的分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。如果不进行分区限制的查询会消耗巨大的资源来处理，如下不带分区的查询语句：

hive> SELECT DISTINCT(planner_id) FROM fracture_ins WHERE planner_id=5;

• 执行后会出现如下错误：

FAILED: Error in semantic analysis: No Partition Predicate Found for Alias "fracture_ins" Table "fracture_ins

• 解决方案是在where中增加分区条件：

hive> SELECT DISTINCT(planner_id) FROM fracture_ins
       > WHERE planner_id=5 AND hit_date=20120101;

• (2)：带有order by的查询
• 对于使用了order by的查询，要求必须有limit语句。因为order by为了执行排序过程会将所有的结果分发到同一个reduce中进行处理，强制要求用户增加这个limit语句可以防止reduce额外消耗资源，如下是不带limit关键字的查询语句：

hive> SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id;

• 出现如下错误：

FAILED: Error in semantic analysis: line 1:56 In strict mode,
limit must be specified if ORDER BY is present planner_id

• 解决方案就是增加一个limit关键字：

hive> SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id
        > LIMIT 100000;

• (3)：限制笛卡尔积的查询
• 对关系型数据库非常了解的用户可能期望在执行join查询的时候不使用on语句而是使用where语句，这样关系型数据库的执行优化器就可以高效的将where语句转换成那个on语句了。不幸的是，Hive并不支持这样的优化，因为如果表非常大的话，就会出现不可控的情况，如下是不带on的语句：

hive> SELECT * FROM fracture_act JOIN fracture_ads
> WHERE fracture_act.planner_id = fracture_ads.planner_id;

• 出现如下错误：

FAILED: Error in semantic analysis: In strict mode, cartesian product
is not allowed. If you really want to perform the operation,
+set hive.mapred.mode=nonstrict+

• 解决方案就是加上on语句：

hive> SELECT * FROM fracture_act JOIN fracture_ads
        > ON (fracture_act.planner_id = fracture_ads.planner_id);

47.sparkSQL一定比hive快吗，能否想出一种场景，sparkSQL比hive慢(重点)

sparkSQL之所以快

• 中间阶段不落盘

• Hadoop 每次 shuffle 操作后，必须写到磁盘，而 Spark 在 shuffle 后不一定落盘，可以 cache 到内存中，以便迭代时使用。如果操作复杂，很多的 shufle 操作，那么 Hadoop 的读写 IO 时间会大大增加，也是 Hive 更慢的主要原因了。

• 消除了冗余的 MapReduce 阶段

• Hadoop 的 shuffle 操作一定连着完整的 MapReduce 操作，冗余繁琐。而 Spark 基于 RDD 提供了丰富的算子操作，且 reduce 操作产生 shuffle 数据，可以缓存在内存中。

• JVM 的优化

• Hadoop 每次 MapReduce 操作，启动一个 Task 便会启动一次 JVM，基于进程的操作。而 Spark 每次 MapReduce 操作是基于线程的，只在启动 Executor 是启动一次 JVM，内存的 Task 操作是在线程复用的。每次启动 JVM 的时间可能就需要几秒甚至十几秒，那么当 Task 多了，这个时间 Hadoop 不知道比 Spark 慢了多少。

• 极端查询
• 这个查询只有一次 shuffle 操作，此时，也许 Hive HQL 的运行时间也许比 Spark 还快，反正 shuffle 完了都会落一次盘，或者都不落盘。

Select month_id
	, sum(sales) 
from T 
group by month_id;