Databricks/Spark SQL 如何查询Map类型字段identityMap的相关数据

提示：Map拆行的逻辑和TSQL中用OPENJSON解析JSON数组为行的逻辑一致，你可以类比理解。

1. 查询每行数据的Map字段key总数 #

不同引擎可直接调用对应函数实现：

• Spark SQL / Hive：使用size()函数
  示例代码：

  SELECT 
    size(identityMap) AS identity_key_count 
  FROM profiles
  -- 过滤空Map避免返回NULL
  WHERE identityMap IS NOT NULL
  

• Presto / Trino：使用cardinality()函数
  示例代码：

  SELECT 
    cardinality(identityMap) AS identity_key_count 
  FROM profiles
  WHERE identityMap IS NOT NULL
  

2. 提取每个key及对应value、统计数量 #

需要先将Map类型的键值对拆分为行格式，再做统计：

Spark SQL / Hive 实现 #

使用LATERAL VIEW EXPLODE语法炸开Map：

-- 拆分Map为每行对应一个key+value
SELECT 
  p.*,
  id_key,
  id_value
FROM profiles p
LATERAL VIEW EXPLODE(identityMap) exploded_table AS id_key, id_value
WHERE identityMap IS NOT NULL

-- 统计全局每个key对应的value总数、去重数
SELECT 
  id_key,
  COUNT(*) AS total_value_count,
  COUNT(DISTINCT id_value) AS distinct_value_count
FROM profiles p
LATERAL VIEW EXPLODE(identityMap) exploded_table AS id_key, id_value
WHERE identityMap IS NOT NULL
GROUP BY id_key

Presto / Trino 实现 #

使用UNNEST搭配MAP_KEYS、MAP_VALUES函数实现：

-- 拆分Map为每行对应一个key+value
SELECT 
  p.*,
  id_key,
  id_value
FROM profiles p,
UNNEST(MAP_KEYS(identityMap), MAP_VALUES(identityMap)) AS t(id_key, id_value)
WHERE identityMap IS NOT NULL

-- 统计全局每个key对应的value总数、去重数
SELECT 
  id_key,
  COUNT(*) AS total_value_count,
  COUNT(DISTINCT id_value) AS distinct_value_count
FROM profiles p,
UNNEST(MAP_KEYS(identityMap), MAP_VALUES(identityMap)) AS t(id_key, id_value)
WHERE identityMap IS NOT NULL
GROUP BY id_key

内容的提问来源于stack exchange，提问作者Alberto De Caro