Integrate data from Altibase to Snowflake using Matillion

Creating a User in Altibase

To create a user in an Altibase database, you will need appropriate administrative privileges (typically the

SYS

isql

Prerequisites

• Access to an Altibase database instance.
• DBA or sufficient privileges to create users.

Step-by-Step Instructions

1. Connect to the Altibase Server

Connect to your Altibase instance using

isql

bash
isql -u SYS -p <SYS_PASSWORD> -s <SERVER_IP>:<PORT_NUMBER>/<DB_NAME>

Replace

<SYS_PASSWORD>

<SERVER_IP>

<PORT_NUMBER>

<DB_NAME>

2. Create a New User

Use the

CREATE USER

CREATE USER new_user IDENTIFIED BY 'user_password';

• new_user

  : The username you wish to create.

• user_password

  : The password for the new user.

Example:

CREATE USER testuser IDENTIFIED BY 'TestPwd123!';

3. Grant Privileges to the User

After creating a user, assign necessary privileges so that the user can perform required actions.

``` -- Grant connect privilege to allow the user to log in GRANT CONNECT TO testuser;

-- Optionally, grant additional privileges (e.g., CREATE TABLE, SELECT, INSERT) GRANT CREATE TABLE, SELECT, INSERT ON some_table TO testuser; ```

4. Verify the User Creation

You can verify that the user was created by querying the system view

SYSTEM_.USERS

SELECT USER_NAME
  FROM SYSTEM_.USERS
 WHERE USER_NAME = 'TESTUSER';

5. (Optional) Change User Password

To change the password for an Altibase user:

ALTER USER testuser IDENTIFIED BY 'NewTestPwd456!';

Note:
- Password policies (such as minimum length and complexity) may be enforced, depending on the database's security configuration. - Always follow your organization's policies for managing database credentials.

For complete details, consult the Altibase documentation.

Installing the JDBC driver

The Altibase JDBC driver is required to enable connectivity between Matillion Data Productivity Cloud and your Altibase database. Please note that, due to licensing or redistribution restrictions, the Altibase JDBC driver is not included in Matillion Data Productivity Cloud by default. Therefore, you must manually obtain, upload, and install the driver before you can create Altibase-based connections.

Step 1: Download the Altibase JDBC Driver

1. Navigate to the official Altibase product page: https://www.altibase.com/product/.
2. Locate the JDBC driver download section. When choosing a driver, select the Type 4 (pure Java) JDBC driver for optimal performance and compatibility.
3. Carefully read and accept any applicable license agreements before downloading the file.
4. Save the JDBC driver (usually a

   .jar

   file) to a secure, accessible location on your system.

Step 2: Upload and Install the Driver into Matillion Data Productivity Cloud

To complete the driver installation, you must upload the

.jar

Key steps include:

• Accessing your Matillion Agent.
• Following the documented process to upload external JDBC drivers to the agent environment.
• Ensuring that the driver is correctly recognized and appears in the Matillion interface.

Step 3: Configuring and Using the Driver

Once the driver is installed and Matillion Data Productivity Cloud has been restarted (if required), you are ready to configure and use the Altibase connection. Detailed usage instructions, including how to define database connections and compose database queries using your newly installed driver, are available at: Database Query in Data Productivity Cloud.

By following these steps and referencing the official documentation links, you can ensure that the Altibase JDBC driver is properly integrated with Matillion Data Productivity Cloud, enabling you to leverage Altibase as a data source within your Matillion workflows.

Checking network connectivity

Before connecting Matillion Data Productivity Cloud to your Altibase database, you must ensure that your Altibase instance accepts incoming network connections from the required sources, depending on your deployment configuration:

• Full SaaS agent configuration:
  Your Altibase database must allow incoming connections from all of the Matillion Data Productivity Cloud agent IP addresses. These IP addresses are available at the following URL: Matillion Allowed IP Addresses. Make sure to update your firewall rules or network access controls to permit inbound traffic from these addresses.

• Hybrid SaaS deployment:
  If you are using a Hybrid SaaS configuration, your Altibase database should accept incoming connections from the IP address range(s) of your own Virtual Private Cloud (VPC). This typically involves configuring your network firewall or security group rules to permit inbound connections from your VPC. You can find useful network troubleshooting and verification tools here: Check Network Access Utilities.

Additionally, if you specify your Altibase database connection using a DNS name (hostname), ensure that the relevant Matillion agent (either Full SaaS or Hybrid SaaS) is able to resolve this DNS address to the correct IP address. Network connectivity issues can occur if DNS resolution is unavailable or misconfigured.

How to Query Data from an Altibase Database

This guide provides instructions for querying data from an Altibase database, considering best practices for data movement into systems such as Snowflake. It also highlights initial and incremental load patterns and includes sample SQL queries.

1. Sample Altibase Query Patterns

Simple SELECT statement:

SELECT id, name, created_at
FROM customers;

SELECT with WHERE clause:

SELECT order_id, amount
FROM orders
WHERE order_date >= '2024-01-01';

Using aggregate functions:

SELECT status, COUNT(*)
FROM tickets
GROUP BY status;

Joining tables:

SELECT c.id, c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'SHIPPED';

2. Datatype Conversion Between Altibase and Snowflake

When extracting data from Altibase and loading into platforms like Snowflake, pay attention to datatype mappings. Some types may convert as follows:

Note: Always review specific column types to minimize conversion issues and ensure data integrity.

3. Initial and Incremental Data Loads

The recommended pattern is to use a once-off initial load followed by incremental loads. Both use the same Database Query component (in tools like Matillion).

A. Initial Load

• Purpose: Copies all relevant records.
• Query Example (No filter clause):

  SELECT *
      FROM orders;

B. Incremental Load

• Purpose: Loads only new or updated records since the last load.
• Query Example (Contains filter for incremental logic):

  SELECT *
      FROM orders
      WHERE last_updated > :last_run_timestamp;

  Here,

  :last_run_timestamp

  is replaced at runtime with the value from the previous load.

For more details on incremental strategies, see: Incremental Load or Data Replication Strategy

Key Points: - Use selective WHERE clauses for incremental loads to optimize processing time and resource usage. - Ensure queries are compatible with Altibase SQL syntax. - Validate data conversion rules, especially when moving between different database systems (e.g., Altibase to Snowflake).

Data Integration Architecture

Loading data into Snowflake in advance of integration is an effective way to manage complexity by splitting the process into two distinct steps: loading and transformation. This "divide and conquer" approach is a hallmark advantage of the ELT (Extract, Load, Transform) architecture. Unlike traditional ETL, where transformations occur before data enters the target system, ELT allows raw data to be rapidly ingested and then processed within Snowflake. Effective data integration necessarily requires data transformation, and data transformation pipelines provide a robust framework for organizing and sequencing these operations. A further benefit of the ELT architecture with Snowflake is that these transformations and integrations take place inside the target database itself. This not only ensures high performance, on-demand scalability, and the ability to process large volumes of data efficiently, but also eliminates the need for dedicated transformation servers or third-party processing platforms—meaning there is no separate infrastructure to provision or maintain, nor any extra associated costs.