如何正确配置Athena表的时间戳格式列以解决解析错误

问题根源 #

你使用的org.openx.data.jsonserde.JsonSerDe（OpenX JSON SerDe）不识别表属性timestamp.formats，这个参数是Hive原生JSON SerDe的专属配置项。OpenX SerDe的时间戳格式配置需要放在SERDEPROPERTIES中，且参数名是单数的timestamp.format，而非复数形式。

解决方法 #

方法一：改用Hive原生JSON SerDe #

Hive原生的org.apache.hive.hcatalog.data.JsonSerDe支持timestamp.formats表属性，能直接解析你指定的时间戳格式。修改后的建表语句如下：

CREATE EXTERNAL TABLE json_tab(
  `timestamp` timestamp
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
WITH SERDEPROPERTIES (
    'ignore.malformed.json' = 'true'
)
LOCATION 's3://bucket/'
TBLPROPERTIES (
    'compression.type' = 'GZIP',
    'timestamp.formats' = 'yyyyMMdd\'T\'HH:mm:ss,yyyyMMdd\'T\'HH:mm:ss.SSS,yyyyMMdd HH:mm:ss'
);

方法二：调整OpenX SerDe的配置参数 #

如果必须使用OpenX SerDe，需将时间戳格式配置移至SERDEPROPERTIES，并使用正确的参数名timestamp.format：

CREATE EXTERNAL TABLE json_tab(
  `timestamp` timestamp
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
    'ignore.malformed.json' = 'true',
    'timestamp.format' = 'yyyyMMdd\'T\'HH:mm:ss,yyyyMMdd\'T\'HH:mm:ss.SSS,yyyyMMdd HH:mm:ss'
)
LOCATION 's3://bucket/'
TBLPROPERTIES (
    'json.max.read.errors' = '100',
    'compression.type' = 'GZIP'
);

额外检查项 #

• 确认S3中的JSON数据里，timestamp字段的格式完全匹配你指定的格式，避免存在无法解析的异常值。
• 若仍有报错，可适当调高json.max.read.errors的值，允许更多格式错误数据被忽略。

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