hive join查询

数据准备

• 首先准备两张表 员工表和部门表
  员工表数据 表示员工姓名和所在部门

shy 销售
zoom 销售
penut 销售
jack 测试
ning 测试
tom 开发
duke 开发
jkl 财务
rookie 财务

• 部门表数据 表示部门名称和部门负责人

销售 马云
测试 马化腾
开发 李彦宏
算法 雷军

• 在hive中创建两张表

-- 员工表
create external table person
(
   name string,
   dep string
) row format delimited fields terminated by ' ';
-- 部门表
create external table dept
(
   dep string,
   own string
) row format delimited fields terminated by ' ';

• 加载数据

load data local inpath '/root/user.txt' overwrite table person;
load data local inpath '/root/dept.txt' into table dept;

笛卡尔积

• 首先我们试一下，直接通过join将两个表连接进行查询 select p.name,p.dep,d.own from person p join dept d;
• 可以发现取的是两个表的笛卡尔积，后面的各种join其实就是以多种不同的方法从笛卡尔积里面筛选数据。

shy     销售    马云
shy     销售    马化腾
shy     销售    李彦宏
shy     销售    雷军
zoom    销售    马云
zoom    销售    马化腾
zoom    销售    李彦宏
zoom    销售    雷军
penut   销售    马云
penut   销售    马化腾
penut   销售    李彦宏
penut   销售    雷军
jack    测试    马云
jack    测试    马化腾
jack    测试    李彦宏
jack    测试    雷军
ning    测试    马云
ning    测试    马化腾
ning    测试    李彦宏
ning    测试    雷军
tom     开发    马云
tom     开发    马化腾
tom     开发    李彦宏
tom     开发    雷军
duke    开发    马云
duke    开发    马化腾
duke    开发    李彦宏
duke    开发    雷军
jkl     财务    马云
jkl     财务    马化腾
jkl     财务    李彦宏
jkl     财务    雷军
rookie  财务    马云
rookie  财务    马化腾
rookie  财务    李彦宏
rookie  财务    雷军

等值join（内连接）

• Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。等值连接就是查出两个表某个字段值相等的数据，两个表的空数据都会去除。
• 将两个表根据部门进行等值join 查询每个员工名称 部门以及它对应部门负责人的名称

select p.name,p.dep,d.own  from person p  join dept d on p.dep=d.dep;

• 可以发现原本表person中财务部门是独有的，表dept中算法部门是独有的，经过等值连接，财务部门和算法部门相关数据都去除了，说明等值join取的是交。

shy     销售    马云
zoom    销售    马云
penut   销售    马云
jack    测试    马化腾
ning    测试    马化腾
tom     开发    李彦宏
duke    开发    李彦宏

左外连接

• 左外连接：JOIN操作符左边表中符合连接条件的所有记录将会被返回。 右边表有匹配数据就输出，没有就补null。

select p.name,p.dep,d.own  from person p left join dept d on p.dep=d.dep;

• 此时person表的财务部门信息是有的，而dept表的算法部门就没有了

shy     销售    马云
zoom    销售    马云
penut   销售    马云
jack    测试    马化腾
ning    测试    马化腾
tom     开发    李彦宏
duke    开发    李彦宏
jkl     财务    NULL
rookie  财务    NULL

右外连接

• 右外连接：JOIN操作符右边表中符合连接条件的所有记录将会被返回。 左边表有匹配数据就输出，没有就补null。
  select p.name,p.dep,d.own from person p right join dept d on p.dep=d.dep;

• 此时person表的财务部门信息没有了，而dept表的算法部门信息保留

shy     销售    马云
zoom    销售    马云
penut   销售    马云
jack    测试    马化腾
ning    测试    马化腾
tom     开发    李彦宏
duke    开发    李彦宏
NULL    NULL    雷军

全外连接

• 全外连接中符合查询条件的两个表数据无论是否为空都会保留

select p.name,p.dep,d.own  from person p out join dept d on p.dep=d.dep;

• 结果如下，两边都可能会补null

ning    测试    马化腾
jack    测试    马化腾
NULL    NULL    雷军
rookie  财务    NULL
jkl     财务    NULL
penut   销售    马云
zoom    销售    马云
shy     销售    马云
duke    开发    李彦宏
tom     开发    李彦宏

多表连接

• 注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
• 一般情况下，hive会给每个join对象启动一个mapreduce job进行执行，但是对于3个及以上的多表join,join有所优化，如果每个join的子句on里的连接键一样，mr会启动一个job完成所有表的join.此外，多表进行join时，一般要把大表放到最后面，这样可以提高效率。