如何在Hive或Presto中将下列字典格式的列转换为不同的格式?

af7jpaap  于 11个月前  发布在  Hive
关注(0)|答案(2)|浏览(277)

我有一个配置单元表如下:
| 事件名称|与会者_按_国家|
| --|--|
| 一|美国:5}|
| B| {“美国”:4,“英国”:3,“加拿大”:2}|
| C| {“英国”:2,“加拿大”:1}|
我想得到一个新的表如下:
| 国家|人数|
| --|--|
| 美国| 9 |
| 英国| 5 |
| CA| 4 |
如何在Hive或Presto中编写查询?

pxq42qpu

pxq42qpu1#

您可以使用以下内容:
如果attendees_per_countries的列类型是字符串,则可以使用以下命令:

WITH sample_data AS (
    select 
        event_name, 
        str_to_map(
            regexp_replace(attendees_per_countries,'[{|}]',''),
            ',',
            ':'
        ) as attendees_per_countries 
    FROM
        raw_data
        
)
select 
    regexp_replace(cm.key,"[' ]","") as country,
    SUM(cm.value) as no_of_people
from sample_data
lateral view explode(attendees_per_countries) cm
GROUP BY regexp_replace(cm.key,"[' ]","")
ORDER BY no_of_people DESC

字符串
但是,如果attendees_per_countries的列类型已经是map,则可以使用以下命令

select 
    regexp_replace(cm.key,"[' ]","") as country,
    SUM(cm.value) as no_of_people
from sample_data
lateral view explode(attendees_per_countries) cm
GROUP BY regexp_replace(cm.key,"[' ]","")
ORDER BY no_of_people DESC


下面是完整的可再现示例

with raw_data AS (
    select 'a' as event_name, "{'US':5}" as attendees_per_countries
    UNION ALL 
    select 'b', "{'US':4, 'UK': 3, 'CA': 2}"
    UNION ALL 
    select 'c', "{'UK':2, 'CA': 1}"
),
sample_data AS (
    select 
        event_name, 
        str_to_map(
            regexp_replace(attendees_per_countries,'[{}]',''),
            ',',
            ':'
        ) as attendees_per_countries 
    FROM
        raw_data
        
)
select 
    regexp_replace(cm.key,"[' ]","") as country,
    SUM(cm.value) as no_of_people
from sample_data
lateral view explode(attendees_per_countries) cm
GROUP BY regexp_replace(cm.key,"[' ]","")
ORDER BY no_of_people DESC


告诉我这对你是否有效

eaf3rand

eaf3rand2#

在presto中,如果你有attendees_per_countries作为map,你可以使用map_values,然后将它们与array_sum/reduce相加(我需要稍后使用,因为Athena不支持前者)。如果没有-你可以将数据视为JSON并将其转换为MAP(VARCHAR, INTEGER),然后使用上述函数:

WITH dataset(event_name, attendees_per_countries) AS (
   VALUES 
('a',   JSON '{"US":5}'),
('b',   JSON '{"US":4, "UK": 3, "CA": 2}'),
('c',   JSON '{"UK":2, "CA": 1}')
 ) 
 
SELECT event_name as country,
       reduce(
               map_values(cast(attendees_per_countries as MAP(VARCHAR, INTEGER))),
               0,
               (agg, curr) -> agg + curr,
               s -> s
           )      as number_of_people
FROM dataset
order by 2 desc

字符串
输出量:
| 国家|人数|
| --|--|
| B| 9 |
| 一| 5 |
| C| 3 |

相关问题