Databricks中MAP类型列集合操作报错的解决方法咨询

错误[UNSUPPORTED_FEATURE.SET_OPERATION_ON_MAP_TYPE]的核心原因是：你的SQL逻辑中存在INTERSECT/EXCEPT这类集合操作，而参与操作的DataFrame包含了MAP类型的ROptions列，Databricks目前不支持集合操作直接处理MAP类型列。

方法1：拆分逻辑，先执行集合操作再生成MAP列 #

如果你的完整SQL是将当前查询结果与其他表做集合操作，可先只保留非MAP列完成集合操作，再关联原始数据生成ROptions列。

示例调整代码：

-- 1. 先执行集合操作（仅保留D_Name、Q_ID）
WITH set_operation_result AS (
    SELECT D_Name, Q_ID
    FROM (
        -- 原查询去掉ROptions列
        SELECT DISTINCT af.Name AS D_Name, af.Q_ID
        FROM CTE ers
        INNER JOIN Fact af ON ers.D_Name = af.Name AND ers.res.Q_ID = af.Q_ID
        LEFT OUTER JOIN Range qrd ON af.DB_Name = qrd.D_Name AND af.Q_ID = qrd.Q_ID AND COALESCE(ers.res.reply,'') = CAST(qrd.CHOICE AS STRING)
        LEFT OUTER JOIN Sect sc ON af.Name = sc.D_Name AND COALESCE(af.SCALE_ID, 0) = COALESCE(sc.SCALE_ID, 0) AND COALESCE(ers.res.reply, '') = CAST(sc.SCALE_LABEL_ID AS STRING)
        GROUP BY af.Name, af.Q_ID
    ) t
    -- 替换为你的实际集合操作
    INTERSECT
    SELECT other_D_Name, other_Q_ID FROM other_table
)
-- 2. 基于集合操作结果，重新关联数据生成ROptions列
SELECT
    sor.D_Name,
    sor.Q_ID,
    map_from_entries(collect_list((CAST(LTRIM(RTRIM(lower(ers.res.reply))) AS STRING)
                      ,named_struct('ReplyText', CASE WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '1' THEN TRIM('VERY POOR')
                      WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '2' THEN TRIM('POOR')
                      WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '3' THEN TRIM('FAIR')
                      WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '4' THEN TRIM('GOOD')
                      WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '5' THEN TRIM('VERY GOOD')
                      WHEN af.Q_TYPE = 'L' THEN COALESCE(Lower(qrd.LABEL), CAST(LTRIM(RTRIM(lower(ers.res.reply))) AS STRING))
                      ELSE LTRIM(RTRIM(ers.res.reply)) END
                        ,'TBFlag', CASE WHEN ers.res.reply BETWEEN CAST(af.R_MIN AS STRING) AND CAST(af.R_MAX AS STRING) THEN 1
                        WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND af.C_Q_FLAG = 'Y' AND CAST(ers.res.reply AS INT) = 5 THEN 1
                        WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND af.C_Q_FLAG = 'N' AND CAST(ers.res.reply AS INT) = 5 THEN 1
                        ELSE 0 END
                        ,'IgFlag', CASE WHEN sc.S_IGNORE_FLAG = 'Y' THEN 1 ELSE 0 END
                        ,'InvalidFlag', CASE WHEN af.SurveyItemPGAnalyticFlag = 1 THEN CASE WHEN ers.res.reply not between 1 and 5 THEN 1 ELSE 0 END
                        WHEN af.C_Flag = 1 THEN CASE WHEN qrd.CHOICE IS NULL THEN 1 ELSE 0 END 
                        ELSE 0 END
                        ,'RScore', CASE WHEN af.SCALE_ID IS NOT NULL AND sc.PRECISION IS NOT NULL THEN CAST(ers.res.reply AS DECIMAL(16, 13))
                        WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' THEN CAST(ROUND((CAST(ers.res.reply AS INT) - 1) * 100 / 4) AS DECIMAL(16, 13))
                        ELSE NULL END)))) AS ROptions
FROM set_operation_result sor
INNER JOIN Fact af ON sor.D_Name = af.Name AND sor.Q_ID = af.Q_ID
INNER JOIN CTE ers ON ers.D_Name = af.Name AND ers.res.Q_ID = af.Q_ID
LEFT OUTER JOIN Range qrd ON af.DB_Name = qrd.D_Name AND af.Q_ID = qrd.Q_ID AND COALESCE(ers.res.reply,'') = CAST(qrd.CHOICE AS STRING)
LEFT OUTER JOIN Sect sc ON af.Name = sc.D_Name AND COALESCE(af.SCALE_ID, 0) = COALESCE(sc.SCALE_ID, 0) AND COALESCE(ers.res.reply, '') = CAST(sc.SCALE_LABEL_ID AS STRING)
GROUP BY sor.D_Name, sor.Q_ID

方法2：拆解MAP列，完成集合操作后重组 #

如果必须在集合操作中保留MAP的内容，可先将MAP拆解为普通字段（key和struct的各个属性），完成集合操作后再重新组装成MAP：

示例调整代码：

-- 1. 拆解MAP为普通字段
WITH exploded_data AS (
    SELECT
        af.Name AS D_Name,
        af.Q_ID,
        CAST(LTRIM(RTRIM(lower(ers.res.reply))) AS STRING) AS reply_key,
        CASE WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '1' THEN TRIM('VERY POOR')
             WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '2' THEN TRIM('POOR')
             WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '3' THEN TRIM('FAIR')
             WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '4' THEN TRIM('GOOD')
             WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND ers.res.reply = '5' THEN TRIM('VERY GOOD')
             WHEN af.Q_TYPE = 'L' THEN COALESCE(Lower(qrd.LABEL), CAST(LTRIM(RTRIM(lower(ers.res.reply))) AS STRING))
             ELSE LTRIM(RTRIM(ers.res.reply)) END AS ReplyText,
        CASE WHEN ers.res.reply BETWEEN CAST(af.R_MIN AS STRING) AND CAST(af.R_MAX AS STRING) THEN 1
             WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND af.C_Q_FLAG = 'Y' AND CAST(ers.res.reply AS INT) = 5 THEN 1
             WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' AND af.C_Q_FLAG = 'N' AND CAST(ers.res.reply AS INT) = 5 THEN 1
             ELSE 0 END AS TBFlag,
        CASE WHEN sc.S_IGNORE_FLAG = 'Y' THEN 1 ELSE 0 END AS IgFlag,
        CASE WHEN af.SurveyItemPGAnalyticFlag = 1 THEN CASE WHEN ers.res.reply not between 1 and 5 THEN 1 ELSE 0 END
             WHEN af.C_Flag = 1 THEN CASE WHEN qrd.CHOICE IS NULL THEN 1 ELSE 0 END 
             ELSE 0 END AS InvalidFlag,
        CASE WHEN af.SCALE_ID IS NOT NULL AND sc.PRECISION IS NOT NULL THEN CAST(ers.res.reply AS DECIMAL(16, 13))
             WHEN af.Q_TYPE = 'A' AND af.DATA_TYPE = 'L' THEN CAST(ROUND((CAST(ers.res.reply AS INT) - 1) * 100 / 4) AS DECIMAL(16, 13))
             ELSE NULL END AS RScore
    FROM CTE ers
    INNER JOIN Fact af ON ers.D_Name = af.Name AND ers.res.Q_ID = af.Q_ID
    LEFT OUTER JOIN Range qrd ON af.DB_Name = qrd.D_Name AND af.Q_ID = qrd.Q_ID AND COALESCE(ers.res.reply,'') = CAST(qrd.CHOICE AS STRING)
    LEFT OUTER JOIN Sect sc ON af.Name = sc.D_Name AND COALESCE(af.SCALE_ID, 0) = COALESCE(sc.SCALE_ID, 0) AND COALESCE(ers.res.reply, '') = CAST(sc.SCALE_LABEL_ID AS STRING)
),
-- 2. 执行集合操作（基于拆解后的字段）
set_operation_result AS (
    SELECT D_Name, Q_ID, reply_key, ReplyText, TBFlag, IgFlag, InvalidFlag, RScore
    FROM exploded_data
    -- 替换为你的实际集合操作
    INTERSECT
    SELECT D_Name, Q_ID, reply_key, ReplyText, TBFlag, IgFlag, InvalidFlag, RScore FROM other_exploded_table
)
-- 3. 重新组装MAP列
SELECT
    D_Name,
    Q_ID,
    map_from_entries(collect_list((reply_key, named_struct('ReplyText', ReplyText, 'TBFlag', TBFlag, 'IgFlag', IgFlag, 'InvalidFlag', InvalidFlag, 'RScore', RScore)))) AS ROptions
FROM set_operation_result
GROUP BY D_Name, Q_ID

额外注意 #

你的原查询中GROUP BY af.ame存在笔误，应该是GROUP BY af.Name，已在示例中修正。

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