Integrate data from Raima Database Manager (RDM) to Snowflake using Matillion

Creating a User in Raima Database Manager (RDM)

Raima Database Manager (RDM) provides user and security management features starting from RDM 14.1, where database identities (users) can be created and managed. Users are associated with roles to control their access and permissions within a database. The following instructions guide you through the process of creating a new user (identity) in an RDM database using SQL.

Prerequisites

• RDM 14.1 or later.
• Sufficient privileges to create users (requires the

  SECURITY_ADMIN

  role).
• Access to RDM SQL Command Line or a similar SQL client.

1. Connect to the Database

-- Example: Connect as an existing admin user
CONNECT TO 'your_database'; -- replace with your database name
AUTHORIZATION 'admin_username', 'admin_password';

2. Create a New User (Identity)

RDM uses the

CREATE IDENTITY

-- Syntax: Create a user (identity) and assign a password
CREATE IDENTITY username
    AUTHENTICATED BY 'user_password';

Example:

CREATE IDENTITY alice
    AUTHENTICATED BY 'Secur3Passw0rd!';

3. Assign a Role to the User

To grant permissions, you assign one or more roles to the new user. The built-in role

PUBLIC

DBA

READONLY

SECURITY_ADMIN

GRANT

-- Grant DBA role to the new user
GRANT DBA TO alice;

You can also create custom roles as needed and assign them to the user.

4. Confirm User Creation (Optional)

You can view all created identities using:

-- List all users (identities)
SELECT * FROM rdm_sys.identities;

Note: Usernames and passwords are case-sensitive. Ensure that you replace

username

user_password

Installing the JDBC driver

At the time of writing, the Raima Database Manager (RDM) JDBC driver is not bundled with Matillion Data Productivity Cloud by default. This is due to licensing or redistribution restrictions imposed by Raima. As such, if you require integration with an RDM database, you will need to obtain and install the JDBC driver manually.

Download the JDBC driver

To get started, you should download the appropriate JDBC driver from the official Raima website:

• Visit https://www.raima.com/products/rdm

When selecting a driver, prioritize a Type 4 JDBC driver (commonly referred to as a “thin” driver), as this option is generally preferred for use with Matillion, offering better compatibility and performance in cloud-based environments.

Install the JDBC driver in Matillion Data Productivity Cloud

Once you have downloaded the correct JDBC driver JAR file, you need to upload it into your Matillion environment so that it can be used in data integrations and transformations. Matillion provides a documented process for uploading third-party (external) JDBC drivers:

• Please follow the steps provided in the Matillion documentation at
  https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

This documentation explains how to add the JAR file to your Matillion Agent and ensure that it is recognized and available for use in orchestrations and data pipelines.

Configuring database queries

After installing the driver, you can use it within your data workflows in Matillion to perform operations against your RDM database. For details on configuring database queries and using custom drivers within Matillion, refer to this resource:

• https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

These instructions explain how to specify connection parameters and utilize the external JDBC driver within the Database Query component of your data pipelines.

Make sure that configuration values such as hostname, port, database, and credentials are appropriate for your RDM instance and network environment.

By following these steps, you will be able to securely add RDM connectivity to your Matillion Data Productivity Cloud workflows.

Checking network connectivity

When connecting Matillion Data Productivity Cloud to a Raima Database Manager (RDM) database, you must ensure that the RDM database is configured to allow incoming network connections appropriate to your deployment type:

• Full SaaS Agent Configuration:
  The RDM database must permit incoming connections from the set of IP addresses used by Matillion’s SaaS agents. The required IPs are documented at Allowing IP Addresses. Ensure your database firewall or access control lists are updated accordingly.

• Hybrid SaaS Deployment:
  If you are using a Hybrid SaaS deployment, configure your RDM database to allow incoming connections from the IP address range of your own virtual private cloud (VPC). You can use the tools available at Check Network Access to verify connectivity and troubleshoot access issues.

Additionally, if you are connecting to the Raima Database Manager (RDM) database using a DNS name instead of an IP address, the resolving agent (whether Full SaaS or Hybrid SaaS) must be able to resolve the DNS name properly. Ensure any relevant DNS records are publicly accessible or correctly configured within your private network to support this requirement.

Querying Data from Raima Database Manager (RDM)

This guide outlines how to query data from a Raima Database Manager (RDM) database, focusing on sample SQL statements, type conversion considerations with Snowflake, and recommended patterns for initial and incremental data loads.

1. Example RDM SQL SELECT Statements

Use standard SQL to query data from RDM tables. Common examples:

``` -- Select all rows from 'customers' SELECT * FROM customers;

-- Select specific columns from 'orders' SELECT order_id, customer_id, order_date FROM orders;

-- Aggregate sales by product SELECT product_id, SUM(quantity) AS total_sold FROM order_items GROUP BY product_id;

-- Filtered query with WHERE clause SELECT * FROM products WHERE discontinued = 0; ```

2. Considerations for Datatype Conversion (RDM ⇔ Snowflake)

When extracting data from RDM into Snowflake, datatype conversion is likely. Notable points:

• Integer types (e.g.,

  SMALLINT

  ,

  INT

  ,

  BIGINT

  ) map directly, though sizes may differ.
• Floating-point types (

  FLOAT

  ,

  DOUBLE

  ) will map, but differences in precision (‘decimals’) can occur.
• Character/string types (

  CHAR

  ,

  VARCHAR

  ) are generally compatible, but be aware of maximum length limitations.
• Date/time types (

  DATE

  ,

  TIME

  ,

  TIMESTAMP

  ) are supported, but always check for timezone and formatting mismatches.
• Nullability, encoding, and other column constraints may need additional handling.

3. Pattern: Initial and Incremental Loads

The optimal pattern for extracting data from RDM uses both initial (full) and incremental (delta) loads. Reference: Matillion Incremental Load and Data Replication Strategy.

• Initial Load: Extracts the entire contents of a table for the first time.
• Incremental Load: Extracts only data that has been added or modified since the last load, minimizing data transfer.

Both approaches can use the same Database Query component (e.g., Matillion's Database Query), with differences in filtering logic:

Initial Load Example (No Filter Clause)

SELECT * FROM orders;

Incremental Load Example (With Filter Clause)

SELECT * FROM orders
WHERE updated_at > '2024-06-10 00:00:00';

updated_at

• Ensure a reliable mechanism (such as high water-mark timestamps or numeric keys) for tracking and updating the filter value after each incremental load.

Tip: Always test data extraction queries after datatype conversion to handle any incompatibilities between RDM and Snowflake.

Data Integration Architecture

Loading data in advance of its integration is a fundamental principle of the Extract, Load, and Transform (ELT) architecture. This approach effectively divides the data integration process into two distinct stages: first, raw data is ingested and stored within the Snowflake database; subsequently, the integration and transformation tasks are performed directly on that data. By adopting this model, organizations are able to “divide and conquer” complex integration workflows, simplifying maintenance and troubleshooting. Data integration inherently involves the transformation of data to fit target models, apply business logic, or standardize formats. The most efficient and reliable way to achieve this is through dedicated data transformation pipelines, which orchestrate, streamline, and automate these processes within Snowflake. An additional advantage of the ELT architecture is that both transformation and integration occur directly inside the Snowflake environment. As a result, organizations benefit from fast, on-demand, and highly scalable computation, while avoiding the complexity and cost associated with external data processing infrastructure.