Hive侧视图分解

iqxoj9l9 于 2021-06-26 发布在 Hive

关注(0)|答案(2)|浏览(333)

我在其中一个字段中有嵌套Map的数据。例如，数据位于一个名为“customers”的表中，如下所示：
姓名：比尔·琼斯
地址：{“billing”：{“street”：“123 main”，“city”：“chicago”，“state”：“il”}，“shipping”：{“street”：“432 copper”，“city”：“new york”，“state”：“ny”}}
我可以写什么样的查询来分解整个数据集？
我从以下几点开始：

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

这让我有一部分的路要走。它将“billing”和“shipping”字段分开。然而，我不能爆炸出剩下的。我试过了，但收到一条错误信息：

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

我知道我做得不对，但不确定解决办法是什么？
谢谢，
车坦

Hive

来源：https://stackoverflow.com/questions/42403306/hive-lateral-view-explode

2条答案

按热度按时间

ds97pgxw1#

根据您的评论，您有以下结构
姓名（字符串）、地址（Map>）、电子邮件（字符串）、电话（字符串）、支出（整数）
这里是一个字符串的Map，而不是一个Map，所以你可以用下面的方法来查询数据

select name, key1, 
value1.street1,
value1.street2,
value1.city,
value1.state
from customers
lateral view explode(address) table1 as key1, value1

赞(0）回复(0）举报 2021-06-26

mrphzbgm2#

演示

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    |
+------------+--------------+------------+---------+----------+-------+

赞(0）回复(0）举报 2021-06-26

我来回答

Hive侧视图分解

2条答案

演示

相关问题

热门标签

最新问答