为什么我在 Amazon Athena 中尝试读取 JSON 数据时出现错误？

解决方法

检查以下常见问题：

使用正确版本的 JSON SerDe

Athena 使用以下两种版本的 JSON SerDes 来处理 JSON 数据：

• 本机 Apache Hive/HCatalog JsonSerDe (org.apache.hive.hcatalog.data.JsonSerDe)
• OpenX SerDe (org.openx.data.jsonserde.JsonSerDe)

如果您不确定自己使用的是哪个版本的 SerDe，可以两个 SerDe 版本都试试。如果您使用 OpenX SerDe，则可以忽略格式错误的记录，找出导致错误的记录，如下例所示。当 ignore.malformed.json 设置为 true 时，格式错误的记录将返回为 NULL。

CREATE EXTERNAL TABLE json (
    id int,
    name string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/';

查询新表以找出有格式错误记录的文件。例如：

SELECT "$PATH", * FROM your_table where your_column is NULL

每条记录使用一行

以下 JSON 记录格式正确：

{ "id" : 50, "name":"John" }
{ "id" : 51, "name":"Jane" }
{ "id" : 53, "name":"Jill" }

以下 JSON 记录格式不正确：

{
  "id" : 50,
  "name":"John"
},
{
  "id" : 51,
  "name":"Jane"
}
{
  "id" : 53,
  "name":"Jill"
}

这些记录格式也不正确：

{ "id" : 50, "name":"John" } { "id" : 51, "name":"Jane" } { "id" : 53, "name":"Jill" }

在每列中使用正确的数据类型

以下示例中的第二行包含错误的 “年龄” 数据类型。列值应为 “11” 而不是 “十一”。这会导致以下错误消息： HIVE_BAD_DATA: Error parsing field value 'eleven' for field 1: For input string: "eleven".

{"name":"Patrick","age":35,"address":"North Street"}
{"name":"Carlos","age":"eleven","address":"Flowers Street"}
{"name":"Fabiana","age":22,"address":"Main Street"}

使用正确的压缩 JSON 文件扩展名

当您使用压缩的 JSON 文件时，该文件必须以 “.json” 结尾，后跟压缩格式的扩展名，例如 “.gz”。例如，"myfile.json.gz" 是 gzip 文件的正确格式扩展名。

使用不区分大小写的列或将 case.insensitive 属性设置为 false

Athena 默认不区分大小写。如果您的列名仅有大小写之别（例如，“Column” 和 “column”），Athena 会生成错误 ("HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key")，并且您的数据在 Athena 中不可见。避免此问题的最简单方法是使用不区分大小写的列来生成数据。

如果您使用 OpenX SerDe，则可以使用区分大小写的密钥名称。为此，请将 case.insensitive SerDe 属性设置为** false**，然后为大写密钥添加映射。例如，可以通过以下类似方法使用大写和小写列：

{"Username": "bob1234", "username": "bob" }

使用以下 SerDe 属性：

CREATE external TABLE casesensitive_json (user_name String,username String)
ROW FORMAT serde 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'mapping.user_name' = 'Username','case.insensitive'='false')
LOCATION 's3://awsexamplebucket/';

确保 JSON SerDE 表中的所有行均为 JSON 格式

要查找 Athena 表中是否存在无效的 JSON 行或文件名，请执行以下操作：

1.    使用输入文件中不存在的分隔符创建表。运行以下类似的命令：

CREATE EXTERNAL TABLE IF NOT EXISTS json_validator (jsonrow string) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '%'
location 's3://awsexamplebucket/';

2.    运行与以下类似的查询以返回无效 JSON 行的文件名、行详细信息和 Amazon S3 路径。

WITH testdataset AS (SELECT "$path" s3path,jsonrow,try(json_parse(jsonrow)) isjson FROM json_validator)
SELECT * FROM testdataset WHERE ISJSON IS NULL;

相关信息

读取 JSON 数据的最佳实践

排除 JSON 相关错误