Amazon Redshift

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

You can ingest data from your Amazon Redshift database using Hevo Pipelines and replicate it to a Destination of your choice.


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

1. Add Inbound 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.

    Click Cluster

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

    Click Security group ID

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

    Click Edit

  6. In 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 address for your region. Repeat this step to whitelist all the IP addresses.

    5. Click Save.

2. (Optional) Verify Hevo IPs are Whitelisted

  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.

    Click Cluster

  4. In the Properties tab, Network and security settings, click the link text under the VPC ID link.

    Click VPC ID

  5. In the Your VPCs page, Details tab, click the link text under Main network ACL.

    Main Network ACL

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

    Edit Inbound Rules


Create a Database User and Grant Privileges

  1. (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>';
      
  2. Grant SELECT privilege for all or specific tables to the user.

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

    2. 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
      
  3. 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

  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.

    Click Cluster

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

    Locate hostname


Configure Amazon Redshift Connection Settings

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

  1. Click PIPELINES in the Asset Palette.

  2. Click + CREATE in the Pipelines List View.

  3. In the Select Source Type page, select Amazon Redshift.

  4. In the Configure your Amazon Redshift Source page, specify the following:

    Redshift settings

    • Pipeline Name: A unique name for your Pipeline.

    • 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 that can read the tables in your database.

    • Database Password: Password for the database user.

    • Database Name: The database that you wish to replicate.

    • Connect through SSH: - 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. Refer to the content for your Amazon Redshift variant for steps to do this.

    • Advanced Settings:

      • Include New Tables in the Pipeline: Applicable for all Pipeline modes except Custom SQL.

        If enabled, Hevo automatically ingests data from tables created in the Source after the Pipeline has been built. 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 change this setting later.


Object Settings

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

To do this:

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

    Select objects

    Note: Each object represents a table in your database.

  2. In the CONFIGURE SOURCE OBJECTS page, specify the query mode to be used for each selected object.


Limitations

None.


See Also


Revision History

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

Date Release Description of Change
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.
Last updated on 25 Jan 2022