Building a cost efficient, petabyte-scale lake house with Amazon S3 lifecycle rules and Amazon Redshift Spectrum: Part 1

The continuous growth of data volumes combined with requirements to implement long-term retention (typically due to specific industry regulations) puts pressure on the storage costs of data warehouse solutions, even for cloud native data warehouse services such as Amazon Redshift. The introduction of the new Amazon Redshift RA3 node types helped in decoupling compute from storage growth. Integration points provided by Amazon Redshift Spectrum, Amazon Simple Storage Service (Amazon S3) storage classes, and other Amazon S3 features allow for compliance of retention policies while keeping costs under control.

An enterprise customer in Italy asked the AWS team to recommend best practices on how to implement a data journey solution for sales data; the objective of part 1 of this series is to provide step-by-step instructions and best practices on how to build an end-to-end data lifecycle management system integrated with a data lake house implemented on Amazon S3 with Amazon Redshift. In part 2, we show some additional best practices to operate the solution: implementing a sustainable monthly ageing process, using Amazon Redshift local tables to troubleshoot common issues, and using Amazon S3 access logs to analyze data access patterns.

Amazon Redshift and Redshift Spectrum

At re:Invent 2019, AWS announced new Amazon Redshift RA3 nodes. Even though this introduced new levels of cost efficiency in the cloud data warehouse, we faced customer cases where the data volume to be kept is an order of magnitude higher due to specific regulations that impose historical data to be kept for up to 10–12 years or more. In addition, this historical cold data must be accessed by other services and applications external to Amazon Redshift (such as Amazon SageMaker for AI and machine learning (ML) training jobs), and occasionally it needs to be queried jointly with Amazon Redshift hot data. In these situations, Redshift Spectrum is a great fit because, among other factors, you can use it in conjunction with Amazon S3 storage classes to further improve TCO.

Redshift Spectrum allows you to query data that resides in S3 buckets using already in place application code and logic used for data warehouse tables, and potentially performing joins and unions of Amazon Redshift local tables and data on Amazon S3.

Redshift Spectrum uses a fleet of compute nodes managed by AWS that increases system scalability. To use it, we need to define at least an external schema and an external table (unless an external schema and external database are already defined in the AWS Glue Data Catalog). Data definition language (DDL) statements used to define an external table include a location attribute to address S3 buckets and prefixes containing the dataset, which could be in common file formats like ORC, Parquet, AVRO, CSV, JSON, or plain text. Compressed and columnar file formats like Apache Parquet are preferred because they provide less storage usage and better performance.

For a data catalog, we could use AWS Glue or an external hive metastore. For this post, we use AWS Glue.

S3 Lifecycle rules

Amazon S3 storage classes include S3 Standard, S3-IA, S3 One-Zone, S3 Intelligent-Tiering, S3 Glacier, and S3 Glacier Deep Archive. For our use case, we need to keep data accessible for queries for 5 years and with high durability, so we consider only S3 Standard and S3-IA for this time frame, and S3 Glacier only for long term (5–12 years). Data access to S3 Glacier requires data retrieval in the range of minutes (if using expedited retrieval) and this can’t be matched with the ability to query data. We can adopt Glacier for very cold data if you implement a manual process to first restore the Glacier archive to a temporary S3 bucket, and then query this data defined via an external table.

S3 Glacier Select allows you to query on data directly in S3 Glacier, but it only supports uncompressed CSV files. Because the objective of this post is to propose a cost-efficient solution, we didn’t consider it. If for any reason you have constraints for storing in CSV file format (instead of compressed formats like Parquet), Glacier Select might also be a good fit.

Excluding retrieval costs, the cost for storage for S3-IA is typically around 45% cheaper than S3 Standard, and S3 Glacier is 68% cheaper than S3-IA. For updated pricing information, see Amazon S3 pricing.

We don’t use S3 Intelligent Tiering because it bases storage transition on the last access time, and this resets every time we need to query the data. We use the S3 Lifecycle rules that are based either on creation time or prefix or tag matching, which is consistent regardless of data access patterns.

Simulated use case and retention policy

For our use case, we need to implement the data retention strategy for trip records outlined in the following table.

For this post, we create a new table in a new Amazon Redshift cluster and load a public dataset. We use the New York City Taxi and Limousine Commission (TLC) Trip Record Data because it provides the required historical depth.

We use the Green Taxi Trip Records, based on monthly CSV files containing 20 columns with fields like vendor ID, pickup time, drop-off time, fare, and other information. 

Preparing the dataset and setting up the Amazon Redshift environment

As a first step, we create an AWS Identity and Access Management (IAM) role for Redshift Spectrum. This is required to allow access to Amazon Redshift to Amazon S3 for querying and loading data, and also to allow access to the AWS Glue Data Catalog whenever we create, modify, or delete a new external table.

1. Create a role named BlogSpectrumRole.
2. Edit the following two JSON files, which have the IAM policies according to the bucket and prefix used in this post, as needed, and attach the policies to the role you created:
   S3-Lakehouse-Policy.JSON

   	{
   	    "Version": "2012-10-17",
   	    "Statement": [
   	        {
   	            "Sid": "VisualEditor0",
   	            "Effect": "Allow",
   	            "Action": "s3:*",
   	            "Resource": [
   	                "arn:aws:s3:::rs-lakehouse-blog-post",
   	                "arn:aws:s3:::rs-lakehouse-blog-post/extract_longterm/*",
   	                "arn:aws:s3:::rs-lakehouse-blog-post/extract_midterm/*",
   	                "arn:aws:s3:::rs-lakehouse-blog-post/extract_shortterm/*",
   	    "arn:aws:s3:::rs-lakehouse-blog-post/accesslogs/*"
   	            ]
   	        }
   	    ]
   	}

   Glue-Lakehouse-Policy.JSON

   {
   	"Version": "2012-10-17",
   	"Statement": [
   		{
   			"Sid": "VisualEditor0",
   			"Effect": "Allow",
   			"Action": [
   				"glue:CreateDatabase",
   				"glue:DeleteDatabase",
   				"glue:GetDatabase",
   				"glue:GetDatabases",
   				"glue:UpdateDatabase",
   				"glue:CreateTable",
   				"glue:DeleteTable",
   				"glue:BatchDeleteTable",
   				"glue:UpdateTable",
   				"glue:GetTable",
   				"glue:GetTables",
   				"glue:BatchCreatePartition",
   				"glue:CreatePartition",
   				"glue:DeletePartition",
   				"glue:BatchDeletePartition",
   				"glue:UpdatePartition",
   				"glue:GetPartition",
   				"glue:GetPartitions",
   				"glue:BatchGetPartition"
   				],
   			"Resource": [
   				"*"
   			]
   		}
   	]
   }

Now you create a single-node Amazon Redshift cluster based on a DC2.large instance type, attaching the newly created IAM role BlogSpectrumRole.

1. On the Amazon Redshift console, choose Create cluster.
2. Keep the default cluster identifier redshift-cluster-1.
3. Choose the node type DC2.large.
4. Set the configuration to single node.
5. Keep the default dbname and ports.
6. Set a primary user password.
7. Choose Create cluster.
8. After the cluster is configured, check the attached IAM role on the Properties tab for the cluster.
9. Take note of the IAM role ARN, because you use it to create external tables.

Copying data into Amazon Redshift

To connect to Amazon Redshift, you can use a free client like SQL Workbench/J or use the AWS console embedded query editor with the previously created credentials.

1. Create a table according to the dataset schema using the following DDL statement:

   	create table greentaxi(
   	vendor_id integer,
   	pickup timestamp,
   	dropoff timestamp,
   	storeandfwd char(2),
   	ratecodeid integer,
   	pulocid integer,
   	dolocid integer,
   	passenger_count integer,
   	trip_dist real,
   	fare_amount real,
   	extra real,
   	mta_tax real,
   	tip_amount real,
   	toll_amount real,
   	ehail_fee real,
   	improve_surch real,
   	total_amount real,
   	pay_type integer,
   	trip_type integer,
   	congest_surch real
   );

The most efficient method to load data into Amazon Redshift is using the COPY command, because it uses the distributed architecture (each slice can ingest one file at the same time).

2. Load year 2020 data from January to June in the Amazon Redshift table with the following command (replace the IAM role value with the one you created earlier):

   copy greentaxi from ‘s3://nyc-tlc/trip data/green_tripdata_2020’ 
   	iam_role ‘arn:aws:iam::123456789012:role/BlogSpectrumRole’ 
   	delimiter ‘,’ 
   	CSV 
   	dateformat ‘auto’
   	region ‘us-east-1’ 
   ignoreheader as 1;

Now the greentaxi table includes all records starting from January 2019 to June 2020. You’re now ready to leverage Redshift Spectrum and S3 storage classes to save costs.

Extracting data from Amazon Redshift

You perform the next steps using the AWS Command Line Interface (AWS CLI). For download and installation instructions, see Installing, updating, and uninstalling the AWS CLI version 2.

Use the AWS CONFIGURE command to set the access key and secret access key of your IAM user and your selected AWS Region (same as your S3 buckets) of your Amazon Redshift cluster.

In this section, we evaluate two different use cases:

• New customer – As a new customer, you don’t have much Amazon Redshift old data, and want to extract only the oldest monthly data and apply a lifecycle policy based on creation date. Storage tiering only affects future data and is fully automated.
• Old customer – In this use case, you come from a multi-year data growth, and need to move existing Amazon Redshift data to different storage classes. In addition, you want a fully automated solution but with the ability to override and decide what and when to transition data between S3 storage classes. This requirement is due to many factors, like the GDPR rule “right to be forgotten.” You may need to edit historical data to remove specific customer records, which changes the file creation date. For this reason, you need S3 Lifecycle rules based on tagging instead of creation date.

New customer use case

The UNLOAD command uses the result of an embedded SQL query to extract data from Amazon Redshift to Amazon S3, producing different file formats such as CSV, text, and Parquet. To extract data from January 2019, complete the following steps:

1. Create a destination bucket like the following:

   aws s3 mb s3://rs-lakehouse-blog-post

2. Create a folder named archive in the destination bucket rs-lakehouse-blog-post:

   aws s3api put-object --bucket rs-lakehouse-blog-post -–key archive

3. Use the following SQL code to implement the UNLOAD statement. The SELECT on Data data types requires quoting as well as a SELECT statement embedded in the UNLOAD command:

   unload ('select * from greentaxi where pickup between ''2019-01-01 00:00:00'' and ''2019-01-30 23:59:59''')
   to 's3://rs-lakehouse-blog-post/archive/5to12years_taxi'
   iam_role 'arn:aws:iam::123456789012:role/BlogSpectrumRole'; 

The output shows that the UNLOAD statement generated two files of 33 MB each. By default, UNLOAD generates at least one file for each slice in the Amazon Redshift cluster. My cluster is a single node with DC2 type instances with two slices. This default file format is text, which is not storage optimized.

To simplify the process, you create a single file for each month so that you can later apply lifecycle rules to each file. In real-world scenarios, extracting data with a single file isn’t the best practice in terms of performance optimization. This is just to simplify the process for the purpose of this post.

5. Create your files with the following code:

   unload ('select * from greentaxi where pickup between ''2019-01-01 00:00:00'' and ''2019-01-31 23:59:59''')
   to 's3://rs-lakehouse-blog-post/archive/green_tripdata_2019-01'
   iam_role 'arn:aws:iam::123456789012:role/BlogSpectrumRole' parquet parallel off;

The output of the UNLOAD commands is a single file (per month) in Parquet format, which takes 80% less space than the previous unload. This is important to save costs related to both Amazon S3 and Glacier, but also for costs associated to Redshift Spectrum queries, which is billed by amount of data scanned.

6. You can check how efficient Parquet is compared to text format:

   aws s3 ls s3://rs-lakehouse-blog-post/archive/

   2020-08-12 17:17:42   14523090 green_tripdata_2019-01000.parquet 

You get in stdout a single JSON with merge of 15IAtoGlacier and 12S3toS3IA.

Defining the external schema and external tables

Before deleting the records you extracted from Amazon Redshift with the UNLOAD command, we define the external schema and external tables to enable Redshift Spectrum queries for these Parquet files.

1. Enter the following code to create your schema:

   create external schema taxispectrum
   	from data catalog
   	database 'blogdb'
   	iam_role 'arn:aws:iam::123456789012:role/BlogSpectrumRole'
   create external database if not exists;

2. Create the external table taxi_archive in the taxispectrum external schema. If you’re walking through the new customer use case, replace the prefix extract_midterm with archive:

   create external table taxispectrum.taxi_archive(
   	vendor_id integer,
   	pickup timestamp,
   	dropoff timestamp,
   	storeandfwd char(2),
   	ratecodeid integer,
   	pulocid integer,
   	dolocid integer,
   	passenger_count integer,
   	trip_dist real,
   	fare_amount real,
   	extra real,
   	mta_tax real,
   	tip_amount real,
   	toll_amount real,
   	ehail_fee real,
   	improve_surch real,
   	total_amount real,
   	pay_type integer,
   	trip_type integer,
   	congest_surch real)
   	partitioned by (yearmonth char(7))
   	stored as parquet
   location 's3://rs-lakehouse-blog-post/extract_midterm/'

3. Add the six files stored in Amazon S3 and three files stored in S3-IA as partitions (if you’re walking through the new customer use case, you can skip the following partitioning steps). The following code shows March and April:

   ALTER TABLE taxispectrum.taxi_archive
   ADD PARTITION (yearmonth='2019-03') 
   LOCATION 's3://rs-lakehouse-blog-post/extract_midterm/03/';
   ALTER TABLE taxispectrum.taxi_archive
   ADD PARTITION (yearmonth='2019-04') 
   LOCATION 's3://rs-lakehouse-blog-post/extract_midterm/04/';

4. Continue this process up to December 2019, using extract_shortterm instead of extract_midterm.
5. Check the table isn’t empty with the following SQL statement:

   Select count (*) from taxispectrum.taxi_archive 

You get the number of entries in this external table.

7. Optionally, you can check the partitions mapped to this table with a query to the Amazon Redshift internal table:

   select * from svv_external_partitions

   Run a SELECT command using partitioning in order to optimize costs related to Redshift Spectrum scanning:

   select * from taxispectrum.taxi_archive where yearmonth='2019-11' and fare_amount > 20

8. Redshift Spectrum scans only specific partitions matching yearmonth.

The final step is cleaning all the records extracted from the Amazon Redshift local tables:

delete from public.greentaxi where pickup between '2019-01-01 00:00:00' and '2019-11-30 23:59:59'

About the Authors

Cristian Gavazzeni is a senior solution architect at Amazon Web Services. He has more than 20 years of experience as a pre-sales consultant focusing on Data Management, Infrastructure and Security. During his spare time he likes eating Japanese food and travelling abroad with only fly and drive bookings.

Francesco Marelli is a senior solutions architect at Amazon Web Services. He has lived and worked in London for 10 years, after that he has worked in Italy, Switzerland and other countries in EMEA. He is specialized in the design and implementation of Analytics, Data Management and Big Data systems, mainly for Enterprise and FSI customers. Francesco also has a strong experience in systems integration and design and implementation of web applications. He loves sharing his professional knowledge, collecting vinyl records and playing bass.