CODEWITHSUNDEEP
sqlhivehiveql
Devin Roberts
Devin Roberts

Reputation: 87

Hive - Using Lateral View Explode with Joined Table

I am building some analysis and need to prep the date by joining two tables and then unpivot a date field and create one record for each "date_type". I have been trying to work with lateral view explode(array()) function but I can't figure out how to do this with columns from two separate tables. Any help would be appreciated, open to completely different methods.

TableA:

loan_number app_date
123 07/09/2022
456 07/11/2022

TableB:

loan_number funding_date amount
123 08/13/2022 12000
456 08/18/2022 10000

Desired Result:

loan_number date_type date_value amount
123 app_date 07/09/2022 12000
456 app_date 07/11/2022 10000
123 funding_date 08/13/2022 12000
456 funding_date 08/18/2022 10000

Here is some sample code related the example above I was trying to make work:

SELECT
    b.loan_number,
    b.amount,
    Date_Value
    
FROM TableA as a
    LEFT JOIN 
    TableB as b
        ON a.loan_number=b.loan_number
    LATERAL VIEW explode(array(to_date(a.app_date),to_date(b.funding_date)) Date_List AS Date_value

Upvotes: 0

Views: 498

Answers (1)

Shawn.X
Shawn.X

Reputation: 1363

No need lateral view explode, just union, try below:

with base_data as (
select
    a.loan_number,
    a.app_date,
    b.funding_date,
    b.amount
from
    tableA a 
join
    tableB b on a.loan_number = b.loan_number
) 
select
    loan_number,
    'app_date'  as date_type,
    app_date    as date_value,
    amount
from
    base_data
union all
select
    loan_number,
    'funding_date'  as date_type,
    funding_date    as date_value,
    amount
from
    base_data

Upvotes: 1

Related Questions