Integrate data from Exasol to Snowflake using Matillion

Creating a User in Exasol

To create a new user in an Exasol database, you require sufficient administrative privileges (such as having the

CREATE USER

1. Basic User Creation

The fundamental statement for user creation is the

CREATE USER

CREATE USER username IDENTIFIED BY 'strong_password';

• username: The login name for the new user.
• strong_password: A secure password for the user.

Example:

CREATE USER dev_user IDENTIFIED BY 'S3cureP@ssw0rd!';

2. Granting Privileges

After creating the user, you typically grant necessary system and/or object privileges. For example, to allow a user to create sessions (log in) and work with a specific schema:

GRANT CREATE SESSION TO dev_user;
GRANT SELECT, INSERT, UPDATE ON schema_name.* TO dev_user;

Adjust the privileges to fit your use case.

3. Additional User Configuration (Optional)

For more controlled user management, you can:

• Set a password expiry date:

  CREATE USER temp_user IDENTIFIED BY 'T3mpPassw0rd!' PASSWORD EXPIRE;

• Restrict user by IP address:

  ALTER USER dev_user LIMIT CONNECT TO '192.168.10.100';

4. User Deletion (If Needed)

To remove an existing user:

DROP USER dev_user;

This command irrevocably deletes the user and cannot be undone.

Installing the JDBC Driver

To connect Matillion Data Productivity Cloud to an Exasol database, it is necessary to install the Exasol JDBC driver manually. As of the time of writing, Matillion does not bundle the Exasol JDBC driver by default. This is due to licensing or redistribution restrictions imposed by the driver’s publisher. The following instructions outline the process for downloading and installing the JDBC driver, as well as referencing further documentation for installation and usage within Matillion.

1. Download the Exasol JDBC Driver

1. Go to the official Exasol JDBC driver download page:
   https://www.exasol.com/portal/display/DOWNLOADS/JDBC+Drivers

2. On this page, look for a Type 4 JDBC driver (sometimes referred to as a "pure Java" driver). This is the most commonly supported and recommended type for use with data integration tools such as Matillion. Download the appropriate JAR file for your Matillion Agent environment.

2. Upload the Driver to Matillion

Install the JDBC driver into your Matillion Data Productivity Cloud deployment following Matillion’s procedure for adding external drivers. Detailed instructions are available in the Matillion documentation:

• Uploading External Drivers

You’ll need to log in to your Matillion environment and follow the steps to upload and make the JAR file available to relevant agents or integrations.

3. Connect to Exasol

After uploading the driver, you can now connect to your Exasol database from within Matillion. For comprehensive details on configuring your database queries and leveraging the new connection, consult the following guide:

• Database Query Documentation

By ensuring the Type 4 JDBC driver is available in your instance, you enable Matillion Data Productivity Cloud to establish secure and direct connections to your Exasol databases.

Checking network connectivity

To successfully connect Matillion Data Productivity Cloud to your Exasol database, you must ensure your database accepts incoming network connections from the appropriate sources, depending on your deployment configuration:

• Full SaaS agent configuration:
  The Exasol database must allow inbound connections from the static IP addresses used by Matillion’s cloud agents. You can find the up-to-date list of required IP addresses here: Matillion Security Documentation.

• Hybrid SaaS deployment:
  In this setup, the Exasol database should permit incoming connections from the IP ranges of your own virtual private cloud (VPC), where your private Hybrid SaaS agent is running. To test and validate network accessibility, utilities are available at: Matillion Network Access Checker.

Additionally, if you are referencing your Exasol database by a DNS hostname, make sure that the Full SaaS or Hybrid SaaS agent can resolve the DNS address. This typically requires appropriate DNS configuration and firewall rules allowing DNS lookup requests.

Querying Data from Exasol Database

This guide describes how to query data from an Exasol database, including recommended loading strategies and important considerations for datatype compatibility with Snowflake.

Example Exasol SQL SELECT Statements

You can issue standard SQL queries against Exasol. Here are some typical query patterns:

``` -- Selecting all columns from a table SELECT * FROM sales.orders;

-- Selecting specific columns with a WHERE clause filtering by a date SELECT order_id, customer_id, amount FROM sales.orders WHERE order_date >= DATE '2024-01-01';

-- Joining tables SELECT o.order_id, c.customer_name, o.amount FROM sales.orders o JOIN sales.customers c ON o.customer_id = c.customer_id WHERE o.order_status = 'SHIPPED';

-- Aggregating data SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_spent FROM sales.orders GROUP BY customer_id; ```

Datatype Conversion: Exasol and Snowflake

When extracting data from Exasol for use in Snowflake, be aware of datatype differences—automatic conversion may occur. For example:

Recommendation: Always review and test your ETL pipeline to ensure data is converted as expected, especially for date/time and floating-point types.

Best Practice: Initial and Incremental Loads

The recommended pattern is: - Initial (Full) Load: Extract all relevant data with no filter. - Incremental Load: Afterwards, extract only data that newly appeared or recently changed.

Using the Database Query Component

You can use the Database Query component for both initial and incremental loads by configuring the SQL statement differently:

Initial Load Example

No filter clause—extracts the entire table:

SELECT *
FROM sales.orders;

Incremental Load Example

With a filter for only newly added/changed rows, e.g., based on a timestamp column:

SELECT *
FROM sales.orders
WHERE order_date > '2024-06-01 00:00:00';

For a comprehensive guide on incremental loading and data replication strategy, see: Matillion Incremental Load Strategy

Note: In both load types, you are able to reuse the same Database Query component, simply modifying the SQL to include or exclude the appropriate filter.

By following these principles, you can efficiently query and transfer data from Exasol to Snowflake in a robust, scalable manner.

Data Integration Architecture

In a modern data integration workflow, loading data in advance of integration—known as the Extract, Load, and Transform (ELT) architecture—enables organizations to divide and conquer the process by splitting it into two distinct steps: first loading raw data into the target database, and then integrating and transforming that data as needed. This approach is particularly advantageous because it allows data transformation—which is critical for integration—to be executed within robust data transformation pipelines. These pipelines automate and orchestrate the sequence of operations needed to clean, standardize, and join data from multiple sources. By leveraging ELT, both the transformation and integration steps occur inside the target Snowflake database, delivering additional benefits such as rapid, on-demand scalability and reduced infrastructure costs; there is no need for separate data processing platforms or servers, as all computation is performed natively within Snowflake’s highly performant environment.