Hive 进阶篇

1. CTE与CTAS 语法

1.1 CTE语句

公用表表达式（CTE）是一个临时结果集，该结果集是从with子句中指定的简单查询派生而来的，该查询紧接在select或insert关键字之前。

-- 基本CTE
with new_table as (select * from student where sex='male')
select name,age,money,sex from new_table;

-- from前置CTE
with new_table as (select * from student where sex='female')
from new_table select name,age,money,sex;

-- 链式调用的CTE
with new_tb1 as (select name,age,sex,money from student where money>=5000),
    new_tb2 as (select *,count(1) over() from new_tb1 where sex='female')
select * from new_tb2;

-- 结合union的CTE
with new_tb1 as (select * from student where sex='male' and money>5000),
    new_tb2 as (select * from student where sex='female' and money>5000)
select name,age,money,sex from new_tb1
union
select name,age,money,sex from new_tb2;

1.2 CTAS语句

CTAS是利用CTE产生的结果集来创建的新表，建表的数据来源于select。

-- 基本CTAS
create table if not exists student_ctas
as
with new_tb as (select * from student where money<5000)
select * from new_tb;

-- 上面的语句等价于下面这个
create table student_ctas_test like student;
with new_tb as (select * from student where money<5000)
insert into student_ctas_test
select * from new_tb;

2. join 连接

在Hive中共支持6种join语法。分别是：
inner join（内连接）、left join（左连接）、right join（右连接）、full outer join（全外连接）、left semi join（左半开连接）、cross join（交叉连接，也叫做笛卡尔乘积）。

2.0 表的创建

-- 职工表
create table employee(
    id int comment '职工id',
    name string comment '名字',
    level string comment '职务',
    salary int comment '薪资',
    dept string comment '部门'
)row format delimited fields terminated by ',';

-- 职工地址信息表
create table employee_add(
    id int comment '职工id',
    homeno string comment '具体住所',
    area string comment '居住地方',
    city string comment '居住城市'
)row format delimited fields terminated by ',';

-- 职工联系方式表
create table employee_conn(
    id int comment '职工id',
    phno string comment '电话',
    email string comment '邮箱'
)row format delimited fields terminated by ',';

2.1 内连接

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。内连接是最常见的一种连接，它也被称为普通连接或自然连接。其中inner可以省略，inner join == join。

-- 以下三种都是内连接的使用方法
select * from employee inner join employee_add on employee.id =employee_add.id;
select * from employee join employee_add on employee.id =employee_add.id;
-- 隐式调用
select * from employee t1 ,employee_add t2  where t1.id=t2.id;

2.2 左右连接

left join中文叫做是左外连接(Left Outer Jion)或者左连接，其中outer可以省略，left outer join是早期的写法。left join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。

-- 左连接
select * from employee e left outer join employee_add ea on e.id=ea.id;
select * from employee e left join employee_add ea on e.id=ea.id;

right join中文叫做是右外连接(Right Outer Jion)或者右连接，其中outer可以省略。right join时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回 。

-- 右连接
select * from employee e right outer join employee_add ea on e.id=ea.id;
select * from employee e right join employee_add ea on e.id=ea.id;

2.3 全外连接

full outer join 等价 full join ,中文叫做全外连接或者外连接。包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的操作将上述两个结果集合并为一个结果集。

-- 全外连接
select * from employee e full outer join employee_add ea on e.id=ea.id;
select * from employee e full join employee_add ea on e.id=ea.id;

2.4 左半开连接

left semi join 会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件。从效果上来看有点像inner join之后只返回左表的数据。

-- 左半开连接
select * from employee e left semi join employee_add ea on e.id=ea.id;

2.5 交叉连接

cross join ，将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。对于大表来说，cross join慎用。在SQL标准中定义的cross join就是无条件的inner join。在HiveSQL语法中，cross join 后面可以跟where子句进行过滤，或者on条件过滤。

-- 交叉连接
--隐式交叉连接
select * from employee t1 ,employee_add t2;
select * from employee e cross join employee_add;   -- 25条数据
select * from employee e cross join employee_add ea on e.id=ea.id;  -- 4条数据

笛卡尔积 ：
例如，A={a,b}, B={0,1,2}，则
A×B = { (a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2) }
B×A = { (0, a), (0, b), (1, a), (1, b), (2, a), (2, b) }

2.6 Hive join 使用注意事项

这里的特效针对Hive 3.1版以上的。

1. 使用复杂的联接表达式

   select b.* from a join b on (a.id =