为什么我在 Amazon Athena 中尝试读取 JSON 数据时遇到错误?
上次更新日期:2022 年 5 月 23 日
我在 Amazon Athena 中尝试读取 JSON 数据时,遇到 NULL 或数据不正确错误。
解决方法
检查以下常见问题:
使用正确版本的 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" }
在每列中使用正确的数据类型
下例的第二行包含用于“age”的错误数据类型。列值应为“11”而不是“eleven”。这会导致出现以下错误消息: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;