Hive查询Join
Select a.val,b.val From a [Left|Right|Full Outer] Join b On (a.key==b.key);
现有两张表：sales 列出了人名及其所购商品的 ID；things 列出商品的 ID 和名称：
hive> select * from sales;
OK
Joe 2
Hank 4
Ali 0
Eve 3
Hank 2
Time taken: 0.085 seconds, Fetched: 5 row(s)
hive> select * from things;
OK
2 Tie
4 Coat
3 Hat
1 Scarf
Time taken: 0.069 seconds, Fetched: 4 row(s)
1.内连接
Hive只支持等值连接，这意味着在 ON 关键字后的表达式中只能使用等号。具体JAVA-API实现详见：MR案例：内连接
hive> select sales.*,things.*
> from sales JOIN things ON(sales.id = things.id);
Joe 2 2 Tie
Hank 4 4 Coat
Eve 3 3 Hat
Hank 2 2 Tie
此外还可以在Where子句中指定连接条件。
hive> select sales.*,things.*
> from sales,things
> where sales.id = things.id;
OK
Joe 2 2 Tie
Hank 4 4 Coat
Eve 3 3 Hat
Hank 2 2 Tie
单个的连接用一个 MR 作业实现。但是，如果多个连接的连接条件中使用了相同的列，那么平均每个连接可以至少用一个 MR 作业来实现。可以在查询前使用 Explain关键字 来查看 Hive将为某个查询使用多少个 MR 作业：【此部分在以后详述】
hive> explain
> select sales.*,things.*
> from sales join things on (sales.id=things.id);
2.外连接
外连接可以让你找到连接表中不能匹配的数据行。前面的内连接，【Ali】那一行没有出现在输出中。因为她所购买商品的ID没有在things表中出现。具体JAVA-API实现详见MR案例：外连接
左外连接：就可以显示左边表的所有数据行：T_Name1 LEFT OUTER JOIN T_Name2 ON ()
hive> select sales.*,things.*
> from sales LEFT OUTER JOIN things ON(sales.id = things.id);
OK
Joe 2 2 Tie
Hank 4 4 Coat
Ali 0 NULL NULL
Eve 3 3 Hat
Hank 2 2 Tie
Time taken: 13.387 seconds, Fetched: 5 row(s)
右外连接：T_Name1 RIGHT OUTER JOIN T_Name2 ON ()
hive> select sales.*,things.*
> from sales RIGHT OUTER JOIN things ON(sales.id = things.id);
OK
Joe 2 2 Tie
Hank 2 2 Tie
Hank 4 4 Coat
Eve 3 3 Hat
NULL NULL 1 Scarf
Time taken: 14.54 seconds, Fetched: 5 row(s)
全外连接：T_Name1 FULL OUTER JOIN T_Name2 ON ()
hive> select sales.*,things.*
> from sales FULL OUTER JOIN things ON(sales.id = things.id);
OK
Ali 0 NULL NULL
NULL NULL 1 Scarf
Hank 2 2 Tie
Joe 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
Time taken: 44.671 seconds, Fetched: 6 row(s)
半连接：T_Name1 LEFT SEMI JOIN T_Name2 ON ()
hive> select * from things
> where things.id in
> (select id from sales);
OK
2 Tie
4 Coat
3 Hat
Time taken: 15.633 seconds, Fetched: 3 row(s)
In查询可以转化为 半连接查询。必须遵循一个限制：右表(sales)只能在ON子句中出现。
hive> select * from things
> LEFT SEMI JOIN sales ON(sales.id=things.id);
OK
2 Tie
4 Coat
3 Hat
Time taken: 13.169 seconds, Fetched: 3 row(s)
3.Map-side Join 具体JAVA-API实现详见MR案例：Map-Join
- Join操作在 map 阶段完成，因此无需 reduce 阶段
- 适合 一个大表，一个小表 的 Join 操作
- 思想：小表复制到各个节点上，并加载到内存中；而对大表进行分片，每个分片与小表完成 Join 操作
select /*+ mapjoin(things) */ sales.*,things.* from sales join things on sales.id=things.id; //等同于 select sales.*,things.* from sales join things on sales.id=things.id;
- hive 0.6 的时候默认认为写在select 后面的是大表，前面的是小表，或者使用 /*+mapjoin(map_table) */ 手工进行设定。
- hive 0.7 以后这个计算是自动完成，设置 hive.auto.convert.join=true ，hive会自动判断哪个是小表，哪个是大表。判断小表的依据是hive.smalltable.filesize=25000000L(默认是25M)，当小表超过这个大小，hive会自动转化成common join，即reduce-join。
4.Reduce-side Join 具体JAVA-API实现详见MR案例：Reduce-Join
- Join操作在reduce task中完成 【默认的join方式】
- 适合两个大表连接操作
- 思想：map端按照连接字段进行hash，reduce 端完成连接操作
5.用于多于2个表的Join(有区别)
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1);
如果 Join 的 key 相同，不管有多少个表，都会则会合并为一个 Map-Reduce
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2);
如果 Join 的条件不相同，Map-Reduce 的任务数目和 Join 操作的次数是相对应.(本例2次)
6.Join每次MR任务的逻辑
reducer 会缓存 join 序列中除了最后一个表的所有表的记录， 再通过最后一个表将结果序列化到文件系统。 这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。例如：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1);
使用 1 次MR任务，reduce 端会缓存 a 表和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 join 结果。
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2);
使用 2 次MR任务，第一次缓存 a 表，用 b 表序列化；第二次缓存第一次 map/reduce 任务的结果，然后用 c 表序列化。
