Day1 hive join语句与排序

1 join

• Hive 支持通常的SQL JOIN语句，但是只支持等值连接,Hive不支持ON字句中的谓词间使用OR

-- 对于非等值连接:不支持
select * from teacher t inner join course c  on t.t_id > c.t_id and t.course=c.course;
-- 对于非等值连接:不支持
select a.ymd,a.price_close,b.price_close
from stocks a join stocks b on a.ymd<=b.ymd
where a.symbol='AAPL' and b.symbol='IBM';
-- Oracle与MySQL都支持join on like
SELECT *  FROM table1 LEFT JOIN table2 ON table1.xxx LIKE CONCAT('%',table2.yyy,'%') 

1 INNER JOIN(内连接)

• 表示进行连接的两个表中都存在于连接标准相匹配的数据才会被保留下来

select * from teacher t inner join course c  on t.t_id = c.t_id and t.course=c.course;
-- 等价于
select * from teacher t join course c  on t.t_id = c.t_id and t.course=c.course;

• 多表连接

select a.ymd,a.price_close,b.price_close,c.price_close
from stocks a join stocks b on a.ymd<=b.ymd
			  join stocks c on a.ymd<=c.ymd
where a.symbol='AAPL' and b.symbol='IBM' and c.symbol='GE';

大多数情况下，Hive会对每对join连接对象启动一个MapReduce任务，上例中，会首先启动一个MapReduce job对表a,b进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表c进行连接操作。
Q：为什么用a.ymd<=c.ymd？
当对3个或者更多表进行join连接是，如果每个ON子句都使用相同的连接键的话，那么只会产生一个MapReduce job。HIVE总是按照从左到右的顺序执行的，HIVE同时假定查询中最后一个表是最大的那个表。在对每行记录进行连接操作时，他会尝试将其他表缓存起来，然后扫描最后那个表进行计算，因此，JOIN优化需要保证连续查询中的表的大小从左到右是依次增加的。

2 LEFT OUTER JOIN左外连接

等同于 LEFT JOIN。表示操作符左边表符合where子句的所有记录都会返回，而右表没有符合ON后面的连接条件的记录，右边表指定选择的列的值都是NULL。

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s left outer join dividend 
	on s.ymd=d.ymd and s.symbol=d.symbol
where s.symbol='AAPL';

• where 子句增加分区过滤器可以加快查询速度

3 RIGHT OUTER JOIN 右外连接

• 返回右表所有符合where语句的记录。坐标不匹配的字段值用NULL代替

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s right outer join dividend 
	on s.ymd=d.ymd and s.symbol=d.symbol
where s.symbol='AAPL';

4 FULL OUTER JOIN全外连接

• 返回所有表中符合where语句条件的所有记录，如果任一表的指定字段没有符合条件的值的话，那么就用NULL值代替。

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s full outer join dividend 
	on s.ymd=d.ymd and s.symbol=d.symbol
where s.symbol='AAPL';

5 LEFT SEMI-JOIN（左半开连接）

• 返回左边表的记录，前提是其记录对于右边表满足on语句中的判定条件，HIVE中没有 RIGHT SEMI-JOIN.
• HIVE 不支持的查询

-- 查找出d表中ymd，symbol及其对应的开盘价
select s.ymd,s.symbol,s.price_close from stocks s 
where s.ymd,s.symbol in 
(select d.ymd,d.symbol from dividend d);

• 正确方法

select s.ymd,s.symbol,s.price_close 
from stocks s left semi join dividend d on s.ymd=d.ymd and s.symbol=d.symbol;

• semi join 比通常的inner join更加高效

6 JOIN笛卡尔积

• 返回表a每一行与表b的映射
• 如果使用了错误的连接会导致一个执行时间长、运行缓慢的笛卡尔积查询，如下例中，在很多数据库中会被优化成内连接，但在HIVE中没有优化：

select *
from stocks join dividends 
where stocks.symbol=dividends.symbol and stocks.symbol='AAPL';

2 ORDER BY

• 同一般的SQL一样，对查询结果集执行一个全局排序，表示有一个所有的数据都通过一个reducer进行处理的过程。

select s.ymd,s.symbol,s.price_close 
from stocks s 
order by s.ymd asc, s.symbol asc;

3 SORT BY

• 先指定reducer的数量，只会在每个reducer中对数据进行排序，也就是执行一个局部排序过程
• 根据mapreduce.job.reduces的值，生成指定数量的ReduceTask，对其进行排序，不是全局排序。

--设置reduce个数，生成5个reduce
set mapreduce.job.reduces = 5;
-- 查看设置reduce个数
set mapreduce.job.reduces;

select s.ymd,s.symbol,s.price_close 
from stocks s 
sort by s.ymd asc, s.symbol asc;

-- 将查询结果导入到文件中（按照员工编号降序排序）
insert overwrite local directory '/home/hadoop/datas/sortby-result' 
select * from employInfo sort by employID desc;

4 DISTRIBUTE BY(含有SORT BY)

• DISTRIBUTE BY保证具有相同股票交易码的记录会分发到同一个reducer中进行处理，sort by按照我们的期望对数据进行排序
• DISTRIBUTE BY必须在SORT BY前面
• 进行分区，类似于MapReduce的partition，可以结合sort by使用。测试的时候需要指定reduce数量
• 作用：
  在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。distribute by和sort by的常见使用场景有：
  • Map输出的文件大小不均
  • Reduce输出文件大小不均
  • 小文件过多
  • 文件超大
• 运行过程：设置reduce个数5，如果ymd个数为6，那就对ymd=1为一个reduce，ymd=2为一个reduce，ymd=5，6为一组，再在组内按照symbol排序

--设置reduce个数，生成5个reduce
set mapreduce.job.reduces = 5;

select s.ymd,s.symbol,s.price_close 
from stocks s 
distribute by s.ymd
sort by s.ymd asc, s.symbol asc;

5 CLUSTER BY

• 全局排序
• 按照 s.ymd分组，并且按照 s.ymd排序
• 不能使用降序排序

select s.ymd,s.symbol,s.price_close 
from stocks s 
cluster by s.ymd;