Reltio Integration Hub

 View Only

How to Connect to Redshift in Reltio Integration Hub

By Michael Pearlman posted 10-27-2022 17:00

  

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

lsdp5YieR367P7WGUXyD_Image 10-27-22 at 4.13 PM.jpeg

 

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)

upCKE24vSByrlYF5IY6A_Image 10-27-22 at 4.15 PM.jpeg

 

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

ZENiZ0SxQFiQ4y6n9LLr_Image 10-27-22 at 4.17 PM.jpeg



2. Click on “Edit Inbound Rules”

unknown.jpg



3. Add a rule with the below settings and hit “Save rules”

unknown.png



4. Ensure the following “Outbound Rule” is present

unknown_1.png



5. Go back to your cluster management screen and click Actions → Modify publicly accessible setting and set it to enable

unknown_2.png

unknown_3.png

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

unknown_4.png



Step 3.
Enter Required Information

unknown_5.png

unknown_6.png


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.

unknown_7.png



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

unknown_8.png



2. An On-Prem Agent within the group

unknown_9.png



3. The package installed and configured on your machine

unknown_10.png



Example config.yml file:

JNRRxCfWtv94lrx7cQbJYksM7TrlDyMjSqszaZm8xXraY-_zsuwHeKD2OCsTDr4SGPT4j8UIU4HPtYAEkP5HTXH7TrTfpB2Tp8oflBPpKu51aEOykY4G35Fl0wmT.png

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.

t9CzoTS1aXivJd3fa1Pi3OnR4YEfo6t4JSDHgXjL9jK0ZLOdYwc2TbEIH9V8oPvEXHQ84VBFsDQFM-BDCYVo4O6-j05NJTRi0HV4ihuEEZT1IgMTbZcAKwuL6Gyq.png


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!

kpeaQH_huUN8V8kKlj8gWE8nTMEcyC-0wp6z_6nkNEbSn36NO9ISW_CtArpIfRigk2ZUCynlqtHhWcYUvhjV82Gj14ExqmMfu9106M5uf1MbZj3BCxrglNvEBSXH.png


#Reltiointegrationhub

0 comments
2545 views

Permalink