How to import nested json file in Athena

Question

The below is the nested json format I am trying to create in a table in Amazon Athena.

Can anyone help me to create the table and query the table?

{
  "Environment": "agilent-aws-sbx-21",
  "Source1": "sdx:HD3,dev:HQ2,test:HT1,prod:HP1",
  "Source2": "",
  "Source3": "",
  "Source4": ""
}

i have tried like this but query was not executing

CREATE EXTERNAL TABLE cfg_env ( environment string, source1 string, source2 string, source3 string, souce4 string ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ( 'serialization.format' = '1' ) STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://agilent-aws-sbx-21-enterprise-analytics/it_share/config/config_env/' TBLPROPERTIES ('classification'='json');

What have you tried? What problem are you facing? For tips on asking a good question, please see: How do I ask a good question? — John Rotenstein

shiva shiva · Accepted Answer · 2021-07-27T09:38:58

i have tried like this but query was not executing

CREATE EXTERNAL TABLE cfg_env ( environment string, source1 string, source2 string, source3 string, souce4 string ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ( 'serialization.format' = '1' ) STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://agilent-aws-sbx-21-enterprise-analytics/it_share/config/config_env/' TBLPROPERTIES ('classification'='json');

How to import nested json file in Athena

1 Answers