Hello - I have a complex JSON file that I need to parse, store the results in a staging table and then move it to ORC table by applying some transformations. The problem is that the JSON has a lot of objects and arrays. I was able to parse the file and store it in a stage JSONSerDe table by defining the various objects and arrays. However, I would like to denormalize the data and store it in the target ORC table. How do I map the JSON array in 1 row and create multiple rows out of it? Sample Data - {"eventHeader":{"eventType":"x",
"eventOutcome":"declined"},
"eventPayload":{"nr":"01",
"Flag":false,
"from":"45",
"to":"67",
"amts":[{"type":"A","amt":1,"impPrcsnAmt":4},
{"type":"B","amt":23,"impPrcsnAmt":4},
{"type":"C","amt":98,"impPrcsnAmt":4}
]
}} This shows up as one row in the stage table. As mentioned above, I would like to move it into 3 rows in the target table. Can anyone help and advise?
... View more
