How to set date DDL in Amazon Athena

amazon-athena

429 观看

1回复

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


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 日
32x32