Why didn't my Athena query return results after I added new partitions?

Resolution

This issue might occur if you used one of the following methods to add the new partitions:

• MSCK REPAIR TABLE
• partition projection
• AWS Glue Data Catalog
• AWS Glue console

To resolve the issue, follow these steps for your use case.

MSCK REPAIR TABLE

The MSCK REPAIR TABLE command works only with Hive-style partitions, whose data paths contain key value pairs connected by equal signs. For example, country=us/..., year=2021/month=01/day=26/..., and so on.

Additionally, the MSCK REPAIR TABLE command might fail to add new partitions, especially with large partitions in the Amazon Simple Storage Service (Amazon S3) bucket. This can also cause performance and time out issues because the MSCK REPAIR TABLE command loads all partitions in the data range. For example, if the existing data is from 2020 and the new partition data is from 2023, then all partitions from 2020 are read.

To add non-Hive style partitions, run the ALTER TABLE ADD PARTITION command similar to the following:

ALTER TABLE table_name ADD [IF NOT EXISTS]
  PARTITION
  (partition_col1_name = partition_col1_value
  [,partition_col2_name = partition_col2_value]
  [,...])
  [LOCATION 'location1']
  [PARTITION
  (partition_colA_name = partition_colA_value
  [,partition_colB_name = partition_colB_value
  [,...])]
  [LOCATION 'location2']
  [,...]

Partition projection

In partition projection, table properties are defined when the table is created. Make sure that the table properties meet the required specifications or the partition might not be read.

Use the following command to list the table partitions:

"SELECT * FROM 'table_name$partitions'"

In the output, check the partitions data format. If date formats like year, month, day, or hour are used, then add the necessary table properties for range, interval, and digits.

For more information, see Supported types for partition projection.

AWS Glue Data Catalog or AWS Glue console

If you used the Data Catalog or AWS Glue console to define the partition columns, then verify the partition data type. The partition data type must match the data type defined in the S3 bucket.

Note: It' a best practice to use consistent data types with the Data Catalog or AWS Glue console and the S3 bucket.

Related information

Why doesn't my MSCK REPAIR TABLE query add partitions to the AWS Glue Data Catalog?

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