Change the string to tabular format using Athena query

Question

I have this JSON file

{"Item":{"author":{"S":"John"},"title":{"S":"Page A"},"mytext":{"S":"This is sample text"}}}
{"Item":{"author":{"S":"Ram"},"title":{"S":"Page B"},"mytext":{"S":"Another book text"}}}

I created a table in Athena:

CREATE EXTERNAL TABLE financials_raw_json (
  Item string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://lambdaoks/AWSDynamoDB/016/data/' 

I get the results as string. This is expected and correct.

But is it possible to get tabular results? For e.g.

author  title   mytext
John    Page A  This is sample text
Ram Page B  Another book text

Answer 1

Why are you trying to explode a string instead of creating the table as it exists in DynamoDB?

CREATE EXTERNAL TABLE IF NOT EXISTS financials_raw_json (
  Item struct <author:struct<S:string>,
               title:struct<S:string>,
               myText:struct<S:string>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION LOCATION 's3://lambdaoks/AWSDynamoDB/016/data/'

If you don't want to create the table manually you could run a Glue crawler on it with a JSON classifier which will provide you with the same output automatically.