Redshift Cross Region Replication: 4 Easy Steps

Most cloud services house robust support for seamless & real-time replication of data, a functionality that most organisations seek to achieve. Amazon Web Services (AWS) is one such cloud service by Amazon that provides users and businesses with robust end-to-end cloud-based solutions & APIs. One of the most popular services that Amazon Web Services provides is the Redshift data warehouse. It allows users to access, store, retrieve and replicate their data on demand & seamlessly across a diverse set of regions.

This article focuses on Amazon Redshift Cross Region Replication & aims at providing you with a comprehensive step-by-step guide to help you set up replication in Amazon Redshift, and seamlessly replicate your data across tables present in a diverse set of regions. Upon a complete walkthrough of the content, you will have in-depth knowledge of data replication in Amazon Redshift, and you will be able to set up Cross Region Replication in Amazon Redshift with ease.

Table of Contents

• Introduction to Amazon Redshift
• Steps to Set up Amazon Redshift Cross Region Replication
  • Step 1: Extracting Data from Amazon Redshift
  • Step 2: Creating an Amazon Redshift Table in the Target Region
  • Step 3: Loading Data to Target Amazon Redshift Table
  • Step 4: Replicating Data from Multiple Tables (Optional)
• Conclusion

Introduction to Amazon Redshift

Amazon Redshift is a fully-managed petabyte-scale cloud-based data warehouse, designed to store large scale data sets and perform insightful analysis on them in real-time.

It is highly column-oriented & designed to connect with SQL-based clients and business intelligence tools, making data available in real-time. Supporting PostgreSQL 8, Amazon Redshift delivers exceptional performance and efficient querying. Each Amazon Redshift data warehouse contains a collection of computing resources (nodes) organized in a cluster, each having an engine of its own and a database.

For further information on Amazon Redshift, you can check the official website here.

Prerequistes

• Working knowledge of Amazon Redshift.
• An Amazon Redshift account.
• An Amazon Redshift table.
• A general idea about data replication.

Steps to Set up Amazon Redshift Cross Region Replication

Amazon Redshift allows users to replicate their data across numerous regions by extracting data from their tables using the unload command and then loading the data in the target tables via Amazon S3.

You can implement this using the following steps:

• Step 1: Extracting Data from Amazon Redshift
• Step 2: Creating an Amazon Redshift Table in the Target Region
• Step 3: Loading Data to Target Amazon Redshift Table
• Step 4: Replicating Data from Multiple Tables (Optional)

Step 1: Extracting Data from Amazon Redshift

To start replicating your Amazon Redshift data, you first need to extract the data from your Amazon Redshift table in the form of files and load it to intermediate storage such as Amazon S3 buckets. To do this, you can use the unload command to extract data, either in a “parallel off mode”(One File per Table) or “parallel on mode”(One File per Slice).

Parallel Off Mode

In case you want to extract data in the parallel off mode, you can use the unload command to load data from your Amazon Redshift table into the Amazon S3 bucket as follows:

unload ('select * from merchant_details') to 's3://staging-area/extract/redshift_east_region_extract/merchant_details_' credentials 'aws_access_key_id=***********;aws_secret_access_key=***********' parallel off;

A new file “merchant_details_000” will now appear in your Amazon S3 bucket.

Parallel On Mode

In case you’re working with tables that house a large volume of complex data, you should use the unload command in the “parallel on mode” and then create a manifest file, containing information about the data extract files.

To do this, you can use the unload command as follows:

unload ('select * from merchant_details') to 's3://staging-area/extract/redshift_east_region_extract/merchant_details_' credentials 

'aws_access_key_id=***********;aws_secret_access_key=***********;' manifest;

Amazon Redshift will now create multiple files containing your data as follows:

Each extracted file uses “|” as the delimiter by default. In case you want to modify or change the delimiter, you can use the delimiter option and the escape option (Special Characters).

This is how you can extract data from Amazon Redshift, to start setting up Amazon Redshift Cross Region Replication.

Step 2: Creating an Amazon Redshift Table in the Target Region

Once you’ve extracted the data from your Amazon Redshift table into Amazon S3 buckets, you need to create an Amazon Redshift table in the target region. To do this, you can make use of the table view provided by the AWS team, available in the GitHub repository and then execute a DDL command to create the Amazon Redshift table. You can use the following line of code to select the DDL:

select ddl from v_generate_tbl_ddl where tablename='merchant_details’' and schemaname = ‘finance’ order by seq;

This is how you can create the target table for the Amazon Redshift Cross Region Replication process.

Step 3: Loading Data to Target Amazon Redshift Table

With your target Amazon Redshift table now ready, you can now leverage the Amazon Redshift copy command to load data into the table.

In case you want to load a single file, you can use the following line of code:

copy merchant_details from 's3://staging-area/extract/redshift_east_region_extract/merchant_details_000' credentials 'aaws_access_key_id=**********;aws_secret_access_key=********** region 'us-east-1';

In case you want to load multiple files, you can use the following line of code:

copy merchant_details from 's3://staging-area/extract/redshift_east_region_extract/merchant_details_manifest' credentials 'aaws_access_key_id=**********;aws_secret_access_key=**********; manifest region 'us-east-1';

Ensure that you mention the manifest file instead of the source file, otherwise, Amazon Redshift will start looking for data in the same region. In case you want to learn more about the Amazon Redshift copy command, you can check the official AWS documentation here.

This is how you can set up Amazon Redshift Cross Region Replication via Amazon S3. In case you want to replicate data from multiple tables, you can check the next step.

Step 4: Replicating Data from Multiple Tables (Optional)

To replicate your data from numerous Amazon Redshift tables to any other region, you can modify the unload command as follows:

select 'unload ('''|| table_name ||') to ''s3://staging-area/extract/redshift_east_region_extract/'||table_name||'_'' credentials ''aws_access_key_id=**********;aws_secret_access_key=**********; manifest; region 'us-east-1'' 
from (select distinct table_name from information_schema.columns where table_schema = 'finance') ORDER BY seq;

Once you’ve extracted the data from your Amazon Redshift tables, you now need to fetch the DDL from the table view, available in GitHub to create the target Amazon Redshift tables in the different region. To do this, you can use the following line of code:

select ddl from v_generate_tbl_ddl where schemaname = 'finance' order by seq;

With your Amazon Redshift table now ready, you can use the following lines of code to import the data from your extracted files to your target Amazon Redshift table:

select 'copy '|| table_name ||' from ''s3://staging-area/extract/redshift_east_region_extract/'|| table_name||'_manifest'' credentials ''aws_access_key_id=**********;aws_secret_access_key=**********; manifest region ''us-east-1'';' 
from (select distinct table_name from information_schema.columns where table_schema = 'finance') ORDER BY seq;

This is how you can set up Amazon Redshift Cross Region Replication to replicate data from multiple tables.

Conclusion

This article teaches you how to set up Amazon Redshift Cross Region Replication with ease, and answers all your queries regarding it. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. These methods, however, can be challenging especially for a beginner & this is where Hevo saves the day. Hevo Data, a No-code Data Pipeline, can help you replicate data in real-time without writing any code. Hevo being a fully-managed system provides a highly secure automated solution to help perform replication in just a few clicks using its interactive UI.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for you!

Why don’t you share your experience of setting up Amazon Redshift Cross Region Replication in the comments? We would love to hear from you!