关键字:Hive Join、Hive LEFT|RIGTH|FULL OUTER JOIN、Hive LEFT SEMI JOIN、Hive Cross Join
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中只要是涉及到两个表关联,首先得了解一下数据,看是否存在多对多的关联。
Hive相关文章(持续更新):
—-Hive中的数据库(Database)和表(Table)
hive优化之——控制hive任务中的map数和reduce数
相关推荐
Hive的数据类型和文件格式章节中,读者将了解到基本数据类型、集合数据类型,以及Hive如何处理文本文件数据编码和读时模式。理解这些对于正确存储和处理数据至关重要。 HiveQL,即Hive查询语言,是Hive的核心部分。...
【大数据虚拟机Linux VM复习题库】主要涵盖了大数据处理、Hadoop生态系统中的Hive以及Linux虚拟机相关的知识。以下是对这些知识点的详细说明: 1. **数据仓库与数据库系统的关系**: 数据仓库是一个用于报告和数据...
本篇文章将深入探讨Hive的组件、MapReduce的工作原理以及Hive QL的使用,包括JOIN操作、GROUP BY聚合以及COUNT DISTINCT等高级特性。 首先,我们来看Hive的主要组件。Facebook开发的Hive包含了多个关键部分,如HDFS...
Impala 是参照谷歌的新三篇论文(Caffeine、Pregel、Dremel)中的 Dremel 实现而来,其中旧三篇论文分别是(BigTable、GFS、MapReduce)分别对应我们即将学的 HBase 和已经学过的 HDFS 以及 MapReduce。 Impala 是...
在Map任务中,我们可以使用`map()`方法来读取输入的键值对(通常是行和行内容),然后使用`emit()`方法输出新的键值对(单词和1)。在Reduce任务中,`reduce()`方法接收一组相同的键和对应的值列表(即单词和其出现...