I created a table in Amazon Athena with defined partitions, but when I query the table, zero records are returned
Last updated: 2018-09-17
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
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:
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.