How to connect Amazon Redshift to Oracle

Part 3 of 3 in the series How to Develop Analytics Leveraging Data in Multiple Cloud Platforms

This is part three of a three-part blog series describing how to develop analytics leveraging data in multiple cloud platforms. In part one, we covered the basis of multi-cloud analytics structure and how to use Oracle Analytics Cloud (OAC) to create reports and analytics with data housed in Amazon Redshift and Oracle ADW.

Part two highlighted specifics on establishing a connection from OAC to Amazon Redshift, and how to set up security settings to access that data in Amazon Redshift.

To better understand this post, catch up on parts one and two!
Apps Associates is a premier enterprise applications partner with more than twenty years of experience. Over the years, we have experienced customer implementation projects where it is necessary to connect Amazon Redshift to other Oracle tools for testing, loading data and creating reports directly from databases.
In this third and final post, we will discuss how to connect Amazon Redshift to other Oracle tools like SQL Developer, ODI Studio and Data Visualization.

How to connect Amazon Redshift to SQL Developer: 

Step 1: We need JDBC 4.2 without the AWS SDK Jar file. This can be downloaded from the AWS cluster console.

Step 2: Copy the respective cluster JDBC URL and ODBC URL.

Step 3: Open SQL Developer. Choose Tools -> Preferences->Database->Third Party JDBC Drivers->Add Entry and select the Jar file.

Step 4: Set up a new connection.

How to connect Amazon Redshift to ODI 12c:

Step 1: Download the Amazon Redshift JDBC Drivers from Amazon Web Services. Link

Step 2: Move the JDBC drivers to theuserlib directory in ODI.

Windows directory: …\AppData\Roaming\odi\oracledi\userlib

Step 3: Click on the new data server under Amazon Redshift in ODI Studio>Topology>Physical Architecture.

Step 4: Fill in JDBC details and User credentials accordingly and test the connection using ODI Agent or local agent.

How to connect Amazon Redshift to OAC Data Visualization Using Remote Data Gateway:

To connect any database to OAC Data Visualization, Oracle recommends creating a Data Gateway.

The latest release of OAC includes the new Remote Data Gateway (RDG) utility for accessing databases. Data Gateway replaces the Remote Data Connector utility that was used in previous releases. Even though Remote Data Connector can still be used, it’s deprecated starting with Oracle Analytics Cloud 105.3 and will be removed in future releases. Oracle recommends moving to Remote Data Gateway for the upcoming release of version 105.3 to avoid loss of functionality.

1. Login to OAC Console and Enable Data Gateway.

2. For Each Data Gateway use a Data Gateway Agent configuration to generate an authorization key for the machine.

3. Copy the configuration key paste it in the Public Key section and create the required configuration.

Creating a connection in OAC:

1. Click on Create Connection on the OAC Home Page.

2. Select the ConnectionType

3. In Create Connection provide the required details and Save to create connection successfully.

For more information on this topic and other technical how-tos, be sure to subscribe to the Apps Associates blog.

If you’re looking for a technology partner who can support you at every stage of the digital transformation journey, contact Apps Associates today.

About the Author

Vamsi Krishna N is working as Principal Consultant at Apps Associates. He has over 11 Years of experience and is highly innovative, creative, enthusiastic and dedicated professional with a diverse working experience in Analytics. Skilled in Oracle, Amazon Web Services and Azure Analytics. Worked on Wide range of Data Warehousing Tools like ODI, OBIEE, AWS Glue, Power BI and Tableau. Helped Customers in providing efficient Data Warehouse designs and Implemented the Solutions End to End on Multiple projects.