Cannot query Athena tables as the Glue crawler generated data having = instead of : in the column values

Question

I have created tables in Athena using Crawler to run on S3 path having GZ file format data, the table is generated but has data with = between key value pairs than : between them

**Sample data from table:**

`payload={x=[{y={z=123, code=null, p=null, q=null}}],r=10}`

I can query the data by dot for some, but for the ones inside array I am unable to use json_extract functions.

How can I add the separator as : instead of = or make some change in Athena while querying to extract values from array ?

Answer

This issue seems to be related to source data. During my replication I found that the input data should be something like below to get the output you are seeing Athena.

{"string": "payload={x=[{y={z=123, code=null, p=null, q=null}}],r=10}"}

Also, please note that Athena only reads json files that confirm with formats mentioned in [1]. Hence, request you to please reach out to support engineering with your input data to better understand the issue at hand.

————————

Reference:

[1] https://aws.amazon.com/premiumsupport/knowledge-center/error-json-athena/

================

Have a nice day!

Cannot query Athena tables as the Glue crawler generated data having = instead of : in the column values

Relevanter Inhalt