How to set date DDL in Amazon Athena

1
votes

I tried to create a table in Amazon's Athena using this DDL however after querying, I see that all of my dates values (in transactiondate field) are NULL. The source data format is simply mm/dd/yyyy i.e. 08/01/2017.

Any ideas?

CREATE EXTERNAL TABLE wf2( transactiondate date, amount decimal(10,0), description string, filename string)

1
amazon-athena
you have to provide the date string in specific format only. - Dhaval

1 Answers

1
votes

String value of date has to be in specific format for Athena to understand it as Date type. DDL of Athena are handled in Hive.

As per hive the expected format is 'yyyy-mm-dd'. Reference

The same goes for Timestamp type. Timestamps in text files have to use the format yyyy-mm-dd hh:mm:ss[.f...]. If they are in another format, declare them as the appropriate type (INT, FLOAT, STRING, etc.) and use a UDF to convert them to timestamps.