Apache Hive高级查询

一、Hive的数据查询（select）

select用于映射符合指定查询条件的行

Hive SELECT是数据库标准SQL的子集

使用方法类似于MySQL

SELECT 1;
SELECT [DISTINCT] column_nam_list FROM table_name;
SELECT * FROM table_name;
SELECT * FROM employee WHERE name!='Lucy'  LIMIT 5;

CTE和嵌套查询

-- CTE语法
WITH t1 AS (SELECT …) SELECT * FROM t1
-- 嵌套查询示例
SELECT * FROM (SELECT * FROM employee) a;

进阶语句

列匹配正则表达式

SET hive.support.quoted.identifiers = none;
SELECT `^o.*` FROM offers;	

虚拟列（Virtual Columns）

INPUT__FILE__NAME：Mapper Task的输入文件名称
BLOCK__OFFSET__INSIDE__FILE：当前全局文件位置

二、JOIN - 关联查询

指对多表进行联合查询

JOIN用于将两个或多个表中的行组合在一起查询

类似于SQL JOIN，但是Hive仅支持等值连接

内连接：INNER JOIN

外连接：OUTER JOIN RIGHT JOIN, LEFT JOIN, FULL OUTER JOIN

交叉连接：CROSS JOIN

隐式连接：Implicit JOIN

Area C = Circle1 JOIN Circle2
Area A = Circle1 LEFT OUTER JOIN Circle2
Area B = Circle1 RIGHT OUTER JOIN Circle2
AUBUC = Circle1 FULL OUTER JOIN Circle2

mapjoin 在Map端完成

小表关联大表   可进行不等值连接

set hive.auto.convert.join = true（默认值）
运行时自动将连接转换为MAPJOIN

三、集合操作union

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

UNION ALL：合并后保留重复项

UNION：合并后删除重复项（v1.2之后)

装载数据：LOAD移动数据

LOAD用于在Hive中移动数据

原始数据被移动到目标表/分区，不再存在于原始位置

LOAD DATA LOCAL INPATH '/home/dayongd/Downloads/employee.txt' 
OVERWRITE INTO TABLE employee;
-- LOCAL表示文件位于本地，OVERWRITE表示覆盖现有数据
LOAD DATA LOCAL INPATH '/home/dayongd/Downloads/employee.txt' 
OVERWRITE INTO TABLE employee_partitioned  PARTITION (year=2014, month=12);
-- 没有LOCAL,文件位于HDFS文件系统中
LOAD DATA INPATH '/tmp/employee.txt'  
OVERWRITE INTO TABLE employee_partitioned PARTITION (year=2017, month=12）

装载数据：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 table_name VALUES，支持插入值列表

Hive数据插入文件

使用insert语句将数据插入/导出到文件

-- 从同一数据源插入本地文件，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导出数据

EXPORT TABLE employee TO '/tmp/output3';
EXPORT TABLE employee_partitioned partition (year=2014, month=11) TO '/tmp/output5';

使用IMPORT导入数据

IMPORT TABLE employee FROM '/tmp/output3';
IMPORT TABLE employee_partitioned partition (year=2014, month=11) FROM '/tmp/output5';

数据排序 - ORDER BY

ORDER BY (ASC|DESC)类似于标准SQL

只使用一个Reducer执行全局数据排序

速度慢,应提前做好数据过滤

支持使用CASE WHEN或表达式 支持按位置编号排序

set hive.groupby.orderby.position.alias=true;
select * from offers order by case when offerid = 1 then 1 else 0 end;
select * from offers order by 1;

SORT BY/DISTRIBUTE BY

ORT BY对每个Reducer中的数据进行排序

当Reducer数量设置为1时，等于ORDER BY

排序列必须出现在SELECT column列表中

DISTRIBUTE BY类似于标准SQL中的GROUP BY

确保具有匹配列值的行被分区到相同的Reducer

不会对每个Reducer的输出进行排序

通常使用在SORT BY语句之前

SELECT department_id , name, employee_id, evaluation_score
FROM employee_hr 
DISTRIBUTE BY department_id SORT BY evaluation_score DESC;

CLUSTER BY

CLUSTER BY = DISTRIBUTE BY + SORT BY

不支持ASC|DESC

排序列必须出现在SELECT column列表中

为了充分利用所有的Reducer来执行全局排序，可以先使用CLUSTER BY，然后使用ORDER BY

聚合函数类似于mysql 就不多介绍

窗口函数是一组特殊函数

扫描多个输入行来计算每个输出值，为每行数据生成一行结果 可以通过窗口函数来实现复杂的计算和聚合

Function (arg1,..., arg n) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_clause>])

PARTITION BY类似于GROUP BY，未指定则按整个结果集

只有指定ORDER BY子句之后才能进行窗口定义

可同时使用多个窗口函数

过滤窗口函数计算结果必须在外面一层

按功能可划分为：排序，聚合，分析

排序窗口函数 

ROW_NUMBER()    RANK()    DENSE_RANK()    PERCENT_RANK()   NTILE(2)

聚合函数

count()    sum()    avg()    min()    max()   

分析函数

lead()  lead lag first_value last_value 

聚合函数作为窗口函数的用法
聚合函数 over (partition by  order by )

窗口子句包括哪几部分定义，用什么固定写法
窗口子句只能作用于非窗口函数
一般作用于聚合函数指定over前面的函数的作用范围
rows between 开始 and  结束
range between 开始 and 结束
开始：unbounded  preceding  current row  n preceding   n following  
结束：current row n preceding n following unbounded