如何在AWS Athena创建表时为字符串型嵌套JSON字段定义Schema

你的原始数据里role字段是转义后的JSON字符串，不是原生的嵌套JSON结构，直接用struct类型定义会和实际存储的字符串类型不匹配，导致解析报错。以下是两种可行的解决思路：

方案1：用视图封装解析逻辑（推荐，不改动原始数据） #

先创建读取原始数据的外部表，将role定义为string类型，再创建视图自动把字符串解析为struct，查询视图的体验和你期望的一级表完全一致：

-- 1. 创建读取原始数据的外部表
CREATE EXTERNAL TABLE profile_raw (
    id string,  -- 原始id是字符串格式，后续转bigint
    profile string,
    role string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<bucket_name>/<path>';

-- 2. 创建视图，自动解析role为struct
CREATE VIEW profile AS
SELECT
    CAST(id AS bigint) AS id,
    profile,
    FROM_JSON(role, 'struct<admin_role:string, developer_role:string>') AS role
FROM profile_raw;

如果你的Hive版本低于2.3.0（不支持FROM_JSON函数），可以用GET_JSON_OBJECT手动提取字段组装struct：

CREATE VIEW profile AS
SELECT
    CAST(id AS bigint) AS id,
    profile,
    NAMED_STRUCT(
        'admin_role', GET_JSON_OBJECT(role, '$.admin_role'),
        'developer_role', GET_JSON_OBJECT(role, '$.developer_role')
    ) AS role
FROM profile_raw;

方案2：用CTAS创建结构化物理表 #

如果你希望直接创建一个存储结构化数据的物理表，可以用CTAS语句将解析后的结果写入新表：

-- 1. 先创建原始数据外部表（同方案1第一步）
CREATE EXTERNAL TABLE profile_raw (
    id string,
    profile string,
    role string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<bucket_name>/<path>';

-- 2. 创建结构化的外部表
CREATE EXTERNAL TABLE profile (
    id bigint,
    profile string,
    role struct<admin_role:string, developer_role:string>
)
LOCATION 's3://<bucket_name>/<path_structured>'  -- 指定新的存储路径
AS
SELECT
    CAST(id AS bigint),
    profile,
    FROM_JSON(role, 'struct<admin_role:string, developer_role:string>') AS role
FROM profile_raw;

关键注意点 #

• 原始数据中的id是字符串类型（值为"212"），直接定义为bigint会解析失败，必须通过CAST转换类型。
• 若使用FROM_JSON，需要确保Hive版本在2.3.0及以上，低版本请用GET_JSON_OBJECT的兼容方案。

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