`
yugouai
  • 浏览: 498428 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

Hive的JOIN用法

 
阅读更多

Hive表连接的语法支持如下:

join_table:
    table_reference JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON equality_expression ( AND equality_expression )*

equality_expression:
    expression = expression

 hive只支持等连接,外连接,左半连接。hive不支持非相等的join条件(通过其他方式实现,如left outer join),因为它很难在map/reduce job实现这样的条件。而且,hive可以join两个以上的表。

 

例子

 

写join查询时,有几个典型的点要考虑,如下:

 

等连接

 

只有等连接才允许

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

这两个是合法的连接

 

SELECT a.* FROM a JOIN b ON (a.id <> b.id)

这个是不允许的。

 

多表连接

 

同个查询,可以join两个以上的表

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
 

join的缓存和任务转换

 

hive转换多表join时,如果每个表在join字句中,使用的都是同一个列,只会转换为一个单独的map/reduce。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

这个会转换为单独的map/reduce任务,只有b表的key1列在join被调用。

 

另一方面

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

被转换为两个map/reduce任务,因为b的key1列在第一个join条件使用,而b表的key2列在第二个join条件使用。第一个map/reduce任务join a和b。第二个任务是第一个任务的结果join c。

在join的每个map/reduce阶段,序列中的最后一个表,当其他被缓存时,它会流到reducers。所以,reducers需要缓存join关键字的特定值组成的行,通过组织最大的表出现在序列的最后,有助于减少reducers的内存。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

三个表,在同一个独立的map/reduce任务做join。a和b的key对应的特定值组成的行,会缓存在reducers的内存。然后reducers接受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)

 这里有两个map/reduce任务在join计算被调用。第一个是a和b做join,然后reducers缓存a的值,另一边,从流接收b的值。第二个阶段,reducers缓存第一个join的结果,另一边从流接收c的值。

 

在join的每个map/reduce阶段,通过关键字,可以指定哪个表从流接收。

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

 三个表的连接,会转换为一个map/reduce任务,reducer会把b和c的key的特定值缓存在内存里,然后从流接收a的每一行,和缓存的行做join。

 

join的结果

 

LEFT,RIGHT,FULL OUTER连接存在是为了提供ON语句在没有匹配时的更多控制。例如,这个查询:

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

将会返回a的每一行。如果b.key等于a.key,输出将是a.val,b.val,如果a没有和b.key匹配,输出的行将是 a.val,NULL。如果b的行没有和a.key匹配上,将被抛弃。语法"FROM a LEFT OUTER JOIN b"必须写在一行,为了理解它如何工作——这个查询,a是b的左边,a的所有行会被保持;RIGHT OUTER JOIN将保持b的所有行, FULL OUTER JOIN将会保存a和b的所有行。OUTER JOIN语义应该符合标准的SQL规范。

 

join的过滤

 

Joins发生在where字句前,所以,如果要限制join的输出,需要写在where字句,否则写在JOIN字句。现在讨论的一个混乱的大点,就是分区表

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

将会连接a和b,产生a.val和b.val的列表。WHERE字句,也可以引用join的输出列,然后过滤他们。

但是,无论何时JOIN的行找到a的key,但是找不到b的key时,b的所有列会置成NULL,包括ds列。这就是说,将过滤join输出的所有行,包括没有合法的b.key的行。然后你会在LEFT OUTER的要求扑空。

也就是说,如果你在WHERE字句引用b的任何列,LEFT OUTER的部分join结果是不相关的。所以,当外连接时,使用这个语句

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07'

join的输出会预先过滤,然后你不用对有a.key而没有b.key的行做过滤。RIGHT和FULL join也是一样的逻辑。

 

join的顺序

 

join是不可替换的,连接是从左到右,不管是LEFT或RIGHT join。

SELECT a.val1, a.val2, b.val, c.val
FROM a
JOIN b ON (a.key = b.key)
LEFT OUTER JOIN c ON (a.key = c.key)

首先,连接a和b,扔掉a和b中没有匹配的key的行。结果表再连接c。这提供了直观的结果,如果有一个键都存在于A和C,但不是B:完整行(包括 a.val1,a.val2,a.key)会在"a jOIN b"步骤,被丢弃,因为它不在b中。结果没有a.key,所以当它和c做LEFT OUTER JOIN,c.val也无法做到,因为没有c.key匹配a.key(因为a的行都被移除了)。类似的,RIGHT OUTER JOIN(替换为LEFT),我们最终会更怪的效果,NULL, NULL, NULL, c.val。因为尽管指定了join key是a.key=c.key,我们已经在第一个JOIN丢弃了不匹配的a的所有行。

 

为了达到更直观的效果,相反,我们应该从

FROM c LEFT OUTER JOIN a ON (c.key = a.key) LEFT OUTER JOIN b ON (c.key = b.key).

LEFT SEMI JOIN实现了相关的IN / EXISTS的子查询语义的有效途径。由于Hive目前不支持IN / EXISTS的子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);

可以重写为

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

mapjoin

 

但如果所有被连接的表是小表,join可以被转换为只有一个map任务。查询是

SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a join b on a.key = b.key

不需要reducer。对于每一个mapper,A和B已经被完全读出。限制是a FULL/RIGHT OUTER JOIN b不能使用。

 

如果表在join的列已经分桶了,其中一张表的桶的数量,是另一个表的桶的数量的整倍,那么两者可以做桶的连接。如果A有4个桶,表B有4个桶,下面的连接:

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key

只能在mapper工作。为了为A的每个mapper完整抽取B。对于上面的查询,mapper处理A的桶1,只会抽取B的桶1,这不是默认行为,要使用以下参数:

set hive.optimize.bucketmapjoin = true;

 

如果表在join的列经过排序,分桶,而且他们有相同数量的桶,可以使用排序-合并 join。每个mapper,相关的桶会做连接。如果A和B有4个桶,

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM A a join B b on a.key = b.key

只能在mapper使用。使用A的桶的mapper,也会遍历B相关的桶。这个不是默认行为,需要配置以下参数:

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;

 

翻译自 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

分享到:
评论

相关推荐

    hive sql + left join 数据缺失

    ### Hive SQL Left Join 数据缺失问题解析 #### 一、问题背景 在大数据处理场景中,Hive 是一种广泛使用的工具,...通过上述方法,可以有效解决Hive 3版本中Left Join操作出现的数据缺失问题,确保数据分析的准确性。

    Hive优化方法整理

    3. 解决方法 2:赋与空值分新的 key 值,使用 left outer join 语句进行关联。 处理不同数据类型关联产生的数据倾斜 1. 对于用户表中 user_id 字段为 int,log 表中 user_id 字段既有 string 类型也有 int 类型。 ...

    Hive总结.docx

    - 使用Stitch Join或其他聚合方法来平衡负载。 总的来说,Hive是一个强大的大数据分析工具,它简化了在Hadoop上的数据查询和分析过程,为大数据分析提供了便利。通过理解其原理、掌握SQL语法、优化技巧和解决数据...

    Hive查询sql left join exists

    本文主要关注LEFT JOIN和EXISTS子句的使用,这两个都是数据查询中常见的技术,特别是在大数据处理领域,如Hadoop环境下的Hive。 首先,LEFT JOIN(左外连接)是连接两个表的一种方式,返回所有左表(在FROM子句中...

    Hive基本命令整理

    Hive基本命令整理 作为大数据处理的重要工具,Hive 提供了许多实用的...Hive 不支持 in 查询,但是可以使用左半连接来实现: ``` hive&gt; SELECT * FROM things LEFT SEMI JOIN sales ON (things.id = sales.id); ```

    Hive教程.pdf

    - Hive支持内连接(INNER JOIN)、左连接(LEFT JOIN)、右连接(RIGHT JOIN)以及全连接(FULL OUTER JOIN),但在某些情况下可能需要特殊处理以避免数据倾斜。 - **Inner Join**: - `SELECT t1.*, t2.* FROM ...

    Hive原理与实现

    Hive提供了多种Join类型,包括Common Join、Map Join、Bucket Map Join等,每种Join都有其适用场景: - **Common Join**:标准的Join操作,适用于大多数情况。 - **Map Join**:适用于小表参与Join的情况,可以将小...

    HiveSQL实战题目.docx

    解决方法是使用 JOIN 语句将三个表连接起来,然后使用 WHERE 语句过滤出满足条件的学生信息和课程分数。 2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数: 这个题目要求读者使用 HiveSQL 查询学生表、...

    Hive_优化——深入浅出学Hive

    2. **笛卡尔积**:在严格模式下,Hive 不允许笛卡尔积,可以通过添加 Join Key 或使用 MapJoin 避免。 3. **控制 Map 数**:合理设置 Map 数量,避免过多小文件导致的任务开销。 4. **JOIN**:优化 JOIN 操作,...

    hive on tez 常见报错问题收集

    当遇到此运行时异常时,可以尝试禁用向量化执行或者混合Grace Hash Join,设置`hive.vectorized.execution.enabled`为false,以及`hive.mapjoin.hybridgrace.hashtable`和`hive.vectorized.execution.reduce....

    Hive SQL 编译过程详解

    - **Join的实现原理**:在Hive中,Join操作通常通过MapReduce来实现。例如,在一个内连接中,Map阶段将不同表的数据分别标记,然后在Reduce阶段,依据标记判断并合并来自不同表的记录。这种方法确保了JOIN操作的...

    hive官方文档整理

    这份“hive官方文档整理”PDF文件,无疑是深入理解Hive功能和用法的宝贵资源。以下是对Hive核心知识点的详细解析: 1. **Hive概述**:Hive是一个基于Hadoop的数据仓库工具,它允许使用SQL-like语言(HQL,Hive ...

    hive优化建议.docx

    在大数据分析领域,Hive 是一个广泛使用的工具,它基于 Hadoop 平台,提供了SQL-like 的查询语言,使得处理大规模数据...通过上述方法,我们可以有效地提升Hive查询的速度和资源利用率,为大数据分析提供更高效的平台。

    《企业级Hive实战课程》大纲

    - `hive-e`、`hive-f`、`hive-v`、`hive-i`、`hive-S`等命令的使用方法及其应用场景。 3. **Hive表操作** - 内部表、外部表、分区表的定义与区别; - 表的创建、数据加载与导出操作; - 各种表类型的使用场景与...

    hive入门文档笔记

    ### Hive入门文档笔记 #### 一、Hive简介与安装配置 Hive 是一个构建在 Hadoop 之上的数据仓库工具,它...通过对这些内容的学习,读者可以更好地理解和掌握 Hive 的使用方法,为后续的大数据分析工作打下坚实的基础。

    部分普通sql查询在hive中的实现方式

    ### 部分普通SQL查询在Hive中的实现方式 Hive是一款基于Hadoop的数据仓库工具,...以上是在Hive中实现部分普通SQL查询的方法总结,希望对你有所帮助。在实际应用过程中,还需要根据具体情况灵活调整和优化查询语句。

    Hive基本操作命令大全

    Hive是一个基于Hadoop的数据仓库工具,可以使用SQL语言来查询和管理大规模数据。本文档将详细介绍Hive的基本操作命令,从创建表、加载数据、查询数据到数据管理等方面进行整理。 创建表 Hive提供了多种方式创建表...

    programming_hive-master.zip

    通过对这些代码的学习,可以掌握Hive的使用方法,理解Hive如何处理大数据,以及如何编写高效的HQL语句。 此外,Hive的优化策略也是学习的重点,包括选择合适的分区策略、使用索引、调整执行计划、优化JOIN操作等。...

    Hive练习数据包.zip

    5. **桶(Bucketing)**:桶是Hive中另一种优化查询性能的方法,它将数据分桶存储,使得相同键值的数据落在相同的桶内,有助于提高JOIN操作的效率。 6. **视图**:Hive支持创建视图,可以简化复杂查询,隐藏底层表...

    Hive收集的电子文档

    10. **优化技巧**:Hive提供了多种优化手段,如EXPLAIN计划查看任务执行逻辑,使用CLUSTER BY或DISTRIBUTE BY进行数据分布控制,以及使用JOIN优化等。 通过深入学习和理解这些知识点,你可以有效地使用Hive进行大...

Global site tag (gtag.js) - Google Analytics