Connect AWS Redshift Data Warehouse to Google Data Studio

Introduction

Ever wondered if it’s possible to visualize your Redshift data in Google Data Studio? A quick google search will land you to a good amount of paid services which will integrate the two for you. But what if I tell you that you can easily integrate your Redshift Data Warehouse by using a built in connector offered by datastudio, that too, for absolutely free?

Too good to be true? Follow this guide to know how you can connect your AWS Redshift data with Google Data Studio and start reporting:

AWS Redshift Configuration

First thing you should do is to make sure datastudio has permissions to access your redshift cluster. For this you need to make your redshift cluster, publicly accessible. The publicly accessible setting should be set to Yes within the Network and Security section of your cluster properties. The right configuration will look something like this:

**Remember to take into account the security requirement of your data as a publicly accessible cluster means your database might be exposed to security threats.

Next step to make the cluster accessible is to allow the datastudio IPs to go past the AWS firewall and access the database. To do this you need to go back to the Network and security block in the cluster properties and click on the VPC security group.

This will take you to AWS’s EC2 Management Console where you will see your security group listed. Click on the Security Group ID to see the details. On the inbound rules tab in the details page, click on edit inbound rules button which will look something like this:

This should take you to the inbound rules page where there will be an option to add inbound rules. These are the IPs that need to be added as inbound rules to make sure your cluster is accessible by Datastudio:

• 64.18.0.0/20
• 64.233.160.0/19
• 66.102.0.0/20
• 66.249.80.0/20
• 72.14.192.0/18
• 74.125.0.0/16
• 108.177.8.0/21
• 173.194.0.0/16
• 207.126.144.0/20
• 209.85.128.0/17
• 216.58.192.0/19
• 216.239.32.0/19

** You need to make sure that all of the 12 IPs listed above are added as inbound rules as they belong to datastudio servers and you are giving them permission to access the redshift cluster**

You can use the following configuration for each IP:

Type: All Traffic

Protocol: All (by default)

Port: All (by default)

Source: Custom

In the end, your list of inbound rules will look something like this:

We are done with the most important part of the connection and now it’s time to jump to datastudio! But hey, don’t forget to save your settings before leaving and jumping onto the next step.

Data Studio Configuration

Open your datastudio report and go to its data sources to add a new data source. Among the list of data source connectors you will find a PostgreSQL connector. This is the connector that is going to do the job for you.

In the connector configuration window you need to give details about your aws cluster and give authorization credentials to access the cluster.

The hostname and database name is something that you will find in your cluster properties. The images below will help you know where to find them:

**Remember to only take the hostname from the endpoint and the port should be added to the port field configuration.

The username and password are for the Master username and password of your database. Your IAM user must have complete access to the redshift cluster, otherwise the authentication will fail.

Once you have entered all the information, it will look something like this:

Click on authenticate and voila! you will see the tables within your cluster.

Now it’s finally time to add the data source to the report and enjoy creating some wonderful visualizations. Best of luck!