hive高级查询、Map Join【配案例演示，有图有真相】

一、hive嵌套查询和CTE

CTE嵌套查询演示
CTE跟以前的mysql中的嵌套查询是一样的效果，把查询结果作为另一个查询的表，再对这张表进行查询，这种属于嵌套查询。相比之下，hive的STE显得更有逻辑。
创建两张表，插入数据，演示CTE嵌套查询

#创建表abc
> create table abc(
    > userid int,
    > username string,
    > salary int,
    > position string
    > );
#插入数据
> insert into abc values
    > (1,'zs',5000,'manager'),
    > (2,'ls',4500,'manager'),
    > (3,'cnl',3500,'employee'),
    > (4,'lyq',7000,'manager'),
    > (5,'zk',3000,'employee'),
    > (6,'pl',3200,'employee');
#创建表a
> create table a(
    > userid int,
    > sex string,
    > status string
    > );
#插入数据
insert into a values
    > (1,'male','no'),
    > (2,'male','yes'),
    > (3,'female','yes'),
    > (4,'male','yes'),
    > (5,'male','yes'),
    > (6,'male','no');
    
#mysql嵌套查询演示（查询用户为管理者并且薪资在4500以上的员工信息）
select * from (select * from abc where position='manager') b 
where b.salary > 4500 ;

#用CTE演示嵌套查询（查询用户为管理者并且薪资在4500以上的员工信息）
with b as (select * from abc where position='manager')
select * from tab1;

二、hive JOIN - 关联查询

• 指对多表进行联合查询
• JOIN用于将两个或多个表中的行组合在一起查询
• 类似于SQL JOIN，但是Hive仅支持等值连接
• JOIN发生在WHERE子句之前

#查询员工所有信息
select abc.*,a.* from abc join a where a.userid=abc.userid;

三、Hive MAPJOIN

介绍MapReduce工作原理

Mapper阶段

1. MapTask根据Inputsplit逻辑块去获取数据。文件读取时通过TextinputFormat（–> RecordReader -->read()）一次读一行的方式返回（key，value）键值对。
2. 获取到的（key，value）键值对经过Mapper中的的map方法逻辑处理形成（k,v）键值对（注：k：行的起始偏移量，v行的内容），再通过context.write方法将其写出
3. OutPutCollector收集器会接收到（k,v）键值对，并将其写入到环形缓冲区。
4. 环形缓冲区会存储这些键值对，当缓冲区存储超过设定的80%，将会发生溢写现象，将会触发spill溢出。
5. 在spill溢出之前还会存在对这些键值对的处理，会将环形缓冲区里面的每个键值对的hash值进行取余运算，得到partition值，通过partition值分区，相同的分到一个区，同一partition内的按照key进行快速排序，外部排序。
6. combiner阶段会将环形缓冲区中排序后的内存数据不断spill溢出到本地磁盘文件，如果map阶段处理的数据量较大，可能会溢出多个文件，这些多个文件会被merge合并成大的溢出文件，采用的是归并排序。maptask最终结果文件还是分区且区内有序的文件（局部归并）。

Reduce阶段

1. reduce task根据自己的分区号，去各个map task节点上copy相同partition值的数据到reduce task本地磁盘工作目录；
2. reduce task会把同一分区的来自不同的map task的结果文件，再进行merge合并成一个大文件（归并排序），大文件内容按照k有序；
3. 合并成大文件后，shuffle过程也就结束了，后面进行reduce task的逻辑运算过程，首先调用GroupingComparator对大文件里面的数据进行分组，从文件中每次取出一组（k,values）键值对，调用用户自定义的reduce（）方法进行逻辑处理；
4. 最后通过OutputFormat方法将结果数据写到part-r-000**文件中。

注： shuffle阶段非常昂贵，需要数据迁移，如果数据块多了，会增加负担，所以尽量在combiner阶段对数据进行一次局部的归并，将小文件，变成较大的文件，在进行传输。

join运行原理

common join的运行机制

Common join一开始会被编译为MapReduce任务。Common join任务同样和MapReduce一样有Map阶段和Reduce阶段。Mapper从连接表中读取数据将（key，values）键值对输出到中间文件中。Hadoop在所谓的shffle阶段对这些键值对根据key进行合并。Reducer将排序结果作为输入进行join。由于Shuffle阶段大家昂贵，，所以减少Shuffle和Reduce阶段的代价可以提高性任务性能。

Map join的运行机制

Map join 的目的是减少Shuffle和Reducer阶段的代价，并仅在Map阶段进行join。通过这样做，当其中一个链接表足够小可以装在内存时，所有Mapper都可以将数据保存中并完成join。因此，所有join操作都可以在Mapper阶段完成。但是正因如此，这种类型的Map join存在一些扩展问题。当成千上万个Mapper同时从HDFS将小的连接表读入内存时，链接表很容易成为性能瓶颈，导致Mapper在读取操作期间超时。
这时，为了解决性能瓶颈问题，就会使用分布式缓存。

分布式缓存
Hive-1641 解决了这个扩展问题。分布式缓存的基本思路是，在原始Common join 的MapReduce任务之前创建一个新的MapReduce本地任务。这个新任务是将小表数据从HDFS上读取到内存中的哈希表中。读取文成后，将内存中的哈希表序列化为哈希表文件。
在下一阶段的MapReduce任务启动时，会将这个哈希表文件上传到hadoop分布式缓存中，该缓存会将这些文件发送到每个Mapper的本地磁盘上。因此，所有的Mapper都可以将此持久化的哈希表文件加载会内存，并像之前一样进行join。这样优化后，小表的读取只需要一次就可以了，即使多个Mapper在不同的机器上运行，则分布式缓存只需要将哈希表文件的一个副本发送到这台机器上。

根据文件大小将Join转换成为MapJoin
hive通过自动将Common Join 转换成为 Map Join 来解决此问题。对于Map Join来说，查询处理器直到哪个输入表是大表，其他输入表在执行阶段被识别为小表，并将这些表保存在内存中。但是，查询处理器在编译时不知道输入文件大小，很可能会是两张大表，这样的情况下将没法处理。
这是Common join执行流程，查询处理器会生成一个包含任务的Conditional Task。Conditional Task直到每个输入表确切文件大小，即使是临时存在的中间表。如果所有的表都很大无法转换Map join，那么就只能执行Common join 任务。如果一个表很大而其他的表足够小可以运行Map join，则将Conditional Task选择相应的Map Join本地任务来运行。通过这种机制，动态的将Common join 转换成为 Map join。

确定小表的范围
如果小标的总大小大于25MB，Conditional Task会选择原始Common Join来运行。
25MB也可以通过set hive.smalltable.filesize来修改。

三、hive集合操作

所有子集数据必须具有相同的名称和类型

• UNION ALL：合并后保留重复项
• UNION：合并后删除重复项（v1.2之后）

可以在顶层查询中使用（0.13.0之后）

• ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY 和LIMIT适用于合并后的整个结果

集合其他操作可以使用JOIN/OUTER JOIN来实现

• 差集、交集

四、装载数据：INSERT表插入数据

使用INSERT语句将数据插入表/分区

-- INSERT支持OVERWRITE(覆盖)和INTO(追加)
INSERT OVERWRITE/INTO TABLE tablename1 
[PARTITION (partcol1=val1, partcol2=val2 ...)] 
select fileds,... from tb_other;

• Hive支持从同一个表进行多次插入
• INSERT INTO中TABLE关键字是可选的
• INSERT INTO可以指定插入到哪些字段中
• INSERT INTO table_name VALUES，支持插入值列表
• 数据插入必须与指定列数相同

多种方式插入数据

-- INSERT不支持的写法
INSERT OVERWRITE TABLE test select 'hello'; 
-- 通过查询语句插入
insert into employee select * from ctas_employee; 
-- 多插入
from ctas_employee
insert overwrite table employee select *
insert overwrite table employee_internal select *;
-- 插入到分区
from ctas_patitioned 
insert overwrite table employee PARTITION (year, month)
select *,'2018','09';
-- 通过指定列插入(insert into可以省略table关键字)
insert into employee(name) select 'John' from test limit 1;
-- 通过指定值插入
insert into employee(name) value('Judy'),('John');

-- 从同一数据源插入本地文件，hdfs文件，表
from ctas_employee
insert overwrite local directory '/tmp/out1'  select *
insert overwrite directory '/tmp/out1' select *
insert overwrite table employee_internal select *;
-- 以指定格式插入数据
insert overwrite directory '/tmp/out3'
row format delimited fields terminated by ','
select * from ctas_employee;
-- 其他方式从表获取文件
hdfs dfs -getmerge <table_file_path>

五、Hive数据交换 - IMPORT/EXPORT

使用EXPORT导出数据

-- 将employee表数据到处到/tmp/output文件夹下
EXPORT TABLE employee TO '/tmp/output';
-- 分区表中数据块为year=2014，month=11的数据导入到 /tmp/output 下。
EXPORT TABLE employee_partitioned partition (year=2014, month=11) TO '/tmp/output';

使用IMPORT导入数据

-- 将表数据导入到 /tmp/output 文件夹中
IMPORT TABLE employee FROM '/tmp/output';
-- 将年份为2014，并且月分为11的数据导入到/tmp/output 文件夹中 
IMPORT TABLE employee_partitioned partition (year=2014, month=11) FROM '/tmp/output';