Create detailed cost and usage reports for Amazon RDS and Amazon Aurora - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resources

Create detailed cost and usage reports for Amazon RDS and Amazon Aurora

Created by Lakshmanan Lakshmanan (AWS) and Sudarshan Narasimhan

Environment: Production

Technologies: Databases; Cost management; Analytics

AWS services: Amazon Athena; Amazon Aurora; Amazon RDS; AWS Billing and Cost Management

Summary

This pattern shows how to track usage costs for Amazon Relational Database Service (Amazon RDS) or Amazon Aurora clusters by configuring user-defined cost allocation tags. You can use these tags to create detailed cost and usage reports in AWS Cost Explorer for clusters across multiple dimensions. For example, you can track usage costs at the team, project, or cost center level, and then analyze the data in Amazon Athena.

Prerequisites and limitations

Prerequisites

Limitations

For tagging restrictions, see the AWS Billing User Guide.

Architecture

Target technology stack

  • Amazon RDS or Amazon Aurora

  • AWS Cost and Usage Report

  • AWS Cost Explorer

  • Amazon Athena

Workflow and architecture

The tagging and analysis workflow consists of these steps:

  1. A data engineer, database administrator, or AWS administrator creates user-defined cost allocation tags for the Amazon RDS or Aurora clusters.

  2. An AWS administrator activates the tags.

  3. The tags report metadata to AWS Cost Explorer.

  4. A data engineer, database administrator, or AWS administrator creates a monthly cost allocation report.

  5. A data engineer, database administrator, or AWS administrator analyzes the monthly cost allocation report by using Amazon Athena.

The following diagram shows how to apply tags to track usage costs for Amazon RDS or Aurora instances.

 

Applying tags to track usage costs for database instances and clusters

The following architecture diagram shows how the cost allocation report is integrated with Amazon Athena for analysis.

Querying cost allocation reports in Athena

The monthly cost allocation report is stored in an Amazon S3 bucket that you specify. When you set up Athena with the AWS CloudFormation template, as described in the Epics section, the template provisions several additional resources, including an AWS Glue crawler, an AWS Glue database, an Amazon Simple Notification System (Amazon SNS) event, AWS Lambda functions, and AWS Identity and Access Management (IAM) roles for the Lambda functions. As new cost data files arrive in the S3 bucket, event notifications are used to forward these files to a Lambda function for processing. The Lambda function initiates an AWS Glue crawler job to create or update the table in the AWS Glue Data Catalog. This table is then used to query data in Athena.

 

Tools

  • Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL.

  • Amazon Aurora is a fully managed relational database engine that's built for the cloud and compatible with MySQL and PostgreSQL.

  • Amazon Relational Database Service (Amazon RDS) helps you set up, operate, and scale a relational database in the AWS Cloud.

  • AWS CloudFormation is an infrastructure as code (IaC) service that allows you to easily model, provision, and manage AWS and third-party resources.

  • AWS Cost Explorer helps you view and analyze your AWS costs and usage.

Epics

TaskDescriptionSkills required

Create user-defined cost allocation tags for your Amazon RDS or Aurora cluster.

To add tags to a new or existing Amazon RDS or Aurora cluster, follow the instructions in Adding, listing, and removing tags in the Amazon Aurora User Guide.

Note: For information about how to set up an Amazon Aurora cluster, see the instructions for MySQL and PostgreSQL in the Amazon Aurora User Guide.

AWS administrator, Data engineer, DBA

Activate the user-defined cost allocation tags.

Follow the instructions in Activating user-defined cost allocation tags in the AWS Billing User Guide.

AWS administrator
TaskDescriptionSkills required

Create and configure cost and usage reports for your clusters.

  1. Sign in to the AWS Management Console and open the AWS Billing console.

  2. In the left navigation pane, choose Cost & Usage Reports.

  3. Choose Create report.

  4. Provide a report name, keep the default settings for other options, and then choose Next.

  5. Choose Configure and provide the details of an existing S3 bucket. You can also choose to create a new S3 bucket from this screen. Choose Next.

  6. Verify the default policy that will be applied to your bucket, select the confirmation check box, and then choose Save.

  7. For Report path prefix, specify the prefix you want to prepend to the report name.

  8. For Time granularity, choose Hourly, Daily or Monthly, depending on how often you want data to be collected for the report.

  9. For Report versioning, choose whether you want new versions of the report to be created separately or overwrite the existing report with each version.

  10. For Enable report data integration for, choose Amazon Athena. Verify that the compression type is set to Parquet.

  11. Choose Next.

  12. Review the report settings, and then choose Review and Complete.

The data will be available in 24 hours.

App owner, AWS administrator, DBA, General AWS, Data engineer
TaskDescriptionSkills required

Analyze the cost and usage report data.

  1. Set up and use Athena to analyze the report data. For instructions, see Querying Cost and Usage Reports using Amazon Athena in the AWS Cost and Usage Reports User Guide. We recommend that you use the AWS CloudFormation template provided by Athena.

  2. Run Athena queries. For example, you can use the following SQL query to check the status of the data refresh.

select status from cost_and_usage_data_status

For more information, see Running Amazon Athena queries in the AWS Cost and Usage Reports User Guide.

Note: When you run your SQL query, make sure that the correct database is selected from the dropdown list.

App owner, AWS administrator, DBA, General AWS, Data engineer

Related resources

References

Tutorials and videos