Hive--高级查询(CTE,JOIN)，装载数据(LOAD),数据交换(IMPORT,EXPORT),数据排序(ORDER BY、SORT BY、DISTIBUTE BY、CLUSTER BY )

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!='Jack'  LIMIT 4;

CTE和嵌套查询

CTE（Common Table Expression）

-- 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：当前全局文件位置

Hive JOIN - 关联查询

• 指对多表进行联合查询
• JOIN用于将两个或多个表中的行组合在一起查询
• 类似于SQL JOIN，但是Hive仅支持等值连接
  • 内连接：INNER JOIN
  • 外连接：OUTER JOIN
    • RIGHT JOIN, LEFT JOIN, FULL OUTER JOIN（左外联,右外联,全联）
  • 交叉连接：CROSS JOIN
  • 隐式连接：Implicit JOIN
• JOIN发生在WHERE子句之前

装载数据：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);

• LOCAL：指定文件位于本地文件系统
• OVERWRITE表示覆盖现有数据

装载数据：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 t(x,y,z)
• INSERT INTO table_name VALUES，支持插入值列表
• 数据插入必须与指定列数相同

INSERT OVERWRITE TABLE test select 'hello'; -- INSERT不支持的写法
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)-- 典型的ETL模式
select *,'2018','09';
-- 通过指定列插入(insert into可以省略table关键字)
insert into employee(name) select 'John' from test limit 1;-- 指定列有利于 data schema changes
-- 通过指定值插入
insert into employee(name) value('Judy'),('John');

Hive数据插入文件

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

• 文件插入只支持OVERWRITE
• 支持来自同一个数据源/表的多次插入
• LOCAL：写入本地文件系统
• 默认数据以TEXT格式写入，列由^A分隔
• 支持自定义分隔符导出文件为不同格式,CSV,JSON等

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

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';

Hive数据排序 - 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;

select from 在mysql中，中间的是 投影， 会查到全部数据，但是只显示投影

select from order by 在 hive 中 ， 执行这种查询（涉及到 统计，排序 ）相当于 进行MapReduce操作 , 一执行 语句 ，MapReduce启动，就要通过select from 中间的值告诉MapReduce，需要聚合的是什么，相当于是 hash 键值对的键

order by 后面跟的数值 和select from中间的组合起来理解 from 后面相当于map ，生成键值对，根据条件进行分区操作（partition）,进行了map操作 ，hash，进行分区，后面的 reduce 的对象得是这个键，不然就相当于在这个MapReduce进程中停止

SORT BY

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

• 当Reducer数量设置为1时，等于ORDER BY
• 排序列必须出现在SELECT column列表中

DISTRIBUTE BY

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;--默认ASC,正序-DESC,倒序

CLUSTER BY

CLUSTER BY = DISTRIBUTE BY + SORT BY

• 不支持ASC|DESC
• 排序列必须出现在SELECT column列表中
• 为了充分利用所有的Reducer来执行全局排序，可以先使用CLUSTER BY，然后使用ORDER BY
• cluster by = distribute by+sort by