Share
Amazon Redshift Setup Guide

Amazon Redshift is a fully managed, reliable data warehouse service in the cloud that offers large-scale storage and analysis of data sets, and can perform large-scale database migrations as well. It is a part of the larger cloud-computing platform, Amazon Web Services (AWS).

In Hevo Activate, you can configure Amazon Redshift as a Warehouse while creating an Activation, to synchronize data with your Target CRM application.

Prerequisites

Whitelist Hevo’s IP Addresses

You need to whitelist the Hevo IP address for your region to enable Hevo to connect to your Amazon Redshift database.

To do this, you need to add the inbound and outbound rules for your database, and configure a virtual private cloud (VPC):

1. Add Inbound and Outbound Rules

  1. Log in to the Amazon Redshift dashboard.

  2. In the left navigation pane, click Clusters.

  3. Click the Cluster that you want to connect to Hevo.

    Redshift Database Clusters

  4. In the Properties tab, Network and security settings, click the link text under the VPC security group to open the Security Groups page.

    Redshift - VPC security group

  5. On the Security Groups page, click the Inbound rules tab, and then, click Edit inbound rules.

    Create Inbound Rules

  6. On the Edit inbound rules page:

    Edit Inbound Rules

    1. Click Add rule.

    2. In the Type column, select Redshift from the drop-down.

    3. In the Port Range column, enter the port of your Amazon Redshift cluster. Default value: 5439.

    4. In the Source column, select Custom from the drop-down and enter Hevo’s IP addresses for your region.

    5. Click Save rules.

  7. On the Security Groups page, click the Outbound rules tab, and then, click Edit outbound rules.

    Create Outbound Rules

  8. Repeat Step 6 on the Outbound rules page to configure outbound rules.

2. Configure a Virtual Private Cloud (VPC)

  1. Log in to the Amazon Redshift dashboard.

  2. In the left navigation pane, click Clusters.

  3. Click the Cluster that you want to connect to Hevo.

    Redshift Database Clusters

  4. In the Properties tab, Network and security settings, click on the link text under Virtual private cloud (VPC) to open the VPC.

    Redshift - VPC ID

  5. On the Your VPCs page, in the Details panel, click on the link text under Main network ACL.

    Main Network ACL

  6. On the Network ACLs page, click the Inbound rules tab and ensure that the IP addresses you added are set to Allow. Else, click Edit inbound rules to change the setting.

    Amazon Redshift VPC - Inbound Rules

  7. On the Network ACLs page, click the Outbound rules tab and repeat Step 6 for the outbound rules.

Note: If you have enabled enhanced VPC routing while creating your Amazon Redshift cluster, ensure that your VPC is configured correctly. Refer to section, Warehouse Considerations.

Create a User and Grant Privileges

1. Create a user (optional)

  1. Log in to your Amazon Redshift database as a superuser or a user with CREATE privilege.

  2. Enter the following command:

      CREATE USER <USER_NAME> WITH PASSWORD '<password>';

  # Example
  CREATE USER hevo WITH PASSWORD '<password>';

2. Grant privileges to the user

  1. Log in to your Amazon Redshift database as a superuser.

  2. Grant the required privileges as follows:

    1. Grant CREATE and USAGE privileges to the database user for an existing database:

      # Grant privileges to the database user that you created earlier

GRANT CREATE ON DATABASE <database_name> TO <USER_NAME>;

# Grant privileges to the database user on the <span class='glossary-term' data-toggle='glossary-tooltip' data-class='glossary-tooltip' data-for='schema'>schema</span>.

GRANT CREATE ON SCHEMA <SCHEMA_NAME> TO <USER_NAME>;
GRANT USAGE ON SCHEMA <SCHEMA_NAME> TO <USER_NAME>;

    2. Grant SELECT privilege to the database user on specific tables:

      GRANT SELECT ON TABLE <schema_name>.<table_name> TO <USER_NAME>; # specific table

Retrieve the Hostname and Port Number (Optional)

  1. Log in to the Amazon Redshift dashboard.

  2. In the left navigation pane, click Clusters.

  3. Click the Cluster that you want to connect to Hevo.

    Redshift Database Clusters

  4. On the Clusters page, locate the JDBC URL and the Port.

    Default value: 5439.

    Use this JDBC URL as the database host and the Port as the database port in Hevo while configuring your Warehouse for an Activation.

    Database Hostname and Port

Configure Amazon Redshift as a Warehouse

Perform the following steps to configure Amazon Redshift as an Activate Warehouse:

  1. Do one of the following:

    • Click ACTIVATE in the Navigation Bar, and:

      1. In the Activations tab, click + CREATE ACTIVATION.

        Create Activation

      2. On the Select Warehouse page, click + ADD WAREHOUSE.

        Add a new Warehouse

      3. On the Select Warehouse Type page, select Amazon Redshift.

    • Click DESTINATIONS in the Navigation Bar, and:

      1. Click + CREATE DESTINATION in the Destinations List View.

      2. On the Add Destination page, select Amazon Redshift as the Destination type.

  2. On the Configure your Amazon Redshift Warehouse page, specify the following:

    Configure the Redshift Warehouse

    • Warehouse Name: A unique name for your Warehouse.

    • Database Hostname: The Amazon Redshift host’s IP address or DNS name that you retrieved in Step 3 above.

      Note: If the hostname URL is jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev, enter examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com.

    • Database Port: The port on which your Amazon Redshift server listens for connections. This can be the port number that you retrieved in Step 3 or the one that you specified while configuring the database. Default value: 5439.

    • Database User: The database user that you created. A user with a non-administrative role in the Redshift database.

    • Database Password: The password of the database user.

    • Database Name: The name of the database from which data is to be synchronized.

    • Database Schema: The name of the schema on which the database user has SELECT and USAGE privileges. Default value: public.

    • Additional Settings:

      • Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel. Read Connecting Through SSH. Else, whitelist Hevo’s IP addresses for your region.

        Note: While whitelisting Hevo’s IP addresses, you must also make the Amazon instance publicly accessible.

      • Sanitize Table/Column Names?: Enable this option to remove all non-alphanumeric characters and spaces in a table or column name, and replace them with an underscore (_). Read Name Sanitization.

  3. Click TEST CONNECTION to test connectivity with the Amazon Redshift Warehouse.

  4. Once the test is successful, click SAVE & CONTINUE.

Warehouse Considerations

  • Hevo copies the data identified for synchronization from your Amazon Redshift data warehouse to an S3 bucket using the UNLOAD command. Hence, if you have enabled enhanced VPC routing, ensure that your VPC is configured correctly. Enhanced VPC routing affects the way your Amazon Redshift cluster accesses other resources in your AWS network, such as the S3 bucket, specifically for the COPY and UNLOAD commands. Read Enhanced VPC Routing in Amazon Redshift.


See Also

Last updated on Sep 03, 2024

Tell us what went wrong

Skip to the section