Overview
This blog will discuss how to use the Reltio Integration Hub (RIH) to create a simple and secure connection between Reltio and Redshift, which can then be used in RIH recipes that orchestrate the transfer of data between the two platforms.
Redshift Connection approaches in Reltio Integration Hub
There are two connection approaches which will be detailed below: Standard connection (which leverages a username and password over the public internet) and use of the On-Prem Agent. The standard connection can be used when your security teams authorize enabling certain public internet access to your Redshift cluster (whitelabeled IP addresses). The On-Prem Agent is available to teams who cannot enable any internet traffic directly to their Redshift cluster.
Creating your Redshift Cluster and Database
In order to have a connection between Reltio and Redshift, you will first need to create a Redshift cluster and database. (If you already have a Redshift database you can ignore this section). Then you will be able to connect to Redshift using the Workato connection in RIH.
Step 1: Log into the AWS Console
Log into the AWS Console and navigate to the “AWS Redshift” service
Step 2: Create a Cluster
Create a new cluster (upper right hand corner button). You can use all the default settings. See example settings below. You will create an Admin user in this step (record the username and password)
Step 3: Create the Redshift Database
In the query editor (from your Redshift cluster screen you will have the option to open a SQL Editor) you will execute multiple SQL queries which will create the Redshift Database, Schema, and Tables you want. There will automatically be a “Dev” database with a “Public” schema and associated tables. Depending on your cluster configuration, you may also have a “Sample Data” Database as well. Follow the AWS Documentation which provides example SQL queries in sequential order (You can copy and paste them and just rename the DB, Schema, Table).
Your Redshift cluster and Database are now ready for use.
Using a Standard Redshift Connection
This connection will use username and password to connect to Redshift over the public internet.
Step 1: Enable Public Access/Networking
Modify the security settings of your Redshift instance in AWS Console.
1. Click into your VPC security group
2. Click on “Edit Inbound Rules”
3. Add a rule with the below settings and hit “Save rules”
4. Ensure the following “Outbound Rule” is present
5. Go back to your cluster management screen and click Actions → Modify publicly accessible setting and set it to enable
* Note if you only want to enable public access for specific IP addresses you can do that in this setting. The IP Addresses required for RIH are listed here.
6. Your cluster is now configured for specified external traffic and can be accessed via the Redshift connection in RIH
Step 2: Select the “Redshift” Connection
Back in the Reltio Console - Integration Hub app you can choose “Redshift” as a new connection type to establish.
Step 3. Enter Required Information
You will leave the “is this app in a private network?” as “No gateway”. You can then enter the Schema that you created or use the default “Public” schema. Then hit “Connect”.
Using the On-Prem Agent Connection
The On-Prem Agent is needed when the database is in a private network, and username and password cannot be used in a connection. The On-Prem Agent is a software package installed from Workato that enables a secure connection between the RIH recipe and the Database without needing to enable public access to the database. The following steps detail how to install and configure the On-Prem Agent.
Step 1: Ensure Access to On-Prem Groups
Within RIH you will need the appropriate role to see the On-Prem Agent capability. Your Reltio administrator should add the role “ROLE_INTEGRATION_CUSTOMER_ADMIN” for your Reltio tenant.
Step 2: Create On-Prem Group and Agent
Follow the steps outlined in the Workato documentation to configure an On-Prem Group and Agent. You’ll need to download the package for the appropriate OS you plan to use either on your machine or your virtual machine.
1. Important: In the config.yml file in the downloaded package you’ll set up a “connection profile” which you will reference in your RIH connection.
When you are done with the configuration and installation you should have:
1. An On-Prem Group
2. An On-Prem Agent within the group
3. The package installed and configured on your machine
Example config.yml file:
The Workato docs provide examples of the terminal/command line scripts that you should use to run the agent on your machine before “enabling” it in the On-Prem Group
Once the agent is “active” you can setup your Redshift Connection
Step 3: Create the Redshift Connection
Back in the Reltio Console - Integration Hub app you can choose “Redshift” as a new connection type to establish.
1. Now you will select the appropriate On-Prem Group in the “Is this app in a private network?” field of the connection. Then in the “On-prem connection profile” field you should enter the connection profile name you used in the config.yml file (This needs to be the exact name you use in the config file!).
You are now ready to use this connection in a recipe!
#Reltiointegrationhub
#Blog