Apache Hive高级查询（1）

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 (Common Table Expression）

--CTE语法
with t as (select * from emp) select * from t;

嵌套查询

--嵌套查询示例
select * from (select * from emp) a;

列匹配正则表达式

SET hive.support.quoted.identifiers = none;
SELECT `^o.*` FROM offers;	
select `customer_(.{2}|.name|.{3}) from customers;

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

示例
c = 1 join 2
b = 1 right [outer] join 2
a = 1 left [outer] join 2
aUbUc = 1 full [outer] join 2

join - mapjoin
MapJoin操作在Map端完成
小表关联大表
可进行不等值连接
开启join操作
set hive.auto.convert.join = true（默认值）
运行时自动将连接转换为MAPJOIN
MAPJOIN操作不支持：
在union all ,lateral view,group by/join/sort by/cluster by/distribute by等操作后面
在union,join 以及其他 MAPJOIN之前

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

--union all 合并后保留重复项
select a.id from (select id from employee_id union all select id from empid) a order by a.id limit 5;
--union 合并后删除重复项（v.1.2之后）
select a.id from (select id from employee_id union select id from empid) a order by a.id limit 5;

可以在顶层查询中使用 order by ,sort by distribute by,limit适用于合并后的整个结果
集合其他操作可以使用join/outer join来实现
差集、交集

装载数据 ： load移动数据
load用于在hive中移动数据

--overwrite 表示覆盖表中原有数据
--into 表示载入数据，若表中载入之前存在数据则追加

--加载本地数据（local），指定本地文件位置，加载完后本地数据仍存在
load data local inpath '/root/employee.txt' [overwrite] into table employee;
--加载hdfs文件系统,指定hdfs系统文件位置，加载完后hdfs系统中的文件也会相应的的移动到新的位置
load data inpath '/opt/hive/warehouse/hivetest/employee/employee.txt [overwrite] into table emp;

装载数据 ： insert 表插入数据
使用insert 语句将数据插入表、分区

--inset 支持overwrite（覆盖）、into（追加）
insert into/overwrite employee [partition(year=2020,month=9)] values ...
--insert into可以指定插入哪些字段，指定的字段必须与values后插入值对应，类型、列数也需一致
insert into employee(id,name...)

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 *;
-- 插入到分区（典型的ELT模式）
from ctas_patitioned 
insert overwrite table employee PARTITION (year, month)
select *,'2018','09';
-- 通过指定列插入(insert into可以省略table关键字)
--指定列有利于 data schema changes
insert into employee(name) select 'John' from test limit 1;
-- 通过指定值插入
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 (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对每个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;
--默认ASC正序，DESC倒序

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

SELECT name, employee_id FROM employee_hr CLUSTER BY name;