Integrate data from MonetDB to Amazon Redshift using Matillion

Creating a User in MonetDB

To add a new user identity in a MonetDB database, you will execute SQL statements either from a MonetDB client such as mclient or from an administrative interface. This process requires you to have administrative privileges (i.e., you must be a database administrator, often aliased as user monetdb).

Prerequisites

• Access to a running MonetDB instance.
• Appropriate rights to connect and execute statements as an administrator.

Step 1: Log In as an Administrative User

Connect to your database as the

monetdb

mclient -d <database_name> -u monetdb -P <admin_password>

Replace

<database_name>

<admin_password>

Step 2: Create the User

Use the

CREATE USER

CREATE USER "<username>" WITH PASSWORD '<password>';

• <username>

  : The user name. User names are case-sensitive if double-quoted.

• <password>

  : The user’s password.

Example:

CREATE USER "reporting_user" WITH PASSWORD 'Secur3P@ssw0rd';

Step 3: Grant Privileges

By default, new users have no access privileges. Grant rights according to your needs, for example:

GRANT SELECT ON <schema>.<table> TO "reporting_user";
-- Or, to grant all privileges on all tables in the schema:
GRANT ALL ON SCHEMA <schema> TO "reporting_user";

Replace

<schema>

<table>

Step 4: (Optional) List All Users

To see a list of defined users:

SELECT * FROM users;

This allows you to verify that the new user was added successfully.

Note: Changing a password can be accomplished via:

ALTER USER "<username>" SET PASSWORD '<new_password>';

And to remove a user:

DROP USER "<username>";

Refer to the MonetDB SQL documentation for further details on advanced privilege management.

Installing the JDBC driver

At the time of writing, the MonetDB JDBC driver is not bundled by default with Matillion Data Productivity Cloud. This is due to licensing or redistribution restrictions associated with the MonetDB JDBC driver. Consequently, to connect Matillion to a MonetDB database, you must manually download and install the driver.

Follow these instructions to add the MonetDB JDBC driver to your Matillion Data Productivity Cloud environment:

1. Download the MonetDB JDBC Driver

• Visit the MonetDB JDBC driver download page: https://www.monetdb.org/Documentation/JDBC.
• Locate and download the appropriate version. Look specifically for the Type 4 JDBC driver, as it is purely Java-based and does not require native libraries. Type 4 drivers are generally the recommended option for Matillion integration.

2. Upload the Driver to Matillion Data Productivity Cloud

• To add the downloaded MonetDB JDBC driver JAR file to your Matillion environment, refer to the instructions provided by Matillion: Uploading External Drivers.
• In summary, you will generally need to access the admin console for your Matillion Agent, locate the external drivers management section, and upload the MonetDB JDBC driver JAR file you downloaded earlier.

3. Configure and Use the Driver

• After installing the driver, configuration is required to start using it within Matillion. For procedures on how to set up and connect to databases using your installed JDBC driver, refer to: Database Query Usage Instructions.
• This documentation will detail how to create database connections, configure authentication, and manage queries via the installed MonetDB JDBC driver.

Following these instructions will enable connectivity between Matillion Data Productivity Cloud and your MonetDB database using the appropriate Type 4 JDBC driver.

Checking network connectivity

When configuring connectivity between Matillion Data Productivity Cloud and your MonetDB database, you must ensure that the MonetDB instance is accessible for incoming connections based on your deployment model:

• Full SaaS agent configuration:
  Allow incoming connections to MonetDB from the IP addresses listed at: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/. These addresses must be permitted in your MonetDB instance's firewall or network access control lists.

• Hybrid SaaS deployment:
  Allow incoming connections from your own virtual private cloud (VPC) where the Hybrid SaaS agent is deployed. You can verify network access to MonetDB using the utilities and tools provided at: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

Additionally, if you are referencing the MonetDB database using a DNS hostname, ensure that the Full SaaS or Hybrid SaaS agent can resolve this DNS address to the correct IP address. This may require updating your DNS settings or ensuring that your network environment provides the necessary DNS resolution capabilities.

Querying Data from MonetDB

MonetDB is a high-performance column-oriented database optimized for analytical queries. To query data from MonetDB, you use standard SQL syntax via SQL clients, integration tools, or ETL platforms.

Example MonetDB SQL SELECT Statements

Below are some basic examples:

``` -- Select all columns and rows from a table SELECT * FROM sales_orders;

-- Select specific columns with a filter SELECT order_id, customer_id, order_date FROM sales_orders WHERE order_date >= '2024-01-01';

-- Aggregate and group data SELECT customer_id, SUM(order_total) AS total_spent FROM sales_orders GROUP BY customer_id;

-- Join multiple tables SELECT so.order_id, c.customer_name FROM sales_orders so JOIN customers c ON so.customer_id = c.id WHERE so.status = 'SHIPPED'; ```

Datatype Conversion: MonetDB vs. Redshift

When integrating MonetDB with Amazon Redshift (e.g., via ETL), be mindful of datatype differences. Some conversions may be necessary; for example:

Review your ETL / migration tooling documentation for specifics about how datatypes are mapped or converted.

Initial and Incremental Loads Pattern

A robust pattern when extracting data from MonetDB involves:

• Initial Load: Extract all data once (e.g., for a brand new replication or migration).
• Incremental Loads: Periodically extract only new or updated data based on a filter (e.g., a timestamp or primary key range).

Both operations can use the same Database Query component in your ETL workflow.

Initial Load Query

During the initial load, run a simple query without filters:

SELECT *
FROM sales_orders;

Incremental Load Query

For incremental loads, add a filter clause. For example, if tracking by last modified date:

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

Or using a high-water-marking technique, such as loading records with an

order_id

SELECT *
FROM sales_orders
WHERE order_id > 100000;

For an in-depth guide on this ETL pattern, see Matillion's article on incremental load data replication strategy.

Data Integration Architecture

One of the key advantages of the ELT (Extract, Load, Transform) architecture is its ability to break down data integration into two distinct steps—loading data in advance before starting integration. This "divide and conquer" approach helps organizations manage complexity, as they can quickly ingest raw data and then systematically focus on necessary transformation and integration tasks. Data integration inherently involves transforming disparate datasets to yield meaningful, analyzable outputs, and data transformation pipelines provide the optimal means to accomplish these tasks efficiently. Another notable benefit of ELT is that both data transformation and integration are performed within the target Redshift database itself. This makes the process fast, scalable, and available on demand, while also eliminating the need—and associated cost—of maintaining separate data processing infrastructure outside of Redshift.