Integrate data from YugabyteDB to Databricks using Matillion

Creating a User in YugabyteDB

YugabyteDB is a distributed SQL database compatible with the PostgreSQL wire protocol. Therefore, user and role management is performed using standard PostgreSQL statements such as

CREATE ROLE

CREATE USER

Prerequisites

• Ensure you have the appropriate administrative privileges, typically with the

  ysqlsh

  shell or a suitable PostgreSQL-compatible client.
• Connection to your YugabyteDB cluster is required.

Step 1: Connect to YugabyteDB

Use the

ysqlsh

ysqlsh -h <hostname> -p <port> -U <admin_user> -d <database>

Replace

<hostname>

<port>

<admin_user>

<database>

Step 2: Create a User

You can create a user using the following SQL command:

CREATE USER username WITH PASSWORD 'your_password';

Example:

CREATE USER app_user WITH PASSWORD 'S3cureP@ss!';

This command creates a new user named

app_user

Step 3: Assign Privileges (Optional)

To enable the new user to connect to specific databases or perform actions on database objects, grant appropriate privileges. For example, to allow usage of the database, you might use:

GRANT CONNECT ON DATABASE mydb TO app_user;

To grant the ability to create, read, update, and delete data on a specific schema:

GRANT USAGE ON SCHEMA public TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

Step 4: Verification

The new user can now connect using their credentials:

ysqlsh -U app_user -h <hostname> -p <port> -d <database>

Note:
In YugabyteDB,

CREATE ROLE

CREATE USER

LOGIN

CREATE USER

CREATE ROLE ... LOGIN

For more details, refer to YugabyteDB Documentation: User and Role Management.

Installing the JDBC Driver

At the time of writing, the required JDBC driver for YugabyteDB is not bundled with Matillion Data Productivity Cloud due to licensing or redistribution restrictions. As a result, you will need to manually obtain and install this driver to enable connectivity. Follow the steps below to download and upload the necessary JDBC driver.

Step 1: Download the JDBC Driver

1. Navigate to the PostgreSQL JDBC page: YugabyteDB uses the PostgreSQL wire protocol, so you can use a compatible PostgreSQL JDBC driver. Visit https://jdbc.postgresql.org/.
2. Select a Type 4 JDBC Driver: Type 4 drivers are the recommended choice as they are platform-independent and communicate directly using the database's network protocol.
3. Download the driver JAR: Find the latest stable version of the Type 4 JDBC driver and download the JAR file to your local machine. This file is usually named in the format

   postgresql-<version>.jar

   .

Step 2: Upload the JDBC Driver to Matillion Data Productivity Cloud

1. Review the official documentation: For specific steps on uploading external drivers, consult the guide at Matillion: Uploading External Drivers.
2. Access the driver management interface: Log in to the relevant Matillion environment and navigate to the area where external JDBC drivers can be uploaded.
3. Upload the JAR file: Follow the prompts to upload the previously downloaded JDBC driver JAR file. Be sure to select the correct option or location that allows Matillion to utilize this driver in connector configurations.

Step 3: Configure and Use the Driver

Once the JDBC driver has been uploaded:

• Refer to the Matillion documentation on using Database Query integrations for establishing a connection profile and running queries against your YugabyteDB databases.
• During the setup process, ensure you select your newly uploaded driver when configuring connections to YugabyteDB resources.

Following these steps ensures that Matillion Data Productivity Cloud can interact with your YugabyteDB database using the PostgreSQL-compatible JDBC driver you installed.

Checking network connectivity

To ensure that Matillion Data Productivity Cloud can connect to your YugabyteDB database, you must verify that the database accepts incoming connections based on your chosen deployment configuration:

• Full SaaS Agent Configuration: The YugabyteDB database must allow incoming connections from the IP addresses listed in the Matillion Data Productivity Cloud documentation. Refer to this URL for the full list of required source IP addresses.

• Hybrid SaaS Deployment: The YugabyteDB database must accept incoming connections from the IP address ranges used by your own Virtual Private Cloud (VPC). You can find tools to help you check network access and identify relevant IPs at the Matillion Exchange network check utility.

If you configure access to YugabyteDB using a DNS host name instead of an IP address, ensure that your selected Full SaaS or Hybrid SaaS agent can successfully resolve the database’s address to its underlying IP address. Network issues can occur if the agent cannot perform DNS lookup for the database.

Querying Data from YugabyteDB

This guide provides instructions for technical users on querying data from a YugabyteDB database. The instructions include SQL examples and recommendations for initial and incremental extract patterns, especially relevant when YugabyteDB data is integrated with platforms such as Databricks.

Example YugabyteDB Queries

YugabyteDB is PostgreSQL-compatible; thus, standard SQL

SELECT

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

-- Select specific columns SELECT id, first_name, last_name, department FROM employees;

-- Get rows where department is 'finance' SELECT * FROM employees WHERE department = 'finance';

-- Retrieve recent changes based on a last_update timestamp SELECT * FROM employees WHERE last_update > '2024-06-01 00:00:00';

-- Aggregate: Count employees per department SELECT department, COUNT(*) FROM employees GROUP BY department; ```

Datatype Considerations

When moving data from YugabyteDB to Databricks, be aware that datatype conversion may occur. Common conversions include:

• uuid

  ,

  jsonb

  in YugabyteDB may be mapped to

  STRING

  or serialized text in Databricks.
• YugabyteDB’s

  timestamp with time zone

  may convert to Databricks’

  TIMESTAMP

  (UTC interpreted).
• Numeric or array types may get cast to compatible Databricks types (e.g.,

  DECIMAL

  ,

  ARRAY<STRING>

  ).

It is recommended to inspect data mappings if precision or special types are critical in your workflow.

Loading Patterns: Initial vs Incremental

Initial Data Load

For a once-off "full" load, you typically fetch all data from a table without a filter. In ETL tools (such as Matillion), use the

Database Query

WHERE

Example:

SELECT * FROM orders;

Usage: - Run this query once to capture the initial dataset. - Transfer or process the entire result set in Databricks.

Incremental Data Loads

For routine updates, load only data that has changed since the last extraction—using a suitable filter (e.g.,

last_update

modified_at

Example incremental load query using a timestamp:

SELECT * FROM orders WHERE last_update > '{last_extracted_timestamp}';

Or incremental by unique integer:

SELECT * FROM orders WHERE order_id > {last_max_order_id};

Notes: - The same

Database Query

WHERE

For further details and incremental load design, refer to Matillion’s Incremental Load Data Replication Strategy.

Pro tip: Always validate datatype compatibility between YugabyteDB and downstream platforms (like Databricks) when designing your ETL/ELT logic.

Data Integration Architecture

A key advantage of the ELT (Extract, Load, Transform) architecture is the separation of data loading from integration and transformation tasks. By loading the data into Databricks in advance, organizations can divide and conquer the data integration challenge, first focusing on reliably ingesting data, and then addressing transformation and integration as a distinct, subsequent step. Since successful data integration requires systematic data transformation, the most efficient approach is to implement transformation pipelines, which can automate and orchestrate complex workflows. Furthermore, the ELT model allows all transformation and integration operations to occur within the target Databricks database itself. This not only enables faster, on-demand, and highly scalable processing—leveraging Databricks' powerful compute engine—but also eliminates the need for additional data processing infrastructure and associated costs.