Introduction

Data import and export from data repositories is a standard data administration process. From developers to administrators, almost everyone has a need to extract the data from database management systems. In the AWS Data Lake concept, AWS S3 is the data storage layer and Redshift is the compute layer that can join, process and aggregate large volumes of data. To serve the data hosted in Redshift, there can often need to export the data out of it and host it in other repositories that are suited to the nature of consumption. AWS S3 is one of those central storage repositories in AWS that is integrated with almost all the data and analytics services supported by AWS. By that virtue, one of the fundamental needs of Redshift professionals is to export data from Redshift to AWS S3. In this article, we will learn step-by-step how to export data from Amazon Redshift to Amazon S3 and different options related to it.

AWS Redshift Setup

In this article, it’s assumed that a working AWS Redshift cluster is in place. Redshift beginners can refer to this article, Getting started with AWS Redshift, to create a new AWS Redshift cluster. Once the cluster is in place, it would look as shown below on the Redshift Clusters page.

As we need to export the data out of the AWS Redshift cluster, we need to have some sample data in place. It’s assumed to you have at least some sample data in place. If not, in one of my previous articles, I explained how to load data in Redshift, which can be referred to create some sample data. Once the cluster is ready with sample data, connect to the cluster. I have a users table in the Redshift cluster which looks as shown below. Connect to the Redshift cluster using IDE of choice.

Let’s say that we intend to export this data into an AWS S3 bucket. The primary method natively supports by AWS Redshift is the “Unload” command to export data. The syntax of the Unload command is as shown below. This command provides many options to format the exported data as well as specifying the schema of the data being exported. We will look at some of the frequently used options in this article.

#aws #aws rds