I ran a CREATE TABLE statement in Amazon Athena with expected columns and their data types, but when I run the query select * from <table-name>, the output is Zero records returned

Here are some common reasons why the query might return zero records.

File selected in crawler settings

If you're using a crawler, be sure that the crawler is pointing to the Amazon Simple Storage Service (Amazon S3) bucket rather than to a file.

Incorrect LOCATION path

Verify the input data LOCATION path to Amazon S3. If the input LOCATION path is incorrect, then Athena returns zero records.

Partitions not yet loaded

If your table has defined partitions, the partitions might not yet be loaded into the AWS Glue Data Catalog or the internal Athena data catalog. Because Athena applies schemas on-read, Athena creates metadata only when a table is created. The data is parsed only when the query is run. Because data might not yet be located at the specified location, Athena returns zero records. If the partitions are defined in the CREATE TABLE statement, then load the partition information into the catalog by using the commands MSCK REPAIR TABLE or ALTER TABLE ADD PARTITION.

MSCK REPAIR TABLE

If the partitions are stored in a format that Athena supports, run the command MSCK REPAIR TABLE to load a partition's metadata into the catalog. For example, if you have a table that is partitioned on Year, then Athena expects 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 located at the S3 paths that Athena expects, then repair the table by running a command similar to the following: 

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, load the partition information by using a command similar to the following: 

MSCK REPAIR TABLE Employee;

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

ALTER TABLE ADD PARTITION

If the partitions aren't stored in a format that Athena supports, or are located at different S3 paths, run the command ALTER TABLE ADD PARTITION for each partition. For example, suppose that your data is located at the following 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 paths, 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, running the query 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

Updated: 2018-09-17