Amazon Redshift
On This Page
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.
-
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
-
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:
-
Click Add Rule.
-
In the Type column, select Redshift from the drop-down.
-
In the Port Range column, enter the port of your Amazon Redshift cluster. Default value: 5439.
-
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.
-
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.
-
Log in to your Amazon Redshift database as a
superuser
or a user withCREATE
privilege. -
Enter the following command:
CREATE USER hevo WITH PASSWORD '<password>';
-
-
Grant
SELECT
privilege for all or specific tables to the user.-
Log in to your Amazon Redshift database as a
superuser
. -
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 Asset Palette.
-
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.
-
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.
-
-
-
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:
-
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.
See Also
Revision History
Refer to the following table for the list of key updates made to this page:
Date | Release | Description of Change |
---|---|---|
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. |