Hive Union Failed due to SemanticException Schema of both sides of union should match-CodePudding

I have 2 tables and I want to get all the records for both tables. So my plan is to do Union and join both tables but when I tried to do it, I'm getting an error saying, "Error while compiling statement: FAILED: SemanticException Schema of both sides of union should match". Here are my both tables Table A

id  name        Start_date  budget  dueDate
1   ans2022     20/12/2022  230     25/12/2022
2   moj_2322    23/12/2022  345     30/12/2022
3   jjo1922     19/11/2022  450     28/11/2022
5   oit1522     15/12/2022  150     27/12/2022
8   oiye2122    21/12/2022  375     29/12/2022

Table B

id  Account op_name     Op_id
1   12098   Abco        1
1   12098   cbdi        2
1   12098   asdt        3
2   20940   wedq        4
2   20940   sadw        5
3   41895   2022_asr    6
4   39805   gtr43       7
4   39805   ress        8
5   12098   fgtre       9
5   12098   sehy        10
6   23565   redj        11
6   23565   23ertbs     12

Result I'm expecting is below

id  name        Start_date  budget  Account     op_name     Op_id
1   ans2022     20/12/2022  230     12098       Abco        1
1   ans2022     20/12/2022  230     12098       cbdi        2
1   ans2022     20/12/2022  230     12098       asdt        3
2   moj_2322    23/12/2022  345     20940       wedq        4
2   moj_2322    23/12/2022  345     20940       sadw        5
3   jjo1922     19/11/2022  450     41895       2022_asr    6
4                                   39805       gtr43       7
4                                   39805       ress        8
5   oit1522     15/12/2022  150     12098       fgtre       9
5   oit1522     15/12/2022  150     12098       sehy        10
6                                   23565       redj        11
6                                   23565       23ertbs     12
8   oiye2122    21/12/2022  375

below is my code and getting the above error

select id, name, Start_date, budget from 
A
group by id, name, Start_date, budget
  union
select id, Account, op_name, Op_id
  from
  B
group by id, Account, op_name, Op_id

Any help would be appriciate.

Thanks

CodePudding user response：

This is a case for LEFT JOIN

SELECT b.id,  a.name,a.Start_date, a.budget,a.dueDate
b.Account, b.op_name,b.Op_id
FROM TableB b 
LEFT OUTER JOIN TableA a 
ON (b.id = a.id);

You can fin d out more about joins here