I have two tables in Hive (arch and noarch) with the following structure:

I need to get a full data view arch noarch and join them by res_id.

I tried to do different variations of left joins but I was getting either results from arch and nothing from noarch or vice versa. I guess I should have used union all, but struggling to write it correctly.

Could you please help me with the right query?

EDIT:

I'd like to get kinda united view for these two tables when I search for particular res_ids.

Say I have the following data:

The ultimate goal is to get full data from both tables: 111 222 333 444).

CodePudding user response：

You can use UNION ALL:

select tr_id, res_id, info_json, created_at, updated_at, src
from
(select tr_id, res_id, info_json, created_at, updated_at, 'NoArch' as src 
  from Table2NoArch

union all

select tr_id, res_id, info_json, null created_at, null updated_at, 'Arch' as src 
  from Table1Arch
)u
where res_id in (111,333,444)

created_at and updated_at are absent in one Table1Arch, NULLs are selected, you can use current_timestamp or current_date instead.

Added src column, so you can easily find out the source of data.