`

hive join

阅读更多

hive(0.9.0):
1.支持equality joins, outer joins, and left semi joins
2.只支持等值条件
3.支持多表join

原理
hive执行引擎会将HQL“翻译”成为map-reduce任务,如果多张表使用同一列做join则将被翻译成一个reduce,否则将被翻译成多个map-reduce任务。
eg:
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)将被翻译成1个map-reduce任务
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
将被翻译成2个map-reduce任务
这个很好理解,一般来说(map side join除外,后面会介绍),map过程负责分发数据,具体的join操作在reduce完成,因此,如果多表基于不同的列做join,则无法在一轮map-reduce任务中将所有相关数据shuffle到统一个reducer
对于多表join,hive会将前面的表缓存在reducer的内存中,然后后面的表会流式的进入reducer和reducer内存中其它的表做join。
eg:

[plain] view plaincopy
 
  1. SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  

在reducer中,a、b表待join的数据会放在内存中。
这会引起一些问题,如果reducer个数不足或者a、b表数据过大,则可能oom
因此,我们需要将数据量最大的表放到最后,或者通过“STREAMTABLE”显示指定reducer流式读入的表
eg:
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)此时,b、c表数据在reducer将放在内存中

Outer join
Outer join包括left、right、full outer join,其目的是针对不匹配的情况做一些控制。 
表a:

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)LEFT OUTER JOIN:如果a.key中找不到对应的b.key,则输出a.val,NULL

LEFT OUTER JOIN可以用来代替not in(not in 在Hive0.8才支持)
eg:
select a.key from a left outer join b on a.key=b.key where b.key1 is null

实例:

[plain] view plaincopy
 
  1. hive> select * from a ;  
  2. OK  
  3. key     value  
  4. 1       a  
  5. 2       b  
  6. 3       c  
  7. Time taken: 0.155 seconds  
  8. hive> select * from b;   
  9. OK  
  10. key     value  
  11. 1       d  
  12. 2       e  
  13. 4       f  
  14. hive> SELECT a.value, b.value FROM a LEFT OUTER JOIN b ON (a.key=b.key);   
  15. OK  
  16. value   value  
  17. a       d  
  18. b       e  
  19. c       NULL  
  20. hive> SELECT a.value, b.value FROM a RIGHT OUTER JOIN b ON (a.key=b.key);  
  21. OK  
  22. value   value  
  23. a       d  
  24. b       e  
  25. NULL    f  
  26. hive> SELECT a.value, b.value FROM a FULL OUTER JOIN b ON (a.key=b.key);   
  27. OK  
  28. value   value  
  29. a       d  
  30. b       e  
  31. c       NULL  
  32. NULL    f  


Left Semi Join

hive之前(现已支持!)不支持in/exists,left semi join是in/exists更有效率的实现。
eg:
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)

Map Side Join
假如join两张表,其中有一张表特别小(可以放到内存中),那么可以使用Map-side join。Map side join是在mapper中做join,原理是将其中一张join表放到每个mapper任务的内存中,从而不用reducer任务,在mapper中就完成join。Map side join不适合FULL/RIGHT OUTER JOIN,理由大家思考下。
示例:
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key


Bucketed Map Join
Bucketed map join是一种特殊的map side join,其针对的是所有的表都使用待join的key作为bucket列,并且bucket数量彼此有倍数关系的场景。在这种场景下,由于不需要将整张表导入内存,只需要将相应的bucket导入内存,因此,适宜一些数据量比较大的表。
例如,Table a使用key作为bucket列,共有8个bucket,Table b也是用key作为bucket列,有16个bucket,则使用Map side join,a只需要将b对应的2个bucket放入内存即可,如下:
SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a join b on a.key = b.key


在不一点left semi join的原理:

只用B表的join字段做reduce端的过滤,感觉不是semi join这个词的意思

这里有个left semi join的explain:

STAGE PLANS:
  Stage: Stage-4
    Conditional Operator

  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        t1:t 
          TableScan
            alias: t
            Select Operator
              expressions:
                    expr: dt
                    type: string
                    expr: regexp_extract(params, '&orderNo=([^&]*)', 1)
                    type: string
              outputColumnNames: _col1, _col2
              Reduce Output Operator
                key expressions:
                      expr: lower(trim(_col2))
                      type: string
                sort order: +
                Map-reduce partition columns:
                      expr: lower(trim(_col2))
                      type: string
                tag: 0
                value expressions:
                      expr: _col1
                      type: string
                      expr: _col2
                      type: string
        t2:t 
          TableScan
            alias: t
            Filter Operator
              predicate:
                  expr: (substring(ordercreatetime, 0, 10) = '2014-11-01')
                  type: boolean
              Select Operator
                expressions:
                      expr: orderno
                      type: string
                outputColumnNames: _col0
                Group By Operator
                  bucketGroup: false
                  keys:
                        expr: _col0
                        type: string
                  mode: hash
                  outputColumnNames: _col0
                  Reduce Output Operator
                    key expressions:
                          expr: lower(trim(_col0))
                          type: string
                    sort order: +
                    Map-reduce partition columns:
                          expr: lower(trim(_col0))
                          type: string
                    tag: 1
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          condition expressions:
            0 {VALUE._col1} {VALUE._col2}
            1 
          handleSkewJoin: false
          outputColumnNames: _col1, _col2
          Select Operator
            expressions:
                  expr: _col1
                  type: string
                  expr: _col2
                  type: string
            outputColumnNames: _col0, _col1
            File Output Operator
              compressed: false
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

  Stage: Stage-0
    Fetch Operator
      limit: -1

 

 

 

 

分享到:
评论

相关推荐

    Hive Summit 2011-join

    Hive Summit 2011-join介绍了Facebook中Hive实现的多种join策略。Hive作为一个数据仓库工具,主要用于处理大规模数据集的分析和查询,而join操作是数据仓库中常见且关键的操作之一。在大数据的背景下,如何高效地...

    使用Hive进行join查询的时报错

    NULL 博文链接:https://weigang-gao.iteye.com/blog/2260663

    Hive-Summit-2011-join.zip_hive

    【标题】:“Hive-Summit-2011-join.zip_hive”指的是一个关于HiveJoin策略的讨论,源自2011年Facebook峰会上的分享。 【描述】:“Hive: Join Strategies. Facebook summit-2011”说明了这个主题集中在Hive的连接...

    hive sql + left join 数据缺失

    ### Hive SQL Left Join 数据缺失问题解析 #### 一、问题背景 在大数据处理场景中,Hive 是一种广泛使用的工具,用于对存储在 Hadoop 文件系统中的数据进行数据汇总、查询和分析。随着Hive版本的不断更新,部分用户...

    分布式数据仓库Hive大全

    4. Hive Join 29 5. HIVE参数设置 31 6. HIVE UDF 33 6.1 基本函数 33 6.1.1 关系操作符 33 6.1.2 代数操作符 34 6.1.3 逻辑操作符 35 6.1.4 复杂类型操作符 35 6.1.5 内建函数 36 6.1.6 数学函数 36 6.1.7 集合函数...

    HIVE从入门到精通.pdf

    #### 九、Hive Join - **Join操作**:支持多种类型的JOIN操作,如INNER JOIN、LEFT JOIN等。 #### 十、Hive基本语法 - **常用语句**:包括SELECT、INSERT、UPDATE、DELETE等SQL-like语句。 #### 十一、Hive操作...

    Hive用户指南 Hive user guide 中文版

    - **JOIN**:掌握JOIN的原则和技巧是提高Hive查询性能的关键。 - **合并小文件**:处理大量的小文件可能会影响Hive的性能。 #### 十、Hive FAQ - 提供了一些常见问题解答,帮助用户解决使用过程中遇到的问题。 ...

    Hive教程.pdf

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

    Hive用户手册中文版.pdf

    对于Hive的Map/Reduce操作,Hive支持Map Join操作,这种操作适用于小表与大表进行join时的数据处理。同时,Hive支持Bucket和Sampling操作,以帮助用户更有效地处理数据。 Hive的设计目标是简化Hadoop上数据仓库的...

    Hive用户指南(Hive_user_guide)_中文版

    - **8.8 Join中处理null值的语义区别**:Hive处理NULL值的方式可能与传统SQL不同。 #### 九、优化与技巧 - **9.1 全排序**:对于大规模数据集进行全排序可能会导致性能问题。 - **9.2 怎样做笛卡尔积**:在特定...

    hive操作指南

    - Hive支持多种类型的JOIN操作,包括Inner Join、Left Outer Join、Right Outer Join等。 - 示例:`SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.id;` #### 五、...

    Hive优化方法整理

    1. 对于 join 操作,如果出现数据倾斜,应当设置 set hive.exec.reducers.max=200;set mapred.reduce.tasks= 200;以增大 Reduce 个数。 2. 对于 group by 操作,如果出现数据倾斜,应当设置 set hive.groupby....

    Hive总结.docx

    Hive支持大部分SQL标准,包括SELECT、FROM、WHERE、GROUP BY、JOIN等操作,使得用户可以方便地对Hadoop上的数据进行查询和分析。 【Hive函数】 Hive提供了丰富的内置函数,包括聚合函数(SUM、COUNT、AVG等)、数学...

    hive参数配置说明大全

    如果设置为true,则Hive将自动将Reduce端的Common Join转化为Map Join,默认值为false。 5. hive.mapred.local.mem 该参数决定了Mapper/Reducer在本地模式的最大内存量,以字节为单位,0为不限制。如果设置为0,则...

    hive-2.1.0.rar

    Hive支持不同类型的JOIN操作,如INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN,但需要注意的是,JOIN操作在大数据环境中可能非常耗时,因此合理设计表结构和使用分区可以优化JOIN性能。 7. **Hive与其它大数据...

    Hive查询sql left join exists

    在Hive SQL中,JOIN操作是用来合并来自两个或多个表的数据。本文主要关注LEFT JOIN和EXISTS子句的使用,这两个都是数据查询中常见的技术,特别是在大数据处理领域,如Hadoop环境下的Hive。 首先,LEFT JOIN(左外...

    hive

    它考虑了各种因素,如表大小、数据分布、JOIN 操作等,以生成最优的执行计划。 7. **数据生命周期管理**:Hive 提供了数据生命周期管理功能,可以设定数据的保留策略,自动清理过期的数据。 8. **与 BI 工具集成**...

    Hive on Spark EXPLAIN statement

    Hive on Spark EXPLAIN statement : 讲述了 Common Join / Map join / Bucket Map Join / Sorted Merge Bucket Map Join / skew join 在explain 中的 树结构 。In Hive, command EXPLAIN can be used to show the ...

Global site tag (gtag.js) - Google Analytics