In our previous post, we introduced Amazon EC2 Capacity Manager and its data export capability. Amazon EC2 Capacity Manager provides centralized visibility into your Amazon Elastic Compute Cloud (Amazon EC2) capacity usage across all accounts and Regions in your organization. It tracks capacity usage for three types of EC2 capacity: On-Demand instances, Spot instances, and On-Demand Capacity Reservations (ODCR). On the AWS Management Console, it provides 90 days of historical capacity data. With data exports to Amazon Simple Storage Service (Amazon S3), you can retain and analyze capacity trends beyond this period using your preferred analytics tools.
In this post, we demonstrate how to configure EC2 Capacity Manager data exports to Amazon S3 and query historical capacity data using Amazon Athena. This approach helps you identify long-term usage patterns, plan capacity needs, and optimize resource allocation across your organization.
The following diagram illustrates the solution architecture. EC2 Capacity Manager exports capacity data to Amazon S3, where Amazon Athena queries it using SQL with automatic partition discovery.
The solution involves the following steps:
Prerequisites:
EC2 Capacity Manager can export capacity data in compressed CSV (Gzip) or compressed Parquet (Snappy) format. Use Parquet format for query performance in Athena (Parquet’s columnar format is designed to optimize analytical queries).
You can configure data export through the EC2 Capacity Manager console or AWS CLI.
To configure data export using the console:
After you create the data export, EC2 Capacity Manager displays the export details.
If you use an existing S3 bucket, you must add the bucket policy shown in the following example to grant EC2 Capacity Manager write access.
Replace with your AWS account number, with your AWS Region (for example, us-west-2), and with your bucket name.
To configure data export using the AWS CLI :
After creating the data export, wait for the first export to complete before proceeding to set up Athena. You can check the export status using the following command:
The command returns details about your data export configuration, including the delivery status:
Wait until LatestDeliveryStatus shows "delivered" before proceeding to the next section. The first export typically appears in your S3 bucket in a couple of hours. Subsequent exports follow your configured schedule.
After EC2 Capacity Manager exports data to your S3 bucket, you can use Amazon Athena to query the data using standard SQL. Athena uses AWS Glue as its metadata store. Specifically, it relies on the AWS Glue Data Catalog, which contains table definitions that tell Athena where you have stored your data in S3 and how you have structured it. When you create tables in Athena, you’re actually creating metadata entries in the Data Catalog that Athena references when running queries.
You can create the table using AWS Glue crawler or manually with SQL. AWS Glue crawler automatically discovers the complete schema from your exported Parquet files, including optional fields like resource tags if enabled. It helps minimize manual schema definition efforts. If the export format changes in the future, you can re-run the crawler to update the table definition. For detailed instructions on creating a Glue Crawler, see Use a crawler to add a table in the Amazon Athena User Guide.
In this post, we create the table manually using a SQL statement. We also use partition projection for automatic partition discovery. We do this because EC2 Capacity Manager continuously adds new partitions to the S3 bucket according to your configured schedule. As new partitions arrive in S3, Athena doesn’t know about them until you run MSCK REPAIR TABLE or ALTER TABLE ADD PARTITION to update the AWS Glue Data Catalog. This becomes an overhead when data arrives frequently.
With partition projection, you define the partition scheme and specify its range/rules in the table properties. Athena then computes the partitions at query time instead of looking them up in the Glue Data Catalog. So partition projection automatically makes new partitions visible as soon as EC2 Capacity Manager exports the data to S3, eliminating the need for you to update metadata. The CREATE TABLE statement that follows defines the schema for EC2 Capacity Manager exports. If your capacity reservations are already tagged, add the corresponding tag columns (for example, tag_environment string or tag_costcenter string). Alternatively, use an AWS Glue crawler to automatically discover your complete schema, including tag columns.
Replace in the LOCATION clause and storage.location.template property with your bucket name, and capacity-data/ with table name of your choice.
Now that your table is set up, you can explore how to query the exported data.
The following queries demonstrate how to analyze your EC2 capacity data for cost optimization and capacity planning. The queries use date values (year=‘2026’, month=‘04’) and table name capacity_data. Adjust the partition values to match your actual data’s time period and table name to match your value. When querying EC2 Capacity Manager export data:
Discover On-Demand Capacity Reservations with low utilization that are generating cost waste. Identify specific reservations to downsize, cancel, or share with other teams to reduce unnecessary spending.
Sample output:
Get a comprehensive view of ODCR utilization across instance types to identify which instance families have the worst utilization rates. This helps prioritize optimization efforts on the reservations with the highest cost impact.
Sample output:
Analyze average hourly usage patterns across reserved, unreserved, and Spot capacity to identify when your workloads typically hit peak demand. This breakdown helps you understand your capacity mix, plan for peak periods, and optimize your purchasing strategy.
Sample output:
Understand how your ODCR capacity is distributed across AWS Regions and instance types. This geographic view helps you identify Regions with excess capacity that could be redistributed or consolidated to improve utilization and reduce costs.
Sample output:
Pinpoint exactly where you have unused ODCR capacity at the Availability Zone level. This granular view enables you to share unused capacity with other teams in the same AZ or modify reservations to better match actual usage patterns.
Sample output:
To avoid incurring future charges, delete the resources you created:
Warning: This permanently deletes the table definition. Verify that you no longer need to query this data before proceeding.
Replace with your data export ID and with your AWS Region.
Warning: Deleting the S3 bucket permanently removes all exported capacity data. Verify that you have backed up any data you need before proceeding.
In this post, we demonstrated how to configure EC2 Capacity Manager data exports to Amazon S3 and query historical capacity data using Amazon Athena. This approach enables you to retain capacity data beyond the 90-day console limit.
As you scale your capacity management practices, consider integrating these exports with your existing analytics and monitoring workflows. By combining EC2 Capacity Manager data with your broader infrastructure metrics, you can make data-driven decisions about capacity allocation and optimization across your organization.
To deepen your understanding, explore the EC2 Capacity Manager documentation for additional features, learn more about Amazon Athena for advanced query capabilities, and review EC2 capacity optimization best practices. Share your feedback and tell us how you’re using EC2 Capacity Manager data exports to optimize your capacity planning in the comments.
