Amazon Redshift as a Target Connection

Introduction

This document describes the process of integrating Amazon Redshift as a target within Rivery. By integrating Amazon Redshift with Rivery, users can easily extract their data from various sources, transform it as needed, and load it into Amazon Redshift for further analysis or operational use.

Prerequisites

Before using this guide, ensure the following prerequisites are met:

AWS Superuser Account:

Make sure you have signed up for AWS as a Superuser.

AWS Permissions:

Create a Redshift Cluster

1. If you don't already have a Redshift cluste, you can create one by following these instructions.
2. Once your cluster is created, you need to authorize access to the cluster.

Create Your User and Schema within the Redshift Database

In the Cluster creation wizard in AWS, you must define the database where Rivery will upload data. Follow these steps:

1. Connect to your Redshift cluster using the credentials set during cluster creation, using your preferred SQL management tool (e.g., Aginity, SQL Workbench/J or DBeaver).
   Make sure the Redshift cluster is externally accessible. Obtain the host address from the Redshift cluster details.
   
2. Create the user and schema by running the following SQL commands (replace "Rivery User" and "schema name" with your desired names):

       CREATE USER <Rivery User> with password '<myPwd>' CREATEUSER;
       CREATE SCHEMA IF NOT EXISTS <schema name> authorization <Rivery User>;
   

3. Grant the necessary permissions to the user by running the following commands:

   GRANT SELECT on ALL TABLES IN SCHEMA <schema name> TO <Rivery User>;
   GRANT EXECUTE on ALL FUNCTIONS IN SCHEMA <schema name> TO <Rivery User>;
   GRANT USAGE on SCHEMA <schema name> TO <Rivery_User>
   

Replace "Rivery User" with the user previously created and "schema name" with the schema you grant Rivery permission to utilize. Repeat this process for every schema you wish for Rivery to access and utilize data.

Please Note:
Rivery conducts a review of the Access Control Lists (ACLs) to ensure that these permissions are replicated across all tables. The current Grant permissions found within the raw ACLs are structured as shown below:

rivery=arwdRxtDPA/rivery,group test_group=arwdxtD/rivery,user1_name=wx/rivery,user1_name=x/rivery

In this context, the string "arwdRxtDPA" denotes the permissions granted to the Rivery user. Each character within this string corresponds to a specific action, with 'a' representing INSERT, 'r' representing SELECT, 'w' representing UPDATE, and 'd' representing DELETE.
Rivery interprets this string, generates grant statements, and subsequently executes them individually on the respective tables.

Whitelist Our IPs

To whitelist Rivery's IPs, follow these steps:

1. Log in to your AWS account and search for the "Amazon Redshift" service in the AWS Console.
   
2. Access the "clusters" section in the left sidebar.
   
3. Select the cluster you've created.
4. Navigate to the "properties" tab and look for "VPC security group" under "Network and security settings." Click on the Security Group ID.
5. In the "Inbound rules" tab, click "Edit inbound rules."
   
6. Add a rule of type "Redshift" and specify the required source IP addresses as outlined in the documentation.
7. Save the rules to whitelist the specified IPs.
   

Rivery's Redshift Connection

You can connect to Redshift using one of two methods:

• Login Credentials
• SSH Tunnel

Login Credentials

1. Whitelist our IPs.
2. Choose your Connection Name.
3. Enter Host.
4. Fill in the Port Number.
5. Enter your Username and Password
6. Use the Test Connection function to see if your connection is up to the task.
   If the connection succeeded, you can now use this connection in Rivery.

SSH Tunnel (Optional)

To connect via SSH Tunnel, follow our step-by-step tutorial.

Please Note:
When using an SSH tunnel, the SSH port should be left empty or set to the default port of 22.

Custom File Zone (Optional)

Rivery allows you to create a custom FileZone to manage your data in your own S3 service. Your data will be stored in your S3 bucket.

To use a custom File Zone, follow the guidelines listed below:

1. Toggle the Custom File Zone to true.

2. By clicking on FileZone Connection, you can select the previously configured Amazon S3 Connection.

3. Choose a Default Bucket from the drop-down list.

4. Use the Test Connection function to see if your connection is up to the task.
   If the connection was successful, click Save.