Integrate data from GBase to Databricks using Matillion

Creating a User in GBase Database

To create a new user in a GBase database, you must have sufficient administrative privileges (such as the

dba

1. Connect to the GBase Database

First, log in to your GBase server using a client tool such as

isql

gsql

ell
gsql -U dba -d DB_NAME -W

Replace

dba

with your administrator username, and

DB_NAME

with your database name.

2. Create the User

The SQL syntax for creating a user in GBase is as follows:

CREATE USER username IDENTIFIED BY 'password';

Example

To create a user named

testuser

Test@1234

CREATE USER testuser IDENTIFIED BY 'Test@1234';

3. Grant Privileges (Optional)

By default, a new user may have minimal privileges. To allow the user to connect to or work with particular objects, you need to grant the appropriate permissions.

Example: Grant All Privileges on a Database

GRANT ALL PRIVILEGES ON DATABASE DB_NAME TO testuser;

Example: Grant Specific Privileges on a Table

GRANT SELECT, INSERT ON tablename TO testuser;

4. Verify the User Account (Optional)

You can check the user list with the following query:

SELECT * FROM sysusers WHERE username = 'testuser';

or use the system or information schema views, depending on your GBase version.

Note:
The

CREATE USER

and associated privilege management commands may vary slightly based on your GBase version. Refer to your specific GBase documentation for details on supported features and syntax.

Installing the JDBC driver

At the time of writing, the JDBC driver for GBase is not included by default in the Matillion Data Productivity Cloud due to licensing or redistribution restrictions. Therefore, before you can use GBase as a data source or destination within Matillion, you must manually obtain and install the appropriate JDBC driver.

Downloading the GBase JDBC Driver

To get started, download the GBase JDBC driver from the official source. The download link is:

-

When selecting the driver, make sure to choose the Type 4 JDBC driver, as it is a pure Java implementation and is preferred for compatibility and ease of deployment in the Matillion Data Productivity Cloud environment. The downloaded driver will usually be provided as a

.jar

Uploading the Driver to Matillion Data Productivity Cloud

Once you have obtained the

.jar

Matillion provides detailed, vendor-agnostic instructions for uploading external JDBC drivers to your agent at the following link:
https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

Follow these steps closely: - Access the agent to which you want to add the JDBC driver. - Follow the documentation’s guidance for navigating to the driver upload interface and uploading your

.jar

Configuring and Using the GBase Connection in Matillion

After successfully installing the driver, you will be able to configure database components in your Matillion projects to utilize the GBase JDBC connection. For step-by-step guidance on how to create and use a database query component with your newly installed GBase driver, refer to the official usage instructions:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

This documentation will help you establish connections, set credentials, and design queries leveraging your agent’s new connectivity to GBase.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your GBase database, you must configure the GBase database to allow incoming connections based on your deployment type:

• Full SaaS Agent Configuration:
  Permit incoming connections from the IP addresses listed at Matillion allowed IP addresses. This allows the Full SaaS agent to communicate with your GBase database securely.

• Hybrid SaaS Deployment:
  Allow incoming connections from the network range of your own Virtual Private Cloud (VPC), as this is where your Hybrid SaaS agents will run. To help verify and troubleshoot network access, you can use tools provided at Matillion network check utilities.

Additionally, if your GBase database is referenced by a DNS hostname, ensure that either the Full SaaS or Hybrid SaaS agent—depending on your deployment—can successfully resolve the DNS address to reach the database. Proper DNS resolution is essential for establishing and maintaining the connection.

Querying Data from a GBase Database

This guide provides instructions and best practices for querying data from a GBase database, with an emphasis on interoperability with platforms like Databricks and strategies for efficient data ingestion.

Example GBase SQL SELECT Queries

Below are common SQL examples for referencing GBase tables and retrieving data:

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

-- Select specific columns with filtering SELECT customer_id, customer_name, email FROM customers WHERE registration_date >= '2024-01-01';

-- Aggregation with grouping SELECT product_category, COUNT(*) AS num_sales FROM sales WHERE sale_date BETWEEN '2024-05-01' AND '2024-05-31' GROUP BY product_category;

-- Joining two tables SELECT o.order_id, o.order_date, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_status = 'SHIPPED'; ```

Data Type Conversion Between GBase and Databricks

When transferring data from GBase to Databricks (for example, via Matillion or similar ETL tools), data type conversion may occur. Some points to consider:

• Numeric Types: GBase and Databricks both support common types such as

  INTEGER

  ,

  FLOAT

  , and

  DECIMAL

  , but precision/scale differences can occur.
• Strings: GBase’s

  VARCHAR

  and

  CHAR

  generally map to Databricks'

  STRING

  type.
• Date/Time: GBase types such as

  DATE

  ,

  TIME

  , and

  TIMESTAMP

  might require formatting or conversion when loaded into Databricks.
• Binary/Blob: These types may not be directly transferrable or require base64 encoding.

Be sure to review the destination schema in Databricks and cast or convert columns as needed within your SELECT statements.

Data Loading Patterns: Initial and Incremental Loads

The recommended approach for ingesting GBase data into analytic platforms like Databricks is to:

1. Perform a once-off initial full load.
2. Switch to regular incremental loads capturing only new or updated data.

Always use the Database Query component for both workflows for consistency.

Initial Load (Full Extract)

• Extracts all rows from the GBase source table.
• No filter (WHERE clause) is used in this pattern.

-- Example: Initial load, full table extract
SELECT * FROM sales;

Incremental Load (Delta Extract)

• Extracts only new or modified records since the previous load.
• Requires a filter clause (often on a timestamp or surrogate key).

-- Example: Incremental load, filtering on last_modified_time
SELECT *
FROM sales
WHERE last_modified_time >= '2024-06-01 00:00:00';

Tip: Store the timestamp or incremental key value after each load, and refer to it in the next incremental filter.

For more on incremental loading best practices for replication strategy, see the Matillion Incremental Load Article.

Data Integration Architecture

Loading data in advance of integration exemplifies the "divide and conquer" principle by splitting the workflow into two manageable stages: extraction and subsequent loading of raw data, followed by transformation and integration. This modular approach is a hallmark advantage of the ELT (Extract, Load, Transform) architecture, as it allows organizations to decouple complex data preparation steps. Effective data integration necessitates transforming raw data into usable formats, which is best achieved through robust data transformation pipelines designed to automate, orchestrate, and validate each transformation step. A further advantage of ELT rests in performing these data transformation and integration tasks natively within the target Databricks database. By leveraging Databricks’ processing capabilities directly, organizations benefit from fast, on-demand, and highly scalable data operations, all without the need for—and costs associated with—additional, external data processing infrastructure.