How map my custom data type with AWS Athena data type?

Viewed 28

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?

1 Answers

I have gone through your table definition and sample data provided and understood the problem. The actual data has three levels of nested array items where as your table definition has only two levels defined.

So I tried changing data array<array<double>> to data array<array<array<double>>> and was able to query the data field fine. It is up to you to decide on the data type as double or int as per your use case.

enter image description here

Related