I have the data in the format of JSON array which looks something like this:
[{"name": "abhi", "job": "developer"},{"name": "amal", "job": "captain"},{"name": "nizam", "job": "ca"},{"name": "akshay", "job": "doctor"}]
in glue, i created a crawler with custom classifier having jsonpath defined as $[*]. After crawling, it successfully identified the column names as
- name string
- job string
But when i am trying to query the table using Athena, i am not getting the output in the expected format.
Additional details:
- The file from which the data is crawled -> is residing in amazon s3.
- Query used in athena:
SELECT * FROM "<data_source>"."<database_name>"."<table_name>"; - IAM role used is having all the necessary permissions
- Classification -> JSON
- My data is NOT following the format -> single JSON object per line
I was expecting the output to be
| name | job |
|---|---|
| abhi | developer |
| amal | captain |
| nizam | ca |
| akshay | doctor |
The output which i received was
| name | job |
|---|---|
| {"name": "abhi", "job": "developer"} | {"name": "amal", "job": "captain"} |