Integrate data from MonetDB to Databricks using Matillion

Creating a User in MonetDB

The process of adding a new user to a MonetDB database involves executing SQL commands—either through the

mclient

1. Connect to the Database

Open your terminal or preferred SQL client and connect to the desired MonetDB database:

mclient -u monetdb -d <database_name>

Replace

<database_name>

monetdb

2. Create a New User

Execute the following SQL statement to add a new user. Make sure to choose a secure password.

CREATE USER <username> WITH PASSWORD '<strong_password>' NAME '<display_name>' SCHEMA <schema>;

• <username>

  : The system login name for the new user.

• <strong_password>

  : The chosen password (use single quotes).

• <display_name>

  : The full name or description for the user.

• <schema>

  : The schema to use as the default for this user (commonly

  sys

  ).

Example:

CREATE USER alice WITH PASSWORD 'Secur3P@ssw0rd' NAME 'Alice Smith' SCHEMA sys;

3. Grant Privileges

By default, a new user has minimal privileges. Grant the necessary permissions according to your requirements. For example, to grant all privileges on a specific schema:

GRANT ALL ON SCHEMA <schema> TO <username>;

Example:

GRANT ALL ON SCHEMA sys TO alice;

You may grant more granular permissions as needed, such as

SELECT

INSERT

UPDATE

DELETE

GRANT SELECT, INSERT ON <schema>.<table> TO <username>;

Example:

GRANT SELECT, INSERT ON sys.orders TO alice;

4. Verify the User

List the users to confirm the new identity has been successfully created:

SELECT * FROM sys.users;

Additional Notes

• Only users with

  CREATEDB

  or

  CREATEROLE

  privilege can create new users.
• Passwords are case-sensitive and should be stored securely.
• Consult the MonetDB SQL Reference for advanced user and permission management.

Installing the JDBC Driver

At the time of writing, the MonetDB JDBC driver is not included by default with Matillion Data Productivity Cloud due to licensing or redistribution restrictions. Therefore, before you can connect to a MonetDB database from Matillion, you will need to manually download and install the relevant JDBC driver.

Downloading the MonetDB JDBC Driver

1. Navigate to the official MonetDB download page for JDBC drivers: https://www.monetdb.org/Documentation/JDBC.
2. Look for the latest stable version of the MonetDB JDBC driver. Preferentially select the Type 4 JDBC driver, which is the pure Java implementation and generally recommended for compatibility and performance.
3. Download the appropriate

   .jar

   file for the JDBC driver to a location on your local machine.

Installing the Driver into Matillion Data Productivity Cloud

Matillion Data Productivity Cloud supports uploading external JDBC drivers for database connectivity. To install the MonetDB JDBC driver:

1. Follow the official Matillion instructions for uploading external drivers found at: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/.
2. Use the upload interface to add your downloaded MonetDB

   .jar

   file.
3. Complete any additional configuration required as detailed in the Matillion documentation, ensuring the driver is properly registered for use within your environment.

Further Usage in Matillion

Once the MonetDB JDBC driver is installed, you can begin using it in your database components and queries. For instructions on configuring and using database connectivity within Matillion Data Productivity Cloud, refer to: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/.

If you encounter any issues with connecting to your MonetDB instance, ensure your driver is the correct Type 4 JDBC version and has been uploaded according to the provided instructions.

Checking network connectivity

To enable communication between Matillion Data Productivity Cloud and your MonetDB database, you must ensure that the MonetDB instance is configured to allow incoming network connections from the appropriate sources. The required configuration depends on your deployment type:

• Full SaaS Agent Configuration:
  If you are using the Full SaaS agent, your MonetDB database must accept connections from the set of static IP addresses provided by Matillion. A current list of these IP addresses can be found here. Update your network access rules or firewall to permit inbound connections from these addresses.

• Hybrid SaaS Deployment:
  For a Hybrid SaaS deployment, you need to allow inbound connections from your own virtual private cloud (VPC) where the Matillion agent is running. To verify network access or for useful tools to assist with this process, visit the Matillion network utilities page.

Additionally, if you reference your MonetDB instance using a DNS hostname rather than a direct IP address, ensure that your selected Matillion agent (whether Full SaaS or Hybrid SaaS) is able to resolve the DNS address correctly. This is important for maintaining reliable connectivity to your database.

How to Query Data from a MonetDB Database

This guide explains how to query data from a MonetDB database, with attention to initial and incremental data loads, query examples, and considerations for integration with platforms like Databricks.

Querying Data: SQL SELECT Statement Examples

Below are common MonetDB SQL queries:

``` -- Retrieve all records from a table SELECT * FROM sales_orders;

-- Select specific columns SELECT order_id, customer_id, amount FROM sales_orders;

-- Filter using a WHERE clause SELECT * FROM sales_orders WHERE order_date >= '2024-01-01';

-- Aggregate and group data SELECT customer_id, SUM(amount) AS total_spent FROM sales_orders GROUP BY customer_id ORDER BY total_spent DESC; ```

Datatype Conversion Between MonetDB and Databricks

When moving data between MonetDB and Databricks, be aware that datatype conversions may be necessary. Common MonetDB datatypes (e.g.,

INTEGER

DECIMAL

VARCHAR

Check your sync tool's documentation for automatic mapping and identify any manual type conversions that may be needed in your ETL workflows.

Data Loading Best Pattern: Initial and Incremental Loads

The optimal pattern for loading data from MonetDB employs an initial full load, followed by recurring incremental loads. Both operations can be accomplished using the same Database Query component (such as in Matillion, or similar ETL tools).

• Initial Load:
  No filter is required. The entire table or dataset is selected and loaded.

Example Initial Load Query (no filter):

SELECT * FROM sales_orders;

• Incremental Load:
  Uses a filter (such as

  WHERE updated_at >= ?

  ) to load only records that have changed since the last extract. This reduces data transfer and speeds up the process.

Example Incremental Load Query (with filter):

SELECT *
  FROM sales_orders
  WHERE updated_at >= '2024-06-01 00:00:00';

• The above filter should use a timestamp or incremental column that reliably tracks changes or insertions (learn more about incremental loads and strategies here).

Use the same Database Query component for both loading procedures, just modify the SQL to include or omit the incrementality filter as appropriate. This ensures consistency and simplifies ongoing data extraction and synchronization tasks.

Data Integration Architecture

Loading data in advance of integration is a foundational advantage of the ELT (Extract, Load, Transform) architecture, as it allows the overall process to be divided into two distinct steps: first, ingesting raw data into your Databricks database, and then performing any necessary integration or transformation operations within the same environment. This separation makes the workload more manageable and flexible. Data transformation—a critical aspect of integration—is most efficiently performed using well-structured data transformation pipelines, which can be orchestrated and automated in Databricks. Another key benefit of the ELT architecture lies in the fact that data transformation and integration occur directly inside the target Databricks database. This enables fast, on-demand, and scalable processing that leverages the power of Databricks’ optimized computing resources, eliminating the need for additional, external data processing infrastructure and associated costs.