Redshift
  • 3 Minutes to read
  • Dark
    Light

Redshift

  • Dark
    Light

Article Summary

Amazon Redshift is a fully managed data warehousing service provided by Amazon Web Services (AWS). It's designed to handle large-scale data analytics and complex querying, offering high-performance columnar storage and parallel processing capabilities to efficiently process and analyze large datasets for business insights.

Prerequisites

Authorize Connection to Redshift

In AWS Portal

Enable Public Accessibility of Redshift Cluster

  1. Go to the AWS Console and navigate to Redshift Console.
  2. In the navigation panel, choose Clusters and the name of the cluster that you want to connect to.
  3. In the cluster's configuration tab, make sure that Publicly accessible option is set to Yes. If not, please modify the cluster to make it publicly accessible.

Configure Security Group

Whitelist Dataddo IP address in the inbound rules of the cluster's security group.

  1. Go to the AWS Console and navigate to Redshift Console.
  2. In the navigation pane, choose Clusters and the name of the cluster that you want to connect to.
  3. On the configuration tab for the cluster, find the VPC security groups section and select the attached security group.
  4. In the Inbound rules section, choose Edit inbound rules.
  5. For each Dataddo IP set a Custom TCP Rule. Use CIDR notation (e.g. 52.17.68.150/32). The default port value for Redshift is 5439.

In Redshift Shell

Create a Redshift user

  1. Log into Redshift shell.
  2. Run a command to create a new user.
CREATE USER dataddo PASSWORD '***';

Set permissions

  1. Log into Redshift shell.
  2. Grant the CREATE, SELECT, UPDATE, INSERT, DELETE permissiong for the schema you plan to use (the example below presume the public schema is used).
GRANT USAGE ON SCHEMA public TO dataddo;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON ALL TABLES IN SCHEMA public TO dataddo;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON TABLES TO dataddo;

In Dataddo

  1. In the Authorizers tab, click on Authorize New Service and select Redshift.
  2. You will be asked to fill the following fields
    1. Server IP or Hostname: Public IP or hostname of your Redshift cluster.
    2. Database: Name of the database you will use for writing or reading the data.
    3. Username: Redshift user's username.
    4. Password: Redshift user's password.
    5. Port: Port to connect to Redshift. The default value is 5439.
  3. [Optional] Configure the connection via SSH tunnel. To do so, please follow these steps.
  4. Cick on Save.

Create a New Redshift Destination

  1. On the Destinations page, click on the Create Destination button and select the destination from the list.
  2. Select your account from the drop-down menu.
  3. Name your destination and click on Save to create your destination.
Need to authorize another connection?

Click on Add new Account in drop-down menu during authorizer selection and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.

Create a Flow to Redshift

  1. Navigate to Flows and click on Create Flow.
  2. Click on Connect Your Data to add your sources.
  3. Click on Connect Your Data Destination to add the destination.
  4. Choose the write mode and fill in the other required information.
  5. Check the Data Preview to see if your configuration is correct.
  6. Name your flow, and click on Create Flow to finish the setup.

Table Naming Convention

When naming your table, please make sure the table name:

  • Contains only
    • Letters
    • Numbers
    • Underscores
  • Does NOT contain whitespaces.


Was this article helpful?

What's Next