Integrate data from Altibase to Databricks using Matillion

Creating a User in Altibase Database

To create a new user in an Altibase database, you must have sufficient privileges (typically DBA or SYSDBA). The following steps outline the procedure for creating a user, assigning a password, and granting basic privileges.

1. Connect to the Altibase Database

First, connect to your Altibase instance using (

isql

bash
isql -u sys -p <SYS_PASSWORD> -s <SERVER_IP> -n <PORT>

• <SYS_PASSWORD>

  : replace with the SYS user's password

• <SERVER_IP>

  : the database server address

• <PORT>

  : Altibase port (default is 20300)

2. Create a User

Use the following SQL command to create a new user. Replace

<username>

<password>

CREATE USER <username> IDENTIFIED BY <password>;

Example:

CREATE USER alice IDENTIFIED BY SecureP@ssw0rd;

3. Grant Privileges

At minimum, the new user will need the

CONNECT

GRANT CONNECT TO <username>;

Example:

GRANT CONNECT TO alice;

To allow user creation of tables and other resources:

GRANT RESOURCE TO <username>;

4. (Optional) Grant DBA Privileges

If the user needs database administrator rights:

GRANT DBA TO <username>;

Note: This should only be granted to trusted users.

5. Verify User Creation

You can verify that the user has been created by querying the system tables:

SELECT USERNAME FROM SYSTEM_.USERS WHERE USERNAME = '<username>';

Example:

SELECT USERNAME FROM SYSTEM_.USERS WHERE USERNAME = 'ALICE';

Example: Full Script

CREATE USER alice IDENTIFIED BY SecureP@ssw0rd;
GRANT CONNECT, RESOURCE TO alice;

Replace usernames and passwords as required for your security policies.

Installing the JDBC Driver

At the time of writing, the JDBC driver for Altibase is not included by default in Matillion Data Productivity Cloud, due to licensing or redistribution restrictions. You must manually download and install the appropriate driver in order to connect to an Altibase database.

1. Download the Altibase JDBC Driver - Visit https://www.altibase.com/product/. - Search for and download the Altibase JDBC driver. Preferably, obtain the Type 4 JDBC driver, as this is a pure Java driver and generally the most suitable choice for integration with cloud systems like Matillion.

2. Review Compatibility - Ensure the version of the JDBC driver matches both your Altibase database version and the requirements of your Matillion Data Productivity Cloud deployment.

3. Upload the Driver to Matillion Data Productivity Cloud - Matillion requires manual uploading of external JDBC drivers in some scenarios. Instructions for this process can be found here: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/. - Follow the documented process, which typically involves accessing the user interface or your agent installation, uploading the downloaded JDBC driver JAR file, and ensuring the platform recognises the new driver.

4. Configure and Use the Driver - After uploading, configure your connection settings in Matillion to use the new JDBC driver for Altibase. - For detailed usage and configuration instructions, please refer to the usage documentation: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/. - Ensure that authentication, endpoints, and any required parameters specific to Altibase are correctly specified.

By following these steps and referencing the linked documentation, you can successfully enable Altibase connectivity in your Matillion environment.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your Altibase database, you must verify that the database allows incoming network connections according to your deployment configuration:

• Full SaaS agent configuration:
  The Altibase database must be configured to accept incoming connections from the IP addresses used by Matillion Data Productivity Cloud's SaaS agents. The current list of required IP addresses can be found at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/
  Ensure these IP addresses are added to your database or network firewall allowlist.

• Hybrid SaaS deployment:
  If you are using a Hybrid SaaS deployment, your Altibase database must allow incoming connections from your own virtual private cloud (VPC). You can use the network access check utilities provided at:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/
  to help validate proper connectivity from your VPC environment.

Additionally, if you are connecting to the Altibase database using a DNS name rather than a fixed IP address, make sure that your Full SaaS or Hybrid SaaS agent is able to resolve the DNS name successfully to the correct database endpoint. Proper DNS resolution is required for establishing network connectivity.

Querying Data from an Altibase Database

Running SQL Queries with Altibase

Altibase uses standard SQL for querying data. Below are examples of typical SELECT statements:

1. Basic SELECT Statement

SELECT * FROM customers;

2. Filtering Data

SELECT id, name FROM users WHERE status = 'active';

3. Ordering Results

SELECT order_id, total FROM orders ORDER BY total DESC;

4. Aggregation

SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

Considerations for Datatype Conversion

When migrating or integrating data between Altibase and Databricks, be aware that automatic datatype conversion may occur. For example: - TEXT or VARCHAR in Altibase may map to STRING in Databricks. - NUMBER or INTEGER types may map to INT or BIGINT. - DateTime fields may require conversion to TIMESTAMP.

Always review and validate field types when designing your ETL process.

Patterns for Data Loading

A common pattern when loading data from Altibase is to use two phases: 1. Initial (Full) Load 2. Incremental Load

This approach ensures efficiency by syncing the entire dataset once and then updating only with new or changed data.

Both load types typically use the same "Database Query" component in your ETL pipeline.

Initial (Full) Load Example

For a full load, you query the entire table, with no filter clause:

SELECT * FROM transactions;

Incremental Load Example

For incremental loads, use a filter—typically on a column such as a timestamp or an incrementing ID:

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

Replace

modified_time

For further details on incremental load strategies, see Incremental Load - Data Replication Strategy.

Data Integration Architecture

Loading data in advance of integration offers a practical "divide and conquer" approach by separating the integration process into two manageable steps: first, data is loaded into the target environment, and second, it is integrated and transformed as needed. This separation is a key advantage of the Extract, Load, and Transform (ELT) architecture. Effective data integration invariably requires reliable data transformation routines, and the most robust method for accomplishing this is through purpose-built data transformation pipelines. In the ELT paradigm, these transformation and integration processes are performed directly within the target environment—in this case, the Databricks database. This offers substantial benefits: data transformation and integration become fast, scalable, and available on-demand, without the need for additional investment in dedicated data processing infrastructure.