Integrate data from eXtremeDB to Snowflake using Matillion

Creating a User in eXtremeDB

eXtremeDB supports multiple deployment models, including in-memory, persistent, and client/server architectures. User and authentication management is primarily relevant in the client/server variant, where database security and access control are managed by the server.

Below are the steps to create a user (identity) in an eXtremeDB database, using the SQL interface available with eXtremeSQL.

1. Connect to the eXtremeDB Server

Start by connecting to the eXtremeDB SQL server using a suitable SQL command-line tool or a database client.

```ell

Example using mcsql (eXtremeDB SQL command-line tool)

mcsql -h -u -p ```

2. Use the

CREATE USER

Statement

You can create new database users with the

CREATE USER

CREATE USER USERNAME IDENTIFIED BY 'password';

• Replace

  USERNAME

  with the desired username.
• Replace

  'password'

  with a suitably strong password for the user.

Example

CREATE USER analyst IDENTIFIED BY 'Tr0ub4dor&3';

3. Assigning Privileges

After creating a user, you should grant the necessary privileges.

For example, to allow the user to read data from all tables:

GRANT SELECT ON ALL TO analyst;

Or to grant more comprehensive privileges:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TO analyst;

4. Verify User Creation

To list all existing users (if your configuration permits):

SELECT * FROM systable_users;

Note: System catalog views and their names may vary based on the eXtremeDB SQL version and configuration.

Example: Full Script

``` -- Connect as DBA or another highly-privileged user first.

CREATE USER appuser IDENTIFIED BY 'securePassword1!'; GRANT SELECT, INSERT, UPDATE ON ALL TO appuser; ```

For further details, please refer to your version of the eXtremeDB SQL Reference Guide or contact your database administrator.

Installing the JDBC driver

This document outlines the process of downloading and installing the eXtremeDB JDBC driver for integration with Matillion Data Productivity Cloud. Because of licensing or redistribution restrictions, the eXtremeDB JDBC driver is not included with Matillion Data Productivity Cloud by default. To use this driver, you must manually acquire and install it as described below.

Step 1: Download the eXtremeDB JDBC Driver

1. Visit the official eXtremeDB product page: https://www.mcobject.com/products/extremedb/
2. Locate the JDBC driver section, and ensure you download a Type 4 JDBC driver. Type 4 drivers are preferred as they provide direct connectivity between Java applications and databases over standard network protocols, which ensures optimal compatibility with Matillion.
3. Follow any prompts to register or accept licensing terms as required by McObject before downloading the driver package.

Step 2: Prepare the Driver for Installation

Once you have downloaded the eXtremeDB Type 4 JDBC driver archive (typically a

.jar

Step 3: Install the Driver into Matillion Data Productivity Cloud

Matillion Data Productivity Cloud requires manual upload of third-party JDBC drivers. Please refer to Matillion’s guide for uploading external drivers: Uploading External Drivers.

1. Access your Matillion Data Productivity Cloud Agent environment where you wish to install the eXtremeDB JDBC driver.
2. Follow the documented procedure to upload the downloaded JDBC driver

   .jar

   file into the platform.
3. If prompted, restart the Matillion Agent service to complete the driver registration process.

Step 4: Configure and Use the Driver

After successful installation, you are ready to use the eXtremeDB JDBC driver within your Data Productivity Cloud environment. For details on connecting and querying databases with your newly uploaded driver, follow the official usage documentation: Database Query.

Be sure to supply the correct connection details (such as JDBC URL, user credentials, and any necessary advanced properties) for your eXtremeDB instance, as required in the Matillion configuration interfaces.

Checking network connectivity

To enable successful integration between Matillion Data Productivity Cloud and your eXtremeDB database, you must ensure that the eXtremeDB instance is configured to accept incoming connections, according to your deployment model:

• Full SaaS agent configuration: Allow incoming connections from the Matillion-managed IP addresses listed at the following URL: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS deployment: Allow incoming connections from your own virtual private cloud (VPC). To verify network access from your VPC to the eXtremeDB database, utilize the utilities provided at: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

Additionally, if your eXtremeDB database is referenced by a DNS hostname, ensure that either the Full SaaS agent or the Hybrid SaaS agent—depending on your deployment—can resolve the DNS address used. This is essential for establishing successful connections to the database.

Querying Data from an eXtremeDB Database

This guide provides technical instructions for querying data from an eXtremeDB database, including examples of SQL queries, data type conversion considerations between eXtremeDB and Snowflake, and best practices for initial and incremental data loads.

Querying eXtremeDB with SQL SELECT Statements

eXtremeDB supports a subset of SQL for querying data. Here are some sample queries:

``` -- Retrieve all rows from the Customers table SELECT * FROM Customers;

-- Retrieve specific columns from the Orders table SELECT OrderID, CustomerID, OrderDate FROM Orders;

-- Retrieve rows with a WHERE clause SELECT * FROM Products WHERE Category = 'Electronics';

-- Aggregate function usage SELECT Category, COUNT(*) AS ProductCount FROM Products GROUP BY Category; ```

Note: The actual SQL capabilities and syntax may depend on your eXtremeDB deployment and configuration. eXtremeDB also supports powerful native APIs in C/C++ and other languages if needed.

Data Type Conversion between eXtremeDB and Snowflake

When transferring data from eXtremeDB to Snowflake, be aware of possible datatype conversions. eXtremeDB uses its own set of types (e.g.,

int4

double

utf8_varchar

NUMBER

FLOAT

VARCHAR

Example datatype mapping:

Ensure that transformation and casting logic is applied as needed when moving data.

Best Practices: Initial and Incremental Loads

A common and effective pattern is to perform a one-off initial load followed by ongoing incremental loads. Both loads can use the same Database Query component (e.g., within pipelines orchestrated by Matillion ETL, Talend, or other data integration platforms).

Initial Load Example

During the initial load, no filter clause is applied to the query, resulting in a full table/data extraction.

SELECT * FROM Orders;

Incremental Load Example

For ongoing loads, add a filter clause (such as a

WHERE

HAVING

LastUpdated

SELECT * FROM Orders WHERE LastUpdated > '2024-06-16 00:00:00';

Configure your ETL pipeline to dynamically substitute the last successful extraction timestamp (or ID) in the filter criteria for each run. Guidance on structuring incremental loads can be found in the Matillion Knowledge Base: Incremental Load / Data Replication Strategy.

Tip: Use the same Database Query component for both load types, avoiding duplication in your pipeline design—simply toggle query parameters or add filters as needed.

Data Integration Architecture

Loading data into Snowflake in advance of integration exemplifies the "divide and conquer" approach, where the data integration process is split into two distinct steps. This separation is a key advantage of the ELT (Extract, Load, Transform) architecture, as the raw data is first loaded into Snowflake and then transformed as needed for integration and analysis. Effective data integration also relies on robust data transformation, and the most efficient way to achieve this is through the use of data transformation pipelines. These pipelines automate and orchestrate the transformation process directly within Snowflake. By leveraging ELT architecture, organizations benefit from performing data transformation and integration tasks inside the Snowflake database itself, resulting in fast, on-demand, and highly scalable operations. This not only accelerates time-to-value but also eliminates the need for separate data processing infrastructure, leading to cost savings and operational efficiencies.