Analyze your Amazon S3 spend using AWS Glue and Amazon Redshift

The AWS Cost & Usage Report (CUR) tracks your AWS usage and provides estimated charges associated with that usage. You can configure this report to present the data at hourly or daily intervals, and it is updated at least one time per day until it is finalized at the end of the billing period. The Cost & Usage Report is delivered automatically to an Amazon S3 bucket that you specify, and you can download it from there directly. You can also integrate the report into Amazon Redshift, query it with Amazon Athena, or upload it to Amazon QuickSight. For more information, see Query and Visualize AWS Cost and Usage Data Using Amazon Athena and Amazon QuickSight.

This post presents a solution that uses AWS Glue Data Catalog and Amazon Redshift to analyze S3 usage and spend by combining the AWS CUR, S3 inventory reports, and S3 server access logs.

Prerequisites

Before you begin, complete the following prerequisites:

You need an S3 bucket for your S3 inventory and server access log data files. For more information, see Create a Bucket and What is Amazon S3?
You must have the appropriate IAM permissions for Amazon Redshift to be able to access the S3 buckets – for this post, choose two non-restrictive IAM roles (AmazonS3FullAccess and AWSGlueConsoleFullAccess), but restrict your access accordingly for your own scenarios.

Amazon S3 inventory

Amazon S3 inventory is one of the tools 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 a given S3 bucket.

Amazon S3 server access logs

Server access logging provides detailed records for the requests you make to a bucket. Server access logs are useful for many applications, for example in security and access audits. It can also help you learn about your customer base and understand your S3 bill.

AWS Glue

AWS Glue is a fully managed extract, transform, and load (ETL) service that makes it simple and cost-effective to categorize your data, clean it, enrich it, and move it reliably between various data stores. AWS Glue consists of a central metadata repository known as the Data Catalog, a crawler to populate the Data Catalog with tables, an ETL engine that automatically generates Python or Scala code, and a flexible scheduler that handles dependency resolution, job monitoring, and retries. AWS Glue is serverless, so there’s no infrastructure to set up or manage. This post uses AWS Glue to catalog S3 inventory data and server access logs, which makes it available for you to query with Amazon Redshift Spectrum.

Amazon Redshift

Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. You can use Amazon Redshift to efficiently query and retrieve structured and semi-structured data from files in S3 without having to load the data into Amazon Redshift native tables. You can create Amazon Redshift external tables by defining the structure for files and registering them as tables in the AWS Glue Data Catalog.

Setting up S3 inventory reports for analysis

This post uses the Parquet file format for its inventory reports and delivers the files daily to S3 buckets. You can select both the frequency of delivery and output file formats under Advanced settings as shown in the screenshot below:

For more information about configuring your S3 inventory, see How Do I Configure Amazon S3 Inventory?

The following diagram shows the data flow for this solution:

Below steps summarize the data flow diagram represented above:

S3 Inventory Reports are delivered to an S3 bucket that you configure.
The AWS Glue crawler then crawls this S3 bucket and populates the metadata in the AWS Glue Data Catalog.
The AWS Glue Data Catalog is then accessible through an external schema in Redshift.
The S3 Inventory Reports (available in the AWS Glue Data Catalog) and the Cost and Usage Reports (available in another S3 bucket) are now ready to be joined and queried for analysis.

The inventory reports are delivered to an S3 bucket. The following screenshot shows the S3 bucket structure for the S3 inventory reports:

There is a data folder in this bucket. This folder contains the Parquet data you want to analyze. The following screenshot shows the content of the folder.

Because these are daily files, there is one file per day.

Configuring an AWS Glue crawler

You can use an AWS Glue crawler to discover this dataset in your S3 bucket and create the table schemas in the Data Catalog. After you create these tables, you can query them directly from Amazon Redshift.

To configure your crawler to read S3 inventory files from your S3 bucket, complete the following steps:

Choose a crawler name.
Choose S3 as the data store and specify the S3 path up to the data
Choose an IAM role to read data from S3 – AmazonS3FullAccess and AWSGlueConsoleFullAccess.
Set a frequency schedule for the crawler to run.
Configure the crawler’s output by selecting a database and adding a prefix (if any).

This post uses the database s3spendanalysis.

The following screenshot shows the completed crawler configuration.

Run this crawler to add tables to your Glue Data Catalog. After the crawler has completed successfully, go to the Tables section on your AWS Glue console to verify the table details and table metadata. The following screenshot shows the table details and table metadata after your AWS Glue crawler has completed successfully:

Creating an external schema

Before you can query the S3 inventory reports, you need to create an external schema (and subsequently, external tables) in Amazon Redshift. An Amazon Redshift external schema references an external database in an external data catalog. Because you are using an AWS Glue Data Catalog as your external catalog, after you create an external schema in Amazon Redshift, you can see all the external tables in your Data Catalog in Amazon Redshift. To create the external schema, enter the following code:

create external schema spectrum_schema from data catalog
database 's3spendanalysis'
iam_role 'arn:aws:iam::<AWS_IAM_ROLE>';

Querying the table

On the Amazon Redshift dashboard, under Query editor, you can see the data table. You can also query the svv_external_schemas system table to verify that your external schema has been created successfully. See the following screenshot.

You can now query the S3 inventory reports directly from Amazon Redshift without having to move the data into Amazon Redshift first. The following screenshot shows how to do this using the Query Editor in the Amazon Redshift console:

Setting up S3 server access logs for analysis

The following diagram shows the data flow for this solution.

Below steps summarize the data flow diagram represented above:

S3 Server Access Logs are delivered to an S3 bucket that you configure.
These server access logs are then directly accessible to be queried from Amazon Redshift (note that we’ll be using CREATE EXTERNAL TABLE in Redshift Spectrum for this purpose, explained below).
The S3 Server Access Logs and the Cost and Usage Reports (available in another S3 bucket) are now ready to be joined and queried for analysis.

The S3 server access logs are delivered to an S3 bucket. For more information about setting up server access logging, see Amazon S3 Server Access Logging.

The following screenshot shows the S3 bucket structure for the server access logs.

The server access log files consist of a sequence of new-line delimited log records. Each log record represents one request and consists of space-delimited fields. The following code is an example log record:

b8ad5f5cfd3c09418536b47b157851fb7bea4a00486471093a7d765e35a4f8ef s3spendanalysisblog [23/Sep/2018:22:10:52 +0000] 72.21.196.65 arn:aws:iam::<AWS Account #>:user/shayons D5633DAD1063C5CA REST.GET.LIFECYCLE - "GET /s3spendanalysisblog?lifecycle= HTTP/1.1" 404 NoSuchLifecycleConfiguration 332 - 105 - "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.408 Linux/4.9.119-0.1.ac.277.71.329.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.181-b13 java/1.8.0_181" -

Creating an external table

You can define the S3 server access logs as an external table. Because you already have an external schema, create an external table using the following code. This post uses RegEx SerDe to create a table that allows you to correctly parse all the fields present in the S3 server access logs. See the following code:

CREATE EXTERNAL TABLE spectrum_schema.s3accesslogs(
BucketOwner                   varchar(256), 
Bucket                        varchar(256), 
RequestDateTime               varchar(256), 
RemoteIP                      varchar(256), 
Requester                     varchar(256), 
RequestID                     varchar(256), 
Operation                     varchar(256), 
Key                           varchar(256), 
RequestURI_operation          varchar(256),
RequestURI_key                varchar(256),
RequestURI_httpProtoversion   varchar(256),
HTTPstatus                    varchar(256), 
ErrorCode                     varchar(256), 
BytesSent                     varchar(256), 
ObjectSize                    varchar(256), 
TotalTime                     varchar(256), 
TurnAroundTime                varchar(256), 
Referrer                      varchar(256), 
UserAgent                     varchar(256), 
VersionId                     varchar(256))
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^ ]*) ([^ ]*) ([^ ]*)\" (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*)'
  )
STORED AS TEXTFILE
LOCATION
  's3://s3spendanalysisblog/accesslogs/';

Validating the data

You can validate the external table data in Amazon Redshift. The following screenshot shows how to do this using the Query Editor in the Amazon Redshift console:

You are now ready to analyze the data.

Analyzing the data using Amazon Redshift

In this post, you have a CUR file per day in your S3 bucket. The files themselves are organized in a monthly hierarchy. See the following screenshot.

Each day’s file consists of the following files for CUR data:

myCURReport-1.csv.gz – A zipped file of the data itself
myCURReport-Manifest.json – A JSON file that contains the metadata for the file
myCURReport-RedshiftCommands.sql – Amazon Redshift table creation scripts and a COPY command to create the CUR table from a Redshift manifest file
myCURReport-RedshiftManifest.json – The Amazon Redshift manifest file to create the CUR table

Using Amazon Redshift is one of the many ways to carry out this analysis. Amazon Redshift is a fast, fully managed cloud data warehouse that makes it simple and cost-effective to analyze all your data using standard SQL and your existing Business Intelligence (BI) tools. Amazon Redshift gives you fast querying capabilities over structured data using familiar SQL-based clients and BI tools using standard ODBC and JDBC connections. Queries are distributed and parallelized across multiple physical resources.

You are now ready to run SQL queries with the Amazon Redshift SQL Query Editor. This post also uses the psql client tool, a terminal-based front end from PostgreSQL, to query the data in the cluster.

To query the data, complete the following steps:

Create a custom schema to contain your tables for analysis. See the following code:
```
create schema if not exists redshift_schema;
```
You should create your table in a schema other than public to control user access to database objects.

Create a CUR table for the latest month in Amazon Redshift using the CUR SQL file in S3. See the following code:

create table redshift_schema.AWSBilling201910 (
identity_LineItemId VARCHAR(256),
identity_TimeInterval VARCHAR(100),
bill_InvoiceId VARCHAR(100),
bill_BillingEntity VARCHAR(10),
bill_BillType VARCHAR(100),
bill_PayerAccountId VARCHAR(100),
bill_BillingPeriodStartDate TIMESTAMPTZ,
bill_BillingPeriodEndDate TIMESTAMPTZ,
lineItem_UsageAccountId VARCHAR(100),
lineItem_LineItemType VARCHAR(100),
lineItem_UsageStartDate TIMESTAMPTZ,
lineItem_UsageEndDate TIMESTAMPTZ,
lineItem_ProductCode VARCHAR(100),
lineItem_UsageType VARCHAR(100),
lineItem_Operation VARCHAR(100),
lineItem_AvailabilityZone VARCHAR(100),
lineItem_ResourceId VARCHAR(256),
lineItem_UsageAmount DECIMAL(11,2),
lineItem_NormalizationFactor VARCHAR(10),
lineItem_NormalizedUsageAmount DECIMAL(11,2),
lineItem_CurrencyCode VARCHAR(10),
lineItem_UnblendedRate DECIMAL(11,2),
lineItem_UnblendedCost DECIMAL(11,2),
lineItem_BlendedRate DECIMAL(11,2),
lineItem_BlendedCost DECIMAL(11,2),
lineItem_LineItemDescription VARCHAR(100),
lineItem_TaxType VARCHAR(100),
lineItem_LegalEntity VARCHAR(100),
product_ProductName VARCHAR(100),
product_alarmType VARCHAR(100),
product_automaticLabel VARCHAR(100),
product_availability VARCHAR(100),
product_availabilityZone VARCHAR(100),
product_clockSpeed VARCHAR(100),
product_currentGeneration VARCHAR(100),
product_databaseEngine VARCHAR(100),
product_dedicatedEbsThroughput VARCHAR(100),
product_deploymentOption VARCHAR(100),
product_durability VARCHAR(100),
product_ecu VARCHAR(100),
product_edition VARCHAR(100),
product_engineCode VARCHAR(100),
product_enhancedNetworkingSupported VARCHAR(100),
product_eventType VARCHAR(100),
product_feeCode VARCHAR(100),
product_feeDescription VARCHAR(100),
product_fromLocation VARCHAR(100),
product_fromLocationType VARCHAR(100),
product_gpu VARCHAR(100),
product_gpuMemory VARCHAR(100),
product_group VARCHAR(100),
product_groupDescription VARCHAR(100),
product_instanceFamily VARCHAR(100),
product_instanceType VARCHAR(100),
product_instanceTypeFamily VARCHAR(100),
product_io VARCHAR(100),
product_labelingTaskType VARCHAR(100),
product_licenseModel VARCHAR(100),
product_location VARCHAR(100),
product_locationType VARCHAR(100),
product_maxThroughputvolume VARCHAR(100),
product_maxVolumeSize VARCHAR(100),
product_memory VARCHAR(100),
product_messageDeliveryFrequency VARCHAR(100),
product_messageDeliveryOrder VARCHAR(100),
product_minVolumeSize VARCHAR(100),
product_networkPerformance VARCHAR(100),
product_normalizationSizeFactor VARCHAR(100),
product_operation VARCHAR(100),
product_physicalCpu VARCHAR(100),
product_physicalGpu VARCHAR(100),
product_physicalProcessor VARCHAR(100),
product_processorArchitecture VARCHAR(100),
product_processorFeatures VARCHAR(100),
product_productFamily VARCHAR(100),
product_protocol VARCHAR(100),
product_queueType VARCHAR(100),
product_region VARCHAR(100),
product_servicecode VARCHAR(100),
product_servicename VARCHAR(100),
product_sku VARCHAR(100),
product_storage VARCHAR(100),
product_storageClass VARCHAR(100),
product_storageMedia VARCHAR(100),
product_subscriptionType VARCHAR(100),
product_toLocation VARCHAR(100),
product_toLocationType VARCHAR(100),
product_transferType VARCHAR(100),
product_usageFamily VARCHAR(100),
product_usagetype VARCHAR(100),
product_vcpu VARCHAR(100),
product_version VARCHAR(100),
product_volumeType VARCHAR(100),
product_workforceType VARCHAR(100),
pricing_RateId VARCHAR(100),
pricing_publicOnDemandCost DECIMAL(11,2),
pricing_publicOnDemandRate DECIMAL(11,2),
pricing_term VARCHAR(100),
pricing_unit VARCHAR(100),
reservation_AmortizedUpfrontCostForUsage DECIMAL(11,2),
reservation_AmortizedUpfrontFeeForBillingPeriod DECIMAL(11,2),
reservation_EffectiveCost DECIMAL(11,2),
reservation_EndTime TIMESTAMPTZ,
reservation_ModificationStatus VARCHAR(100),
reservation_NormalizedUnitsPerReservation BIGINT,
reservation_RecurringFeeForUsage DECIMAL(11,2),
reservation_StartTime TIMESTAMPTZ,
reservation_SubscriptionId VARCHAR(100),
reservation_TotalReservedNormalizedUnits BIGINT,
reservation_TotalReservedUnits BIGINT,
reservation_UnitsPerReservation BIGINT,
reservation_UnusedAmortizedUpfrontFeeForBillingPeriod DECIMAL(11,2),
reservation_UnusedNormalizedUnitQuantity BIGINT,
reservation_UnusedQuantity BIGINT,
reservation_UnusedRecurringFee DECIMAL(11,2),
reservation_UpfrontValue BIGINT
);

Load the data into Amazon Redshift for the latest month, using the provided CUR Manifest file. See the following code:

copy AWSBilling201910 from 's3://ss-cur//myCURReport/20191001-20191101/fd76beee-0709-42d5-bcb2-bb45f8ba1aae/myCURReport-RedshiftManifest.json'
credentials 'arn:aws:iam::<AWS_IAM_ROLE>'
GZIP CSV IGNOREHEADER 1 TIMEFORMAT 'auto' manifest;

Validate the data loaded in the Amazon Redshift table. See the following code:
```
select * from AWSBilling201910
where lineItem_ProductCode = 'AmazonS3'
and lineItem_ResourceId = 's3spendanalysisblog' limit 10;
```
The following screenshot shows that data has been loaded correctly in the Amazon Redshift table:

Managing database security

You can manage database security in Amazon Redshift by controlling which users have access to which database objects. To make sure your objects are secure, create two groups: FINANCE and ADMIN, with two users in FINANCE and one user in ADMIN. Complete the following steps:

Create the groups where the user accounts are assigned. The following code creates two different user groups:
```
create group finance;
create group admin;
```
To view all user groups, query the PG_GROUP system catalog table (you should see finance and admin here):
```
select * from pg_group:
```
Create three database users with different privileges and add them to the groups. See the following code:
```
create user finance1 password 'finance1Pass'
in group finance;

create user finance2 password 'finance2Pass'
in group finance;

create user admin1 password 'admin1Pass'
in group admin;
```
Validate the users have been successfully created. To view a list of users, query the PG_USER catalog table:
Grant SELECT privileges to the FINANCE group and ALL privileges to the ADMIN group for your table AWSBilling201910 in redshift_schema. See the following code:
```
grant select on table redshift_schema.AWSBilling201910 to group finance; 
grant all on table redshift_schema.AWSBilling201910 to group admin;
```
You can verify if you enforced database security correctly. The user finance1 tried to rename the table AWSBilling201910 in redshift_schema, but got a permission denied error message (due to restricted access). The following screenshot shows this scenario and the subsequent error message:

Example S3 inventory analysis

S3 charges split per bucket. The following query identifies the data storage and transfer costs for each separate S3 bucket:

SELECT
  "lineitem_productcode",
  "lineitem_usagetype",
  "lineitem_resourceid",
  b."storage_class",
  SUM(CASE
    WHEN "lineitem_usagetype" like '%Byte%' THEN "lineitem_usageamount"/1024
    ELSE "lineitem_usageamount"
  END) as "Usage",
  CASE
    WHEN "lineitem_usagetype" like '%Byte%' THEN 'TBs'
    ELSE 'Requests'
  END as "Usage Units",
  sum("lineitem_blendedcost") as cost
from awsbilling201902 a
  join spectrum_schema.data b
    on a.lineItem_ResourceId = b.bucket
where "product_productname" = 'Amazon Simple Storage Service'
group by
  "lineitem_productcode",
  "lineitem_usagetype",
  "lineitem_resourceid",
  b."storage_class"
order by
  sum("lineitem_blendedcost") desc;

The following screenshot shows the results of executing the above query:

Costs are split by type of storage (for example, Glacier versus standard storage).

The following query identifies S3 data transfer costs (intra-region and inter-region) by S3 storage class (usage amount, unblended cost, blended cost):

SELECT
 lineitem_productcode
 ,product_fromlocation
 ,product_tolocation,
  b.storage_class
 ,sum(lineitem_usageamount) usageamount
 ,sum(lineitem_unblendedcost) unblendedcost
 ,sum(lineitem_blendedcost) blendedcost
FROM
awsbilling201902 a
  join spectrum_schema.data b
ON
    a.lineItem_ResourceId = b.bucket
WHERE
 a.lineitem_productcode = 'AmazonS3'
 AND a.product_productfamily = 'Data Transfer'
GROUP BY
 1,2,3,4
ORDER BY
 usageamount desc;