大数据技术架构(组件)7——Hive：Filter PushDown Cases And Outer Join Behavior

最新推荐文章于 2025-12-05 12:03:47 发布

原创最新推荐文章于 2025-12-05 12:03:47 发布 · 909 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#大数据 #架构 #hive

大数据专栏收录该内容

80 篇文章

订阅专栏

博客在关闭优化器的前提下，介绍了Hive中不同连接类型（Inner Join、Left join、Right Join、Full Join）下，Join On中的谓词和Where谓词在左右表的下推情况。如Inner Join中，Join On和Where谓词左右表均可下推；Left join在关闭mapjoin优化时，Join On谓词左表不下推、右表下推等。

1.2、Filter PushDown Cases And Outer Join Behavior

前提:关闭优化器

set hive.auto.convertjoin=false;
set hive.cbo.enable=false;

Inner Join:

1、Join On中的谓词: 左表下推、右表下推

2、Where谓词:左表下推、右表下推

-- 第一种情况: join on 谓词
select
    t1.user_id,
    t2.user_id
from wedw_tmp.tmp_test 1 t1
join wedw_tmp.tmp_test 2 t2
on tl.user_id = t2.user_id
    and tl.user_id='111' 
    and t2.user_id='222'

-- 第二种情况: where谓词
select
    t1.user_id,
    t2.user_id
from wedw_tmp.tmp_test_1 t1
join wedw_tmp.tmp_test_2 t2
on tl.user_id = t2.user_id
where t1.user_id='111' 
    and t2.user_id='222'

Left join:

1、Join On中的谓词: 左表不下推、右表下推(前提：关闭mapjoin优化)

2、Where谓词:左表下推、右表下推

-- 第一种情况: join on 谓词
select
    t1.user_id,
    t2.user_id
from wedw_tmp.tmp_test_1 t1
left join wedw tmp.tmp_test_2 t2
on tl.user_id = t2.user_id 
    and tl.user_id='111' 
    and t2.user_id='222'

-- 第二种情况: where谓词
select
    t1.user_id,t2.user_id
from wedw_tmp.tmp_test_1 t1
left join wedw_tmp.tmp_test 2 t2
on tl.user_id = t2.user_id
where t1.user_id='111' 
    and t2.user_id='222'

Right Join:

1、Join On中的谓词: 左表下推、右表不下推(前提：关闭mapjoin优化)

2、Where谓词:左表下推、右表下

-- 第一种情况: join on 谓词
select
    t1.user_id,
    t2.user_id
from wedw_tmp.tmp_test_1 t1
right join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id 
    and t1.user_id='111' 
    and t2.user_id='222'

--第二种情况: where谓词
select
    t1.user_id,
    t2.user_id
from wedw_tmp.tmp_test_1 t1
right join wedw_tmp.tmp_test_2 t2
    on t1.user_id = t2.user_id
where t1.user_id='111' 
    and t2.user_id='222'

Full Join:

1、Join On中的谓词: 左表不下推、右表不下推(前提：关闭mapjoin优化)

2、Where谓词:左表不下推、右表不下推

-- 第一种情况: join on 谓词
select
    t1.user_id,
    t2.user id
from wedw_tmp.tmp_test_1 t1
full join wedw_tmp.tmp_test 2 t2
    on t1.user_id = t2.user_id 
    and tl.user_id='111' 
    and t2.user_id='222'

-- 第二种情况: where谓词
select
    t1.user_id,
    t2.user_id
from wedw_tmp.tmp_test_1 t1
full join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2 .user_id
where tl.user_id='111' 
    and t2.user_id='222'