在使用hive进行数据加工处理时，经常要进行两个数据表join关联，接下来具体介绍hive sql中要使用的join。
本实验用到a表和b表，表结构如下：
hive> desc a;
OK
id string
name string
Time taken: 0.094 seconds, Fetched: 2 row(s)
hive> select * from a;
OK
1 zhangsan
2 lisi
3 wangwu
Time taken: 0.116 seconds, Fetched: 3 row(s)
hive> desc b;
OK
id string
age int
Time taken: 0.159 seconds, Fetched: 2 row(s)
hive> select * from b;
OK
1 30
2 29
4 21
Time taken: 0.09 seconds, Fetched: 3 row(s)
join（内关联,默认是inner join）
选出两个表中都有的值进行关联。
在使用join时，数据状况是有一个大表和小表，则可以使用mapjoin，需要将小表放到 sql的join之前，执行sql时会将小表广播预加载到内存中，这样在进行join时，省略掉常规关联所需的reduce过程。即使对于很小的数据集，这个优化也明显地要快于常规的连接操作。其不仅减少了reduce过程，而且有时还可以同时减少Map过程的执行步骤。在hive 0.11.0版本之前需要开启这项优化set hive.auto.convert.join = true。而小文件大小定义是通过hive.mapjoin.smalltable.filesize设置，默认是256m
SELECT a.id,
a.name,
b.age
FROM a
join b
ON (a.id = b.id);
--执行结果
1 zhangsan 30
2 lisi 29
LEFT [OUTER] JOIN（左外关联）
以左表为基础，如果右侧表可以和左侧表关联，则值为右侧表值，否则为NULL
SELECT a.id,
a.name,
b.age
FROM a
left join b
ON (a.id = b.id);
--执行结果：
1 zhangsan 30
2 lisi 29
3 wangwu NULL
RIGHT [OUTER] JOIN（右外关联）
以右表为基础，如果左侧表可以和右侧表关联，则值为左侧表值，否则为NULL
SELECT a.id,
a.name,
b.age
FROM a
RIGHT OUTER JOIN b
ON (a.id = b.id);
--执行结果：
1 zhangsan 30
2 lisi 29
NULL NULL 21
FULL [OUTER] JOIN（全关联）
两个表进行关联，如果右表没有值补充NULL、如果左边没有值补充NULL
SELECT a.id,
a.name,
b.age
FROM a
FULL OUTER JOIN b
ON (a.id = b.id);
--执行结果：
1 zhangsan 30
2 lisi 29
3 wangwu NULL
NULL NULL 21
LEFT SEMI JOIN
左边表返回key在右边表中存在的值对应的数据
SELECT a.id,
a.name
FROM a
LEFT SEMI JOIN b
ON (a.id = b.id);
--执行结果：
1 zhangsan
2 lisi
--等价于：
SELECT a.id,
a.name
FROM a
WHERE a.id IN (SELECT id FROM b);
CROSS JOIN（笛卡尔积关联）
返回两个表的笛卡尔积结果，不需要指定关联键
SELECT a.id,
a.name,
b.age
FROM a
CROSS JOIN b;
--执行结果：
1 zhangsan 30
1 zhangsan 29
1 zhangsan 21
2 lisi 30
2 lisi 29
2 lisi 21
3 wangwu 30
3 wangwu 29
3 wangwu 21
网友评论