How to combine two tables to get singel table in Hive

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

Answer

You can use full outer join. However, union with left joins 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.