How Do I Set Up Amazon Redshift and connect with SQL Workbench/J

Posted On: 2023-03-26 16:03:24 AWS

Getting started with Redshift

  • you need an AWS Account
  • Open firewall port (Port No:5439)
  • Permission to access other AWS resources
    • Providing the AWS access key to an IAM user that has the necessary permissions
    • By creating a dedicated IAM role that is attached to the Redshift cluster (recommended)

You can create an IAM role by following these instructions from AWS.

Launching a Redshift cluster

After completing the prerequisites, you’re ready to launch a Redshift cluster.

  • Step 1: While logged in the user with the necessary permissions to perform cluster operations, open the Amazon Redshift console.
  • Step 2: Select the region in which you want to create the cluster.
  • Step 3: Choose Quick Launch Cluster and enter the following values. These are default values for those wanting to explore Redshift while incurring minimal charges. If you already have specific values in mind for your use case, replace these values with those.
    • Node type: dc2.large.
    • Number of compute nodes: 2.
    • Cluster identifier: examplecluster.
    • Master user name: awsuser.
    • Master user password and Confirm password: Enter a password for the master user account.
    • Database port: 5439.
    • Available IAM roles: Choose myRedshiftRole.
  • Step 4: Click Launch Cluster and wait a few minutes for the launch to finish. When done, click Close to return to the list of clusters. The cluster you just launched should be listed there. Check that Cluster Status says available, and Database Health says healthy.
  • Step 5: Choose the cluster you just launched. Click the Cluster button just above the list then click on Modify cluster. In the dialog box that appears, choose the VPC security groups you want to associate with this cluster then click Modify to save the association.

Authorizing access to the Redshift cluster

After following the steps, the Redshift cluster is now launched. To connect to the cluster, you need to configure a security group to authorize access. If the cluster is launched in the EC2-VPC platform, follow these instructions from AWS.

Connecting to the cluster and running queries

Now that you have launched a cluster, you may connect to it and start running queries. Running queries can be done in two ways:

  1. Connect to your cluster from the AWS Management Console using the AWS Query Editor.
  2. Connect to your cluster through a SQL client tool like SQL Workbench/J.

At this point, you can now use your Redshift cluster. You can create tables in the database, upload data to the tables, and try running queries. These activities can be done through the AWS Query Editor or through a SQL client tool of your choice.

How to Monitor Amazon Redshift

Now you know how Amazon Redshift works and why it’s fast and efficient. Still, the best way to know for sure is to see its performance for yourself by monitoring performance. In the next blog posts in this series, we will take a deep dive into how to analyze Redshift queries and how to monitor Amazon Redshift performance with Sumo Logic. Stay tuned.