hive with as的测试使用记录

hive with as的测试使用记录

目的：在做一些sql的测试过程中，需要建相应的临时表支持测试。
with as 可以快速支持相关临时表建设工作。
with table1 as
(
select 1 as t1_id, ‘小满t1_1’ as name, 100 as old
union all
select 2 as t1_id, ‘小满t1_2’ as name, 100 as old
union all
select 3 as t1_id, ‘小满t1_3’ as name, 100 as old
union all
select 4 as t1_id, ‘小满t1_4’ as name, 100 as old
),
table2 as(
select 1 as t1_id, ‘小满t2_1’ as name, 100 as old
union all
select 2 as t1_id, ‘小满t2_2’ as name, 100 as old
union all
select 3 as t1_id, ‘小满t2_3’ as name, 100 as old
union all
select 5 as t1_id, ‘小满t2_5’ as name, 100 as old
),
table3 as(
select 1 as t1_id, ‘小满t3_1’ as name, 100 as old
union all
select 2 as t1_id, ‘小满t3_2’ as name, 100 as old
union all
select 4 as t1_id, ‘小满t3_4’ as name, 100 as old
union all
select 6 as t1_id, ‘小满t3_6’ as name, 100 as old
)

例如在测试：left join 过程发现 on 中条件不同，即结果也不同。
即 ：

select * from table1 t1
left join table2 t2
on t1.t1_id=t2.t1_id
LEFT JOIN table3 t3
on t1.t1_id=t3.t1_id
结果不同于
select * from table1 t1
left join table2 t2
on t1.t1_id=t2.t1_id
LEFT JOIN table3 t3
on t2.t1_id=t3.t1_id
结果不同于
select * from table1 t1
left join table2 t2
on t1.t1_id=t2.t1_id
LEFT JOIN table3 t3
on t1.t1_id=t3.t1_id
and t1.old=t3.old

记住hive 多个left join 怎么执行 原则：
eg:
select * from a left join b on a.abid = b.baid left join c on c.cbid = b.bcid
顺序是先a,b组合成一个虚拟表，然后虚拟表再和C表关联