HIVE --- 高级查询

╰+哭是因爲堅強的太久メ 2024-03-26 13:06 92阅读 0赞

**目录**

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

![6cbf67cffc6e4c79b6d2e871b67ddb6b.png][]

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

![1b935e32ae074a198513a73a655fb0c6.png][]

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

![10fdddf0f240427ca6a204756a110d44.png][]

#### cluster by ####

**cluster by=distribute by+sort by**

> 不支持asc|desc
> 
> 排序列必须出现在select column列表中
> 
> 为了充分利用所有的Reducer来执行全局排序，可以先使用cluster by，然后使用order by
> 
> ![4e71c56937cf4e8f93c68200ce546da6.png][]

SELECT name, employee_id
    FROM employee_id CLUSTER BY name;

![0d3cd06f5a38433085c3be23d5aa1226.png][]

[6cbf67cffc6e4c79b6d2e871b67ddb6b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/6cb6f2c79e9c42429c0b8d4718f8a4de.png
[1b935e32ae074a198513a73a655fb0c6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/d2a901d78ab4472682e94e76b8812bd4.png
[10fdddf0f240427ca6a204756a110d44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c1061426e2fa4f64855a2b1179588b4c.png
[4e71c56937cf4e8f93c68200ce546da6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f5c366344b4f450f81613ff47680487b.png
[0d3cd06f5a38433085c3be23d5aa1226.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/502dcffbfcdc4b9eb9e9055a26a240dc.png