gpt4 book ai didi

amazon-athena - 从嵌套的 json 源创建 Athena 表

转载 作者:行者123 更新时间:2023-12-02 00:30:52 27 4
gpt4 key购买 nike

如何从嵌套的 json 文件创建 Athena 表?这是我的示例 json 文件。我只需要选定的键值对,例如 roofcondition 和 garagestalls。

{   "reportId":"7bc7fa76-bf53-4c21-85d6-118f6a8f4244",
"reportOrderedTS":"1529996028730",
"createdTS":"1530304910154",
"report":"{'summaryElements': [{'value': 'GOOD', 'key': 'roofCondition'},
{'value': '98', 'key': 'storiesConfidence'}{'value': '0', 'key':
'garageStalls'}], 'elements': [{'source': 'xyz', 'imageId': '0xxx_png',
'modelVersion': '1.21.0', 'key': 'pool'}, {'source': 'xyz', 'imageId': '0111_png', 'value': 'GOOD', 'modelVersion': '1.36.0', 'key': 'roofCondition','confidence': '49'}], }", "status":"Success", "reportReceivedTS":"1529996033830" }

最佳答案

首先你发送了错误版本的 JSON 文档,正确的版本应该是这样的:

{"reportId":"7bc7fa76-bf53-4c21-85d6-118f6a8f4244", "reportOrderedTS":"1529996028730", "createdTS":"1530304910154", "report":{"summaryElements": [{"value": "GOOD", "key": "roofCondition"},{"value": "98", "key": "storiesConfidence"},{"value": "0", "key": "garageStalls"}], "elements": [{"source": "xyz", "imageId": "0xxx_png", "modelVersion": "1.21.0", "key": "pool"},{"source": "xyz", "imageId": "0111_png", "value": "GOOD", "modelVersion": "1.36.0", "key": "roofCondition", "confidence": "49"}] }, "status":"Success", "reportReceivedTS":"1529996033830" }

是的,你可以用嵌套的json查询Athena上的表。例如,您可以通过创建下表来实现这一点:

CREATE EXTERNAL TABLE example(
`reportId` string,
`reportOrderedTS` bigint,
`createdTS` bigint,
`report` struct<
`summaryElements`: array<struct<`value`:string, `key`: string>>,
`elements`: array<struct<`source`: string, `imageId`:string, `modelVersion`:string, `key`:string, `value`:string, `confidence`:int>>>,
`status` string,
`reportReceivedTS` bigint
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://example'

这是示例查询:

select reportid,reportorderedts,createdts,
summaryelements.value, summaryelements.key, elements.source, elements.key
from example, UNNEST(report.summaryelements) t(summaryelements), UNNEST(report.elements) t(elements)

有用的链接:

https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html

https://docs.aws.amazon.com/athena/latest/ug/rows-and-structs.html

关于amazon-athena - 从嵌套的 json 源创建 Athena 表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52031322/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com