Integrate data from NuoDB to Amazon Redshift using Matillion

Creating an Identity in NuoDB

To create a new user (identity) in a NuoDB database, you interact with the system using SQL statements. NuoDB manages authentication and user properties via the SQL standard

CREATE USER

Prerequisites

• Ensure you have administrator privileges, as only database administrators (ADMIN authority) can create or alter users.
• Connect to your NuoDB database using the

  nuosql

  CLI or any compatible SQL client.

Step-by-Step Instructions

1. Connect to the Database

nuosql <database_name> --user DBA --password <admin_password>

Replace

<database_name>

<admin_password>

2. Create a User

Use the following SQL statement to create a new user. Substitute

<username>

<password>

CREATE USER <username> IDENTIFIED BY '<password>';

Example:

CREATE USER analyst1 IDENTIFIED BY 'securePW1!';

3. Grant Privileges (Optional)

New users do not have any privileges by default. To allow the user to access or modify data, you must explicitly grant them permissions. For example, to allow the user to connect to the database and perform SELECT queries:

GRANT CONNECT TO analyst1;
GRANT SELECT ON SCHEMA <schema_name> TO analyst1;

Replace

<schema_name>

4. Verify User Creation

You can confirm the user has been created using:

SELECT * FROM system.users;

This statement retrieves a list of all users in the system.

For further details on user and privilege management, refer to the official NuoDB SQL documentation.

Installing the JDBC Driver

To enable connectivity to NuoDB databases from Matillion Data Productivity Cloud, you will need to manually download and install the appropriate JDBC driver. Due to licensing or redistribution restrictions, the NuoDB JDBC driver does not ship with Matillion Data Productivity Cloud and must be acquired separately.

Step 1: Download the NuoDB JDBC Driver

1. Go to the NuoDB official downloads page: https://www.nuodb.com/downloads
2. Locate the JDBC driver section. When presented with multiple options, look specifically for the Type 4 JDBC driver, which is typically recommended for most JDBC-compatible applications, including Matillion Data Productivity Cloud.
3. Download the appropriate JDBC driver package for your operating system and Java version. Note where you save this file, as you will need it in upcoming steps.

Step 2: Install the JDBC Driver in Matillion Data Productivity Cloud

Since Matillion Data Productivity Cloud installs with certain bundled database drivers, external drivers like the NuoDB JDBC driver must be uploaded manually.

1. Review and follow the official installation guidance at:
   https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/
2. Prepare to upload the downloaded NuoDB JDBC driver to all relevant Matillion Agents or instance locations as instructed.
3. Complete the installation by placing the driver file(s) into the appropriate directory, as detailed in the Matillion documentation linked above.
4. Restart or reconfigure your Matillion environment/agent if required, so that the application can recognize and utilize the newly uploaded driver.

Step 3: Configure and Use the Driver in Matillion

With the driver installed, you can now begin creating connections and running components that interact with your NuoDB database.

• Follow the usage instructions provided here for setting up Database Query components using your uploaded driver:
  https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

Refer to these instructions to configure your connections and credentials, specifying the correct JDBC driver that you have just installed.

Note: Ensure that your use of the JDBC driver complies with the NuoDB licensing requirements, as redistribution is restricted and you are responsible for adhering to their terms.

Checking network connectivity

Before connecting Matillion Data Productivity Cloud to your NuoDB database, you must ensure that the NuoDB database allows incoming connections from the appropriate sources, depending on your deployment configuration:

• Full SaaS Agent Configuration:
  The NuoDB database must permit incoming connections from the list of Matillion public IP addresses. You can find the current list of IP addresses that need to be allowed at https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS Deployment:
  Incoming connections should be allowed from the IP ranges associated with your own virtual private cloud (VPC). Use helpful utilities provided at https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/ to verify network access from your VPC to the NuoDB database endpoint.

Additionally, if your NuoDB database is referenced by a DNS hostname rather than a static IP address, ensure that the Matillion agent (whether Full SaaS or Hybrid SaaS) is able to resolve the database’s DNS name. Proper DNS resolution is required for the agent to connect successfully.

Querying Data from a NuoDB Database

This guide explains how to query data from a NuoDB database, with code examples and best practices for initial and incremental data loads, especially in ETL scenarios that leverage tools like Matillion. For more information, see Matillion's article on incremental load data replication strategy.

1. Example: SQL SELECT Statements in NuoDB

Basic Table Query

SELECT * FROM customers;

Conditional Query

SELECT customer_id, customer_name, email
FROM customers
WHERE active = TRUE;

Query with Sorting and Limiting

SELECT order_id, order_date, amount
FROM orders
WHERE order_status = 'SHIPPED'
ORDER BY order_date DESC
LIMIT 100;

2. Datatype Conversion: NuoDB to Redshift

When migrating or querying data across NuoDB and Amazon Redshift, be aware of possible datatype conversions:

Make sure to review column types to avoid schema or load errors during data transfer.

3. Patterns for Initial and Incremental Loads

The reliable pattern for loading data is a once-off initial load followed by incremental loads, using the same ETL component for querying, such as Matillion's Database Query component.

Initial Load: No Filter Clause

For the initial load, select all relevant data:

-- No filter; retrieves all records
SELECT * FROM customers;

Incremental Load: With Filter Clause

For subsequent periodic (incremental) loads, restrict your query to only new or modified data. This is typically achieved by filtering rows based on an "updated_at" or "modified_date" column:

-- Only new/updated records since the last successful load
SELECT *
FROM customers
WHERE updated_at > '2024-06-01 00:00:00';

Tip: Replace the filter value with a variable or the latest timestamp from your destination system to ensure only fresh data is pulled.

Reference: Read more detail on Incremental Load Data Replication Strategy.

4. ETL Component Usage

• Use the same Database Query component in both the initial and incremental load workflows.
• For the initial load, omit the

  WHERE

  clause.
• For incremental loads, add an appropriate

  WHERE

  clause based on your business logic (e.g.,

  WHERE updated_at > ?

  ).

Note: Adjust your ETL orchestration to track and manage high-water marks, ensuring data consistency across loads.

Data Integration Architecture

Loading data into Redshift in advance of integration is a classic example of the "divide and conquer" strategy, where the process is split into two distinct steps: first, extracting and loading the raw data, and second, performing integration and transformation. This modular approach is a key advantage of the Extract, Load, Transform (ELT) architecture, as it allows for greater flexibility and efficiency in pipeline management. Since data integration inherently requires transforming the loaded data to fit business requirements or analytical models, it is best handled using robust data transformation pipelines. With ELT, these transformation and integration processes take place directly inside the target Redshift database. This not only provides rapid, on-demand performance and horizontal scalability, but also eliminates the need for additional data processing infrastructure—helping organizations reduce both operational complexity and cost.