I am uploading the below JSON format data into the AWS S3 bucket.
{
"time": 1663090620000,
"data": [
[
1,
[
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
]
]
]
}
I have data types in my object for the fields in the JSON as mentioned below.
time - Long
data - List<List<Object>>
I tried creating the below schema in Athena with partition projection by pointing to S3 bucket but I am not able to map the data field in my JSON data with amazon Athena supported data types - https://docs.aws.amazon.com/athena/latest/ug/data-types.html.
CREATE EXTERNAL TABLE `test_data_db`.`test_data_table`(
`time` bigint COMMENT 'from deserializer',
`data` array<array<double>> COMMENT 'from deserializer')
PARTITIONED BY (
`id` string,
`creation_date` date
)
ROW FORMAT SERDE
'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://test-data-bucket/test-data'
TBLPROPERTIES (
'has_encrypted_data'='false',
'projection.creation_date.format'='yyyy-MM-dd',
'projection.creation_date.range'='2010-01-01,2050-12-31',
'projection.creation_date.type'='date',
'projection.enabled'='true',
'projection.id.type'='injected',
'storage.location.template'='s3://test-data-bucket/test-data/id=${id}/creation_date=${creation_date}',
'transient_lastDdlTime'='1663357524')
Could you please help me how to map my JSON data field with the Athena data type?
