CODEWITHSUNDEEP
sqlhadoophivebigdata
smp97
smp97

Reputation: 63

HIve query combined table

could you please help with below query.

I have three input table (Table1 , table 2 , table 3)

enter image description here

The output table should have below columns

Date : (combine all the payment_date, receive_date, maturity_date), payment_amount (populate the correct amount for a given payment_date), receive_amount (populate the correct amount for a given receive_date), maturity_amount (populate the correct amount for a given maturity_date), total (payment_amount + receive_amount+ maturity_amount) for a given date column

enter image description here

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Hive supports FULL JOIN, so you can use:

select coalesce(t1.payment_date, t2.receiver_date, t3.maturity_date) as date,
       t1.payment_amount,
       t2.receive_amount,
       t3.maturity_amount
from table1 t1 full join
     table2 t2
     on t2.receive_date = t1.payment_date full join
     table3 t3
     on t3.maturity_date in (t2.receive_date, t1.payment_date)

Upvotes: 1

Related Questions