AWS Athena Create Table with source string

Question

Is there a way to create one table based on json string and keep this whole json string as one column.

for example: I have one file contains two rows, like below

{"name": "Tommy", "Age": 16} 
{"name": "Will", "Age": 20}

What I want is to create one table that contains 3 columns, [name, age, org_string]. When I select org_string from this table, it just returns the original string.

Is that possible, and how could I do that?

Or how to create table with only two columns [name, age], but in the query result contains org_string.

Thanks

Amrit Jangid Amrit Jangid · Accepted Answer · 2019-10-23T09:28:11

Create table as -

CREATE EXTERNAL TABLE `table_name`(
  `org_string` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://bucket/location/'

Query data from table as -

SELECT 
JSON_EXTRACT_SCALAR(org_string, '$.name') as name,
JSON_EXTRACT_SCALAR(org_string, '$.Age') as age,
org_string
from table_name

AWS Athena Create Table with source string

1 Answers