Integrate data from Firebird SQL to Amazon Redshift using Matillion

Creating a User in Firebird SQL

To create a user in Firebird SQL database, you typically need to be connected as a user with sufficient administrative privileges (often SYSDBA). The process slightly differs between Firebird versions, most notably between 2.5 (and earlier) and 3.0 (and later).

For Firebird 3.0 and Later

Firebird 3.0 introduced new SQL commands for user management. The preferred way to create users is with the

CREATE USER

Example: Creating a New User

CREATE USER new_user PASSWORD 'strongpass123';

• new_user

  : The username of the new account (case-insensitive, unless quoted).

• 'strongpass123'

  : The password for the new user (must meet password criteria).

Additional Options

You can specify additional options such as the user's first name, last name, etc.:

CREATE USER new_user
PASSWORD 'strongpass123'
FIRSTNAME 'Jane'
LASTNAME 'Doe'
ACTIVE;

Executing the Command

You can execute this SQL statement using your preferred SQL tool:

• isql

  , the Firebird interactive SQL utility.
• Any Firebird-compatible database administration tool.

Example with isql:

ell
isql -user SYSDBA -password masterkey
SQL> CREATE USER new_user PASSWORD 'strongpass123';
SQL> QUIT;

For Firebird 2.5 and Earlier

User management is not handled with SQL, but with the command-line tool

gsec

Example: Creating a User with gsec

1. Launch

   gsec

   :

ell
   gsec -user SYSDBA -password masterkey

1. Add a new user:

gsec
   add new_user -pw strongpass123

1. Confirm the user was created:

gsec
   display

1. Exit

   gsec

   :

gsec
   quit

Notes

• The

  SYSDBA

  user and its password (

  masterkey

  by default) are required to manage users.
• Users created are global to the Firebird server, not specific to a single database.
• Always follow your organization's password security guidelines.

Reference

• Firebird Documentation – User Management

Installing the JDBC Driver

At the time of writing, the Firebird SQL JDBC driver is not included by default with Matillion Data Productivity Cloud installations. This absence is due to licensing and redistribution considerations. To connect Matillion to a Firebird database, you must manually download and install the appropriate JDBC driver.

Step 1: Download the Firebird JDBC Driver

1. Navigate to the official Firebird JDBC Driver download page: https://www.firebirdsql.org/en/jdbc-driver/.
2. Look for the most recent version of the Type 4 "Jaybird" JDBC driver. Type 4 drivers are generally preferred, as they are pure Java implementations and do not require native libraries.
3. Download the appropriate

   .jar

   file for your environment.

Step 2: Prepare the Driver for Use

• Do not extract the contents of the

  .jar

  file; it must be uploaded to Matillion in its packaged form.
• Ensure you have reviewed any licensing notes or compatibility requirements on the Firebird website before proceeding.

Step 3: Upload the Driver to Matillion Data Productivity Cloud

• Follow the official documentation for uploading external drivers to Matillion, which is available here: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/
• These instructions will walk you through the process of adding the

  .jar

  file to your Matillion Agent or environment so it becomes available for use in database connections.

Step 4: Configure and Use the Driver

• After the driver is installed, consult the Matillion Data Productivity Cloud documentation for database query tasks: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/
• This resource explains how to configure and test database connections within Matillion, select the appropriate driver, and construct queries as needed.

By following these steps and leveraging the official documentation, you'll be able to incorporate the Firebird SQL JDBC driver into your Matillion setup, enabling robust integration with Firebird databases.

Checking network connectivity

To establish a successful connection between Matillion Data Productivity Cloud and your Firebird SQL database, you must ensure that the database allows incoming connections for your chosen deployment configuration:

• Full SaaS Agent Configuration:
  You need to allow incoming connections to your Firebird SQL database from the IP addresses used by the Matillion Data Productivity Cloud Full SaaS agents. The current list of required IP addresses can be found here:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS Deployment:
  For Hybrid SaaS deployments, incoming connections should be allowed from your own virtual private cloud (VPC). You can use the network testing utilities at the following link to help you validate network access and troubleshoot connectivity:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if your Firebird SQL database is referenced using a DNS hostname (rather than a direct IP address), ensure that the Full SaaS or Hybrid SaaS agent can successfully resolve the DNS address to connect to the database.

Querying Data from a Firebird SQL Database

This guide provides instructions for querying data from a Firebird SQL database, focusing on using SQL

SELECT

Querying Data with Firebird SQL

Firebird SQL uses standard SQL syntax with some dialect-specific differences. Here are basic and advanced

SELECT

``` -- Query all columns and rows from a table SELECT * FROM employee;

-- Query specific columns SELECT id, first_name, last_name, hire_date FROM employee;

-- Query with filtering (WHERE clause) SELECT * FROM orders WHERE order_date > '2024-01-01';

-- Aggregate function example SELECT country, COUNT(*) AS total_sales FROM sales GROUP BY country;

-- Join queries SELECT c.customer_id, c.customer_name, o.order_total FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_total > 1000; ```

Datatype Conversion: Firebird SQL to Redshift

When migrating data from Firebird SQL to Amazon Redshift, you may encounter type differences. Some common conversions:

BLOB SUB_TYPE 1

VARCHAR(max)

INTEGER

INTEGER

SMALLINT

SMALLINT

BIGINT

BIGINT

FLOAT

FLOAT4

DOUBLE PRECISION

FLOAT8

DECIMAL

NUMERIC

DECIMAL

DATE

DATE

TIME

TIME

TIMESTAMP

TIMESTAMP

Always check how each Firebird type maps to Redshift and apply appropriate conversions (e.g., cast

BLOB

VARCHAR

SELECT CAST(blob_column AS VARCHAR(32765)) AS text_blob FROM documents;

Loading Patterns: Initial and Incremental Loads

For data extraction, the recommended best pattern is to:

1. Perform a once-off initial load: Extract the entire dataset from Firebird.
2. Follow up with incremental loads: Extract only new or changed data from Firebird.

You should use the same Database Query component for both extract patterns, adjusting only the SQL query's filter.

Initial Load Example

During an initial load, there is no filter clause—all records are fetched.

SELECT * FROM transactions;

Incremental Load Example

For incremental loads, add a filter to select only new or changed rows, typically using a timestamp or auto-incrementing ID column.

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

More on this practice is described in Matillion's guide on Incremental Load Data Replication Strategy.

References
- Firebird SQL Documentation
- Amazon Redshift Data Types
- Matillion Exchange: Incremental Load Strategies

Data Integration Architecture

Loading data into Amazon Redshift in advance of integration is a classic "divide and conquer" approach, widely recognized as an advantage of the Extract, Load, and Transform (ELT) architecture. By splitting the process into two distinct steps—loading, then integrating—you isolate the challenge of bringing data into Redshift from that of transforming and integrating it. Effective data integration depends on robust data transformation, and the most reliable way to accomplish this is through structured data transformation pipelines, which automate and standardize complex transformations. A further benefit of the ELT model is that all transformation and integration logic is executed directly within the target Redshift database. This results in fast, on-demand, and highly scalable performance, while simultaneously eliminating the need for costly, dedicated external data processing infrastructure.