I am getting the below error when I run the select query. Kindly help me with any solutions to overcome it
select id,named_struct('name',name1,'description',description),1 from tab1
union all
select id1,null,0 from tab2;
Error:
SemanticException Schema of both sides of union should match: Column locations is of type struct<name:varchar(20),description varchar(20)> on first table and type void on second table
It is not possible to cast( NULL as struct<...>) because cast works only with primitive types.
The trick here is to use join with single row dummy table containing struct of required type. Join condition should be never satisfied and if you select from joined dummy table it will return NULL compatible with struct type (data type will be taken from dummy table and value will be NULL because it is not joined).
Demo:
with
--your two tables, use real tables instead of CTEs
tab1 as (select 1 as id, 'John' as name, 'Some description' as description),
tab2 as (select 2 as id1),
--dummy table for struct<name:string, description:string> type generation
dummy as (select -9999999 as id, named_struct('name','x','description','x') as dummystruct )
select id,named_struct('name',name,'description',description),1 from tab1
union all
select id1, d.dummystruct,0
from tab2 t2
left join dummy d on t2.id1=d.id --you can use just false as a condition
;
Result:
1 {"name":"John","description":"Some description"} 1
2 NULL 0
Just replace two CTEs in my example (tab1 and tab2) with your real tables.
Very similar question with solution how to get empty array<struct>: https://stackoverflow.com/a/65373624/2700344
