Usecase to Optimize the AWS Cost & Usage Billing reports to clients using AWS Redshift, S3 and Quicksight
Usecases:
- Exporting AWS Partner Billing and Cost Report CSV/SQL/ZIP from S3 Bucket.
- Import into Amazon Redshift for Querying the reports data through SQL commands.
- Import into Amazon QuickSight for generating Customized Analytics Chart Report with data filtering.
Deliverables:
- Wring SQL Queries for generating bill usage and Cost report for various Customer using Redshift and Creating Reports using QuickSight.
Import AWS Partner Billing and Cost Report in CSV format into Amazon Redshift for Querying the reports data through SQL commands and Import into Amazon QuickSight for generating Customized Analytics Chart Report with data filtering.
Usecase Workflow
Step 1: Upload the sample file (Sample Partner CUR.csv) to an Amazon S3 bucket:
Step 2: Access Redshift using PSQL client:
Step 3: Create the Data table structure for the given CSV on Redshift Database:
Data Table Description:
Step 4: Import AWS Partner Billing and Cost Report into Redshift Database using COPY Command through PSQL client CLI.
[Or]
Import the AWS billing report through PSQL GUI:
Step 5: Verify the Loaded the AWS Cost and Billing Data into Redshift:
Step 6: SQL Queries to filter the Data Table:
SQL Queries List in AWS:
SQL Query to filter the Data Tables by lineItem_UsageAccountID, lineItem_ProductCode, lineItem_UsageType, lineItem_UsageAmount.
[or]
Through PSQL GUI:
SQL Query to limit the Data Tables with 10 entries:
Step 6: Export/Unloads the result of SQL queries to one or more files in CSV format on Amazon Simple Storage Service
Postgresql Queries
To filter the AWS costs based on Product Code and Availability zone:
$ select lineitem_productcode, lineitem_availabilityzone, sum(cast(lineitem_unblendedcost as float)) from AWSBillingPartner_Test where lineitem_availabilityzone <> '' group by lineitem_availabilityzone, lineitem_productcode;
To filter the AWS costs based on UserAccountID, Usage Type Group and Availability zone:
$ select DISTINCT lineitem_usageaccountid as User_Account, lineitem_productcode as Product_Usage, lineitem_availabilityzone as User_AZ, sum(cast(lineitem_unblendedcost as float)) as Userusage_cost from AWSBillingPartner_Test where lineitem_usageaccountid IS NOT NULL and lineitem_availabilityzone IS NOT NULL group by lineitem_availabilityzone, lineitem_productcode, lineitem_usageaccountid;
To filter the AWS costs based AWS S3 service usage:
$ select sum(cast(lineitem_unblendedcost as float)) as user_usagecost from AWSBillingPartner_Test where lineitem_productcode='AmazonS3' or pricing_unit='GB';
To filter the AWS costs based AWS EC2 service usage:
$ select lineitem_usageaccountid as user_accountid, sum(cast(lineitem_unblendedcost as float)) as user_usagecost from AWSBillingPartner_Test where lineitem_productcode='AmazonEC2' group by lineitem_usageaccountid;
To calculate the Cost based on the Services and utilization date:
$ select sum(cast(lineitem_unblendedcost as float)) from AWSBillingPartner_Test where lineitem_productcode='AmazonS3' or lineitem_productcode='AmazonEc2' and bill_BillingPeriodEndDate > '2018-03-01';
**'Unloads' the SQL query result to file on Amazon Simple Storage Service (Amazon S3) and download as CSV file.
UNLOAD automatically creates encrypted file on AWS S3:
Download the customized file in CSV format from AWS S3: (Attached sample CSV file in the mail thread)
Mail Content
