How to determine the "date" column in AWS Athena?

Question

I am trying to use Athena AWS to query csv data files on S3, there are several date columns in the csv file, but the column types in Athena do not have a "date", I tried to use "timestamp", but then the date columns could not be requested.

Any suggestions?

Answer 1

In fact, Athena has a column type date.

Here is a short sample table with dates.

CSV:

2016-10-12,2016-10-01,hello,world1
2016-10-13,2016-10-01,hello,world2
2016-10-14,2016-10-01,hello,world3
2016-10-15,2016-10-01,hello,world4

DDL:

CREATE EXTERNAL TABLE test (
  startdate date,
  enddate date,
  val1 string,
  val2 string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION 's3://test-data/test/';

QUERY

select * from test where startdate > DATE'2016-10-13';

Answer 2

Amazon Athena Apache Hive. Apache Hive primitive_type "date" Hive 0.12.0 . , Athena.

: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL