sql - 在配置单元中加入具有几乎相同模式的表

Question

我有表 A 和架构

name            string                                      
address         string                                      
timezone        string                                      
one_key_value   map<string,array<string>>                       
two_key_value   map<string,array<string>>

和具有模式的表 B

name            string                                      
address         string                                      
timezone        string                                      
one_key_value   array<struct<key:string,value:array<string>>                    
two_key_value   array<struct<key:string,value:array<string>>

我想做一个 union all

SELECT * FROM (SELECT * FROM A UNION ALL SELECT * FROM B) tmp; 

得到一个错误

FAILED: SemanticException Schema of both sides of union should match.

有没有办法解决这个问题，因为它们几乎相似，只是表 B 的值在数组中。

Answer 1

爆炸 map列和组装 array<struct<key:string,value:array<string>> , 因此它将与 table_b 中的类型相同，这应该从 Hive 版本 1.3.0 开始工作:

select * from
(
select s.name, s.address, s.timezone, 
       --get arrays array<struct<key:string,value:array<string>>
       collect_set(mystruct1) as one_key_value,
       collect_set(mystruct2) as two_key_value
  from
(
select a.*, 
       --get structs struct<key:string,value:array<string>
       named_struct('key',k1.key, 'value', k1.value) mystruct1,
       named_struct('key',k2.key, 'value', k2.value) mystruct2
  from table_a a
       --explode maps, get key:string, value:array<string>
       lateral view outer explode(one_key_value) k1 key,value     
       lateral view outer explode(two_key_value) k2 key,value  
)s
group by s.name, s.address, s.timezone
) table_a

UNION ALL

select * from table_b
;

还有Brickhouse collect以前版本的 Hive 的 UDF:

add jar /path/to/jar/brickhouse-0.7.1.jar;
create temporary function collect as 'brickhouse.udf.collect.CollectUDAF';

 select * from
    (
    select s.name, s.address, s.timezone, 
           --get arrays array<struct<key:string,value:array<string>>
           collect(mystruct1) as one_key_value,
           collect(mystruct2) as two_key_value
      from
    (
    select a.*, 
           --get structs struct<key:string,value:array<string>
           named_struct('key',k1.key, 'value', k1.value) mystruct1,
           named_struct('key',k2.key, 'value', k2.value) mystruct2
      from table_a a
           --explode maps, get key:string, value:array<string>
           lateral view outer explode(one_key_value) k1 key,value     
           lateral view outer explode(two_key_value) k2 key,value  
    )s
    group by s.name, s.address, s.timezone
    ) table_a

    UNION ALL

    select * from table_b
    ;