Integrate data from Mimer SQL to Snowflake using Matillion

Creating an Identity in Mimer SQL

In Mimer SQL, the term for a database user is identity. An identity must be created before it can access or hold privileges in the database. Follow the steps below to create an identity in a Mimer SQL database.

Prerequisites

• You must have access to the database as a user with

  IDENTITY

  privilege (commonly

  SYSADM

  ).
• The Mimer SQL command line tool (

  bsql/DBA

  ), a suitable SQL client, or a programming interface can be used to execute the SQL statements.

SQL Syntax to Create an Identity

CREATE IDENTITY identity_name 
    [ IDENTIFIED BY password ]
    [ DEFAULT SCHEMA schema_name ]
    [ ACCOUNT STATUS { OPEN | CLOSED } ]
    [ WITH { ADMIN OPTION | NO ADMIN OPTION } ];

• identity_name

  is the name of the new user.

• password

  is the password the user will use to log in.

• DEFAULT SCHEMA

  designates the schema associated with the identity.

• ACCOUNT STATUS

  can be

  OPEN

  (active) or

  CLOSED

  (inactive).

• WITH ADMIN OPTION

  allows the user to grant privileges to others.

Example: Creating a New User Identity

Below is a basic example to create a user named

appuser

AppSecret!

appuser

CREATE IDENTITY appuser
    IDENTIFIED BY 'AppSecret!'
    DEFAULT SCHEMA appuser
    ACCOUNT STATUS OPEN;

Note: Passwords should follow any security policies enforced by your installation.

Granting Privileges to the Identity

After creating the user, you generally need to grant the necessary privileges. For example, to allow

appuser

GRANT USAGE ON SCHEMA appuser TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA appuser TO appuser;

Additional Notes

• Identity names and schema names are normally case-insensitive.
• You can only create identities if you have the proper

  IDENTITY

  privilege.
• The new identity must set their password at first connection if a NULL password is used during creation.

Installing the JDBC Driver

At the time of writing, the Mimer SQL JDBC driver is not bundled with Matillion Data Productivity Cloud by default, due to licensing or redistribution restrictions. Therefore, before you can connect to a Mimer SQL database from Matillion, you must manually acquire and install the appropriate JDBC driver.

Downloading the Mimer SQL JDBC Driver

1. Visit the official Mimer SQL product download page: https://www.mimer.com/products/database/.
2. On the downloads page, locate the section dedicated to JDBC drivers.
3. Download the Type 4 JDBC driver. Type 4 drivers are preferred because they are pure Java implementations and do not require any external native libraries.

Installing the JDBC Driver in Matillion Data Productivity Cloud

After obtaining the JDBC driver

.jar

• Detailed instructions for uploading the JDBC driver are available here:
  Matillion Documentation: Uploading External Database Drivers.

Ensure that you upload the downloaded

.jar

Using the JDBC Driver in Matillion

Once you've completed the installation process, you can use the Mimer SQL connection for your desired workflows. For configuration and usage guidelines, refer to the following documentation:

• Matillion Documentation: Database Query Component Instructions

By following these references, you will be guided through the process of configuring connections and making database queries using the installed driver.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your Mimer SQL database, you must allow incoming connections to your Mimer SQL instance, depending on your deployment configuration:

• Full SaaS agent configuration:
  You must configure your Mimer SQL database to allow incoming connections from the specific Matillion Data Productivity Cloud IP addresses. The list of these IP addresses is available at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/
  Make sure your network or firewall settings permit access from all the listed IPs.

• Hybrid SaaS deployment:
  You should allow incoming connections from your own virtual private cloud (VPC). This means configuring your database or firewall to accept connections originating from your VPC’s network range.
  To assist with network configuration and connectivity checks, you can use the tools provided at:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if you are referencing the Mimer SQL database using a domain name (DNS), ensure that the Full SaaS or Hybrid SaaS agent can resolve the database hostname to the correct IP address. This might require DNS configuration or network rules to permit DNS lookup functionality.

Querying Data from Mimer SQL Database

This guide provides technical instructions for querying data from a Mimer SQL database, with practical SQL SELECT examples and an overview of efficient data loading patterns, including initial and incremental loads. Special attention is given to using the Database Query component for data extraction, suitable for integration or ETL/ELT scenarios, such as loading data into Snowflake.

Mimer SQL SELECT Query Examples

Here are some common query patterns in Mimer SQL using standard SQL syntax:

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

-- Selecting specific columns with conditions SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE department_id = 20;

-- Using aggregate functions SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;

-- Joining tables SELECT e.employee_id, e.first_name, d.department_name FROM employees AS e JOIN departments AS d ON e.department_id = d.department_id WHERE d.location = 'LONDON'; ```

Datatype Conversion: Mimer SQL to Snowflake

When transferring data from Mimer SQL to Snowflake, note the following:

• Datatypes are not always identically named or implemented between the two platforms (e.g.,

  VARCHAR

  in Mimer SQL is equivalent to

  VARCHAR

  in Snowflake, but other types like

  INTEGER

  ,

  DATE

  , or

  DECIMAL

  may differ in length/scale or accepted ranges).
• Some automatic datatype conversion may be necessary for optimal compatibility.
• Review the datatype mappings and adjust your pipeline/queries if needed.

Data Load Patterns: Initial vs Incremental Loads

The recommended approach for efficient data movement is as follows:

Initial Load (Full Extraction)

• Perform a one-time extraction of all data.
• The Database Query component is used without a filter to select all relevant records.

Example:

SELECT *
FROM employees;

Incremental Load (Delta Extraction)

• After the initial load, subsequent extractions should be incremental, fetching only new or changed data.
• The Database Query component is used with a filter clause, usually on a timestamp or an incrementing key.

Example:

SELECT *
FROM employees
WHERE updated_at > :last_run_time;

:last_run_time

Refer to Matillion: Incremental Load Data Replication Strategy for a deeper exploration of this approach.

Note: You should use the same Database Query component for both loading methods; only the SQL WHERE clause changes to accommodate incremental logic.

References
- Matillion Incremental Load Documentation
- Mimer SQL Documentation

Data Integration Architecture

Loading data into Snowflake in advance of integration is a fundamental principle of the ELT (Extract, Load, Transform) architecture, effectively dividing the overall data integration challenge into two manageable steps. This approach allows data to be first loaded into Snowflake and subsequently transformed as needed, rather than trying to prepare it for integration before loading. The transformation step, which is crucial for true data integration, is best accomplished using data transformation pipelines. These pipelines allow for the systematic and automated transformation of raw data into formats suitable for analysis or operational use. A key advantage of the ELT approach is that all data transformation and integration processes occur directly within the Snowflake database itself. This design eliminates the need for external data processing infrastructure, making the transformation process faster, more reactive to changing requirements, and highly scalable—users can leverage Snowflake’s compute resources on demand and only pay for the compute time consumed.