Lack of results for Athena query on new CUR 2.0 report

Question

For Cloud Intelligence Dashboards (CID) we're migrating from CUR to CUR 2.0, which the official documentation (https://catalog.workshops.aws/awscid/en-US/dashboards/foundational/cudos-cid-kpi/migrate-to-cur2) suggests doing using the Data Exports Cloudformation stacks: once deployed in a Source (Payer) account and another deployed in the Destination account.

I have gone ahead and deployed the cloudformation stacks therein with the appropriate parameters, and I've double confirmed this. I then, as the Data Exports stack suggests, created a support ticket requesting a backfill for cid-cur2. This has been done, and I've confirmed that the data has been replicated from Source to Destination account. I can see the parquet files in the correct directories in the Destination Account. Then, I updated the role permissions for the Quicksight to be in line with that and allow access to it.

However, when I then run the following SQL query (as per step 1 of migrate-to-cur2 page)

SELECT billing_period, count(*)
FROM cid_data_export.cur2
GROUP BY 1

It gives 0 results. In fact, even a simple query like the

SELECT * FROM cid_data_export.cur2 LIMIT 10;

I still see 0 results. I don't know what I'm doing wrong, since the stacks got deployed correctly, the report got backfilled with a year's worth of data, and I can confirm the replication is correctly working since I can see it occurred from Source Account to Destination Account.

Iakov · Accepted Answer

Please check the crawler status (default name : cid-DataExportCUR2Crawler).

Nonsi_D · Answer

Hello,

Thank you for reaching out. Here is the AWS Knowledge Center document we are referencing for troubleshooting Athena returning zero results: Why do I get zero records when I query my Amazon Athena table?- https://repost.aws/knowledge-center/athena-empty-results

Based on this document and your setup, here are several common causes for Athena queries returning no results:

1. Check if your CUR 2.0 table partitions are correctly loaded. You can use the 'SHOW PARTITIONS ' command to verify loaded partitions. If some are missing, you may need to run 'MSCK REPAIR TABLE ' if your data follows a Hive-compatible structure. Refer to the documentation on https://docs.aws.amazon.com/athena/latest/ug/partitions.html for more details.

2. Ensure that the LOCATION path in your table definition accurately points to the S3 bucket directory/prefix where the CUR data resides. Example: Paths with file names,can prevent Athena from accessing the data.

3. Athena does not support paths with double slashes (e.g., '//') in the prefix level. Make sure the S3 path in your table’s LOCATION property is free of double slashes.

4. Confirm that the S3 bucket policy, IAM roles, and Athena permissions allow access to the CUR data in the Destination account. Permissions issues often prevent Athena from querying data even if files appear in the bucket.

5. Athena ignores files with names starting with an underscore ('_') or a period ('.'). Ensure that CUR data files in S3 do not start with these prefixes, as they will be skipped in queries.

6. If you are using partition projection, make sure the partitioning scheme aligns with Athena’s configuration. If projections or defined ranges do not match, Athena might return zero rows.

7. Confirm that the Athena table metadata aligns with the CUR 2.0 schema in S3. Mismatched metadata can cause Athena to interpret the data incorrectly, leading to zero results.

If you have reviewed these items and the issue persists, please consider opening a support ticket. Providing the Athena query execution ID(s) will allow AWS Support to further investigate.

Thank you, and have a great day!

re:Post Agent · Answer

There are a few potential reasons why your Athena queries on the new CUR 2.0 report are not returning any results, despite the data being present in the S3 bucket. Here are some troubleshooting steps you can take:

Verify partitions: For Athena to query your Cost and Usage Reports data, you need to upload the data partitions into your Athena table. This step is required for each new AWS CUR report that AWS delivers. Open the Athena console, select your table, and choose "Load Partitions." If you haven't loaded the partitions, your queries won't return any results.
Check S3 location: Ensure that the S3 location path specified in your Athena table definition is correct. If the input LOCATION path in your table is incorrect, Athena won't return any records.
Verify partition definitions: If you used a manual partitioning CREATE TABLE statement, check that the partitions are correctly defined. Make sure the projection.timestamp.range property matches the partitions in your S3 bucket location.
Storage class compatibility: Confirm that your CUR data is not stored in S3 Glacier Flexible Retrieval or S3 Glacier Deep Archive storage classes, as Athena doesn't support querying data in these storage tiers.
Staging bucket: Ensure that the staging bucket used by Athena and QuickSight still exists. By default, this bucket has a name similar to "aws-athena-query-results-<REGION>-<ACCOUNTID>". If this bucket was removed, your Athena queries might fail.

If you've verified all these points and are still encountering issues, you may want to double-check your IAM permissions to ensure that the role you're using has the necessary access to both the S3 bucket containing the CUR data and the Athena service itself.
Sources
Troubleshoot Athena SELECT query issues for CloudTrail logs | AWS re:Post
Staging bucket no longer exists when using Athena with Amazon QuickSight - Amazon QuickSight
Uploading your report partitions - AWS Data Exports

Lack of results for Athena query on new CUR 2.0 report

Relevant content