等值JOIN

Hive支持通常的SQL JOIN语句，==但是只支持等值连接，不支持非等值连接。== 案例操作（1）查询分数对应的姓名

SELECT s.s_id,s.s_score,stu.s_name,stu.s_birth  
FROM score s LEFT JOIN student stu ON s.s_id = stu.s_id;

表的别名

1）好处（1）使用别名可以简化查询。（2）使用表名前缀可以提高执行效率。 2）案例实操合并老师与课程表

select * from techer t join course c on t.t_id = c.t_id;

内连接（INNER JOIN）

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

select * from techer t inner join course c on t.t_id = c.t_id;

左外连接（LEFT OUTER JOIN）

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

查询老师对应的课程

select * from techer t left join course c on t.t_id = c.t_id;

右外连接（RIGHT OUTER JOIN）

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

select * from techer t right join course c on t.t_id = c.t_id;

满外连接（FULL OUTER JOIN）

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

SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;

多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生

select * from techer t 
left join course c 
on t.t_id = c.t_id
left join score s 
on s.c_id = c.c_id
left join student stu 
on s.s_id = stu.s_id;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表techer和表course进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表score;进行连接操作。

排序

全局排序（Order By）

Order By：全局排序，一个reduce 1）使用 ORDER BY 子句排序 ASC（ascend）: 升序（默认） DESC（descend）: 降序 2）ORDER BY 子句在SELECT语句的结尾。 3）案例实操（1）查询学生的成绩，并按照分数降序排列

SELECT * FROM student s LEFT JOIN 
score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;

（2）查询学生的成绩，并按照分数升序排列

SELECT * FROM student s LEFT JOIN score sco 
ON s.s_id = sco.s_id ORDER BY sco.s_score asc;

按照别名排序

按照分数的平均值排序

select s_id ,avg(s_score) avg from score group by s_id order by avg;

多个列排序

按照学生id和成绩进行排序

select s_id ,avg(s_score) avg from score 
group by s_id order by s_id,avg;

每个MapReduce内部排序（Sort By）局部排序

Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。 1）设置reduce个数

set mapreduce.job.reduces=3;

2）查看设置reduce个数

set mapreduce.job.reduces;

3）查询成绩按照成绩降序排列

select * from score sort by s_score;

4）将查询结果导入到文件中（按照成绩降序排列）

insert overwrite local directory 
'/export/servers/hivedatas/sort' select * from score sort by s_score;

分区排序（DISTRIBUTE BY）

Distribute By：类似MR中partition，进行分区，结合sort by使用。注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。案例实操：（1）先按照学生id进行分区，再按照学生成绩进行排序。设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去

set mapreduce.job.reduces=7;

通过distribute by 进行数据的分区

insert overwrite local directory 
'/export/servers/hivedatas/sort' select * from score distribute by s_id sort by s_score;

CLUSTER BY

当distribute by和sort by字段相同时，可以使用cluster by方式。 cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。 1）以下两种写法等价

select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

Hive_JOIN语句与排序