How to Sync Data from Amazon Aurora to Redshift

Tables of Contents:

1. What is Amazon Aurora?
2. What is Amazon Redshift?
3. Why replicate your data from Aurora to Redshift?
4. How can you sync data from Aurora to Redshift?
5. How to sync data from Aurora to Redshift using Integrate.io

What is the best way to sync data from Amazon Aurora to Redshift? In this guide, we’ll break down what these two databases are, why should you be using them, and how to sync your data from Amazon Aurora to Redshift quickly and easily and keep it replicating in near real-time.

What is Amazon Aurora?

Amazon Aurora is a high-performance relational database engine offered by Amazon Web Services (AWS). Since Aurora was explicitly “built for the cloud”, AWS claims it performs 5x faster than a standard mySQL database and 3x faster than a standard PostgreSQL database, but at 1/10th the cost.

What is Amazon Redshift?

Amazon Redshift is a SQL-based, highly scalable data warehouse with an MPP architecture and columnar storage. It is excellent for running analytical queries over large datasets and is the data warehouse of choice for many BI tools, such as Looker, Chartio, and Tableau.

Why replicate your data from Aurora to Redshift?

Aurora is a row-based database, which makes it best for transactional queries or web applications. Want to look up a user’s name based on their id? Easy to do with Aurora. Want to count or average all the widgets of every user? That’s where Redshift excels. For this reason, if you want to analyze your data using any of the popular Business Intelligence tools on the market today, you’re going to need use a data warehouse like Redshift.

How can you sync data from Aurora to Redshift?

Setting up a system to move your data from Aurora to Redshift from scratch is no easy feat. Thankfully, that’s where Integrate.io comes in. Integrate.io monitors your Aurora database’s binary logs to perform Change Data Capture (CDC) in real time. In essence, anytimes a change is made ro your Aurora database, that change is logged to your binary logs. Integrate.io nearly continuously reads that log, translates those changes into what needs to be done on the Redshift database, and executes the change. By using CDC, you can avoid time-consuming procedures like dumping your entire database and loading it into Redshift every night. That way, there is no lag for your data analysis. You can perform analysis on all of your data, whenever you want. If you are using Amazon Aurora right now, and want to run analytics on your data with Amazon Redshift, you can sign up for a no-risk free trial to see if Integrate.io meets your needs.

How to sync data from Aurora to Redshift using Integrate.io

Let’s see how easy it is to replicate from Amazon Aurora to your Redshift. With the assumption that you have already launched an Aurora cluster and Redshift instance, the first thing to do is to create DB parameter groups. Unlike RDS, Aurora has broken the DB parameter groups up into two types: (1) DB parameter groups (instance level); and, (2) Cluster DB parameter groups. We recommend creating 2 new custom parameter groups and modifying the parameters like below. Here we show the two different parameter groups (the default and the custom one that was created):

In the Cluster DB parameter groups, update these parameters:

In the DB parameter group, update these parameters:

Once the above are completed, all you have to do is follow the FlyData web console wizard to complete the setup.