I ran a CREATE TABLE statement in Amazon Athena with expected columns and their data types, but when I run a "select * from <table-name>" query, I get empty results.

Here are some common reasons why the query might return empty results.

File selected in crawler settings

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

Incorrect LOCATION path

Verify the input data LOCATION path to Amazon S3. If the input LOCATION path is incorrect, Athena returns empty results.

Partitions not yet loaded

If your table has defined partitions, the partitions might not be loaded into the AWS Glue Data Catalog or the internal Athena data catalog yet. 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 be located at the specified location yet, Athena returns an empty result. If the partitions are defined in the CREATE TABLE statement, load the partition information into the catalog using MSCK REPAIR TABLE or ALTER TABLE ADD PARTITION.

MSCK REPAIR TABLE

If the partitions are stored in a format that Athena supports, run MSCK REPAIR TABLE. MSCK REPAIR TABLE loads a partition's metadata into the catalog. For example, if you have a table 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 at the S3 paths that Athena expects, 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 command "select * from <table-name>" should return data.

ALTER TABLE ADD PARTITION

If the partitions are not stored in a format that Athena supports, or are located at different S3 paths, run ALTER TABLE ADD PARTITION for each partition. For example, suppose 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 command "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-08-08