Integrate data from IBM Netezza to Amazon Redshift using Matillion

Creating a User in IBM Netezza

This guide explains how to create a user (identity) in an IBM Netezza system using SQL. You will require appropriate administrative privileges (typically, the

ADMIN

Prerequisites

• Command-line access to your Netezza appliance, or access to an SQL client (such as nzsql).
• Administrative privileges on the Netezza system.

Steps to Create a User

1. Log in to the Netezza System

Use the

nzsql

nzsql -u admin_user -pw your_password -d database_name

Alternatively:

nzsql

2. Create the User

Write and execute a

CREATE USER

CREATE USER username WITH PASSWORD 'your_secure_password';

Example:

CREATE USER alice WITH PASSWORD 'Str0ngP@ssw0rd!';

3. (Optional) Assign Explicit Privileges or Roles

The new user will have no privileges on existing objects by default. You can grant database roles or object-specific permissions.

Example – Granting CONNECT privilege to the database:

GRANT CONNECT ON DATABASE database_name TO USER alice;

Example – Granting usage on a specific schema:

GRANT USAGE ON SCHEMA schema_name TO USER alice;

Example – Granting select privilege on a table:

GRANT SELECT ON TABLE schema_name.table_name TO USER alice;

4. (Optional) Set User Properties

You can specify additional properties when creating a user, e.g., limiting sessions or specifying a default schema.

Example:

CREATE USER alice WITH PASSWORD 'Str0ngP@ssw0rd!' DEFAULT_SCHEMA = my_schema;

5. Verifying the User

To list all users and confirm the new user is created:

\du

Or query the system view:

SELECT * FROM _v_user WHERE usename = 'alice';

Note: Always choose strong passwords and manage user privileges according to your organization's security policies.

Installing the JDBC driver

The IBM Netezza JDBC driver is required to enable connectivity between Matillion Data Productivity Cloud and your Netezza database. At the time of writing, due to licensing or redistribution restrictions, this JDBC driver is not included by default in Matillion Data Productivity Cloud. You will need to manually download and install the driver as outlined below.

1. Downloading the IBM Netezza JDBC Driver

To obtain the JDBC driver:

1. Navigate to the official download page for the IBM Netezza JDBC driver: https://www.ibm.com/support/pages/ibm-netezza-jdbc-driver
2. Log in with your IBM account if prompted and review the terms and conditions.
3. Download the driver package suitable for your environment. Select the Type 4 JDBC driver, which is a pure Java implementation and is preferred for integration with Matillion Data Productivity Cloud.

2. Installing the Driver in Matillion Data Productivity Cloud

After downloading the JDBC driver JAR file to your local machine, you must upload it to your Matillion Agent. The Matillion platform allows for custom JDBC driver uploads to enable connections to additional databases such as IBM Netezza.

To install the JDBC driver, follow the instructions provided in the Matillion documentation:
Uploading external drivers to the Matillion Agent

Typical steps include:

• Accessing the list of drivers from your Agent settings.
• Uploading the downloaded JDBC JAR file to the agent.
• Ensuring the changes are correctly deployed and, if needed, restarting the agent so the new driver is recognized.

Refer to the linked documentation above for detailed, step-by-step guidance and requirements for uploading third-party drivers.

3. Using the Driver within Matillion

Once installed, you can configure and use the newly uploaded Netezza JDBC driver within your projects in Matillion Data Productivity Cloud. For details on how to make database connections, utilize data query components, and interact with the Netezza system, refer to:
Database Query usage instructions

By following these steps and consulting the relevant documentation, you can enable seamless integration between Matillion Data Productivity Cloud and your IBM Netezza environment.

Checking network connectivity

To ensure successful integration between Matillion Data Productivity Cloud and your IBM Netezza database, you must configure your network to allow incoming connections based on your deployment type:

• Full SaaS agent configuration:
  You must allow incoming connections to your IBM Netezza database from the IP addresses used by Matillion Data Productivity Cloud’s SaaS agents. For a complete and updated list of these IP addresses, please refer to the following documentation: Allowing IP Addresses.

• Hybrid SaaS deployment:
  If you are using a Hybrid SaaS deployment, configure your firewall or access control lists to accept connections from your own virtual private cloud (VPC). To verify and troubleshoot your network setup, you can use the following utilities: Check Network Access.

Additionally, if the IBM Netezza database is referenced using a DNS hostname (rather than an IP address), ensure that your Full SaaS or Hybrid SaaS agent can resolve the DNS name correctly. Proper DNS resolution is required for the connection to the IBM Netezza database to succeed.

Querying Data from an IBM Netezza Database

IBM Netezza is a data warehouse appliance designed for high-performance analytics and scalable query processing. Accessing and extracting data from Netezza typically involves standard SQL queries. Below are best practices and sample queries to help you extract and move data—especially in environments where data migration (e.g., to Amazon Redshift) is required.

Example Netezza Queries

Basic SELECT Statement

SELECT *
FROM customer
WHERE signup_date > '2023-01-01';

Selecting Specific Columns

SELECT customer_id, first_name, last_name, balance
FROM accounts
WHERE balance > 1000.0;

Joining Tables

SELECT o.order_id, c.customer_name, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'SHIPPED';

Aggregation Example

SELECT product_id, COUNT(*) AS total_orders
FROM order_items
GROUP BY product_id
HAVING COUNT(*) > 10;

Datatype Conversion Between Netezza and Redshift

When transferring data from Netezza to Amazon Redshift, be aware that some datatypes do not match exactly—automatic conversion may occur. Common scenarios:

Note: Always verify columns for correct mapping, especially for data such as dates, decimals, or large text fields.

Initial and Incremental Loads

The efficient data replication pattern involves two main phases:

1. Initial Load

• The Database Query component queries the entire dataset without a filter.
• Purpose: To perform a complete "one-time" extraction of all data.
• Example:

SELECT *
    FROM transactions;

2. Incremental Loads

• The same Database Query component is used, but with a filter (WHERE clause) to select only new or updated rows since the last load.
• Purpose: To efficiently synchronize only changed data on a periodic basis (e.g., daily loads).
• Example:

SELECT *
    FROM transactions
    WHERE updated_at > '2024-06-01 00:00:00';

• The value in the filter clause (e.g., the latest

  updated_at

  timestamp) is usually parameterized based on the last successful transfer.

For additional implementation details and best practices, see: Incremental Load Data Replication Strategy on Matillion Exchange

Tip: Using a consistent Database Query component and adjusting only the WHERE clause allows for easy automation of both initial and incremental data loads.

Data Integration Architecture

Loading data in advance of integration is a key strategy within the ELT (Extract, Load, Transform) architecture, effectively dividing a complex data integration process into two manageable steps. By first loading raw data into Redshift and then transforming it as needed, organizations are able to address the challenges of scale and complexity more efficiently. Data integration invariably requires data transformation to harmonize, cleanse, and enrich data from diverse sources, and this is best achieved through the use of robust data transformation pipelines. A further advantage of the ELT approach is that both data transformation and integration processes occur within the Redshift database itself. This means these operations can leverage the inherent scalability, speed, and on-demand resources of Redshift, eliminating the need for separate or external data processing infrastructure and minimizing additional costs.