I created a table in Amazon Athena with defined partitions, but when I query the table, zero records are returned

Last updated: 2020-04-07

I ran a CREATE TABLE statement in Amazon Athena with expected columns and their data types. When I run the query SELECT * FROM table-name, the output is "Zero records returned."

Resolution

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 Amazon S3 LOCATION path for the input data. If the input LOCATION path is incorrect, then Athena returns zero records.

Double slash in LOCATION path

Athena doesn't support table location paths that include a double slash (//). For example, the following LOCATION path returns empty results:

s3://awsexamplebucket/myprefix//input//

To resolve this issue, copy the files to a location that doesn't have double slashes. Example AWS Command Line Interface (AWS CLI) command:

aws s3 cp s3://awsexamplebucket/myprefix//input// s3://awsexamplebucket/myprefix/input/ --recursive

Partitions not yet loaded

Athena creates metadata only when a table is created. The data is parsed only when you run the query. 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. Use MSCK REPAIR TABLE or ALTER TABLE ADD PARTITION to load the partition information into the catalog.

MSCK REPAIR TABLE

If the partitions are stored in a format that Athena supports, run 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 Amazon S3 paths like this:

  • s3://awsexamplebucket/athena/inputdata/year=2020/data.csv
  • s3://awsexamplebucket/athena/inputdata/year=2019/data.csv
  • s3://awsexamplebucket/athena/inputdata/year=2018/data.csv

If the data is located at the Amazon S3 paths that Athena expects, then repair the table by running a command like this:

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

After the table is created, load the partition information:

MSCK REPAIR TABLE Employee;

After the data is loaded, run the SELECT * FROM table-name query again.

ALTER TABLE ADD PARTITION

If the partitions aren't stored in a format that Athena supports, or are located at different Amazon S3 paths, run ALTER TABLE ADD PARTITION for each partition. For example, suppose that your data is located at the following Amazon S3 paths:

  • s3://awsexamplebucket/athena/inputdata/2020/data.csv
  • s3://awsexamplebucket/athena/inputdata/2019/data.csv
  • s3://awsexamplebucket/athena/inputdata/2018/data.csv

Given these paths, run a command like this:

ALTER TABLE Employee ADD
    PARTITION (year=2020) LOCATION 's3://awsexamplebucket/athena/inputdata/2020/'
    PARTITION (year=2019) LOCATION 's3://awsexamplebucket/athena/inputdata/2019/'
    PARTITION (year=2018) LOCATION 's3://awsexamplebucket/athena/inputdata/2018/'

After the data is loaded, run the SELECT * FROM table-name query again.


Did this article help you?

Anything we could improve?


Need more help?