如何在Hive或Presto中将字典格式列转换为按国家汇总人数的格式

前提说明 #

默认你所用的表名为event_attendees，若attendees_per_countries为JSON字符串类型，需先转换为Map类型再执行拆解逻辑。

Presto 写法 #

SELECT 
  country,
  SUM(people_count) AS number_of_people
FROM event_attendees
-- 将Map类型字段拆解为国家、人数两列
CROSS JOIN UNNEST(attendees_per_countries) AS t(country, people_count)
GROUP BY country
ORDER BY number_of_people DESC;

如果attendees_per_countries是JSON字符串，调整字段转换逻辑即可：

SELECT 
  country,
  SUM(people_count) AS number_of_people
FROM (
  SELECT 
    -- 把JSON字符串转为Map类型
    CAST(json_parse(attendees_per_countries) AS map(varchar, int)) AS attendees_map
  FROM event_attendees
) t
CROSS JOIN UNNEST(attendees_map) AS t(country, people_count)
GROUP BY country
ORDER BY number_of_people DESC;

Hive 写法 #

SELECT 
  country,
  SUM(people_count) AS number_of_people
FROM event_attendees
-- 用LATERAL VIEW + EXPLODE拆解Map字段
LATERAL VIEW EXPLODE(attendees_per_countries) tmp AS country, people_count
GROUP BY country
ORDER BY number_of_people DESC;

如果attendees_per_countries是JSON字符串，先通过from_json转换为Map类型：

SELECT 
  country,
  SUM(people_count) AS number_of_people
FROM (
  SELECT 
    -- 把JSON字符串转为Map类型
    from_json(attendees_per_countries, 'map<string,int>') AS attendees_map
  FROM event_attendees
) t
LATERAL VIEW EXPLODE(attendees_map) tmp AS country, people_count
GROUP BY country
ORDER BY number_of_people DESC;

内容的提问来源于stack exchange，提问作者yueyue