Integrate data from H2 Database to Amazon Redshift using Matillion

Creating a User in H2 Database

In H2 Database, you can create and manage database users using standard SQL statements. Users are defined at the database level and can be assigned specific privileges.

Prerequisites

• You must have administrative privileges (typically the

  SA

  user) to create new users.
• You should be connected to your H2 database using a management tool (e.g., H2 Console, JDBC client, or command-line).

Creating a User

To create a user in H2, use the

CREATE USER

devuser

MySecretPass

CREATE USER devuser PASSWORD 'MySecretPass';

Replace

devuser

MySecretPass

Granting Privileges

By default, a new user has no privileges. To enable the user to perform specific actions, you must grant the required permissions. For example, to grant all privileges to the new user:

GRANT ALL ON SCHEMA PUBLIC TO devuser;

Or, to grant minimal privileges, such as

SELECT

GRANT SELECT ON SCHEMA PUBLIC TO devuser;

Example Workflow

Here is a combined script that creates a user and grants full schema privileges:

``` -- Create the user CREATE USER devuser PASSWORD 'MySecretPass';

-- Grant access to all tables in the PUBLIC schema GRANT ALL ON SCHEMA PUBLIC TO devuser; ```

Note: User and privilege management applies per database schema. Adjust the schema name if you are using a schema other than

PUBLIC

Listing Existing Users

To view all users in the H2 database:

SELECT * FROM INFORMATION_SCHEMA.USERS;

This will list usernames and their administrative status.

For further details, refer to the official H2 documentation on user management.

Installing the JDBC Driver

The H2 Database JDBC driver is not included by default in Matillion Data Productivity Cloud due to licensing or redistribution restrictions. To connect Matillion to an H2 database, you must manually download and install the necessary JDBC driver (preferably a Type 4 JDBC driver).

Follow the steps below to download, install, and configure the H2 JDBC driver for use with Matillion Data Productivity Cloud.

1. Download the H2 JDBC Driver

1. Visit the official H2 Database download page: https://www.h2database.com/html/download.html.
2. Identify and download the Type 4 JDBC driver, which is a platform-independent, pure Java driver suitable for most integration scenarios. The driver is typically packaged as a JAR file (e.g.,

   h2-*.jar

   ).

2. Install the Driver in Matillion Data Productivity Cloud

Once you have downloaded the JDBC driver JAR, you'll need to upload it to your Matillion environment. Installation details are documented in Matillion's official guide: Uploading External JDBC Drivers. Follow the instructions there to upload the JAR file and make it available to your Matillion instance.

3. Connect to H2 Database from Matillion

After installation, you can configure and use the driver in Matillion for database connectivity and queries. Refer to the official documentation for usage instructions:
Database Query Component Usage.

Be sure to select the newly installed driver when setting up the connection, then proceed with your data integration tasks as needed.

Checking network connectivity

To successfully connect Matillion Data Productivity Cloud to your H2 Database database, you must ensure that the H2 Database is configured to accept incoming network connections from the correct sources, depending on your deployment mode:

• Full SaaS Agent Configuration:
  You must allow incoming connections to your H2 Database from the set of IP addresses used by Matillion. The list of current IP addresses is published at: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS Deployment:
  In this configuration, allow incoming connections from the network range of your own virtual private cloud (VPC) where the Hybrid SaaS Agent is running. To help verify correct network access between your VPC and the H2 Database, use the connection utilities provided here: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

If your H2 Database server is referenced by a DNS hostname instead of a direct IP address, ensure that the Full SaaS or Hybrid SaaS agent is able to resolve the DNS name to an accessible IP address. If DNS resolution fails, network connectivity between Matillion Data Productivity Cloud and your H2 Database will not be established.

Querying Data from a H2 Database

This guide explains how to query data from an H2 Database, with an emphasis on best practices for initial and incremental data loads, and considerations for data type conversion (e.g., when loading into Amazon Redshift).

Example H2 Database Query Syntax

H2 Database uses standard SQL syntax for querying data. Below are basic examples of

SELECT

``` -- Select all columns from a table SELECT * FROM employees;

-- Select specific columns SELECT id, first_name, last_name, salary FROM employees;

-- Filtering results SELECT * FROM employees WHERE department = 'Engineering';

-- Filtering with a date range SELECT * FROM sales WHERE transaction_date >= '2024-01-01' AND transaction_date <= '2024-03-31';

-- Sorting results SELECT * FROM products ORDER BY created_at DESC;

-- Aggregate query with grouping SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department; ```

Data Type Conversion: H2 Database and Redshift

When moving data from H2 Database to Amazon Redshift, be aware that datatype conversion may be necessary. Here are some common mappings:

INTEGER

INTEGER

BIGINT

BIGINT

BOOLEAN

BOOLEAN

VARCHAR(n)

VARCHAR(n)

DOUBLE

DOUBLE PRECISION

DATE

DATE

TIMESTAMP

TIMESTAMP

BLOB

BYTEA

Pay special attention to:

• Maximum string lengths (

  VARCHAR

  )
• Differences in date/time precision
• Representation of booleans (inserts/updates)

Initial Load vs Incremental Load Patterns

The recommended approach for data movement is to use an initial load followed by incremental loads, via the same Database Query component. Details:

Initial Load

• Purpose: Populate the destination table with all existing data.
• Query pattern: No filter clause is needed.

Example:

SELECT * FROM employees;

Incremental Load

• Purpose: Populate the destination table with only new or changed data since the previous load.
• Query pattern: Includes a filter clause, often based on a column such as a timestamp or an increasing primary key.

Example:

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

Notes

• Use a column that reliably captures insert/update time or an incrementing key.
• Reference Matillion's incremental load strategy for detailed implementation guidance.

You may use parameters in your ETL process to dynamically inject the last-loaded value.

SELECT * FROM employees WHERE updated_at > '${last_loaded_timestamp}';

By following these patterns, you can structure your data extraction logic efficiently and prepare it for later transformation and loading into Redshift or other destinations.

Data Integration Architecture

Loading data into Amazon Redshift ahead of integration is a classic example of the "divide and conquer" strategy, where the process is thoughtfully split into two distinct phases. This separation is a key advantage of the Extract, Load, Transform (ELT) architecture: first, data is extracted from source systems and loaded directly into Redshift, and only then is it transformed and integrated as needed. Since data integration inherently involves data transformation, employing robust data transformation pipelines within Redshift is considered a best practice; these pipelines ensure transformations are consistent, repeatable, and scalable. By leveraging the ELT model, all data transformation and integration tasks are executed within the target Redshift database itself. This approach delivers multiple benefits: it provides speedy, on-demand processing, automatic scalability with your cluster, and eliminates the need for separate, potentially costly, data processing infrastructure outside of Redshift.