Amazon Redshift

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

• An active AWS account.

• An active Amazon Redshift instance.

• Hevo’s IP addresses are whitelisted.

• SELECT and USAGE privileges are granted to the database user.

• Database hostname and port number of the Amazon Redshift instance are available.

---

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.

   

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

   

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

   

6. On the Edit inbound rules page:

   

   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.

   

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.

   

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

   

5. On the Your VPCs page, in the Details panel, click on the link text under 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.

   

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.

   

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.

   

---

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.

        

     2. On the Select Warehouse page, click + ADD 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:

   

   • 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.

---

Revision History