Integrate data from Exasol to Databricks using Matillion

Creating a User in Exasol Database

To create a new user (identity) in an Exasol database, you will use SQL statements executed via SQL clients such as EXAplus, DBeaver, SQL Editor in EXAoperation, or any similar tool connected to your Exasol instance. You must have sufficient privileges, typically as a database administrator or a user with

CREATE USER

Step 1: Connect to the Database

Ensure that you are connected to your Exasol database with a user account that has the necessary privileges to create other users.

Step 2: Create the User

You can create a new user with the

CREATE USER

CREATE USER my_new_user IDENTIFIED BY 'Str0ngInitPass!';

• Replace

  my_new_user

  with the desired username.
• Change

  'Str0ngInitPass!'

  to a secure initial password for your requirements.

Step 3: Grant Privileges to the User

By default, the new user will have no privileges. You must explicitly grant them the required privileges, such as access to specific schemas, the ability to create sessions, or administrative roles.

For example, to grant the ability to create a session (i.e., to log in):

GRANT CREATE SESSION TO my_new_user;

To grant privileges on a specific schema (e.g., to allow SELECT, INSERT, UPDATE and DELETE on all tables in the schema MY_SCHEMA):

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA MY_SCHEMA TO my_new_user;

Granting an existing role (e.g.,

SYS

GRANT SYS TO my_new_user;

Step 4: (Optional) Force Password Change on First Login

It is a best practice to require the user to change their password upon first use:

ALTER USER my_new_user PASSWORD EXPIRE;

Step 5: (Optional) Set Additional User Properties

You can further configure user properties, such as resource limits or password policies, using the

ALTER USER

Note: All SQL keywords in Exasol are case-insensitive, but object names are case-sensitive if quoted. Passwords should always be managed securely.

Installing the JDBC Driver

To enable connectivity between Matillion Data Productivity Cloud and your Exasol database, you must manually install the Exasol JDBC driver. Please note that, at the time of writing, this driver is not distributed by default with Matillion Data Productivity Cloud due to third-party licensing and redistribution restrictions imposed by Exasol.

Follow the steps below to obtain and install the driver:

1. Download the Exasol JDBC Driver

• Access the Exasol JDBC driver downloads at: https://www.exasol.com/portal/display/DOWNLOADS/JDBC+Drivers
• On the downloads page, search for the Type 4 JDBC driver for your intended version of the Exasol database. The Type 4 driver is a pure Java driver and does not require native libraries, making it the preferred option for Matillion Data Productivity Cloud integrations.
• Download the latest stable release of the Type 4 JDBC driver JAR file to your local work environment.

2. Install the JDBC Driver in Matillion Data Productivity Cloud

• Installation steps for uploading external JDBC drivers are detailed here: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/
• In summary:
• Log in to Matillion Data Productivity Cloud as a user with the necessary permissions.
• Navigate to the section for uploading external drivers (see the URL above for step-by-step instructions).
• Upload the previously downloaded JDBC driver JAR file, specifying Exasol as the target destination as required.

3. Next Steps: Using the JDBC Driver

• Once installed, you can configure Matillion Data Productivity Cloud to connect to Exasol and issue database queries.
• Refer to the product's usage documentation for setting up connections and working with the integrated JDBC driver: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

By following these steps, you will have enabled support for Exasol JDBC connectivity within your Matillion Data Productivity Cloud environment using the officially referenced and up-to-date third-party artefact.

Checking network connectivity

To enable successful connections between Matillion Data Productivity Cloud and your Exasol database, you must ensure that your Exasol instance is configured to allow inbound network traffic from the appropriate source, depending on your deployment architecture:

• Full SaaS agent configuration: Configure your Exasol database to accept incoming connections from the IP addresses used by the Matillion Data Productivity Cloud SaaS agents. The current list of required IP addresses can be found at the following URL: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS deployment: Configure your Exasol database networking or firewall rules to accept incoming connections from your organization's virtual private cloud (VPC). For guidance and utilities to help test connectivity and network access from your hybrid environment, see: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

Additionally, if the Exasol database is referenced by a DNS name (rather than a direct IP address), ensure that whichever agent you are using (Full SaaS or Hybrid SaaS) can resolve the database's DNS address to the correct underlying IP address. Proper DNS resolution is required for successful connectivity.

Querying Data from an Exasol Database

This guide outlines how to query data from an Exasol database and provides guidance for handling data loads, including patterns for initial and incremental data replication. It also highlights considerations for datatype conversion between Exasol and Databricks.

Examples of Exasol SQL SELECT Statements

Below are examples of common SELECT queries in Exasol SQL syntax:

``` -- Select all columns from the "sales" table SELECT * FROM sales;

-- Select specific columns SELECT order_id, customer_id, total_amount FROM sales;

-- Aggregate data: Count orders per customer SELECT customer_id, COUNT(order_id) AS order_count FROM sales GROUP BY customer_id;

-- Join two tables SELECT c.name, s.total_amount FROM customers c JOIN sales s ON c.customer_id = s.customer_id WHERE s.order_date >= '2024-01-01';

-- Use a WHERE clause for filtering SELECT * FROM sales WHERE total_amount > 1000; ```

Datatype Conversion: Exasol ↔ Databricks

When exchanging data between Exasol and Databricks (for example, using ETL tools), be aware that some datatypes do not have a direct correspondence and conversions may occur. For instance:

Tip: Always validate the data schema and test your pipeline to ensure compatibility between source and destination.

Initial Load vs. Incremental Load Pattern

A robust and scalable pattern for data ingestion is to:

1. Perform a once-off initial load of the complete data set.
2. Follow up with ongoing incremental loads to retrieve only data that has changed (new or updated records).

Use the Database Query component (e.g., in Matillion ETL) for both initial and incremental data loads.

Initial Load

• Approach: Retrieve all required data from the source table.
• Query Example: (No filter in the WHERE clause.)

SELECT *
FROM sales;

• Component Configuration: Do not set a filter clause in the

  Database Query

  component.

Incremental Load

• Approach: Retrieve only new or changed data since the last successful load, using a filter column such as an updated timestamp or incrementing ID.
• Query Example: (Includes a filter condition.)

SELECT *
FROM sales
WHERE last_modified > '2024-06-10 00:00:00';

• Component Configuration: Specify a filter clause in the

  Database Query

  component to select only the delta data. Update the filter value after every successful load.

Further Reading:
Refine your incremental load strategy by reviewing Incremental Load Data Replication Strategy.

Note: Each ETL tool may have different best practices and settings for Exasol and Databricks integration; always consult your platform’s official documentation.

Data Integration Architecture

Loading data in advance of integration represents a "divide and conquer" approach to data management by splitting the process into two distinct steps, an important advantage of the ELT (Extract, Load, Transform) architecture. In this model, raw data is first loaded into the target Databricks database, and only then is the necessary integration and transformation applied. Data integration invariably requires transformation—such as aligning schemas, cleansing, and standardizing records—which is most effectively performed using dedicated data transformation pipelines. Another significant benefit of the ELT approach is that both transformation and integration run natively inside the Databricks platform. This means they can leverage Databricks’ fast, scalable, and on-demand processing capabilities, avoiding the need for separate, costly data processing infrastructure.