Integrate data from Oracle Database to Amazon Redshift using Matillion

Creating an Identity in Oracle Database

Creating a user in Oracle Database involves several steps, including connecting as a privileged user, executing a

CREATE USER

Prerequisites

• Oracle Database instance accessible
• DBA privileges (e.g., access via a user such as

  sys

  or

  system

  , or another user with

  CREATE USER

  privilege)

1. Connect as a Privileged User

Connect to the database as a user with sufficient privileges, usually

sysdba

bash
sqlplus sys@ORCL as sysdba

Or for a local connection:

bash
sqlplus / as sysdba

2. Create the User

Replace

<username>

<password>

USERS

TEMP

CREATE USER <username>
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  QUOTA UNLIMITED ON USERS;

Example:

CREATE USER app_user
  IDENTIFIED BY StrongPwd#2024
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  QUOTA UNLIMITED ON USERS;

3. Grant Permissions

Typically, new users require at least

CREATE SESSION

DBA

``` -- Basic connect privilege GRANT CREATE SESSION TO ;

-- (Optional) Privileges to create objects: GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO ; ```

Example:

GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE, CREATE VIEW TO app_user;

4. (Optional) Grant Roles

For full administrative access (not usually recommended for application users):

GRANT DBA TO <username>;

5. Verify the User

Connect using the new user credentials:

```bash sqlplus app_user@ORCL

or

sqlplus app_user/StrongPwd#2024@ORCL ```

Notes

• Always use strong, complex passwords for database accounts.
• Minimize granted privileges—apply the principle of least privilege.
• If connecting remotely, ensure the listener and network configuration permit access.

Checking the JDBC driver

The JDBC driver for Oracle Database is already installed in Matillion Data Productivity Cloud, so there is no need to download or install it separately. To confirm the presence of the JDBC driver, follow these steps:

1. Create a new Orchestration pipeline in your Matillion Data Productivity Cloud project.
2. Add a "Database Query" component to your pipeline.
3. In the settings for the "Database Query" component, locate the Database Type dropdown menu.
4. Look for Oracle Database in the list of available database types. If it appears in the dropdown, the JDBC driver is installed and ready to use.

If you need to verify which version of the JDBC driver is currently in use, please refer to the detailed instructions provided here: Database Driver Versions.

Checking network connectivity

To enable successful connectivity between Matillion Data Productivity Cloud and your Oracle Database, you must ensure that the database is configured to allow incoming connections according to the type of Matillion deployment you are using:

• Full SaaS Agent:
  If you are using a Full SaaS agent configuration, the Oracle Database must allow incoming connections from the IP addresses listed at:
  Matillion Data Productivity Cloud IP Allowlist Documentation

• Hybrid SaaS Deployment:
  For Hybrid SaaS deployments, incoming connections from your own virtual private cloud (VPC) must be permitted. To assist in checking network access from your VPC, you can use utilities provided by Matillion at:
  Matillion Network Access Checker

Additionally, if your Oracle Database is referenced via a DNS hostname (rather than a direct IP address), ensure that the Full SaaS or Hybrid SaaS agent is able to resolve the hostname to the correct IP address, as DNS resolution is required for connectivity.

Querying Data from an Oracle Database

This guide provides technical instructions for querying data from an Oracle Database using SQL

SELECT

Oracle Database SQL Query Examples

To query data in Oracle Database, use the standard

SELECT

``` -- Select all columns from an "employees" table SELECT * FROM employees;

-- Select specific columns with a filter SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE department_id = 10;

-- Aggregate function usage example SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;

-- Join two tables SELECT e.employee_id, e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; ```

Datatype Conversion: Oracle Database vs Redshift

When loading data from Oracle Database into Amazon Redshift, different database systems' datatypes may require explicit conversion. Examples of common Oracle-to-Redshift datatype mappings include:

NUMBER(p,s)

DECIMAL(p,s)

VARCHAR2(n)

VARCHAR(n)

DATE

DATE

TIMESTAMP

TIMESTAMP

CLOB

VARCHAR(65535)

BLOB

Careful review of your schema and use of CAST/CONVERT functions in SQL or ETL tools may be necessary to assure compatibility.

Data Load Patterns: Initial and Incremental Loads

The best practice for replicating data from Oracle Database is to use a two-phase data load strategy with the same

Database Query

Initial Load

On the first run, your

Database Query

SELECT * FROM employees;

Incremental Loads

For future loads, filter on a suitable column (typically a timestamp or auto-incrementing key):

SELECT * FROM employees
WHERE hire_date > TO_DATE(:last_extracted, 'YYYY-MM-DD HH24:MI:SS');

:last_extracted

For a comprehensive strategy discussion, refer to Matillion Incremental Load Data Replication Strategy.

Note: Ensure the chosen filter column (e.g.,

hire_date

last_updated

Database Query

Data Integration Architecture

Loading data in advance of integration is a core strategy for dividing and conquering the data integration challenge, by breaking the process into two distinctive steps: loading and then transforming the data. This modular approach forms a key advantage of the ELT (Extract, Load, Transform) architecture, as it allows raw data to be quickly ingested before any complex integration logic is applied. Effective data integration inherently requires robust data transformations, and the most reliable method for achieving this is through the use of structured data transformation pipelines. These pipelines ensure consistency, traceability, and efficiency in processing large datasets. Notably, another significant benefit of the ELT architecture is that all data transformation and integration tasks are performed inside the target Redshift database itself. As a result, transformations are fast, occur on-demand as needed, and scale seamlessly as data volumes grow, all while eliminating the need to invest in, maintain, or manage separate data processing infrastructure.