hive聚合函数之JOIN原理及案例

1.数据准备

原始数据
创建dept.txt文件，并赋值如下内容，上传HDFS。

部门编号 部门名称 部门位置id10行政部170020财务部180030教学部190040销售部1700

创建emp.txt文件，并赋值如下内容，上传HDFS。

员工编号 姓名 岗位薪资部门7369张三研发800.00307499李四财务1600.00207521王五行政1250.00107566赵六销售2975.00407654侯七研发1250.00307698马八研发2850.00307782金九\N2450.0307788银十行政3000.00107839小芳销售5000.00407844小明销售1500.00407876小李行政1100.00107900小元讲师950.00307902小海行政3000.00107934小红明讲师1300.0030

2.创建部门表

create table if not exists dept(deptno int,-- 部门编号dname string,-- 部门名称loc int-- 部门位置)row format delimited fields terminated by '\t';

创建员工表

create table if not exists emp(empno int,-- 员工编号ename string, -- 员工姓名job string, -- 员工岗位（大数据工程师、前端工程师、java工程师）sal double, -- 员工薪资deptno int-- 部门编号)row format delimited fields terminated by '\t';

导入数据

load data inpath 'hdfs://flinkv1:8020/input/dept.txt' into table dept;load data inpath 'hdfs://flinkv1:8020/input/emp.txt' into table emp;

2.等值Join

Hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接。
案例实操
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称。

selecte.empno,e.ename,d.dnamefrom emp e join dept don e.deptno = d.deptno;

3.表的别名

好处
（1）使用别名可以简化查询。
（2）区分字段的来源。
案例实操
合并员工表和部门表。

select e.*,d.* from emp e join dept d on e.deptno = d.deptno;

4.内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

selecte.empno,e.ename,d.deptnofrom emp e join dept don e.deptno = d.deptno;

5.左外连接

左外连接：join操作符左边表中符合where子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

6.右外连接

右外连接：join操作符右边表中符合where子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

7.满外连接

满外连接：将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

8.多表连接

注意：连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

创建location.txt文件，并赋值如下内容，上传HDFS。

部门位置id部门位置1700北京1800上海1900深圳

create table if not exists location(loc int, -- 部门位置idloc_name string -- 部门位置)row format delimited fields terminated by '\t';

load data inpath 'hdfs://flinkv1:8020/input/location.txt' into table location;

大多数情况下，Hive会对每对join连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

9.笛卡尔集

笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

10.联合（union & union all）

union&union all上下拼接
union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。
union和union all在上下拼接sql结果时有两个要求：
（1）两个sql的结果，列的个数必须相同
（2）两个sql的结果，上下所对应列的类型必须一致

案例实操
将员工表30部门的员工信息和40部门的员工信息，利用union进行拼接显示。

select *from empwhere deptno=30unionselect *from empwhere deptno=40;