Working with Oracle RDS connection

Applies to: Kyvos Enterprise Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

Kyvos supports Oracle RDS as a source data connection for reading data from the Oracle RDS data warehouse.

Note

If using Oracle RDS, and upgrading from Kyvos 2022.3 to a later version, ensure that you remove the 'ojdbc8.jar' from the kyvos.connection.classpath.common.jar property. The property is displayed on the Connection screen when you click the Properties link and search for the kyvos.connection.classpath.common.jar property.

Prerequisites

Oracle RDS Server and Kyvos running in the same VPC

Once the Oracle RDS is created, navigate to the security group attached to Oracle RDS and add an inbound rule on port 1521 to connect RDS to kyvos instances.

Refer to the below image for adding an inbound rule.

Oracle RDS Server and Kyvos running in different VPC

Refer to the Amazon documentation to learn how to create a peering connection and accept the connection between the two VPC networks.

Perform the following steps for route tables of both the subnets (subnet of RDS and subnet of BI server of non-RDS Deployment)

Go to the subnet, and click on Route tables.
Click Edit routes.
Add route.
Provide CIDR range of other VPC in destination and peering connection in the target.
Save route tables.
After the entry in route tables, open the security group of Oracle DB RDS.
Add an inbound rule with Custom TCP on port 45421 for the IP of the BI server (BI server of the secondary deployment in which we will manually configure Oracle DB RDS).

Refer to the below screenshot

For every BI node, you need to add an inbound rule.

Oracle RDS Version and Configuration

Creating Oracle RDS connection

To set up an Oracle RDS connection, perform the following steps.

From the Toolbox, click Setup, then Connections.
From the Actions menu ( ⋮) click Add Connection.
On the Connections page, enter details as:


Parameter/Field	Comments/Description
Name	Provide a name for the connection.
Category	Select the Warehouse option. There may be more than one warehouse connection.
Provider	For Provider, select Oracle.
Driver Class	Provide the class as oracle.jdbc.OracleDriver
URL	Enter the connection URL for the RDS database in jdbc:oracle:thin:@localhost:port:orcl format. Where jdbc is the API, Oracle is the database, thin is the driver, localhost is the server name on which oracle is running. For example, jdbc:oracle:thin:@kyv-oracle.ctdjrop1241x.us-east-1.rds.amazonaws.com:6875:orcl

Click the Test button from the top left to validate the connection settings.
If the connection is valid, click the Save button.
Go to the newly created connection and click Connection Properties.
Click the Save You can now use the RDS connection to explore data.