HiveQL查询操作全解(三):JOIN语句

Hive支持通常的SQL JOIN，但是支持等值连接

1、INNER JOIN

内连接中，只有进行连接的两个表中都存在于连接标准相匹配的数据才会被保留下来。比如下面的例子：

SELECT a.ymd,a.price_close,b.price_close
FROM stocks a JOIN stocks b ON a.ymd=b.ymd
WHERE a.symbol = 'APPL'  AND b.symbol ='IBM';

也可以进行多表连接：

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 = b.ymd
WHERE a.symbol = 'APPL' AND b.symbol = 'IBM" AND c.symbol ='GE';

在多表连接时，我们可以想办法对连接进行优化，HIVE嘉定查询中最后一个表是最大的那个表。在对每行记录进行连接操作时，它会尝试将其他表缓存起来，然后扫描最后那个表进行计算。因此，用户需要保证连续查询中表的大小是从左到右是依次增加的。

如果我们没有把最大的表放在最后面，我们可以显式地告诉查询优化器哪个表是大表：

SELECT /*+STREAMTABLE(a)*/ 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 = b.ymd
WHERE a.symbol = 'APPL' AND b.symbol = 'IBM" AND c.symbol ='GE';

2、LEFT OUTER JOIN

在左外连接中，JOIN操作符左边表中符合WHERE自居的所有记录将会被返回。JOIN操作符右边表中如果没有符合ON后面连接条件的记录时，那么从右边表指定选择的列的值将会是NULL。

SELECT a.ymd,a.price_close,b.price_close
FROM stocks a LEFT OUTER JOIN stocks b ON a.ymd=b.ymd
WHERE a.symbol = 'APPL'  AND b.symbol ='IBM';

3、OUTER JOIN

外连接的定义大家一定都知道啦，不过有一个问题值得我们讨论。在WHERE自居中增加分区过滤器可以加快查询速度，我们先看如下的代码:

SELECT s.ymd,s.symbol,s.price_close,d.dividend
FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd=d.ymd AND  s.symbol=d.symbol
WHERE s.symbol='APPL' AND s.exchange='NASDAQ' and d.exchange='NASDAQ'

看上去是外连接，但其实结果却是内连接的结果！为什么？因为在连接的时候，会限制性JOIN语句，然后再将结果通过WHERE语句进行过滤，在到达WHERE语句时，d.exchange字段中大多数时NULL，这些当然不满足WHERE的条件，因此被过滤掉。

一个直接有效的方法是，直接在WHERE条件中去掉d.exchange='NASDAQ'，当然，这种方法不一定总令人满意。还有一个适用于所有种类连接的解决方案，那即是使用嵌套SELECT 语句，将过滤写在连接之前：

SELECT s.ymd,s.symbol,s.price_close,d.dividend FROM
(SELECT * FROM stocks WHERE symbol='AAPL' AND exchange='NASDAQ') s
LEFT OUTER JOIN
(SELECT * FROM dividends WHERE symbol='APPL' AND exchange='NASDAQ') d
ON s.ymd=d.ymd;

4、RIGHT OUTER JOIN

右外连接会返回右边表所有符合WHERE语句的记录，左表中匹配不上的字段值用NULL代替。

5、FULL OUTER JOIN

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

6、LEFT SEMI-JOIN

左半开连接会返回左边表的记录，前提是其记录对于右边表满足ON语句中的判定条件。
Hive中不支持以下查询：

SELECT s.ymd,s.symbol,s.price_close FROM stocks s
WHERE s.ymd,s.symbol IN
(SELECT d.ymd,d.symbol FROM dividends d);

不过用户可以通过LEFT SEMI JOIN实现同样的效果

SELECT s.ymd,s.symbol,s.price_close
FROM stocks s LEFT SEMI JOIN dividends d ON s.ymd=d.ymd AND s.symbol = d.symbol;

SEMI-JOIN比通常的INNER JOIN要更高效，原因是对于左表中一条指定的记录，在右边表一旦找到匹配的记录，Hive就会立刻停止扫描。从这点来看，左边表中选择的列是可以预测的。

7、笛卡尔积JOIN

笛卡尔积是一种连接，表示左边表的行树乘以右边表的行数等于笛卡尔积结果集的大小。

8、map-side JOIN

如果所有表中有一张是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中，Hive可以在map端执行连接过程。这是因为Hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接操作所需要的reduce过程，即使对于很小的数据集，这个优化也明显地快于常规的连接操作。

最后编辑于：2017.12.10 07:24:26