Integrate data from MariaDB to Databricks using Matillion

Creating a User in MariaDB

To enable secure and controlled access to a MariaDB database, you must create individual users with tailored privileges. Follow the step-by-step instructions and example SQL scripts below.

1. Connect to MariaDB as an Administrative User

You need administrative credentials (such as the

root

mysql -u root -p

2. Create a New User

The general syntax to create a new user is:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• username

  : The name of the new user you want to create.

• host

  : The host from which the user can connect (e.g.,

  'localhost'

  ,

  '%'

  for any host, or a specific IP address).

• password

  : The password for the user.

Example:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'S3cureP@ssw0rd';

This statement creates a user named

app_user

localhost

3. Grant Privileges to the User

Users require appropriate privileges to perform actions on databases. The most common command is:

GRANT privileges ON database.table TO 'username'@'host';

• privileges

  : Type of permissions (e.g.,

  ALL PRIVILEGES

  ,

  SELECT

  ,

  INSERT

  ,

  UPDATE

  , etc.).

• database.table

  : The specific database and table, or use

  *.*

  for all.

Granting All Privileges Example:

GRANT ALL PRIVILEGES ON example_db.* TO 'app_user'@'localhost';

Granting Specific Privileges Example:

GRANT SELECT, INSERT ON example_db.* TO 'app_user'@'localhost';

4. Apply Changes

Flush the privileges to ensure that the changes take effect immediately:

FLUSH PRIVILEGES;

5. Test the New User

To verify the user creation, try logging in with the new credentials:

mysql -u app_user -p -h localhost

Note:
Always choose strong, unique passwords and restrict user privileges following the principle of least privilege.

Checking the JDBC driver

The JDBC driver for MariaDB is already installed in Matillion Data Productivity Cloud, so there is no need to download or install it manually. To confirm that the JDBC driver is present, follow these steps:

1. Create a new Orchestration pipeline in Matillion.
2. Add a Database Query component to your pipeline.
3. In the properties of the Database Query component, locate the Database Type dropdown.
4. Look for MariaDB in the list of available database types.

If you see MariaDB listed, the JDBC driver is present and ready for use.

For instructions on how to check the version of the JDBC driver, visit the official documentation at: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/#database-driver-versions.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your MariaDB database, you must configure your MariaDB database to accept incoming network connections according to your agent deployment type:

• Full SaaS Agent Configuration:
  If you are using the Full SaaS agent, you need to allow incoming connections from the IP addresses listed in the official Matillion documentation: Allowing IP Addresses. Make sure you review this list regularly, as allowed IP addresses may change over time.

• Hybrid SaaS Deployment:
  If you are using a Hybrid SaaS deployment, you must allow incoming connections from your own Virtual Private Cloud (VPC) where the Matillion agent is running. You can find utilities to help identify and check network access from your VPC at Check Network Access.

Additionally, if the MariaDB database is referenced using a DNS hostname, ensure that the Full SaaS or Hybrid SaaS agent is able to resolve this DNS address. This may require updating DNS settings or network policies so that the agent can access the required DNS servers.

How to Query Data from a MariaDB Database

This guide explains how to query data from a MariaDB database, focusing on best practices for initial and incremental data loading, SQL examples, and considerations for cross-platform datatype conversions (such as with Databricks).

Example MariaDB SELECT Statements

The most basic query retrieves all columns and rows from a table:

SELECT * FROM employees;

To return specific columns:

SELECT employee_id, first_name, last_name, hire_date FROM employees;

To filter results using a

WHERE

SELECT * FROM employees
WHERE department = 'Sales' AND status = 'Active';

To sort results:

SELECT * FROM employees
ORDER BY hire_date DESC;

To aggregate data:

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

To join tables:

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Datatype Conversion: MariaDB to Databricks

When moving data from MariaDB to Databricks, datatypes may require conversion. Some differences to be aware of:

• MariaDB

  DATETIME

  ,

  DATE

  , or

  TIMESTAMP

  types typically convert to Databricks

  TIMESTAMP

  or

  DATE

  .
• MariaDB

  VARCHAR

  /

  TEXT

  become Databricks

  STRING

  .
• MariaDB numeric types, like

  INT

  ,

  BIGINT

  ,

  DECIMAL

  , generally map directly, but review precision/scale.
• MariaDB

  BOOLEAN

  may become Databricks

  BOOLEAN

  or

  TINYINT

  , depending on connector settings.

Always review and test datatype mappings to avoid data loss or unexpected behavior.

Data Loading Patterns: Initial and Incremental Loads

A recommended approach for loading data from MariaDB is:

1. Initial (Full) Load: Load all historical data in one operation.
2. Incremental Load: On subsequent runs, only extract new or updated records.

Both loads can be handled with the same Database Query component in your ETL tool.

• Initial Load: Use a query without a filter:

SELECT * FROM employees;

• Incremental Load: Add a filter, such as on a timestamp or incrementing ID column:

SELECT * FROM employees
    WHERE last_modified >= '2024-06-01 00:00:00';

Replace

'2024-06-01 00:00:00'

Further details on this pattern: Incremental Load Data Replication Strategy (Matillion Exchange)

Data Integration Architecture

Loading data into the system in advance of integration allows organizations to break down the data integration challenge into more manageable steps, a key benefit of the Extract, Load, Transform (ELT) architecture. By first loading raw data into the Databricks database, teams can subsequently perform integration and transformation activities with greater flexibility and control. Effective data integration hinges on robust data transformation, which is best achieved using dedicated data transformation pipelines that automate, standardize, and simplify complex data preparation tasks. Another significant advantage of the ELT approach is that all transformation and integration occurs within the target Databricks database. This not only ensures fast, on-demand, and scalable processing by leveraging Databricks’ native compute capabilities, but also minimizes the need for external data processing infrastructure—allowing organizations to optimize costs and streamline operations.