HIVE --- 高级查询

目录

CTE和嵌套查询

嵌套查询

关联查询（join）

MapJoin

MapJoin操作在Map端完成

开启MapJoin操作

MAPJOIN不支持的操作

union

数据交换（import/export）

数据排序

order by

sort by

distribute  by

cluster by

---

CTE和嵌套查询

-- CTE语法
WITH t1 AS (SELECT …) SELECT * FROM t1;
-- CTE演示
with tab1 as (select id,name,age from people) 
select * from tab1;

嵌套查询

-- 嵌套查询示例
SELECT * FROM (SELECT * FROM employee) ;

关联查询（join）

指对多表进行联合查询

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

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

JOIN发生在WHERE子句之前

内连接：inner join

外连接：outer join

        right join, left join, full outer join

交叉连接：cross join

隐式连接：Implicit join

MapJoin

MapJoin操作在Map端完成

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

开启MapJoin操作

set hive.auto.convert.join = true（默认值）

运行时自动将连接转换为MapJoin

MAPJOIN不支持的操作

在union all, lateral view, group by/join/sort by/cluster by/distribute by等操作后面

在union, join以及其他 MapJoin之前

union

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

UNION ALL：合并后保留重复项

UNION：合并后删除重复项

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

数据交换（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';

数据排序

order by

order by （asc|desc）类似于标准SQL

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

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

持使用case when或表达式

支持按位置编号排序

select employee_id,name
from employee_id
order by employee_id;

 

sort by

sort by对每个Reducer中的数据进行排序

当Reducer数量设置为1时，等于order by

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

distribute  by

distribute  by类似于标准SQL中的group by

根据相应列以及对应reduce的个数进行分发

•     默认是采用hash算法
•     根据分区字段的hash码与reduce的个数进行模除

通常使用在SORT BY语句之前

select name,skills_score,depart_title
from employee_id
distribute by depart_title sort by skills_score desc;

cluster by

cluster by=distribute  by+sort by

不支持asc|desc

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

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

SELECT name, employee_id
FROM employee_id CLUSTER BY name;