Athena partitioned table yielding no results

Question

I'm trying to create a partitioned table in Athena. On S3, I have the following directory structure, as per the guidelines:

aws s3 ls s3://arapbi/polygon/tickers/
                           PRE dt=2014-01-06/
                           PRE dt=2014-01-07/
                           PRE dt=2014-01-08/
                           PRE dt=2014-01-09/
                           PRE dt=2014-01-10/ 
                           ...

Where a single csv file lives in each folder.

I've tried to create a partitioned table in Athena using the following:

CREATE EXTERNAL TABLE tickers
    (rownum STRING,
    ticker STRING,
    timestamp timestamp,
    open DOUBLE,
    close DOUBLE,
    volume_weighted_average_price DOUBLE,
    volume DOUBLE,
    transactions DOUBLE,
    date STRING
)
PARTITIONED BY (dt STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\","skip.header.line.count"="1",  "serialization.null.format"="")
LOCATION 's3://arapbi/polygon/tickers';

The table creation succeeds. When I query the table, though, it returns no results.

When I create this table without the partitions, everything works as expected. Something about partitioning is causing this table to not return any results.

I'm still trying to debug this but wanted to ask in case anyone has had this problem before. Thanks in advance if you've got some ideas!

Answer 1

It turns out MSCK REPAIR TABLE tickers is all it needs :)