亚马逊 Athena 无法读取 S3 JSON 对象文件，而且 Athena 的选择查询对于 JSON 键列返回空结果集。

Question

我在 Athena 中创建了一个具有以下结构的表

CREATE EXTERNAL TABLE s3_json_objects (
    devId string,
    type string,
    status string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://mybucket/folder1/data/athena_test/';

S3 bucket objects contains JSON structure like this

{
    "devId": "00abcdef1122334401",
    "type": "lora",
    "status": "huihuhukiyg"
}

然而，下面的SQL语句可以正确运行并且仅返回正确的计数结果。

SELECT count(*) as total_s3_objects FROM "athena_db"."s3_json_objects"

BUT whenever I query below SQL select statement to fetch the JSON values from S3, It's returns result sets with empty values for columns

SELECT devid FROM "athena_db"."s3_json_objects"
SELECT json_extract(devid , '$.devid') as Id FROM "athena_db"."s3_json_objects"
SELECT * FROM "athena_db"."s3_json_objects"

此外，发布问题到StackOverflow之前我查看了以下链接和AWS Athena文档。 无法通过Amazon Athena读取json文件 AWS Athena从字符串字段中提取json_extract查询返回空值 任何评论或建议将不胜感激。

- SamDev

Answer 1

JSON必须在单行中，正如AWS Athena文档中的此页面所述。您可以在单独的行上拥有多个JSON对象，但每个完整的对象只能跨越一行。

示例（所有内容都可以在一个S3对象中）：

{"devId": "a1", "type": "b1", "status": "c1"}
{"devId": "a2", "type": "b2", "status": "c2"}

- Tom

---

我的对象只跨越一行，但这并没有帮助。如果我执行查询 "select * from xxx"，它会显示像这样的消息：| 1 | foo | {"int_field": "1", "str_field": "hello"} |，而如果我使用 json_extract，它只显示数字。我需要生成 JSON，因为如果进行简单选择，则所有数字都在引号中。 - Sergius

@Sergius，此外，您不能在值之间留有空格。请删除所有空格。 - h-rai

Answer 2

由于 Glue 引擎采用了 Spark，因此它可以读取多行 JSON 对象。如果你无法轻松地在线生成这些 JSON 对象，则可以通过 Glue 将这些 JSON 对象转换为 Parquet 格式。

- Tanveer Uddin

Answer 3

使用jsonlines将JSON转换为jsonlines，然后Athena就能够获取所有行。

- Somabho