Post Syndicated from Dagar Katyal original https://aws.amazon.com/blogs/big-data/analyze-amazon-s3-storage-costs-using-aws-cost-and-usage-reports-amazon-s3-inventory-and-amazon-athena/

Since its launch in 2006, Amazon Simple Storage Service (Amazon S3) has experienced major growth, supporting multiple use cases such as hosting websites, creating data lakes, serving as object storage for consumer applications, storing logs, and archiving data. As the application portfolio grows, customers tend to store data from multiple application and different business functions in a single S3 bucket, which can grow the storage in S3 buckets to hundreds of TBs. The AWS Billing console provides a way to look at the total storage cost of data stored in Amazon S3, but sometimes IT organizations need to understand the breakdown of costs of a particular S3 bucket by various prefixes or objects corresponding to a particular user or application. There are various reasons to analyze the costs of S3 buckets, such as to identify the spend breakdown, do internal chargebacks, understand the cost breakdown by business unit and application, and many more. As of this writing, there is no easy way to do a cost breakdown of S3 buckets by objects and prefixes.

In this post, we discuss a solution using Amazon Athena to query AWS Cost and Usage Reports and Amazon S3 Inventory reports to analyze the cost by prefixes and objects in an S3 bucket.

Overview of solution

The following figure shows the architecture for this solution. First, we enable the AWS Cost and Usage Reports (AWS CUR) and Amazon S3 Inventory features, which save the output into two separate pre-created S3 buckets. We then use Athena to query these S3 buckets for AWS CUR data and S3 object inventory data to correlate and allocate the cost breakdown at the object or prefix level.

To implement the solution, we complete the following steps:

1. Create S3 buckets for AWS CUR, S3 object inventory, and Athena results. Alternatively, you can create these respective buckets when enabling the respective individual features, but for the purpose of this post, we create all of them at the beginning.
2. Enable the Cost and Usage Reports.
3. Enable Amazon S3 Inventory configuration.
4. Create AWS Glue Data Catalog tables for the CUR and S3 object inventory to query using Athena.
5. Run queries in Athena.

Prerequisites

For this walkthrough, you should have the following prerequisites:

• An AWS account.
• AWS Identity and Access Management (IAM) permissions for the following services:
  • Amazon S3 – Create and manage S3 buckets.
  • AWS Billing and Cost Management – Create Cost and Usage Reports.
  • Athena – Create tables and run queries. AWS Glue Data Catalog permissions are needed to create tables.

Create S3 buckets

Amazon S3 is an object storage service offering industry-leading scalability, data availability, security, and performance. Customers of all sizes and industries can store and protect any amount of data for virtually any use case, such as data lakes, cloud-native applications, and mobile apps. With cost-effective storage classes and easy-to-use management features, you can optimize costs, organize data, and configure fine-tuned access controls to meet specific business, organizational, and compliance requirements.

For this post, we use the S3 bucket s3-object-cost-allocation as the primary bucket for cost allocation. This S3 bucket is conveniently modeled to contain several prefixes and objects of different sizes for which cost allocation needs to be done based on the overall cost of the bucket. In a real-world scenario, you should use a bucket that has data for multiple teams and for which you need to allocate costs by prefix or object. Going forward, we refer to this bucket as the primary object bucket.

The following screenshot shows our S3 bucket and folders.

Now let’s create the three additional operational S3 buckets to store the datasets generated to calculate costs for the objects. You can create the following buckets or any existing buckets as needed:

• cur-cost-usage-reports-<account_number> – This bucket is used to save the Cost and Usage Reports for the account.
• S3-inventory-configurations-<account_number> – This bucket is used to save the inventory configurations of our primary object bucket.
• athena-query-bucket-<account_number> – This bucket is used to save the query results from Athena.

Complete the following steps to create your S3 buckets:

• On the Amazon S3 console, choose Buckets in the navigation pane.
• Choose Create bucket.
• For Bucket name, enter the name of your bucket (cur-cost-usage-reports-<account_number>).
• For AWS Region, choose your preferred Region.
• Leave all other settings at default (or according to your organization’s standards).
• Choose Create bucket.
  
• Repeat these steps to create s3-inventory-configurations-<account_number> and athena-query-bucket-<account_number>.

Enable the Cost and Usage Reports

The AWS Cost and Usage Reports (AWS CUR) contains the most comprehensive set of cost and usage data available. You can use Cost and Usage Reports to publish your AWS billing reports to an S3 bucket that you own. You can receive reports that break down your costs by the hour, day, or month; by product or product resource; or by tags that you define yourself.

Complete the following steps to enable Cost and Usage Reports for your account:

• On the AWS Billing console, in the navigation pane, choose Cost & Usage Reports.
• Choose Create report.
• For Report name, enter a name for your report, such as account-cur-s3.
• For Additional report details, select Include resource IDs to include the IDs of each individual resource in the report.Including resource IDs will create individual line items for each of your resources. This can increase the size of your Cost and Usage Reports files significantly, which can affect the S3 storage costs for your CUR, based on your AWS usage. We need this feature enabled for this post.
• For Data refresh settings, select whether you want the Cost and Usage Reports to refresh if AWS applies refunds, credits, or support fees to your account after finalizing your bill.When a report refreshes, a new report is uploaded to Amazon S3.
• Choose Next.
• For S3 bucket, choose Configure.
• For Configure S3 Bucket, select an existing bucket created in the previous section (cur-cost-usage-reports-<account_number>) and choose Next.
• Review the bucket policy, select I have confirmed that this policy is correct, and choose Save. This default bucket policy provides Cost and Usage Reports access to write data to Amazon S3.
• For Report path prefix, enter cur-data/account-cur-daily.
• For Time granularity, choose Daily.
• For Report versioning, choose Overwrite existing report.
• For Enable report data integration for, select Amazon Athena.
• Choose Next.
• After you have reviewed the settings for your report, choose Review and Complete.
  

The Cost and Usage reports will be delivered to the S3 buckets within 24 hours.

The following sample CUR in CSV format shows different columns of the Cost and Usage Report, including bill_invoice_id, bill_invoicing_entity, bill_payer_account_id, and line_item_product_code, to name a few.

Enable Amazon S3 Inventory configuration

Amazon S3 Inventory is one of the tools Amazon S3 provides to help manage your storage. You can use it to audit and report on the replication and encryption status of your objects for business, compliance, and regulatory needs. Amazon S3 Inventory provides comma-separated values (CSV), Apache Optimized Row Columnar (ORC), or Apache Parquet output files that list your objects and their corresponding metadata on a daily or weekly basis for an S3 bucket or a shared prefix (objects that have names that begin with a common string).

Complete the following steps to enable Amazon S3 Inventory on the primary object bucket:

• On the Amazon S3 console, choose Buckets in the navigation pane.
• Choose the bucket for which you want to configure Amazon S3 Inventory.
  This will be the existing bucket in your account that has data that needs to be analyzed. This could be your data lake or application S3 bucket. We created the bucket s3-object-cost-allocation with some sample data and folder structure.
• Choose Management.
• Under Inventory configurations, choose Create inventory configuration.
• For Inventory configuration name, enter s3-object-cost-allocation.
• For Inventory scope, leave Prefix blank.
  This is to ensure that all objects are covered for the report.
• For Object Versions, select Current version only.
• For Report details, choose This account.
• For Destination, choose the destination bucket we created (s3-inventory-configurations-<account_number>).
• For Frequency, choose Daily.
• For Output format, choose as Apache Parquet.
• For Status, choose Enable.
• Keep server-side encryption disabled. To use server-side encryption, choose Enable and specify the encryption key.
• For Additional fields, select the following to add to the inventory report:
  • Size – The object size in bytes.
  • Last modified date – The object creation date or the last modified date, whichever is the latest.
  • Multipart upload – Specifies that the object was uploaded as a multipart upload. For more information, see Uploading and copying objects using multipart upload.
  • Replication status – The replication status of the object. For more information, see Using the S3 console.
  • Encryption status – The server-side encryption used to encrypt the object. For more information, see Protecting data using server-side encryption.
  • Bucket key status – Indicates whether a bucket-level key generated by AWS KMS applies to the object.
  • Storage class – The storage class used for storing the object.
  • Intelligent-Tiering: Access tier – Indicates the access tier of the object if it was stored in Intelligent-Tie
    
• Choose Create.
  

It may take up to 48 hours to deliver the first report.

Create AWS Glue Data Catalog tables for CUR and Amazon S3 Inventory reports

Wait for up to 48 hours for the previous step to generate the reports. In this section, we use Athena to create and define AWS Glue Data Catalog tables for the data that has been created using Cost and Usage Reports and Amazon S3 Inventory reports.

Athena is a serverless, interactive analytics service built on open-source frameworks, supporting open-table and file formats. Athena provides a simplified, flexible way to analyze petabytes of data where it lives.

Complete the following steps to create the tables using Athena:

• Navigate to the Athena console.
• If you’re using Athena for the first time, you need to set up a query result location in Amazon S3. If you preconfigured this in Athena , you can skip this step.
  • Choose View settings.
    
  • Choose Manage.
  • In the section Query result location and encryption, choose Browse S3 and choose the bucket that we created (athena-query-bucket-<account_number>).
  • Choose Save.
    
  • Navigate back to the Athena query editor.
• Run the following query in Athena to create a table for Cost and Usage Reports. Verify and update the section for <<LOCATION>> at the end of the query and point it to the correct S3 bucket and location. Note that the new table name should be account_cur.

  CREATE EXTERNAL TABLE `account_cur`(
  `identity_line_item_id` string,
  `identity_time_interval` string,
  `bill_invoice_id` string,
  `bill_billing_entity` string,
  `bill_bill_type` string,
  `bill_payer_account_id` string,
  `bill_billing_period_start_date` timestamp,
  `bill_billing_period_end_date` timestamp,
  `line_item_usage_account_id` string,
  `line_item_line_item_type` string,
  `line_item_usage_start_date` timestamp,
  `line_item_usage_end_date` timestamp,
  `line_item_product_code` string,
  `line_item_usage_type` string,
  `line_item_operation` string,
  `line_item_availability_zone` string,
  `line_item_resource_id` string,
  `line_item_usage_amount` double,
  `line_item_normalization_factor` double,
  `line_item_normalized_usage_amount` double,
  `line_item_currency_code` string,
  `line_item_unblended_rate` string,
  `line_item_unblended_cost` double,
  `line_item_blended_rate` string,
  `line_item_blended_cost` double,
  `line_item_line_item_description` string,
  `line_item_tax_type` string,
  `line_item_legal_entity` string,
  `product_product_name` string,
  `product_availability` string,
  `product_description` string,
  `product_durability` string,
  `product_event_type` string,
  `product_fee_code` string,
  `product_fee_description` string,
  `product_free_query_types` string,
  `product_from_location` string,
  `product_from_location_type` string,
  `product_from_region_code` string,
  `product_group` string,
  `product_group_description` string,
  `product_location` string,
  `product_location_type` string,
  `product_message_delivery_frequency` string,
  `product_message_delivery_order` string,
  `product_operation` string,
  `product_platopricingtype` string,
  `product_product_family` string,
  `product_queue_type` string,
  `product_region` string,
  `product_region_code` string,
  `product_servicecode` string,
  `product_servicename` string,
  `product_sku` string,
  `product_storage_class` string,
  `product_storage_media` string,
  `product_to_location` string,
  `product_to_location_type` string,
  `product_to_region_code` string,
  `product_transfer_type` string,
  `product_usagetype` string,
  `product_version` string,
  `product_volume_type` string,
  `pricing_rate_code` string,
  `pricing_rate_id` string,
  `pricing_currency` string,
  `pricing_public_on_demand_cost` double,
  `pricing_public_on_demand_rate` string,
  `pricing_term` string,
  `pricing_unit` string,
  `reservation_amortized_upfront_cost_for_usage` double,
  `reservation_amortized_upfront_fee_for_billing_period` double,
  `reservation_effective_cost` double,
  `reservation_end_time` string,
  `reservation_modification_status` string,
  `reservation_normalized_units_per_reservation` string,
  `reservation_number_of_reservations` string,
  `reservation_recurring_fee_for_usage` double,
  `reservation_start_time` string,
  `reservation_subscription_id` string,
  `reservation_total_reserved_normalized_units` string,
  `reservation_total_reserved_units` string,
  `reservation_units_per_reservation` string,
  `reservation_unused_amortized_upfront_fee_for_billing_period` double,
  `reservation_unused_normalized_unit_quantity` double,
  `reservation_unused_quantity` double,
  `reservation_unused_recurring_fee` double,
  `reservation_upfront_value` double,
  `savings_plan_total_commitment_to_date` double,
  `savings_plan_savings_plan_a_r_n` string,
  `savings_plan_savings_plan_rate` double,
  `savings_plan_used_commitment` double,
  `savings_plan_savings_plan_effective_cost` double,
  `savings_plan_amortized_upfront_commitment_for_billing_period` double,
  `savings_plan_recurring_commitment_for_billing_period` double,
  `resource_tags_user_bucket_name` string,
  `resource_tags_user_cost_tracking` string)
  PARTITIONED BY (
  `year` string,
  `month` string)
  ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
  OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  LOCATION
  '<<LOCATION>>'

• Run the following query in Athena to create the table for Amazon S3 Inventory. Verify and update the section for <<LOCATION>> at the end of the query and point it to the correct S3 bucket and location.
  • To get the exact value of the location, navigate to the bucket where inventory configurations are stored and navigate to the folder path Hive . Use the S3 URI to replace <<LOCATION>> in the query.

    CREATE EXTERNAL TABLE s3_object_inventory(
             bucket string,
             key string,
             version_id string,
             is_latest boolean,
             is_delete_marker boolean,
             size bigint,
             last_modified_date bigint,
             storage_class string,
             is_multipart_uploaded boolean,
             replication_status string,
             encryption_status string,
             intelligent_tiering_access_tier string,
             bucket_key_status string
    ) PARTITIONED BY (
            dt string
    )
    ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
      STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
      OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
      LOCATION '<<LOCATION>>';
    

• We need to refresh the partitions and add new inventory lists to the table. Use the following commands to add data to the CUR table and Amazon S3 Inventory table:

  MSCK REPAIR TABLE `account_cur`;
  
  MSCK REPAIR TABLE s3_object_inventory;

Run queries in Athena to allocate the cost of objects in an S3 bucket

Now we can query the data we have available to get a cost allocation breakdown at the prefix level.

We need to provide some information in the following queries:

• Update <<YYYY-MM-DD>> with the date for which you want to analyze the data
• Update <<prefix>> with the prefix values for your bucket that needs to be analyzed
• Update <<bucket_name>> with the name of the bucket that needs to be analyzed

We use the following part of the query to calculate the size of storage being used by the target prefix that we want to calculate the cost for:

select date_parse(dt,'%Y-%m-%d-%H-%i') dt, cast (sum(size) as double) targetPrefixBytes
from s3_object_inventory
where date_parse(dt,'%Y-%m-%d-%H-%i') = cast('<<YYYY-MM-DD>>' as timestamp)
and key like '<<prefix>>/%'
group by dt

Next, we calculate the total size of the bucket on that particular date:

select date_parse(dt,'%Y-%m-%d-%H-%i') dt, cast (sum(size) as double) totalBytes
from s3_object_inventory
where date_parse(dt,'%Y-%m-%d-%H-%i') = cast('<<YYYY-MM-DD>>' as timestamp)
group by dt

We query the CUR table to get the cost of a particular bucket on a particular date:

select line_item_usage_start_date as dt, sum(line_item_blended_cost) as line_item_blended_cost
from "account_cur"
where line_item_product_code = 'AmazonS3'
and product_servicecode = 'AmazonS3'
and line_item_operation = 'StandardStorage'
and line_item_resource_id = '<<bucket_name>>'
and line_item_usage_start_date = cast('<<YYYY-MM-DD>>' as timestamp)
group by line_item_usage_start_date

Putting all of this together, we can calculate the cost of a particular prefix (folder or a file) on a specific date. The complete query is as follows:

with
cost as (select line_item_usage_start_date as dt, sum(line_item_blended_cost) as line_item_blended_cost
from "account_cur"
where line_item_product_code = 'AmazonS3'
and product_servicecode = 'AmazonS3'
and line_item_operation = 'StandardStorage'
and line_item_resource_id = '<<bucket_name>>'
and line_item_usage_start_date = cast('<<YYYY-MM-DD>>' as timestamp)
group by line_item_usage_start_date),
total as (select date_parse(dt,'%Y-%m-%d-%H-%i') dt, cast (sum(size) as double) totalBytes
from s3_object_inventory
where date_parse(dt,'%Y-%m-%d-%H-%i') = cast('<<YYYY-MM-DD>>' as timestamp)
group by dt),
target as (select date_parse(dt,'%Y-%m-%d-%H-%i') dt, cast (sum(size) as double) targetPrefixBytes
from s3_object_inventory
where date_parse(dt,'%Y-%m-%d-%H-%i') = cast('<<YYYY-MM-DD>>' as timestamp)
and key like '<<prefix>>/%'
group by dt)
select target.dt,
(target.targetPrefixBytes/ total.totalBytes * 100) percentUsed,
cost.line_item_blended_cost totalCost,
cost.line_item_blended_cost*(target.targetPrefixBytes/ total.totalBytes) as prefixCost
from target, total, cost
where target.dt = total.dt
and target.dt = cost.dt

The following screenshot shows the results table for the sample data we used in this post. We get the following information:

• dt – Date
• percentUsed – The percentage of prefix space compared to overall bucket space
• totalCost – The total cost of the bucket
• prefixCost – The cost of the space used by the prefix

Clean up

To stop incurring costs, be sure to disable Amazon S3 Inventory and Cost and Usage Reports when you’re done.

Delete the S3 buckets created for the Amazon S3 Inventory reports and Cost and Usage Reports to avoid storage charges.

Other methods for Amazon S3 storage analysis

Amazon S3 Storage Lens can provide a single view of object storage usage and activity across your entire Amazon S3 storage. With S3 Storage Lens, you can understand, analyze, and optimize storage with over 29 usage and activity metrics and interactive dashboards to aggregate data for your entire organization, specific accounts, Regions, buckets, or prefixes. All of this data is accessible on the Amazon S3 console or as raw data in an S3 bucket.

S3 Storage Lens doesn’t provide cost analysis based on an object or prefix in a single bucket. If you want visibility of storage usage and trends across the entire storage footprint along with recommendations on cost efficiency and data protection best practices, S3 Storage Lens is the right option. But if you want a cost analysis of specific S3 buckets and looking for ways to get cost allocation of S3 objects at the object or prefix level, the solution in this post would be the best fit.

Conclusion

In this post, we detailed how to create a cost breakdown model at the object or prefix level for S3 buckets that contains data for multiple business units and applications. We used Athena to query the reports and datasets produced by the AWS CUR and Amazon S3 Inventory features that, when correlated, give us the cost allocation at the object and prefix level. This solution gives you an easy way to calculate costs for independent objects and prefixes, which can be used for internal chargebacks or just to know the per-object or per-prefix spending in a shared S3 bucket.

About the Authors

Dagar Katyal is a Senior Solutions Architect at AWS, based in Chicago, Illinois. He works with customers and provides guidance for key strategic initiatives important for their business. Dagar has an MBA and has spent years over 15 years working with customers on projects on analytics strategy, roadmap, and using data as a key differentiator. When not working with customers, Dagar spends time with his family and doing home improvement projects.

Saiteja Pudi is a Solutions Architect at AWS, based in Dallas, Tx. He has been with AWS for more than 3 years now, helping customers derive the true potential of AWS by being their trusted advisor. He comes from an application development background, interested in Data Science and Machine Learning.