hive 横向 View 爆炸

Question

我的数据在其中一个字段中具有嵌套映射。例如，数据位于名为“customers”的表中，如下所示:

姓名:比尔·琼斯

地址:{"billing":{"street":"123 Main", "city":"Chicago", "state":"IL"},"shipping":{"street":"432 Copper", "city"":"纽约", "州":"NY"}}

我可以编写什么样的查询来分解整个数据集？

我从以下几点开始:

select name, key1, value1
from customers
lateral view explode(address) table1 as key1, value1

这让我成为了那里的一部分。它拆分了“计费”和“运输”字段。但是，我不能爆掉剩下的。我试过这个，但收到一条错误消息:

select name, key1, key2, value2
from customers
lateral view explode(address) table1 as key1, value1
lateral view explode(value1) table2 as key2, value2

我知道我做得不对，但不确定解决方法是什么？

谢谢，

车坦

Answer 1

演示

create table customers (Name string, addresses map<string,struct<street1:string,street2:string,city:string,state:string>>);

insert into customers 
select  'Bill Jones'
       ,map
        (
            'billing'   ,named_struct('street1','123 Main'   ,'street2','' ,'city','Chicago'  ,'state','IL')
           ,'shipping'  ,named_struct('street1','432 Copper' ,'street2','' ,'city','New York' ,'state','NY')
        )
;

选项 1

select  name

       ,addresses['billing'].street1   as billing_street1 
       ,addresses['billing'].street2   as billing_street2
       ,addresses['billing'].city      as billing_city   
       ,addresses['billing'].state     as billing_state  
                                       
       ,addresses['shipping'].street1  as shipping_street1
       ,addresses['shipping'].street2  as shipping_street2
       ,addresses['shipping'].city     as shipping_city   
       ,addresses['shipping'].state    as shipping_state  

from    customers
;

+------------+-----------------+-----------------+--------------+---------------+------------------+------------------+---------------+----------------+
|    name    | billing_street1 | billing_street2 | billing_city | billing_state | shipping_street1 | shipping_street2 | shipping_city | shipping_state |
+------------+-----------------+-----------------+--------------+---------------+------------------+------------------+---------------+----------------+
| Bill Jones | 123 Main        |                 | Chicago      | IL            | 432 Copper       |                  | New York      | NY             |
+------------+-----------------+-----------------+--------------+---------------+------------------+------------------+---------------+----------------+        
        

选项 2

select  name 
       ,key             as address_type
       ,value.street1
       ,value.street2
       ,value.city
       ,value.state
        
from    customers
        lateral view explode(addresses) a  
;

+------------+--------------+------------+---------+----------+-------+
|    name    | address_type |  street1   | street2 |   city   | state |
+------------+--------------+------------+---------+----------+-------+
| Bill Jones | billing      | 123 Main   |         | Chicago  | IL    |
| Bill Jones | shipping     | 432 Copper |         | New York | NY    |
+------------+--------------+------------+---------+----------+-------+