Integrate data from InterSystems IRIS to Amazon Redshift using Matillion

Creating a User in InterSystems IRIS

This guide describes how to create a new user identity in an InterSystems IRIS database environment using both the Management Portal and an SQL script.

Using the Management Portal

1. Log in to the InterSystems IRIS Management Portal with administrative privileges.

2. Navigate to System Administration > Security > Users.

3. Click the Create New User button.

4. Fill out the required fields:

   • User Name: Unique identifier for the user (e.g.,

     myuser

     ).
   • Full Name: Descriptive name for the user.
   • Password: Set the initial password.
   • Optionally configure roles, account status, and other properties according to your requirements.

5. Click Save to create the new account.

Using SQL Scripts

You can also create a user via the terminal or any SQL client connected to your IRIS instance.

Example SQL Script

CREATE USER myuser IDENTIFIED BY 'MyStrongP@ssw0rd';
GRANT %DB_IRISSAMPLE TO myuser; -- Grant access to a namespace/role (optional)

Notes: - Replace

myuser

'MyStrongP@ssw0rd'

%DB_IRISSAMPLE

%Admin_Security

Additional SQL Options

• Disable an account after creation:

  ALTER USER myuser ACCOUNT UNLOCK;

• Assign multiple roles:

  GRANT %DB_IRISSAMPLE, %DB_USER TO myuser;

For further customization rules on passwords, account expiry, or audit settings, consult the InterSystems IRIS Security Documentation.

Installing the JDBC driver

At the time of writing, the JDBC driver for InterSystems IRIS is not bundled with Matillion Data Productivity Cloud due to licensing constraints and redistribution restrictions. This means that administrators must obtain the driver separately and upload it manually to their Matillion environment before it can be used for database connectivity.

To get started, follow these steps:

1. Download the JDBC Driver
   Visit the official InterSystems IRIS product page: https://www.intersystems.com/products/intersystems-iris/. On this page, look for the section or link to "Download" or "Product Downloads".
2. Be sure to locate a Type 4 JDBC driver (also referred to as a “pure Java” JDBC driver), as this is preferred for maximum portability and compatibility with cloud-based platforms like Matillion.

3. You may be required to register for an InterSystems account or accept license terms before you are able to access the download.

4. Review Matillion Supported Driver Instructions
   While Matillion does not distribute the IRIS JDBC driver itself, the process to upload any external JDBC driver is straightforward. For detailed instructions and best practices for adding external JDBC drivers, refer to: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/.

5. The core steps involve uploading the appropriate JDBC JAR file to your Matillion Agent, following the interface prompts to register the new connection type, and restarting the agent service if required.

6. Configure and Use the Driver
   Once the driver JAR has been uploaded and registered within Matillion Data Productivity Cloud, you will need to create or edit a database connection utilizing the newly available InterSystems IRIS JDBC driver option.

7. Comprehensive instructions for configuring and running database queries with newly-uploaded JDBC drivers are available here: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/.

By following these steps and utilizing the referenced resources, you can successfully enable InterSystems IRIS JDBC support in your Matillion Data Productivity Cloud environment.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your InterSystems IRIS database, you must configure your database to allow incoming connections based on your chosen deployment type:

• Full SaaS agent configuration:
  Configure your InterSystems IRIS database to allow incoming connections from the Matillion Data Productivity Cloud’s IP addresses. The specific IP addresses you need to allow are listed at: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS deployment:
  The InterSystems IRIS database must allow incoming connections from your own virtual private cloud (VPC) where your Hybrid SaaS agent is running. To help verify that your network configuration allows proper connectivity, you can use the utilities provided at: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

Additionally, if the InterSystems IRIS database is being referenced by a DNS hostname rather than an IP address, the Full SaaS or Hybrid SaaS agent must have the necessary network access and configuration to resolve that DNS address successfully.

Querying Data from InterSystems IRIS Database

This documentation provides guidance for technical users querying data from an InterSystems IRIS database, highlighting usage with SQL statements, datatype considerations, and best practices for data loading (specifically for ETL integration scenarios such as with the Matillion ELT platform).

Example SQL SELECT Statements

Below are some sample SQL queries you might execute against an InterSystems IRIS database:

``` -- Select all records from a table SELECT * FROM Patient;

-- Select specific columns SELECT PatientID, Name, DateOfBirth FROM Patient WHERE DateOfBirth >= '2000-01-01';

-- Using aggregate functions SELECT City, COUNT(*) AS PatientCount FROM Patient GROUP BY City;

-- Filtering with joins SELECT p.Name, a.AppointmentDate FROM Patient p JOIN Appointment a ON p.PatientID = a.PatientID WHERE a.AppointmentDate BETWEEN '2024-01-01' AND '2024-06-01'; ```

Data Type Conversion Considerations (IRIS vs. Redshift)

When extracting/interfacing data between InterSystems IRIS and Amazon Redshift, be aware that datatype conversions may occur. For example:

Tip: Pay special attention to large string/blob datatypes (like

%Library.GlobalCharacterStream

or

%Stream.GlobalCharacter

) as Redshift may impose limits on VARCHAR sizes.

Best Patterns for ETL Data Loading

The recommended loading pattern is:

• Once-off initial load (also called “full extract”)
• Incremental load (replicating only new or changed data since the last load)

Matillion’s Database Query component is recommended for both load types.

Initial Load (Full Extract)

During an initial load, you extract all data from the source table. No filter clause is used. For example:

SELECT PatientID, Name, DateOfBirth, LastUpdated
FROM Patient;

Configure the Database Query component in Matillion without a

WHERE

Incremental Load (CDC or Delta Extract)

In incremental loads, you extract only new or updated rows, typically by filtering on a

LastUpdated

For example, to extract only records updated since a certain point:

SELECT PatientID, Name, DateOfBirth, LastUpdated
FROM Patient
WHERE LastUpdated > '2024-06-19 12:00:00';

• The filter value (

  '2024-06-19 12:00:00'

  ) should be set dynamically, using the timestamp of the last successful extraction.
• Re-use the same Database Query component pattern; toggle the filter clause based on loading mode.

For more detail on approaches and incremental load strategies, visit: Incremental Load Data Replication Strategy (Matillion).

Note: Carefully validate data type mappings and test queries in a development environment prior to production use, especially when moving large datasets or sensitive patient/clinical data.

Data Integration Architecture

Loading data into Amazon Redshift in advance of integration exemplifies the "divide and conquer" principle by splitting the data pipeline into two discrete steps: loading (extract and load) and integrating (transform). This approach is fundamental to the ELT (Extract, Load, Transform) architecture, offering notable advantages over traditional ETL workflows. Once data is loaded into Redshift, data integration typically requires various transformations, which are most robustly implemented through data transformation pipelines designed within the database. A further advantage of the ELT model is that both transformation and integration processes occur directly within the target Redshift environment. This not only results in fast and on-demand data processing but also leverages Redshift's native scalability and parallelism, while eliminating the need for, and the costs associated with, external data processing infrastructure.