[一起学Hive]之十一-Hive中Join的类型和用法-白红宇

[一起学Hive]之十一-Hive中Join的类型和用法

阅读量：4208 次

发布时间：2019-05-26

本文共 3115 字，大约阅读时间需要 10 分钟。

Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持LEFT SEMI JOIN和CROSS JOIN，但这两种JOIN类型也可以用前面的代替。

注意：Hive中Join的关联键必须在ON ()中指定，不能在Where中指定，否则就会先做笛卡尔积，再过滤。

数据准备：

hive> desc lxw1234_a; 
  OK 
  id                      string                                      
  name                    string                                      
  Time taken: 0.094 seconds, Fetched: 2 row(s) 
  hive> select * from lxw1234_a; 
  OK 
  1       zhangsan 
  2       lisi 
  3       wangwu 
  Time taken: 0.116 seconds, Fetched: 3 row(s) 
  hive> desc lxw1234_b; 
  OK 
  id                      string                                      
  age                     int                                         
  Time taken: 0.159 seconds, Fetched: 2 row(s) 
  hive> select * from lxw1234_b; 
  OK 
  1       30 
  2       29 
  4       21 
  Time taken: 0.09 seconds, Fetched: 3 row(s)

10.1 内关联（JOIN）

只返回能关联上的结果。

SELECT a.id, 
  a.name, 
  b.age 
  FROM lxw1234_a a 
  join lxw1234_b b 
  ON (a.id = b.id); 
    
  --执行结果 
    
  1       zhangsan        30 
  2       lisi    	29

10.2 左外关联（LEFT [OUTER] JOIN）

以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。

是否指定OUTER关键字，貌似对查询结果无影响。

SELECT a.id, 
  a.name, 
  b.age 
  FROM lxw1234_a a 
  left join lxw1234_b b 
  ON (a.id = b.id); 
    
  --执行结果： 
  1   zhangsan   30 
  2   lisi        29 
  3   wangwu    NULL

10.3 右外关联（RIGHT [OUTER] JOIN）

和左外关联相反，以RIGTH [OUTER] JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。

是否指定OUTER关键字，貌似对查询结果无影响。

SELECT a.id, 
  a.name, 
  b.age 
  FROM lxw1234_a a 
  RIGHT OUTER JOIN lxw1234_b b 
  ON (a.id = b.id); 
    
  --执行结果： 
  1          zhangsan    	   30 
  2          lisi    	   29 
  NULL       NULL    	   21

10.4 全外关联（FULL [OUTER] JOIN）

以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。

是否指定OUTER关键字，貌似对查询结果无影响。

注意：FULL JOIN时候，Hive不会使用MapJoin来优化。

SELECT a.id, 
  a.name, 
  b.age 
  FROM lxw1234_a a 
  FULL OUTER JOIN lxw1234_b b 
  ON (a.id = b.id); 
    
  --执行结果： 
  1       zhangsan        	30 
  2       lisi    		29 
  3       wangwu  		NULL 
  NULL    NULL    		21

10.5 LEFT SEMI JOIN

以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录。

SELECT a.id, 
  a.name 
  FROM lxw1234_a a 
  LEFT SEMI JOIN lxw1234_b b 
  ON (a.id = b.id); 
    
  --执行结果： 
  1       zhangsan 
  2       lisi 
    
  --等价于： 
  SELECT a.id, 
  a.name 
  FROM lxw1234_a a 
  WHERE a.id IN (SELECT id FROM lxw1234_b); 
    
    
  --也等价于： 
  SELECT a.id, 
  a.name 
  FROM lxw1234_a a 
  join lxw1234_b b 
  ON (a.id = b.id); 
    
  --也等价于： 
  SELECT a.id, 
  a.name 
  FROM lxw1234_a a 
  WHERE EXISTS (SELECT 1 FROM lxw1234_b b WHERE a.id = b.id);

10.6 笛卡尔积关联（CROSS JOIN）

返回两个表的笛卡尔积结果，不需要指定关联键。

SELECT a.id, 
  a.name, 
  b.age 
  FROM lxw1234_a a 
  CROSS JOIN lxw1234_b 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

Hive中的JOIN类型基本就是上面这些，至于JOIN时候使用哪一种，完全得根据实际的业务需求来定，但起码你要搞清楚这几种关联类型会返回什么样的结果。

除非特殊需求，并且数据量不是特别大的情况下，才可以慎用CROSS JOIN，否则，很难跑出正确的结果，或者JOB压根不能执行完。

经验告诉我，Hive中只要是涉及到两个表关联，首先得了解一下数据，看是否存在多对多的关联。

：

—-

如果觉得本博客对您有帮助，请。

转载请注明： »

你可能感兴趣的文章