Amazon QuickSight Redshift Integration: Easy Steps

As data collection within organizations is growing at a rapid rate, organizations are struggling to harness the power of data effectively. Usually, organizations hire analysts to analyze the stored data and then share the insights and dashboards with decision-makers. However, this decreases productivity since decision-makers rely on analysts to make decisions.

To mitigate such challenges, organizations are leveraging QuickSight Redshift in combination with Amazon Redshift to allow everyone in the organization to generate insight and make decisions quickly. While Redshift eliminates the need for decision-makers to clean data, QuickSight Redshift enables users to query in natural language to obtain insights into a colossal amount of data.  

In this tutorial, you will learn to use the Amazon Redshift service with Amazon QuickSight Redshift.

Prerequisites 

Basics understanding of business intelligence tools

Authorize Connections from Amazon QuickSight to Amazon Redshift Clusters

You need to create a security group instance for connecting Amazon QuickSight Redshift. When you connect Amazon Redshift with QuickSight, you are given access to all tables and instances of Amazon Redshift.

There are three ways to establish QuickSight Redshift connect:

1. Manually Enabling Access to Amazon Redshift Cluster in a VPC

Follow the below steps to manually enable a Redshift Cluster in a Virtual Private Cloud (VPC) for authorizing QuickSight Redshift connect. 

Note: You need to have a Redshift Cluster before enabling access to it in VPC.

Step 1.1: Sign in to the AWS Management Console, then open the Amazon Redshift console.

Step 1.2: Select the details page icon next to the Cluster, as shown below.

Step 1.3:Under the Cluster Database Properties section, select the port and note down its value.

Step 1.4: Similarly, under the Cluster Properties section, select the VPC Id and note down its value.

Step 1.5: To open the Amazon VPC Management Console, select the view VPCs as shown in the below image.

Step 1.6: Select the Create Security Group.

Step 1.7: Enter the security group information asked on the Create Security Group page as follows:

• For name tag and group name, enter Amazon-QuickSight-access.
• For Description, enter Amazon-QuickSight-access.
• For VPC, you will select the VPC of your instance. The VPC in the below image is the VPC you noted above.

Step 1.8: Click on the Yes, Create tab. You will see the new security group on the screen.

Step 1.9: Select the new security group, and then the Inbound Rules tab.

Step 1.10: To create a new rule, click on Edit and use the below values.

• For Type, select the Custom TCP rule.
• For Protocol, enter TCP(6).
• For port range, you will have to enter the port number of the Amazon Redshift Cluster to which you are providing access.

For Source, you have to enter the CIDR address block of the AWS region where you want to use Amazon QuickSight. In this example, the CIDR is 52.210.255.224/27 (Ireland).

Step 1.11: To save your new inbound rule, click on the Save button.

Step 1.12: Go to the Clusters page of the Amazon Redshift Management Console and open the details page for the Cluster you want to provide the required access.

Step 1.13: Click on Cluster from a drop-down menu, and then select modify as shown in the below image.

Step 1.14: The currently assigned security groups are selected for VPC Security Groups. You can press CTRL and select Amazon-QuickSight-access in addition to the other selected security groups.

2. Manually Enabling Access to Amazon Redshift Cluster, Which is Not in VPC

Follow the below steps to access the Amazon Redshift Cluster, which is not in VPC.

1. Sign in to the AWS Management Console and open the Amazon Redshift console.
2. In the navigation pane, click on the Security tab.
3. Now, click on the Create Cluster Security group.
4. For the Security Cluster group name, enter Amazon-QuickSight-access and then click on Create.
5. Now, click on the details icon next to the Security group, as shown in the below image.

6. Then, click on Add Connection Type.

7. Enter the below connection information.

• For Connection type, enter CIDR/IP.
• For CIDR/IP, enter the appropriate CIDR address block as shown in the below image. You can see AWS Regions, websites, IP address ranges, and endpoints to check the supported IP address ranges for Amazon QuickSight regions.

8. Click on Authorize.

9. Go to the Clusters page of the Amazon Redshift Management Console and open the details page for the Cluster you want to enable access. Select Cluster and then click on modify.

10. The currently assigned security groups are selected for VPC Security Groups. You can press CTRL and select Amazon-QuickSight-access in addition to the other selected security groups.

11. Click on modify to provide access to Redshift Clusters from QuickSight Redshift.

3. Enabling Access to Amazon Redshift Spectrum

If you want to gain insights into unstructured data from QuickSight, you can leverage Amazon Redshift Spectrum. One of the most widely used services for unstructured data on AWS is S3. You can query data from S3 using Redshift spectrum instead of relying on Amazon Athena, a query service used for analyzing data. You can combine the datasets of Amazon Redshift and Amazon S3 and then access them using queries.

To connect with Redshift Spectrum, follow the below steps.

1. Create an IAM role associated with the Amazon Redshift Cluster.

2. Add the IAM policies like AmazonS3ReadOnlyAccess and AmazonAthenaFullAccess to the IAM role.

3. Register the external schema for the tables you want to use.

In the Redshift spectrum, you only pay for the queries you run. You do not need to grant Amazon QuickSight access to Amazon S3 or Athena for connecting Redshift Spectrum tables. Amazon QuickSight needs access only through the Amazon Redshift Clusters.

Conclusion

In this tutorial, you learned about connecting Amazon Redshift data warehousing with the Amazon QuickSight data visualization tool. However, you can also explore the Amazon QuickSight tool with other Amazon storage services like Amazon S3, Amazon EBS, Amazon Storage Gateway, and more.

See how to connect QuickBooks to Redshift for optimized data analysis. Explore our guide for straightforward instructions on setting up the integration.

Amazon Redshift is a great platform for storing data on which you intend to perform Data Analytics and Visualization. However, at times, you need to transfer this data from multiple sources to your Redshift account for analysis. Building an in-house solution for this process could be an expensive and time-consuming task. Hevo Data, on the other hand, offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. 

This platform allows you to transfer data from 150+ sources like Amazon QuickSight to Amazon Redshift and other Data Warehouses like Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. 

Share your views on Amazon QuickSight Redshift Integration in the comments section!

FAQs to connect Quicksight to Redshift