I created a table in Athena with expected columns and their data types, along with partitioning details, but when I run a “select * from <table-name>”, I get empty results.

First, verify that the input data LOCATION path to S3 is correctly specified. If the input LOCATION path is incorrect, Athena returns empty results.

Next, it's likely that the table has defined partitions, but the partitions are not loaded yet in the Athena Catalog Manager. Because Athena applies schemas on-read, Athena only creates metadata when a table is created; the data is parsed only when the query is run. Because data might not be located at the specified location yet, an empty result is returned. If the partitions are defined in the DDL statement, load the partitioning information in the catalog. Execute one of the following statements based on how the partitions are stored in S3:

MSCK REPAIR TABLE

MSCK REPAIR TABLE will load a partition's metadata in the catalog, provided that the partitions are in the format that Athena expects. For example, if you had a table partitioned on “Year”, then Athena would expect to find the data at S3 paths similar to the following:

s3://mybucket/athena/inputdata/year=2016/data.csv
s3://mybucket/athena/inputdata/year=2015/data.csv
s3://mybucket/athena/inputdata/year=2014/data.csv

If the data is at the S3 paths that Athena expects, you can run a command similar to the following to repair the table:

CREATE EXTERNAL TABLE Employee (
    Id INT,
    Name STRING,
    Address STRING
) PARTITIONED BY (year INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION 's3://mybucket/athena/inputdata/';

After the table is created, you can load the partition information using a command similar to the following:

MSCK REPAIR TABLE Employee;

After the data is loaded, “select * from <table-name>” should return data.

ALTER TABLE ADD PARTITION

If the partitions are not in the format Athena expects, or are located in different S3 paths, execute ALTER TABLE ADD PARTITION for each partition. For example, assume your data is located at the following example S3 paths:

s3://mybucket/athena/inputdata/2016/data.csv
s3://mybucket/athena/inputdata/2015/data.csv
s3://mybucket/athena/inputdata/2014/data.csv

Given these example paths, you can run a command similar to the following:

ALTER TABLE Employee ADD
    PARTITION (year=2016) LOCATION 's3://mybucket/athena/inputdata/2016/'
    PARTITION (year=2015) LOCATION 's3://mybucket/athena/inputdata/2015/'
    PARTITION (year=2014) LOCATION 's3://mybucket/athena/inputdata/2014/'

After the data is loaded, “select * from <table-name>” should return data. 


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2016-12-15