如何快速扁平化Athena表中含大量字段的JSON列？

针对你遇到的5000个字段的JSON列手动编写SELECT语句效率极低的问题，这里有几个实用的高效替代方案：

1. 用Glue Crawler自动生成扁平化表结构 #

Athena和Glue深度集成，Glue Crawler可以自动解析JSON的所有字段并生成对应的扁平表，完全不用手动写字段映射：

• 登录AWS Glue控制台，创建新的Crawler
• 配置Crawler的数据源为你的Athena表对应的底层存储（比如S3路径）
• 在Crawler的配置中，选择"JSON"作为数据格式，开启自动schema检测
• 运行Crawler后，它会自动识别JSON中的所有5000个字段，生成一个包含所有扁平列的新表
• 直接查询这个新表即可，无需手动编写任何字段提取逻辑

2. 脚本动态生成SELECT语句 #

如果不想依赖Glue，可以用脚本（比如Python）自动生成完整的SQL语句：

1. 先执行一条查询获取JSON列的样本数据：

SELECT your_json_column FROM dataset LIMIT 1

2. 用脚本解析这个样本JSON，提取所有键名
3. 循环生成每个字段的提取语句，示例代码：

import json

# 假设从查询结果中拿到的JSON字符串
sample_json = '{"field1": "val1", "field2": "val2", ...}'
json_dict = json.loads(sample_json)

# 生成SELECT部分的字段列表
select_fields = [f"your_json_column ['{key}'] AS {key}" for key in json_dict.keys()]
# 拼接成完整SQL
full_sql = f"""SELECT
    {',\n    '.join(select_fields)},
    department
FROM
    dataset
    CROSS JOIN UNNEST(people) AS t (your_json_column)
"""

4. 直接运行生成好的SQL即可，全程无需手动编写5000个字段

3. 用UNNEST+PIVOT动态转列（适合临时场景） #

如果只是临时查询，也可以用Presto的map_entries函数先把JSON转成键值对行，再通过PIVOT转成列：

SELECT *
FROM (
    SELECT
        department,
        entry.key AS field_name,
        entry.value AS field_value
    FROM dataset
    CROSS JOIN UNNEST(people) AS t (names)
    CROSS JOIN UNNEST(map_entries(json_parse(names))) AS t (entry)
)
PIVOT (
    MAX(field_value)
    FOR field_name IN ('field1', 'field2', ...) -- 这里的列名列表可结合脚本生成
)

不过这种方式在5000列的场景下性能可能不如前两种，更适合小范围临时处理

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