Integrate data from Actian Ingres to Databricks using Matillion

Creating a User in Actian Ingres Database

This guide outlines the steps required to create a new user identity for Actian Ingres. Ingres relies on operating system (OS) user accounts for database authentication; database users are not managed within Ingres itself, but instead at the OS level. Permissions within the database may then be managed for these users. For most deployments, follow the instructions below.

1. Create the Operating System User

Before a user can access an Ingres database, you need to create a corresponding OS user account on the database server.

On Linux/Unix

sudo useradd -m newuser
sudo passwd newuser

newuser

On Windows

Create a user using the Control Panel or the following command in Command Prompt (as an administrator):

cmd
net user newuser strong_password /add

newuser

strong_password

2. Grant Database Permissions

After creating the OS user, assign specific database permissions by logging into Ingres as a DBA (such as

ingres

sql dbname

dbname

Example SQL: Grant Privileges

``` -- Grant CONNECT privilege to the new user GRANT CONNECT TO newuser;

-- Optionally, grant further privileges such as table-level rights: GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO newuser; ```

Note: Replace

tablename

3. Verifying Access

Log out of your session and access Ingres with the new user credentials:

sql dbname -u newuser

Notes

• By default, Ingres uses OS authentication. If your deployment uses database authentication (

  DBMS_AUTHENTICATION

  ), consult your system documentation for alternative methods.
• Additional privilege management, group assignments, and advanced authentication options can be implemented; refer to the official Actian Ingres documentation.

Installing the JDBC driver

The Actian Ingres JDBC driver is not bundled by default with Matillion Data Productivity Cloud, due to licensing or redistribution restrictions. To work with Ingres databases in Matillion, you must acquire and install the driver manually. This guide will walk you through the process.

1. Downloading the JDBC Driver

Go to the Actian homepage for Ingres:
https://www.actian.com/products/actian-ingres/

• On the product page, locate and download the Type 4 JDBC driver for Ingres. The Type 4 driver is a pure Java driver, which offers improved portability and doesn’t require native library dependencies, making it the preferred choice for deployment in cloud environments like Matillion.
• You will typically receive a

  .jar

  file for the JDBC driver.

2. Uploading the Driver to Matillion Data Productivity Cloud

Since Matillion does not provide the Actian Ingres JDBC driver out of the box, you must upload the downloaded

.jar

• Refer to Matillion's official instructions here:
  Uploading External Drivers
• Follow the steps described in the documentation for uploading external JDBC drivers. Pay close attention to driver file compatibility, versioning, naming conventions, and where to place the

  .jar

  file relative to your agent deployment.

3. Configuring and Using the JDBC Driver

After uploading the driver, you can begin using it within Matillion Data Productivity Cloud to create database connections and perform queries.

• For step-by-step usage guidance, consult:
  Using JDBC Drivers in Database Query Components
• The documentation provides detailed information on how to reference the newly installed driver and configure connection parameters for your Ingres database within Matillion's user interface.

By following these steps and referencing the provided documentation links, you can integrate Ingres database access into your Matillion Data Productivity Cloud environment.

Checking network connectivity

To establish a connection between Matillion Data Productivity Cloud and your Actian Ingres database, you must ensure that your database allows incoming network traffic according to your deployment configuration:

• Full SaaS Agent Configuration:
  Allow incoming connections from the outbound IP addresses used by Matillion's SaaS platform. The required list of IP addresses is maintained and updated at: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS Deployment:
  Your Actian Ingres database needs to accept connections from the address range of your own virtual private cloud (VPC). To assist with checking network accessibility from your VPC, helpful utilities are available at: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

If your Actian Ingres database instance is referenced using a DNS hostname rather than a static IP, ensure that your Full SaaS or Hybrid SaaS agent has appropriate DNS resolution capability for that address. This is required for the agent to correctly locate and connect to your database.

How to Query Data from an Actian Ingres Database

This guide explains how to run SQL queries against an Actian Ingres database, with considerations for datatype conversions to platforms like Databricks, and best practices for initial and incremental data loads using ETL tools such as Matillion.

Example: SQL SELECT Queries for Actian Ingres

Below are some examples of basic and advanced SQL queries you can run on an Actian Ingres database:

Simple SELECT

SELECT first_name, last_name, email
FROM customer;

SELECT with WHERE Clause

SELECT order_id, order_date, total_amount
FROM orders
WHERE total_amount > 500.00;

Aggregation and Grouping

SELECT product_id, COUNT(*) AS total_sales
FROM sales
WHERE sale_date >= date('2024-01-01')
GROUP BY product_id;

Join Query

SELECT c.customer_id, c.first_name, o.order_id, o.order_date
FROM customer c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > date('2024-01-01');

Datatype Conversion Concerns (Actian Ingres to Databricks)

When moving data from Actian Ingres to Databricks, automatic or manual datatype conversions may need to take place. Some typical patterns and concerns include:

• Date/Time Types:
• Ingres

  date

  ,

  ansidate

  , or

  timestamp

  types may need mapping to Databricks’

  DATE

  or

  TIMESTAMP

  .
• Character Types:
• Ingres

  char

  or

  varchar

  become Databricks

  STRING

  .
• Numeric Types:
• Ingres

  integer

  ,

  float

  ,

  decimal

  types generally map well but be mindful of range and precision differences.
• Boolean Types:
• Depending on schema, Ingres may use integer (

  0/1

  ) or logical (

  TRUE/FALSE

  ) for booleans.

Test your extraction and transformation logic thoroughly to verify that data fidelity is maintained across platforms.

Initial and Incremental Data Loading Pattern

Recommended approach:
Use a Database Query component (such as that in Matillion) for both initial and incremental loads.

Initial Load

• Behavior: Load all available data (full table scan).
• Query Pattern: No

  WHERE

  filter is used—retrieves the entire dataset.

SELECT * FROM orders;

Incremental Load

• Behavior: Load only new or updated records since the last extraction.
• Query Pattern: Add a filter clause to select data based on a high-water mark (e.g.,

  last_updated

  timestamp or incrementing primary key).

SELECT * FROM orders
WHERE last_updated > '2024-06-01 00:00:00';

Or, if using a numeric key:

SELECT * FROM orders
WHERE order_id > 12345;

You can parameterize the value (

'2024-06-01 00:00:00'

12345

For further details, see: Incremental Load (Data Replication Strategy) – Matillion Exchange

Summary of Best Practice

• Same SQL query template (Database Query component) for both load types.
• No filter clause for initial/full load.
• Parameterize filter clause for incremental loads using reliable, monotonically increasing columns (timestamps or integer IDs).
• Ensure datatype compatibility before loading data to the destination (such as Databricks).

Data Integration Architecture

Loading data into your Databricks database before integrating it is an effective way to "divide and conquer" the challenges of data integration, by splitting the process into two manageable steps. This approach, characteristic of the ELT (Extract, Load, Transform) architecture, offers a major advantage: data is first ingested into the target system, and only then are the necessary transformations and integrations performed. Data integration typically requires transforming source data so it conforms with the target schema and business logic, and this is best accomplished using robust data transformation pipelines. Another significant benefit of the ELT architecture is that both transformation and integration tasks are executed inside the target Databricks database itself. This ensures that processing is fast, on-demand, and highly scalable, while also removing the need to invest in or manage external data processing infrastructure.