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'
总结：
Original: https://blog.csdn.net/sysocc/article/details/128723999
Author: mylife512
Title: 大数据技术架构(组件)7——Hive：Filter PushDown Cases And Outer Join Behavior
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/817849/
转载文章受原作者版权保护。转载请注明原作者出处！