Hive(Athena)：将PK数组映射为FK表值列并保留原表字段

表结构 #

survey表 #

answers表 #

期望输出 #

方案1：直接多表关联（适合固定长度数组） #

如果answers数组的元素数量固定，可直接通过数组索引取出每个PK，多次关联answers表获取对应文本：

SELECT
    s.pk AS survey_pk,
    a1.text AS answer1,
    a2.text AS answer2,
    a3.text AS answer3,
    a4.text AS answer4,
    s.date_created
FROM survey s
LEFT JOIN answers a1 ON a1.pk = s.answers[1] -- Hive/Athena数组索引从1开始
LEFT JOIN answers a2 ON a2.pk = s.answers[2]
LEFT JOIN answers a3 ON a3.pk = s.answers[3]
LEFT JOIN answers a4 ON a4.pk = s.answers[4]

说明 #

• 利用Hive/Athena数组从1开始索引的特性，逐个提取answers数组中的PK值
• 通过LEFT JOIN确保即使数组中某个位置无对应PK，结果仍保留该行（对应列显示NULL）
• 手动指定每一列的名称，同时保留survey表的原始字段

方案2：UNNEST + PIVOT（灵活适配数组长度） #

如果answers数组长度不固定，但需要输出固定列名的结果，可先拆分数组再转置：

WITH survey_unnested AS (
    SELECT
        s.pk AS survey_pk,
        s.date_created,
        a.text,
        -- 生成对应列名（answer1、answer2...）
        CONCAT('answer', CAST(row_number() OVER(PARTITION BY s.pk ORDER BY idx) AS VARCHAR)) AS answer_col
    FROM survey s
    -- 拆分数组并保留元素位置索引
    CROSS JOIN UNNEST(s.answers) WITH ORDINALITY AS t(pk, idx)
    LEFT JOIN answers a ON a.pk = t.pk
)
SELECT
    survey_pk,
    date_created,
    answer1,
    answer2,
    answer3,
    answer4
FROM survey_unnested
-- 转置为指定列
PIVOT (
    MAX(text) FOR answer_col IN ('answer1', 'answer2', 'answer3', 'answer4')
) AS pivot_result

说明 #

• UNNEST(s.answers) WITH ORDINALITY将数组拆分为单行，同时保留元素的位置索引idx
• 用row_number()生成对应列名，确保顺序与原数组一致
• PIVOT将行数据转置为指定列，MAX(text)用于聚合（同一列只会有一个值）

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