Amazon Athena returns no records for Amazon Connect CTR data on S3

There are several potential reasons why your Athena query is returning no records for the Amazon Connect CTR data stored as parquet files in S3. Let's explore some possible causes and solutions:

1. Incorrect S3 location path: Ensure that the LOCATION path in your table creation statement exactly matches the S3 path where your parquet files are stored. Even a small discrepancy can result in Athena not finding the data.

2. Partitions not loaded properly: Although you mentioned running MSCK REPAIR TABLE, it's worth double-checking that the partitions are correctly loaded. You can use the SHOW PARTITIONS statement to view the partitions that have been loaded into the table. If necessary, you may need to manually add partitions using the ALTER TABLE ADD PARTITION command.

3. Partition definition mismatch: If you're using manual partitioning, verify that the partition definitions in your table creation statement match the actual partition structure in your S3 bucket. Pay particular attention to the projection.timestamp.range property if you're using time-based partitioning.

4. Storage class incompatibility: Ensure that your parquet files are not stored in S3 Glacier Flexible Retrieval or S3 Glacier Deep Archive storage classes, as Athena doesn't support querying data in these classes.

5. Data type mismatch: If you're using Athena engine version 3, there might be a data type mismatch between your table schema and the actual data in the parquet files. For example, if a column is defined as varchar in your schema but contains decimal data in the parquet file, Athena engine version 3 will fail to read it. In this case, you may need to modify your schema in AWS Glue to use the correct data types.

6. File naming convention: Although Athena doesn't require specific file extensions for parquet files, ensure that your files are indeed in the parquet format and that Athena can recognize them as such.

7. Permissions: Verify that the IAM role or user executing the Athena query has the necessary permissions to access the S3 bucket and read the parquet files.

If none of these solutions resolve the issue, you may want to double-check the workshop instructions to ensure all steps were followed correctly, including any specific configurations for Amazon Connect CTR data. Additionally, consider reaching out to AWS support if the problem persists, as there might be a more specific issue related to the Amazon Connect CTR data format or integration with Athena.
Sources
Athena query to table stored as parquet files on s3 returns empty result | AWS re:Post
Troubleshoot Athena SELECT query issues for CloudTrail logs | AWS re:Post
Athena engine version 3 - Amazon Athena
AWS Athena Parquet file extension | AWS re:Post

We also create external table without partition fields (year, month, day) in Athena and query returned CTR data. When we add partition again (by manual or by Glue crawler) the query didn't return any data. We think that if table doesn't have partitions ( for 3 columns : year, month, day), query performance will be affected negatively and connect_ctr table doesn't have those columns so we don't know how to query. We need to retrieve CTR data from partitioned table connect_ctr with partitions year, month and day.

I am experiencing the same thing. We've deployed the stack from https://aws.amazon.com/blogs/contact-center/analyze-amazon-connect-contact-trace-record-with-amazon-athena-and-amazon-quicksight-part-1/ successfully to contact centers in the past but this time it isn't working. I don't have a solution yet - am following this post.

UPDATE! your comment about partitions got me pointed in the right direction. The default configuration has a field range for the year partition of 2022-2024. Since we are in 2025 it was not seeing that partition. Select EDIT TABLE and then under the table properties, extend the value range for the projection.year.range

This worked for me, I hope it works for you too.

-- Roberta W