amazon-s3 - 如何将包含 JSON 字段的 CSV 文件加载到 Amazon Athena

Question

我在 s3 中有一个 CSV(制表符分隔)，需要在 JSON 字段上查询。

uid\tname\taddress
1\tmoorthi\t{"rno":123,"code":400111}
2\tkiranp\t{"rno":124,"street":"kemp road"}

如何在 Amazon Athena 中查询此数据？

我应该能够查询:

select uid
from table1
where address['street']="kemp road";

Answer 1

您可以尝试使用json_extract()命令。

来自Extracting Data from JSON - Amazon Athena :

You may have source data with containing JSON-encoded strings that you do not necessarily want to deserialize into a table in Athena. In this case, you can still run SQL operations on this data, using the JSON functions available in Presto.

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},
           {"name":"project2", "completed":true}]}'
    AS blob
)
SELECT
  json_extract(blob, '$.name') AS name,
  json_extract(blob, '$.projects') AS projects
FROM dataset

此示例展示了如何使用 json_extract() 从 JSON 中提取字段。因此，您可能能够执行以下操作:

select uid
from table1
where json_extract(address, '$.street') = "kemp road";