本文介绍了从嵌套的json源创建Athena表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 如何从嵌套的json文件创建Athena表?这是我的示例json文件。我只需要选择一些关键值对,例如Roofcondition和garagestalls。How shall I create a Athena table from the nested json file ? This is my sample json file. I only need selected key value pairs like roofcondition and 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文档,正确的版本应如下所示:First of all you sent wrong version of the JSON document, correct version should look like this:{"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上的表。您可以通过创建下表来实现此目的:Yes, you can query the table on Athena with nested json. You can achieved this, for example by creating the following table: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.keyfrom 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 这篇关于从嵌套的json源创建Athena表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
05-27 07:10
查看更多