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

2 分钟阅读

当我尝试在 Amazon Athena 中读取 JSON 数据时，收到 NULL 或数据不正确错误。

解决方法

检查以下常见问题：

使用正确版本的 JSON SerDe

Athena 使用以下 JSON SerDes 之一来处理 JSON：

如果您需要对创建的表运行 INSERT INTO 查询，请使用 Hive JSON SerDe。

如果您使用 OpenX JSON 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

**注意：**如果您运行 Athena 查询来获取美化输出格式的 JSON 文件，您可以使用 Amazon Ion Hive SerDe。Amazon Ion Hive SerDe 允许一行数据分多行显示。使用此功能查询美化输出格式的 JSON 数据集，或使用换行符分割一行中的字段。

每条记录使用一行

以下 JSON 记录的格式正确，所有三个 JSON SerDe 均可读取：

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

以下 JSON 记录只能由 Amazon Ion Hive SerDe 读取：

{  "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" }

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

以下示例中的第二行包含错误的 “年龄” 数据类型。列值应为 “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 文件时，该文件必须以“.gz”结尾。例如，“myfile.json.gz”或“myfile.gz”是格式正确的扩展名。

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

Athena 默认不区分大小写。如果您的列名区分大小写，例如“Column”和“column”是两个列名，Athena 会生成以下错误：

"HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key"

此外，您的数据在 Athena 中不可见。为避免此错误，请使用不区分大小写的列生成数据。

如果您使用 OpenX SerDe，则可以使用区分大小写的键名称。为此，请将 SerDe 属性 case.insensitive 设置为 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 文件有重复的区分大小写的列名，则更新该文件以删除重复的列：

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

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

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

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

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

运行与以下类似的查询以返回无效 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;