Athena Presto查询Map类型列动态键值对的实现方案

Athena/Presto #

Presto不支持直接用map_var['abc_**']这类通配符写法，但可以通过map_entries或map_filter函数处理Map，过滤出符合前缀的键值对：

提取符合前缀的键值对，转成新Map #

SELECT 
  map_filter(map_var1, (k, v) -> k LIKE 'abc_%') AS filtered_map
FROM tab1;

如果需要更精准的正则匹配（比如只匹配abc_后接数字的键）：

SELECT 
  map_filter(map_var1, (k, v) -> regexp_like(k, '^abc_\\d+$')) AS filtered_map
FROM tab1;

提取符合前缀的所有值，转成数组 #

SELECT 
  array_values(map_filter(map_var1, (k, v) -> k LIKE 'abc_%')) AS filtered_values
FROM tab1;

Hive #

Hive无法直接通过通配符访问Map键，需要先拆分Map为键值对行，过滤后再聚合：

拆分过滤后重新组装成Map #

SELECT 
  original_id,
  str_to_map(concat_ws(',', collect_list(concat(k, ':', v)))) AS filtered_map
FROM (
  SELECT 
    original_id, -- 表的主键/唯一标识列
    k, v
  FROM tab1
  LATERAL VIEW explode(map_var1) kv AS k, v
  WHERE k LIKE 'abc_%'
) t
GROUP BY original_id;

提取符合前缀的值到数组 #

SELECT 
  original_id,
  collect_list(v) AS filtered_values
FROM (
  SELECT 
    original_id,
    k, v
  FROM tab1
  LATERAL VIEW explode(map_var1) kv AS k, v
  WHERE k RLIKE '^abc_\\d+$' -- 正则匹配规则
) t
GROUP BY original_id;

Spark SQL #

Spark SQL支持map_filter函数快速处理，也兼容拆分聚合的方式：

用map_filter直接过滤 #

SELECT 
  map_filter(map_var1, (k, v) -> k LIKE 'abc_%') AS filtered_map,
  array_values(map_filter(map_var1, (k, v) -> regexp_like(k, '^abc_\\d+$'))) AS filtered_values
FROM tab1;

拆分后聚合（兼容旧版本Spark） #

SELECT 
  original_id,
  map_from_arrays(collect_list(k), collect_list(v)) AS filtered_map,
  collect_list(v) AS filtered_values
FROM (
  SELECT 
    original_id,
    k, v
  FROM tab1
  LATERAL VIEW explode(map_var1) kv AS k, v
  WHERE k LIKE 'abc_%'
) t
GROUP BY original_id;

Python（PySpark） #

直接通过DataFrame API操作：

from pyspark.sql import functions as F

# 过滤Map，保留符合前缀的键值对
df = df.withColumn(
    "filtered_map",
    F.map_filter(F.col("map_var1"), lambda k, v: k.like("abc_%"))
)

# 提取符合前缀的值到数组
df = df.withColumn(
    "filtered_values",
    F.array_values(F.map_filter(F.col("map_var1"), lambda k, v: F.regexp_like(k, "^abc_\\d+$")))
)

df.show()

Scala（Spark） #

import org.apache.spark.sql.functions._

val df = spark.table("tab1")
  .withColumn("filtered_map", map_filter(col("map_var1"), (k, v) => k.like("abc_%")))
  .withColumn("filtered_values", array_values(map_filter(col("map_var1"), (k, v) => regexp_like(k, "^abc_\\d+$"))))

df.show()

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