No results returned from Athena partitioned table

Question

Hello, I am currently working on partitioning and have created an external table in Athena, done msck repair table and inserted data to partitioned table from an existing table and when I ran these queries, Athena said 'query successful'. Dynamic structure is used (i.e. on customer id and transaction date(i.e. dt=2023-05-18)

However, when I tried to do cdc to update the partitioned table (through crawling parquet file from existing table to partitioned table),  Athena fails to return the latest record but the data have dynamically already been stored in S3 bucket and according folder. I tried running through msck repair table and inserting data from existing table again, Athena still doesn't show anything. 
I checked the s3 path of the partitioned table, the location is pointing to the correct folder.

Any idea why would this happen?
Please help. Thanks

Answer

Hello,

Based on the information provided. I can suggest the following:

1. Make sure you are following best practices for using Athena with AWS Glue, especially check that your Database, table, column names are lowercase letters, numbers, and the underscore character. [1]

2. Please make sure that to use a partitioned table, the data must be partitioned and the partitioned data might be in either of the following formats [2]:

- Hive style format(Example: s3://doc-example-bucket/example-folder/year=2021/month=01/day=01/my_file.csv) 
- Non-Hive style format(Example: s3://doc-example-bucket/example-folder/2021/01/01/my_file.csv)

According to the information provided, I am assuming that you are using HIVE style partitioning. Kindly make sure that the partition format is correct.

3. Please take time to read this article, especially the sections for “Double slash in LOCATION path” and “Hive hidden files” can be useful in your case [3].

Hope this information helps you. If you are still unable to resolve your problem. I kindly request you to raise a support case with AWS Athena support engineering team with Athena query id/ids and Region for detailed troubleshooting.

Thanks!

References:

[1] Best practices: https://docs.aws.amazon.com/athena/latest/ug/glue-best-practices.html

[2] How can I create and use partitioned tables in Amazon Athena? : https://aws.amazon.com/premiumsupport/knowledge-center/athena-create-use-partitioned-tables/

[3] Why do I get zero records when I query my Amazon Athena table?: https://repost.aws/knowledge-center/athena-empty-results#

No results returned from Athena partitioned table

Relevant content