Integrate data from InterBase to Amazon Redshift using Matillion

Creating an Identity in InterBase: User Creation Instructions

To manage access and security within InterBase, database administrators often need to create new user accounts (identities) at the server level. The following instructions outline how to create a user using SQL scripts with the CREATE USER statement. These steps apply to InterBase XE and newer versions.

Prerequisites

• Ensure you are connected as a user with SYSDBA privileges.
• Use an administrative tool (such as IBConsole, isql, or a third-party client) that allows you to execute SQL statements.

1. Connect to the InterBase Server

If using

isql

isql -user SYSDBA -password masterkey

Replace

masterkey

with the actual password if it has been changed.

2. Create a New User

You can create a new user with the following SQL command:

CREATE USER myuser PASSWORD 'securePass123'
  FIRSTNAME 'Jane'
  MIDDLENAME 'Q'
  LASTNAME 'Public';

• Replace

  myuser

  with the desired username.
• Choose a strong password (at least 8 characters recommended).

• FIRSTNAME

  ,

  MIDDLENAME

  , and

  LASTNAME

  are optional, but can assist with user identification and auditing.

3. Grant Privileges (Optional)

By default, a new user has no access to any databases or objects. You must explicitly grant privileges after user creation (within the context of a specific database connection), for example:

GRANT SELECT, INSERT ON TABLE employees TO myuser;

Privilege management occurs per database.

4. Committing Changes

After executing the

CREATE USER

COMMIT;

5. Verify User Creation

You can list all users (if you have the appropriate privileges):

SHOW USERS;

Note:

CREATE USER

ALTER USER

DROP USER

Installing the JDBC Driver

At the time of writing, the JDBC driver for the InterBase database is not bundled by default with the Matillion Data Productivity Cloud platform. This is due to licensing and redistribution restrictions, which prevent Matillion from distributing the driver directly. Therefore, users who wish to connect to InterBase will need to manually obtain and install the appropriate driver.

To proceed with installation, follow the steps outlined below:

1. Download the InterBase JDBC Driver
2. Visit the official InterBase website: https://www.embarcadero.com/products/interbase
3. On the download page, look specifically for a Type 4 JDBC driver, as this type is most suitable for use with Matillion and does not require native code on the client or server.

4. Download the latest stable version of the Type 4 JDBC driver jar file to your local machine. Ensure you comply with any license terms and agreements as specified by Embarcadero.

5. Install the JDBC Driver into Matillion Data Productivity Cloud

6. Because the driver is not installed by default, you must upload the downloaded jar file to Matillion.
7. Refer to the official Matillion documentation for step-by-step guidance on uploading external drivers: Matillion: Uploading External Drivers.

8. Follow these instructions carefully to ensure the driver is correctly registered and available within your Matillion environment. This process may require administrative permissions.

9. Configure and Use the InterBase JDBC Driver

10. After uploading and registering the driver, you can begin using it to connect to InterBase databases from within Matillion projects.
11. Detailed instructions on configuring a database connection and issuing queries through the Matillion interface can be found here: Matillion: Database Query Usage.

If you encounter any issues related to connectivity or compatibility, consult both the InterBase JDBC driver documentation and the Matillion support resources for troubleshooting guidance.

Checking network connectivity

To ensure reliable communication between the Matillion Data Productivity Cloud and your InterBase database, you must allow incoming network connections to the InterBase instance, according to your deployment configuration:

• Full SaaS Agent Configuration:
  The InterBase database must permit incoming connections from the IP addresses used by Matillion's Full SaaS agent. A list of the current IP addresses is available here: Matillion SaaS Allowed IP Addresses. Be sure to add these addresses to your InterBase server's firewall or allowlist configuration as appropriate.

• Hybrid SaaS Deployment:
  If you are deploying using a Hybrid SaaS agent, configure your InterBase database to allow incoming connections from the IP addresses within your own Virtual Private Cloud (VPC). To help verify network access in this scenario, Matillion provides utilities that can be found here: Check Network Access Utilities.

In addition, if your InterBase database is referenced using a DNS name rather than an IP address, ensure that the Full SaaS or Hybrid SaaS agent is able to resolve the hostname to the correct IP address. Proper DNS resolution is essential for establishing a successful connection.

Querying Data from an InterBase Database

This guide explains how to query data from an InterBase database using SQL, highlights potential datatype conversion issues when moving data to Amazon Redshift, and describes the recommended pattern for initial and incremental data loads using the Database Query component.

InterBase Query Examples

To retrieve data from an InterBase database, use standard SQL SELECT statements. For example:

``` -- Select all columns from a table SELECT * FROM employees;

-- Select specific columns SELECT employee_id, first_name, last_name FROM employees;

-- Select rows with a specific filter SELECT * FROM orders WHERE order_date >= '2024-01-01';

-- Aggregate data SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id; ```

Datatype Conversions: InterBase to Redshift

• When transferring data from InterBase to Amazon Redshift, some datatype conversions may be required.
  • For example:
    • InterBase

      VARCHAR

      → Redshift

      VARCHAR

    • InterBase

      SMALLINT

      /

      INTEGER

      → Redshift

      SMALLINT

      /

      INTEGER

      /

      BIGINT

    • InterBase

      TIMESTAMP

      → Redshift

      TIMESTAMP

  • Some InterBase types (e.g.,

    BLOB

    ,

    ARRAY

    ) may not have direct equivalents in Redshift. Perform output casting or transformation if necessary.
• Review field mappings and test for edge cases during datatype mapping to minimize data loss or conversion errors.

Load Patterns: Initial and Incremental Loads

The recommended best practice for synchronizing data between InterBase and Redshift is:

1. Initial Load:
2. Query all existing data from the source table, without any filter clauses.
3. Example SQL (used in the Database Query component):

   SELECT * FROM sales;

4. The resulting full dataset is then loaded to Redshift.

5. Incremental Loads:

6. After the initial sync, capture and load only new or changed records by adding a filter clause (such as a date or incrementing key).
7. Example SQL (for incremental loads):

   SELECT * FROM sales
        WHERE sale_timestamp > '{{last_loaded_timestamp}}';

8. Keep track of the maximum value of the incrementing field (

   sale_timestamp

   in this case) to support efficient ongoing syncs.

Both initial and incremental loads can be configured with the same Database Query component—differentiating them only by the presence (or absence) of filter criteria.

More Information:
Read about incremental load and data replication in this article on Matillion Exchange.

Data Integration Architecture

Loading data into Amazon Redshift ahead of integration is a fundamental approach to simplifying the complexities of data integration, effectively splitting the process into two distinct and manageable steps. This is a core benefit of the ELT (Extract, Load, Transform) architecture, which allows raw data to be ingested rapidly and prepared for transformation within the target system. Data integration inherently involves transforming data from diverse sources into a unified format, and this process is best managed through robust data transformation pipelines. By performing these transformation and integration tasks directly inside Redshift, the ELT architecture offers further advantages: transformations are executed close to the data, making them fast, scalable, and available on-demand without the need for separate, costly processing infrastructure. This not only streamlines workflow management but also leverages Redshift’s processing power, improving both performance and cost-efficiency.