I have following tables and need to combine them in hive
Could any one please help me how can we achieve this. I tried date part with coalesce and it is fine. But fam part is not able to merge into single column.
Really appreciate your help.
Thanks, Babu
Advertisement
Answer
You can use full outer join
. However, union
with left join
s often looks cleaner:
select df.date, df.name,
coalesce(t1.famcnt1, 0) as famcnt1,
coalesce(t2.famcnt2, 0) as famcnt2,
coalesce(t3.famcnt3, 0) as famcnt3
from ((select date, fam1 from table1
) union -- on purpose to remove duplicates
(select date, fam1 from table2
) union -- on purpose to remove duplicates
(select date, fam1 from table3
)
) df left join
table1 t1
on t1.date = df.date and t1.name = df.name left join
table2 t2
on t2.date = df.date and t2.name = df.name left join
table3 t3
on t3.date = df.date and t3.name = df.name;
If you are happy with NULL
instead of 0
, then no COALESCE()
s are needed at all.