How to set date DDL in Amazon Athena


429 观看


27 作者的声誉

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)

作者: mrmatt11 的来源 发布者: 2017 年 12 月 27 日

回应 1


569 作者的声誉


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.

作者: Dhaval 发布者: 2017 年 12 月 28 日