Why do I get zero records when I query my Athena table?

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

There are several reasons why the SELECT * FROM table-name query might return zero records. Based on your use case, see the related section for common reasons and troubleshooting steps.

Troubleshoot AWS Glue partitions

Check your crawler settings

If you use a crawler, then confirm that the crawler points to the Amazon Simple Storage Service (Amazon S3) bucket and not a specific file. For example, use s3://amzn-s3-demo-bucket/new instead of s3://amzn-s3-demo-bucket/new/data.json.

Use the correct LOCATION path

Verify that the Amazon S3 LOCATION path for the input data is correct. Also, make sure that the S3 bucket path doesn't include a file or wild card. For example, use the s3://amzn-s3-demo-bucket/new LOCATION instead of s3://amzn-s3-demo-bucket/new/data.json.

Use a single slash in the LOCATION path

In Athena, you can't use table location paths that include a double slash (//). For example, the following LOCATION path returns empty results: s3://amzn-s3-demo-bucket/myprefix//input//

To copy the files to a location that doesn't have double slashes, run the following cp AWS CLI command:

aws s3 cp s3://amzn-s3-demo-bucket/myprefix//input// s3://amzn-s3-demo-bucket/myprefix/input/ --recursive

Note: Replace amzn-s3-demo-bucket/myprefix//input// with your current table location path, and amzn-s3-demo-bucket/myprefix/input/ with your new table location path.

It's a best practice to include only underscores or capitalization in your table's column names. For example, you can name a column table_name, but not table-name.

Note: AWS Glue and Athena can't read camel case, capital letters, or special characters other than the underscore.

Create Amazon S3 prefixes for each table

AWS Glue crawlers create separate tables for data that's stored in the same Amazon S3 prefix. However, when you query those tables in Athena, you get zero records. For example, your Athena query returns zero records if your table location is similar to the following examples:

• s3://amzn-s3-demo-bucket/table1.csv
• s3://amzn-s3-demo-bucket/table2.csv

To resolve this issue, create individual Amazon S3 prefixes for each table similar to the following examples:

• s3://amzn-s3-demo-bucket/table1/table1.csv
• s3://amzn-s3-demo-bucket/table2/table2.csv

Then, run the following query to update the location for your table:

ALTER TABLE table1 SET LOCATION 's3://amzn-s3-demo-bucket/table1';

Note: Replace amzn-s3-demo-bucket with your bucket name, and table1 with your table name.

Load your partitions

Athena creates metadata only when you create a table. The data parses only when you run the query. If your table has defined partitions, then AWS Glue Data Catalog or the internal Athena data catalog might not have loaded the partitions.

If Athena supports the format of your partitions, then run MSCK REPAIR TABLE to load a partition's metadata into the catalog. For example, you have a table that's partitioned on Year. In this case, Athena expects to find the data at Amazon S3 paths similar to the following examples:

• s3://amzn-s3-demo-bucket/athena/inputdata/year=2020/data.csv
• s3://amzn-s3-demo-bucket/athena/inputdata/year=2019/data.csv
• s3://amzn-s3-demo-bucket/athena/inputdata/year=2018/data.csv

If your data exists at the Amazon S3 paths that Athena expects, then repair the table with 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://amzn-s3-demo-bucket/athena/inputdata/';

After Athena creates the table, run the following command to load the partition information:

MSCK REPAIR TABLE Employee;

After Athena loads the data, run the following query again:

SELECT * FROM Employee;

If Athena doesn't support the format of your partitions, or the partitions are in different Amazon S3 paths, then run ALTER TABLE ADD PARTITION for each partition.

For example, your data is located at these example Amazon S3 paths:

• s3://amzn-s3-demo-bucket/athena/inputdata/2020/data.csv
• s3://amzn-s3-demo-bucket/athena/inputdata/2019/data.csv
• s3://amzn-s3-demo-bucket/athena/inputdata/2018/data.csv

For the preceding paths, run a command similar to the following example:

ALTER TABLE Employee ADD PARTITION (year=2020) LOCATION 's3://amzn-s3-demo-bucket/athena/inputdata/2020/'
    PARTITION (year=2019) LOCATION 's3://amzn-s3-demo-bucket/athena/inputdata/2019/'
    PARTITION (year=2018) LOCATION 's3://amzn-s3-demo-bucket/athena/inputdata/2018/'

After Athena loads the data, run the following query again:

SELECT * FROM Employee;

Check your file names

Verify that your file names don't start with an underscore or period.

When you process a query, Athena considers these files as placeholders and ignores them. If all the files in your Amazon S3 path have names that start with an underscore or period, then you get zero records.

For example, if you process a query with the following files in your Amazon S3 path, then you get zero records:

• s3://amzn-s3-demo-bucket/athena/inputdata/_file1
• s3://amzn-s3-demo-bucket/athena/inputdata/.file2

Note: If your Amazon S3 path includes placeholders and files that start with different characters, then Athena ignores only the placeholders and queries the other files. As a result, you might get one or more records.

Troubleshoot partition projection

Confirm that your values are within the range bounds

If your values are beyond the range bounds defined for partition projection, then your query returns zero rows.

For example, your data starts in 2020 and you define it as the following: projection.timestamp.range'='2020/01/01,NOW

If you run the following query, then the query completes successfully but returns zero rows:

SELECT * FROM table-name WHERE timestamp = '2019/02/02'

Verify your storage template's partitioning scheme

If your Amazon S3 storage location doesn't use the .../column=value... location pattern, then you must specify a custom Amazon S3 partitioning scheme. If you don't define a custom scheme, then your query returns zero records.

Use the correct custom storage template

If you use a custom template, then make sure that the template allows Athena to build your partition locations. Also, make sure that each placeholder and Amazon S3 path ends with a single forward slash.

For example, you define a year partition column with the PARTITIONED BY (year string) data definition language (DDL) statement, and your storage location is s3://amzn-s3-demo-bucket/athena/inputdata/Year=2022/. This location returns zero records. To resolve this issue, update the storage location to s3://amzn-s3-demo-bucket/athena/inputdata/Year=${year}.

Verify your partition properties

If you have partition columns with the enum, integer, date, or injected partition column type, then make sure that you correctly configure the partition properties. The configuration must allow Athena to build partition locations that match the structure of your data on Amazon S3.

For example, you have time-related data that comes in daily 1 hour after midnight in the s3://amzn-s3-demo-bucket/athena/inputdata/2022-01-01-01-00 location.

In this example, the Athena table uses the following partition properties:

'projection.dt.format' = 'yyyy-MM-dd-HH-mm','projection.dt.range' = '2022-01-01-00-00,NOW',
'projection.dt.interval' = '1',
'projection.dt.interval.unit' = 'DAYS'

Queries that run on the example table return zero records because the property's file locations correspond to midnight s3://amzn-s3-demo-bucket/athena/inputdata/2022-01-01-00-00.

To resolve this issue, set the projection.dt.range property to 2022-01-01-01-00,NOW.

Related information

Create tables in Athena

Use a crawler to add a table

Set up partition projection