Oracle is a multi-model database management system commonly used for running online transaction processing, data warehousing and mixed database workloads.
The K3 Oracle source adaptor supports pulling data from an Oracle database. The following instructions will walk you through how to set up the Oracle source adaptor.
1. Select Datebase: Oracle as the source adaptor
After selecting Database: Oracle, and the desired target adaptor, the user will be taken to the route configuration page automatically.
2. Click Database Credentials
Enter the connection information and click Test Credentials. Once a checkmark appears next to Test Credentials, click OK. Clicking OK will close the credentials pop up window, bringing the user back to the configuration page.
| Configuration Parameters | Description |
|---|---|
| Subname | Enter the database server and database using the following format: @//<DB_SERVER>:<DB_PORT>/<DB_SID> |
| User | Enter a user that has permissions to read from the Oracle database which will be connected. |
| Password | Enter a password for the user provided in the User field. |
3. Click Build Query
From the configuration page, click Build Query. A pop up window will appear with a premade SQL query started for the user. At this point the user may follow one of the options below:
Option 1: The user can keep the premade query and select a table from the drop down menu. This will pull all data from the chosen table without any filtering.
Option 2: A custom query can be written in place of the premade query to pull from any available table with whatever filtering is desired.
Once the query is written, click Execute Query. A sample of the data received will be displayed in the right hand window
4. Select the delta poll parameter
On the right-hand side of the query pop-up box, K3 will highlight columns it recommends using for delta polling. K3 will only recommend columns that are DateTime columns or incrementing integer columns. K3 will not allow users to select a column that is not highlighted. If the user wants to use a non-highlighted column as the delta poll parameter, the user must manually enter the delta poll parameter.
To use one of the recommended columns for delta polling, add a WHERE clause with the column's name followed by a greater than sign, then click the column name from the right-hand plane.
Example: WHERE snapshot_timestamp > ${rs.col('snapshot_timestamp').max()|$date.toDate('iso_tz','1970-01-01T00:00:00Z')}.
In this example, the delta poll parameter will be snapshot_timestamp. Each new poll of the database will only ingest data with a snapshot timestamp that occurred after the latest snapshot timestamp K3 previously ingested.
Once the delta poll parameter has been chosen, click OK.
5. Adjust the poll schedule
Following the query, the poll schedule needs to be determined. K3 currently supports periodic schedule and Cron scheduling, which is the default.
| Configuration Field | Value |
|---|---|
| Schedule Cron (only available if Cron Scheduling is selected) | Enter the Cron schedule script to determine when the poll will run. |
| Poll Delay (only available if Periodic Scheduling is selected) | Enter the Poll Delay in milliseconds to determine when the poll will run. |
6. Name the Route and click Create
Once the database connection is established, the SQL query is approved, and the poll schedule is set, name the route by click Route Name located at the top left of the configuration page, then click Create to create the route.