Integrate data from FairCom c-tree to Amazon Redshift using Matillion

Creating a User in FairCom c-treeACE

This guide outlines the process for creating a user (identity) in a FairCom c-treeACE database using SQL.

Prerequisites

• Valid administrative access to the c-treeACE SQL Server (e.g., the

  ADMIN

  user).
• Familiarity with connecting to c-treeACE using a SQL client.

Steps to Create a User

1. Connect to the Server
   Use your preferred SQL client to connect to the c-treeACE database server as an administrative user.

2. Execute the CREATE USER Statement

Use the following SQL syntax to create a new user:

CREATE USER new_username IDENTIFIED BY 'new_password';

new_username

new_password

Example:

CREATE USER analytics_user IDENTIFIED BY 'Comp!exP@ss2024';

1. Grant Privileges (Optional)
   After creating the user, you may assign privileges. For example, to grant all privileges on a specific database:

GRANT ALL ON mydatabase.* TO analytics_user;

Or, to grant specific privileges:

GRANT SELECT, INSERT ON mydatabase.sales TO analytics_user;

Notes

• User names are case-insensitive unless quoted.
• Passwords can be case-sensitive and should follow your security policies.
• The

  CREATE USER

  command is available in c-treeACE SQL beginning with version 10 and later.

For further details, refer to the FairCom c-treeACE SQL Reference Guide.

Installing the JDBC Driver

This guide outlines the steps required to download, install, and register the FairCom c-treeACE JDBC driver for use with Matillion Data Productivity Cloud. Please note that, at the time of writing, this JDBC driver is not included within the Matillion platform by default due to licensing or redistribution restrictions. You must obtain and install the driver manually.

1. Download the JDBC Driver

To acquire the FairCom c-treeACE JDBC driver:

1. Visit the official product page for c-treeACE at: https://www.faircom.com/products/ctreeACE/
2. Browse the download options and locate the JDBC driver.
3. Prefer the "Type 4" JDBC driver option, if available, as it offers optimal compatibility by operating as a pure Java implementation without requiring native libraries.
4. Complete registration or authentication steps if prompted, and download the JDBC driver package to your local system. The driver will typically be provided as a JAR file.

2. Install the JDBC Driver into Matillion Data Productivity Cloud

After downloading the JDBC driver, you will need to upload it to your Matillion Data Productivity Cloud Agent so that it can be used in database integrations.

Follow the Matillion documentation for uploading external drivers: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

Typical steps include:

• Accessing your Matillion Agent interface.
• Navigating to the section for managing external drivers.
• Uploading the downloaded JDBC driver JAR file to the correct location, which is usually a specified directory dedicated for external dependencies.
• Restarting or refreshing your Matillion Agent to ensure that the new driver is recognized and loaded.

3. Next Steps: Database Configuration and Usage

Once the JDBC driver has been installed, you can proceed to configure connections and utilize c-treeACE databases within your workflows.

Follow usage instructions provided in the Matillion documentation: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

This documentation will assist you in creating data queries, establishing source and target connections, and integrating c-treeACE data into your Matillion Data Productivity Cloud projects.

Checking network connectivity

To successfully connect Matillion Data Productivity Cloud to your FairCom c-treeACE database, you must ensure the database allows incoming connections from the appropriate sources, depending on your deployment configuration:

• Full SaaS Agent Configuration:
  Allow incoming connections to your FairCom c-treeACE database from the Matillion Data Productivity Cloud SaaS agents. You can find the list of source IP addresses that need to be allowed in the database firewall or network security group at: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS Deployment:
  The Hybrid SaaS agent runs within your own virtual private cloud (VPC). In this case, ensure the FairCom c-treeACE database’s security rules allow incoming connections from the private IP ranges of your VPC. To verify network connectivity from your VPC, refer to the utilities available at: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

Additionally, if you are referencing your FairCom c-treeACE database by hostname (DNS), you must also ensure the Matillion agent—whether Full SaaS or Hybrid SaaS—can resolve the DNS address correctly. This typically requires the appropriate DNS service to be accessible from wherever the agent runs.

Querying Data from FairCom c-treeACE Database

This guide explains how to query data from a FairCom c-treeACE database, with consideration for type conversion (such as for Redshift), and describes best practices for both initial and incremental data loads using a Database Query component.

Example c-treeACE SQL SELECT Queries

FairCom c-treeACE supports ANSI SQL for querying data. Here are some sample queries:

``` -- Selecting all columns from the "orders" table SELECT * FROM orders;

-- Selecting specific columns SELECT order_id, customer_id, order_date, total_amount FROM orders;

-- Filtering results by date SELECT * FROM orders WHERE order_date >= '2024-01-01';

-- Aggregating data SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id; ```

Datatype Conversion Considerations

When moving data from FairCom c-treeACE to Amazon Redshift, be aware of potential datatype conversions. For example:

• c-treeACE

  CHAR

  ,

  VARCHAR

  ↔️ Redshift

  VARCHAR

• c-treeACE

  INTEGER

  ,

  SMALLINT

  ,

  BIGINT

  ↔️ Redshift

  INTEGER

  ,

  BIGINT

• c-treeACE

  REAL

  ,

  FLOAT

  ↔️ Redshift

  FLOAT8

• c-treeACE

  DATE

  ,

  TIME

  ,

  TIMESTAMP

  ↔️ Redshift

  DATE

  ,

  TIME

  ,

  TIMESTAMP

• c-treeACE

  BLOB

  /

  CLOB

  (character large object) ↔️ Redshift does not support these directly—consider encoding or transformation

Review all column types before migrating for accurate conversion.

Recommended Data Loading Pattern

Pattern Overview

• Initial Load: Extract all existing data from c-treeACE. No filter is applied in the query.
• Incremental Loads: Only new or changed data is queried. A filter clause is added to retrieve records inserted or updated since the last load.

Both patterns should use the same Database Query component (as recommended in incremental load strategies, see Matillion documentation).

Initial Load Example

-- Full table copy, no WHERE clause
SELECT * FROM orders;

Incremental Load Example

Suppose the

orders

last_modified

-- Only pull records modified since the last extraction
SELECT * FROM orders
WHERE last_modified > '2024-06-01 00:00:00';

You'll typically parameterize the cutoff timestamp for automated ETL processes.

References

• For more information on incremental loads and best practices see: Matillion Incremental Load Data Replication Strategy

Note: Always validate your data pipeline by confirming both full and incremental queries produce expected results after conversion and loading.

Data Integration Architecture

Loading data into Amazon Redshift in advance of integration is an effective way to tackle data integration challenges by splitting the process into two distinct steps: first loading, then transforming and integrating. This “divide and conquer” tactic is a hallmark of the ELT (Extract, Load, Transform) architecture and offers significant practical benefits. Since successful data integration always involves some form of data transformation—such as cleaning, joining, filtering, or aggregating—the optimal approach is to use data transformation pipelines that automate these steps. A further advantage of the ELT model is that data transformation and integration activities are performed directly within the target Redshift database itself. This approach leverages Redshift’s inherent scalability and parallel processing capabilities, making transformation both fast and available on-demand according to usage needs. Additionally, by carrying out processing inside Redshift, organizations avoid the costs and operational overhead of maintaining separate data processing platforms or infrastructure outside the database.