如何在Spark SQL中过滤Hive Map列的指定键值对？

现有一张名为TABLE1的表，其中feature列为Map类型，原始数据如下：

+---------------+----------------------------+
| column_value  |          feature           |
+---------------+----------------------------+
| A37B          | {0:"6",1:"1",2:"a",3:"x"}  |
| ACCOUNT_ID    | {0:"6",1:"2",2:"a",3:"x"}  |
| ANB_200       | {0:"6",1:"3",2:"a",3:"x"}  |
| ANB_201       | {0:"6",1:"4",2:"b",3:"y"}  |
| AS82_RE       | {0:"6",1:"5",2:"b",3:"y"}  |
| ATTR001       | {0:"7",1:"2",2:"b",3:"y"}  |
| ATTR001_RE    | {0:"7",1:"3",2:"c",3:"y"}  |
| ATTR002       | {0:"7",1:"4",2:"c",3:"z"}  |
| ATTR002_RE    | {0:"7",1:"5",2:"c",3:"z"}  |
| ATTR003       | {0:"8",1:"3",2:"d",3:"z"}  |
| ATTR004       | {0:"8",1:"4",2:"d",3:"z"}  |
| ATTR005       | {0:"8",1:"5",2:"d",3:"z"}  |
| ATTR006       | {0:"9",1:"4",2:"d",3:"z"}  |
| ATTR006       | {0:"9",1:"5",2:"d",3:"z"}  |
+---------------+----------------------------+

需要保留feature列中键列表[0,3]对应的键值对，得到如下结果：

+---------------+----------------------------+
| column_value  |          feature           |
+---------------+----------------------------+
| A37B          | {0:"6",3:"x"}              |
| ACCOUNT_ID    | {0:"6",3:"x"}              |
| ANB_200       | {0:"6",3:"x"}              |
| ANB_201       | {0:"6",3:"y"}              |
| AS82_RE       | {0:"6",3:"y"}              |
| ATTR001       | {0:"7",3:"y"}              |
| ATTR001_RE    | {0:"7",3:"y"}              |
| ATTR002       | {0:"7",3:"z"}              |
| ATTR002_RE    | {0:"7",3:"z"}              |
| ATTR003       | {0:"8",3:"z"}              |
| ATTR004       | {0:"8",3:"z"}              |
| ATTR005       | {0:"8",3:"z"}              |
| ATTR006       | {0:"9",3:"z"}              |
| ATTR006       | {0:"9",3:"z"}              |
+---------------+----------------------------+

解决方案 #

方法1：直接构造新Map（兼容所有Spark版本）

如果指定的键数量不多，可直接通过map函数手动提取目标键的值，构造新Map：

SELECT 
  column_value,
  map(
    0, feature[0],
    3, feature[3]
  ) AS feature
FROM TABLE1;

这种方式简单直接，适合键数量较少的场景，无需依赖高版本Spark函数。

方法2：使用filter函数（Spark 3.0+）

若需保留的键列表较长或需动态指定键集合，可使用Spark 3.0及以上版本支持的filter函数，过滤Map中符合条件的键值对：

SELECT 
  column_value,
  filter(
    feature,
    (k, v) -> k IN (0, 3)
  ) AS feature
FROM TABLE1;

如果需要动态传入键列表，可将键集合定义为数组变量，提升灵活性：

WITH params AS (
  SELECT array(0, 3) AS keep_keys
)
SELECT 
  t.column_value,
  filter(
    t.feature,
    (k, v) -> array_contains(p.keep_keys, k)
  ) AS feature
FROM TABLE1 t
CROSS JOIN params p;

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