Athena cannot extract data from the dynamic partitioning table

Based on the information provided, there are a few potential reasons why Athena might not be able to extract data from your dynamic partitioning table:

1. Partition Projection Configuration: Your table properties show that partition projection is enabled, but there might be issues with how it's configured. For dynamic partitioning to work correctly, you need to ensure that the projection settings for each partitioned column are properly defined. This includes setting the correct projection type (such as 'date' for date-based partitions or 'enum' for fixed value partitions) and specifying the appropriate range or values for each partition.

2. Storage Location Template: The storage.location.template property is crucial for Athena to correctly locate your data. Make sure this template accurately reflects your S3 path structure. Based on the S3 path you provided, your storage.location.template should look something like:

   s3://My_bucket/${account_id}/${region}/${service}/${year}/${month}/${day}/

   This template should include placeholders for all your partition columns.

3. Data Types Mismatch: Ensure that the data types defined in your table schema match the actual data types in your S3 files. Mismatches can cause Athena to fail in reading the data.

4. Partition Format: For date-based partitions (year, month, day), make sure the projection.date.format property is set correctly. It should match the format of your partition folders in S3.

5. S3 Permissions: Verify that Athena has the necessary permissions to access the S3 bucket and read the data files.

6. File Format: Ensure that the file format specified in your table definition (STORED AS PARQUET) matches the actual format of your data files in S3.

To troubleshoot:

1. Double-check your partition projection settings for each column.
2. Verify the storage.location.template property.
3. Run a SHOW PARTITIONS query to see if Athena recognizes the partitions.
4. Check the Athena query history for any specific error messages.
5. Try running a simple SELECT query with explicit partition filters to see if it returns any results.

If these steps don't resolve the issue, you may need to compare the exact configuration of this table with your working table to identify any subtle differences.
Sources
Troubleshoot Athena partition projection issues | AWS re:Post
Troubleshoot issues in Athena - Amazon Athena
Use dynamic ID partitioning - Amazon Athena