Amazon Redshift

Last updated on May 30, 2023

Prerequisites

An active AWS account is available.
The Amazon Redshift instance is running.
Hevo’s IP addresses are whitelisted.
SELECT privileges are granted to the database user.
Database hostname and port number of the Source instance are available.
You are assigned the Team Administrator, Team Collaborator, or Pipeline Administrator role in Hevo to create the Pipeline.

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 rules for your database, and verify Hevo IPs are whitelisted:

1. Add Inbound Rules

Log in to the Amazon Redshift dashboard.
In the left navigation pane, click Clusters.
Click the Cluster that you want to connect to Hevo.
In the Properties tab, Network and security settings, click the link text under the VPC security group to open the Security Groups page.
In the Security Groups page, click the Inbound rules tab, and then click Edit inbound rules.
In 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 address for your region. Repeat this step to whitelist all the IP addresses.
5. Click Save.

2. (Optional) Verify Hevo IPs are Whitelisted

Log in to the Amazon Redshift dashboard.
In the left navigation pane, click Clusters.
Click the Cluster that you want to connect to Hevo.
In the Properties tab, Network and security settings, click the link text under the VPC ID link.
In the Your VPCs page, Details tab, click the link text under Main network ACL.
In the Network ACLs page, click the Inbound rules tab and ensure that the IP addresses you added appear in the range of IPs that are set to Allow. Else, click Edit inbound rules to include these IPs in the allowed range.

Create a Database User and Grant Privileges

(Optional) Create a database user.
1. Log in to your Amazon Redshift database as a superuser or a user with CREATE privilege.
2. Enter the following command:
```
CREATE USER hevo WITH PASSWORD '<password>';
```

Grant SELECT privilege for all or specific tables to the user.

Enter the following commands:

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO hevo; #all tables
GRANT SELECT ON TABLE <schema_name>.<table_name> TO hevo; #specific table

Optionally, view the list of tables available in a schema:

SELECT distinct(<table_name>) FROM pg_table_def WHERE <schema_name> = 'pg_catalog';

(Optional) Retrieve the Hostname and Port Number

Log in to the Amazon Redshift dashboard.
In the left navigation pane, click Clusters.
Click the Cluster that you want to connect to Hevo.
Under Cluster Database Properties, locate the JDBC URL and the Port. The default Amazon Redshift port is 5439.

Use this JDBC URL (minus the jdbc:redshift:// part) as the database host and the Port as the database port in Hevo while creating your Pipeline.

For example, in the JDBC URL jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev,
the database host is examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com.

Configure Amazon Redshift Connection Settings

Perform the following steps to configure Amazon Redshift as a Source in Hevo:

Click PIPELINES in the Navigation Bar.
Click + CREATE in the Pipelines List View.
In the Select Source Type page, select Amazon Redshift.
In the Configure your Amazon Redshift Source page, specify the following:
- Pipeline Name: A unique name for your Pipeline, not exceeding 255 characters.
- Database Cluster Identifier: Amazon Redshift host’s IP address or DNS name.
  
  Note: For URL-based hostnames, exclude the initial jdbc:redshift:// part. For example, 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 is listening for connections. Default value: 5439.
- Database User: The authenticated user who has the permissions to read tables in your database.
- Database Password: The password for the database user.
- Select an Ingestion Mode: The desired mode by which you want to ingest data from the Source. The available Ingestion Modes are Table, and Custom SQL. Read Ingestion Modes.
  
  For ingestion mode as Table, read Object Settings to configure the objects to be replicated.
  
  Note: For Custom SQL ingestion mode, all Events loaded to the Destination are billable.
- Database Name: The database that you wish to replicate.
- Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel, instead of directly connecting your Amazon Redshift database host to Hevo. This provides an additional level of security to your database by not exposing your Amazon Redshift setup to the public. Read Connecting Through SSH.
  
  If this option is disabled, you must whitelist Hevo’s IP addresses.
- Advanced Settings:
  - Include New Tables in the Pipeline: Applicable for all Ingestion modes except Custom SQL.
    
    If enabled, Hevo automatically ingests data from tables created in the Source after the Pipeline has been set up. These may include completely new tables or previously deleted tables that have been re-created in the Source.
    
    If disabled, new and re-created tables are not ingested automatically. They are added in SKIPPED state in the objects list, in the Pipeline Overview page. You can update their status to INCLUDED to ingest data. You can include these objects post-Pipeline creation to ingest data.
    
    You can change this setting later.
Click TEST CONNECTION. This button is enabled once you specify all the mandatory fields. Hevo’s underlying connectivity checker validates the connection settings you provide.
Click TEST & CONTINUE to proceed for setting up the Destination. This button is enabled once you specify all the mandatory fields.

Additional Information

Read the detailed Hevo documentation for the following related topics:

Object Settings

Object settings must be configured if the Ingestion mode is Table.

To do this:

Once the Source settings are specified in step 4, select the objects to be replicated in the SELECT OBJECTS YOU WANT TO REPLICATE page, and then, click CONTINUE.

Note: Each object represents a table in your database.
In the CONFIGURE SOURCE OBJECTS page, specify the query mode to be used for each selected object.

Limitations

None.

Revision History

Refer to the following table for the list of key updates made to this page:

Date	Release	Description of Change
Apr-21-2023	NA	Updated section, Configure Amazon Redshift Connection Settings to add a note to inform users that all loaded Events are billable for Custom SQL mode-based Pipelines.
Dec-19-2022	2.04	Updated section, Configure Amazon Redshift Connection Settings to add information that you must specify all fields to create a Pipeline.
Dec-07-2022	2.03	Updated section, Configure Amazon Redshift Connection Settings to mention about including skipped objects post-Pipeline creation.
Dec-07-2022	2.03	Updated section, Configure Amazon Redshift Connection Settings to mention about the connectivity checker.
Apr-21-2022	1.86	Updated section, Configure Amazon Redshift Connection Settings.
Jan-24-2021	NA	Updated section, Whitelist Hevo’s IP Addresses as per the latest Amazon Redshift interface.
Jan-03-2022	1.79	Updated the description of the Include New Tables in the Pipeline advance setting in the Configure Amazon Redshift Connection Settings section.
Jul-26-2021	1.68	Added a note for the Database Cluster Identifier field.
Jul-12-2021	1.67	Added the field Include New Tables in the Pipeline under Source configuration settings.
Feb-22-2021	1.57	Revised the document to include the end-to-end procedure for configuring Amazon Redshift as a Source.