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

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

---

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.

   

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. In the Security Groups page, click the Inbound rules tab, and then click Edit inbound rules.

   

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

---

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

---

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:

   

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

   • 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 change this setting later.

5. Click TEST & CONTINUE to proceed for setting up the Destination.

---

Object Settings

Object settings must be configured if the Ingestion 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.

   

   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

• Connecting Through Reverse SSH Tunnel

---

Revision History