Integrate data from Mimer SQL to Amazon Redshift using Matillion

Creating an Identity in Mimer SQL

To create an identity (user) in a Mimer SQL database, you need sufficient privileges—typically, this operation is performed by a user with DBA (Database Administrator) rights. The process involves using the

CREATE IDENT

Below are step-by-step instructions:

1. Connect to the Database as a DBA

Before creating an identity, connect to Mimer SQL using a privileged account, such as

SYSADM

-- Example connection from a suitable SQL client:
CONNECT SYSADM IDENTIFIED BY 'your_dba_password';

2. Create a New Identity

Run the following SQL statement to create a new identity. Replace

username

password

CREATE IDENT username USING 'password';

Example:

CREATE IDENT johndoe USING 'Strong_Passw0rd';

• username

  : The name of the identity (user) to create.

• password

  : The authentication string. Enclose it in single quotes.

Notes

• Identity names (

  username

  ) are not case-sensitive and are stored in uppercase.
• The

  USING

  clause specifies the authentication string; password rules may apply according to database settings.

3. Grant Privileges to the Identity

A newly created identity does not have any privileges. Grant the required rights, such as the ability to connect to the database:

GRANT CONNECT TO johndoe;

You may also grant additional privileges (like

RESOURCE

DBA

GRANT SELECT, INSERT ON mytable TO johndoe;

4. Verify the New Identity

Test logging into the database with the new identity:

CONNECT johndoe IDENTIFIED BY 'Strong_Passw0rd';

If the connection is successful, the identity was created and granted connect rights correctly.

Installing the JDBC Driver

At the time of writing, the JDBC driver for Mimer SQL is not bundled by default with Matillion Data Productivity Cloud. This is due to licensing or redistribution restrictions associated with the Mimer SQL JDBC driver. To use a Mimer SQL data source with Matillion, you must manually download and install the necessary driver.

Step 1: Download the JDBC Driver

1. Visit the official Mimer SQL Database products page.
2. Locate and download the latest Type 4 JDBC driver for your operating system and platform. A Type 4 driver (also called a “pure Java” driver) is preferred, as it operates entirely in Java and provides optimal compatibility for modern applications and cloud environments.
3. Ensure you comply with any license agreements during download, keeping in mind any distribution or usage restrictions.

Step 2: Install the JDBC Driver into Matillion

To make the JDBC driver available to Matillion Data Productivity Cloud, you'll need to upload it as an external driver. Detailed installation instructions are maintained by Matillion and can be found in the documentation: Uploading External Drivers.

Follow these instructions carefully to ensure the driver is placed in the correct location and is accessible to Matillion's runtime environment. Typically, the installation involves:

• Uploading the downloaded

  .jar

  file containing the JDBC driver.
• Associating it with your Matillion Agent or relevant service.
• Restarting or refreshing your agent as instructed, so the new driver takes effect.

Step 3: Configure Usage in Matillion

Once the driver has been successfully installed, you can connect to Mimer SQL databases from Matillion components such as database queries. Review the Usage Instructions provided in the official documentation to properly configure connections, specifying the correct JDBC driver class name and connection URL format for Mimer SQL.

By following these steps, you will enable secure and functional connections between Matillion Data Productivity Cloud and your Mimer SQL database, ensuring seamless data integration workflows.

Checking network connectivity

To ensure successful connectivity between the Matillion Data Productivity Cloud and your Mimer SQL database, verify that your Mimer SQL database is configured to allow incoming network connections according to your deployment type:

• Full SaaS Agent Configuration:
  If you are using a Full SaaS agent, your Mimer SQL instance must permit incoming connections from the IP addresses published by Matillion. Consult the latest list of required source IP addresses here: Matillion IP Addresses.

• Hybrid SaaS Deployment:
  If you have deployed a Hybrid SaaS agent within your own infrastructure, configure your Mimer SQL database security rules to allow inbound connections from the addresses or CIDR blocks associated with your own Virtual Private Cloud (VPC). To help test and troubleshoot connectivity, you can use the utilities provided at Matillion Network Access Utilities.

Additionally, if your Mimer SQL database is referenced using a DNS hostname rather than a direct IP address, ensure that the agent (whether Full SaaS or Hybrid SaaS) can successfully resolve the DNS name to reach your database server. Failure to resolve the DNS name will prevent Matillion from connecting to your Mimer SQL instance.

Querying Data from Mimer SQL Database

This guide provides technical instructions for querying data from a Mimer SQL database, including example SQL statements, notes on datatype conversion (especially when transferring data to Amazon Redshift), and a recommended approach for performing both initial and incremental data loads.

Example Mimer SQL SELECT Statements

Here are basic examples of querying data from a Mimer SQL database:

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

-- Select specific columns with a WHERE condition SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 100;

-- Aggregate data with GROUP BY SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id; ```

Considerations for Datatype Conversion (Mimer SQL to Redshift)

When extracting data from Mimer SQL to load into Amazon Redshift, be aware that datatype conversion may occur. Here are a few common mappings:

Handle non-compatible datatypes before transferring data, e.g., by casting BLOBs to base64 encoded strings or excluding them from your load process.

Loading Data: Initial and Incremental Loads

Best Pattern: Initial Load Followed by Incremental Loads

For efficiency and reliable synchronization, it’s recommended to perform a one-time initial load followed by incremental loads using the same Database Query component (e.g., in Matillion ETL).

Initial Load

• Approach: Run an unfiltered SELECT statement to capture the entire table's data.
• Example Statement:

  SELECT * FROM employees;

• Usage: Intended for bootstrapping or full data refreshes, performed only once or as needed.

Incremental Load

• Approach: Add a WHERE clause to the SELECT statement to capture only new or changed records since the last load.
  • The filter is often based on a timestamp or an incremental numeric ID.
• Example Statement:

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

• Usage: Scheduled periodically (e.g., hourly, daily) to replicate only changed data for efficiency.

Note: For more information and patterns using Matillion, refer to the Matillion Knowledge Base: Incremental Load Data Replication Strategy.

Summary Table: Load Patterns with Database Query Component

SELECT * FROM tablename;

SELECT * FROM tablename WHERE updated_at > 'YYYY-MM-DD HH:MM:SS';

Data Integration Architecture

Loading data in advance of integration is a fundamental strategy that divides and conquers data engineering challenges by splitting the process into two distinct steps: first, loading the raw data, and second, performing the integration. This approach is central to the Extract-Load-Transform (ELT) architecture, which offers several distinct advantages. Data integration inherently requires that data be transformed—for instance, through cleaning, joining, or aggregation—and the most effective way to manage these transformations is by building dedicated data transformation pipelines. One significant benefit of ELT is that both transformation and integration operations are executed directly within the target Redshift database. This not only ensures the transformations are fast, scalable, and available on-demand, but also eliminates the need to invest in—and manage—separate data processing infrastructure, resulting in simplified operations and cost savings.