文档章节

Hive 中的 LEFT SEMI JOIN 与 JOIN ON 的前世今生

大数据之路
 大数据之路
发布于 2013/12/28 01:50
字数 1089
阅读 8182
收藏 12

hive 的 join 类型有好几种,其实都是把 MR 中的几种方式都封装实现了,其中 join on、left semi join 算是里边具有代表性,且使用频率较高的 join 方式。

1、联系

他们都是 hive join 方式的一种,join on 属于 common join(shuffle join/reduce join),而 left semi join 则属于 map join(broadcast join)的一种变体,从名字可以看出他们的实现原理有差异。

2、区别

(1)Semi Join,也叫半连接,是从分布式数据库中借鉴过来的方法。它的产生动机是:对于reduce side join,跨机器的数据传输量非常大,这成了join操作的一个瓶颈,如果能够在map端过滤掉不会参加join操作的数据,则可以大大节省网络IO,提升执行效率。
实现方法很简单:选取一个小表,假设是File1,将其参与join的key抽取出来,保存到文件File3中,File3文件一般很小,可以放到内存中。在map阶段,使用DistributedCache将File3复制到各个TaskTracker上,然后将File2中不在File3中的key对应的记录过滤掉,剩下的reduce阶段的工作与reduce side join相同。
由于
hive 中没有 in/exist 这样的子句(新版将支持),所以需要将这种类型的子句转成 left semi join。left semi join 是只传递表的 join key 给 map 阶段 , 如果 key 足够小还是执行 map join, 如果不是则还是 common join。关于 common join(shuffle join/reduce join)的原理请参考文末 refer。

(2)left semi join 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

(3)对待右表中重复key的处理方式差异:因为 left semi join 是 in(keySet) 的关系,遇到右表重复记录,左表会跳过,而 join on 则会一直遍历。

最后的结果是这会造成性能,以及 join 结果上的差异。

(4)left semi join 中最后 select 的结果只许出现左表,因为右表只有 join key 参与关联计算了,而 join on 默认是整个关系模型都参与计算了。

3、两种 join 的“坑”

  由于HIVE中都是等值连接,在JOIN使用的时候,有两种写法在理论上是可以达到相同的效果的,但是由于实际情况的不一样,子表中数据的差异导致结果也不太一样。 

写法一: left semi join

select
           a.bucket_id,
        a.search_type,
            a.level1,
        a.name1,
        a.level2,
        a.name2,
        cast((a.alipay_fee) as double) as zhuliu_alipay,
        cast(0 as double) as total_alipay
        from tmall_data_fdi_search_zhuliu_alipay_cocerage_bucket_1 a
     left semi join
     tmall_data_fdi_dim_main_auc b
     on (a.level2 = b.cat_id2
         and a.brand_id = b.brand_id
       and b.cat_id2 > 0
         and b.brand_id > 0
         and b.max_price = 0
     )

结果是 3121 条

写法二: join on

select
           a.bucket_id,
        a.search_type,
            a.level1,
        a.name1,
        a.level2,
        a.name2,
        cast((a.alipay_fee) as double) as zhuliu_alipay,
        cast(0 as double) as total_alipay
        from tmall_data_fdi_search_zhuliu_alipay_cocerage_bucket_1 a
     join   tmall_data_fdi_dim_main_auc b
     on (a.level2 = b.cat_id2
         and a.brand_id = b.brand_id)
  where  b.cat_id2 > 0
         and b.brand_id > 0
         and b.max_price = 0

结果是 3142 条

这两种写法带来的值居然不是相等的,我一直以为理解这两种方式的写法是一样的, 但是统计的结果却是不一样的。 
经过一层一层的查找,发现是由于子表(tmall_data_fdi_dim_main_auc)中存在重复的数据,当使用JOIN ON的时候,A,B表会关联出两条记录,应为ON上的条件符合; 
而是用LEFT SEMI JOIN 当A表中的记录,在B表上产生符合条件之后就返回,不会再继续查找B表记录了,所以如果B表有重复,也不会产生重复的多条记录。 

大多数情况下 JOIN ON 和 left semi on 是对等的,但是在上述情况下会出现重复记录,导致结果差异,所以大家在使用的时候最好能了解这两种方式的原理,避免掉“坑”。


4、Refer

(1)HIVE 中内连接(JOIN ON)与LEFT SEMI JOIN查询结果不一致的分析

http://scholers.iteye.com/blog/1710594

(2)Hadoop 中的两表join

http://www.gemini5201314.net/hadoop/hadoop-%E4%B8%AD%E7%9A%84%E4%B8%A4%E8%A1%A8join.html

(3)What is difference between natural join and semi join?

http://wiki.answers.com/Q/What_is_difference_between_natural_join_and_semi_join#q151543010/page/2

(4)MapReduce 中的两表 join 几种方案简介

http://my.oschina.net/leejun2005/blog/95186

(5)几种 hive join 类型简介

http://my.oschina.net/leejun2005/blog/82523

(6)Hive & Performance 学习笔记

http://my.oschina.net/leejun2005/blog/158491#OSC_h3_8

© 著作权归作者所有

大数据之路
粉丝 1605
博文 514
码字总数 333288
作品 0
武汉
架构师
私信 提问
加载中

评论(3)

l
liuhui_306
楼主的文章各个都非常棒,顶一个
扫大街的程序员
扫大街的程序员
膜拜
仪山湖
仪山湖
好文章,还带有refer,作风严谨,佩服,顶一个
图文并茂详解 SQL JOIN

Join是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行匹配的数据,这...

大数据之路
2013/05/31
7K
5
Hive中的所有Join

内连接:inner join --join优化:在进行join的时候,大表放在最后面 --但是使用 /+streamtable(大表名称)/ 来标记大表,那么大表放在什么位置都行了 select /+streamtable(s)/ s.ymd,d.divid...

qhaiyan
2016/12/03
24
0
hive中的left semi join 替换sql中的in操作

LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。 Hive 当前没有实现 IN/EXISTS 子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是, JOIN 子句中右...

Tmac
2012/12/29
328
0
hive的join操作

hive的join操作 Join的语法规则: www.2cto.com join_table: table_reference JOIN table_factor [join_condition] table_reference {LEFT RIGHT FULL} [OUTER] JOIN table_reference join_......

Zero零_度
2016/10/13
5
0
hive中的 left semi join

LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。 Hive 当前没有实现 IN/EXISTS 子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是, JOIN 子句中右...

张欢19933
2016/09/19
156
0

没有更多内容

加载失败,请刷新页面

加载更多

IT兄弟连 HTML5教程 介绍HTML5给你认识 习题

1.关于HTML5说法正确的是:(C) A.HTML5只是对HTML4的一个简单升级 B.所有主流浏览器都支持HTML5 C.HTML5新增了离线缓存机制 D.HTML5主要是针对移动端进行了优化 2.为了标识一个HTML文...

老码农的一亩三分地
1分钟前
1
0
关于1加手机rom的分析过程

1、关于清理app缓存的信息 framework.jar和services.jar文件,都位于/system/framework目录下面 framework.jar的android.os.Intent类里面定义了action "android.intent.action.CLEAR_PKG",这......

shatian
2分钟前
1
0
[docker]使用root进入docker

docker exec -it --user root ed0 /bin/bash https://blog.csdn.net/kaifeng86/article/details/73237056...

Danni3
15分钟前
2
0
通过重编译icu替换icuxxxx.dll减少QT5发布体积

最近需要使用QT写一个程序,选择了比较新的QT5.3以获得更多特性进行快速开发。但是QTCreator编译出来的程序是动态链接版本,接着就头痛了,自己程序很小,但是所需要携带的动态链接库却很大,...

shzwork
17分钟前
3
0
关于二叉树的前序、中序、后序三种遍历

二叉树遍历分为三种:前序、中序、后序,其中序遍历最为重要。为啥叫这个名字?是根据根节点的顺序命名的。 比如上图正常的一个满节点,A:根节点、B:左节点、C:右节点,前序顺序是ABC(根...

城市之雾
22分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部