我在 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/';
然而,下面的SQL语句可以正确运行并且仅返回正确的计数结果。S3 bucket objects contains JSON structure like this
{ "devId": "00abcdef1122334401", "type": "lora", "status": "huihuhukiyg" }
SELECT count(*) as total_s3_objects FROM "athena_db"."s3_json_objects"
此外,发布问题到StackOverflow之前我查看了以下链接和AWS Athena文档。 无法通过Amazon Athena读取json文件 AWS Athena从字符串字段中提取json_extract查询返回空值 任何评论或建议将不胜感激。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"