一、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阶段
- MapTask根据Inputsplit逻辑块去获取数据。文件读取时通过TextinputFormat(–> RecordReader -->read())一次读一行的方式返回(key,value)键值对。
- 获取到的(key,value)键值对经过Mapper中的的map方法逻辑处理形成(k,v)键值对(注:k:行的起始偏移量,v行的内容),再通过context.write方法将其写出
- OutPutCollector收集器会接收到(k,v)键值对,并将其写入到环形缓冲区。
- 环形缓冲区会存储这些键值对,当缓冲区存储超过设定的80%,将会发生溢写现象,将会触发spill溢出。
- 在spill溢出之前还会存在对这些键值对的处理,会将环形缓冲区里面的每个键值对的hash值进行取余运算,得到partition值,通过partition值分区,相同的分到一个区,同一partition内的按照key进行快速排序,外部排序。
- combiner阶段会将环形缓冲区中排序后的内存数据不断spill溢出到本地磁盘文件,如果map阶段处理的数据量较大,可能会溢出多个文件,这些多个文件会被merge合并成大的溢出文件,采用的是归并排序。maptask最终结果文件还是分区且区内有序的文件(局部归并)。
Reduce阶段
- reduce task根据自己的分区号,去各个map task节点上copy相同partition值的数据到reduce task本地磁盘工作目录;
- reduce task会把同一分区的来自不同的map task的结果文件,再进行merge合并成一个大文件(归并排序),大文件内容按照k有序;
- 合并成大文件后,shuffle过程也就结束了,后面进行reduce task的逻辑运算过程,首先调用GroupingComparator对大文件里面的数据进行分组,从文件中每次取出一组(k,values)键值对,调用用户自定义的reduce()方法进行逻辑处理;
- 最后通过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';