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


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 ?

1 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.





Have a nice day!

answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions