Integrate data from GraphDB (Ontotext) to Databricks using Matillion

Creating an Identity in GraphDB

This guide describes how to create a new user in a GraphDB database using the built-in user management functionality provided by GraphDB's Workbench (web interface) and REST API. User management ensures secure and organized access control, especially in multi-user environments.

1. Using the GraphDB Workbench (Web Interface)

1. Log in with an account that has administrative privileges.
2. From the main menu, navigate to Users & Security > Users.
3. Click the Add User button.
4. Fill out the form:
   • Username: Choose a unique login name for the new user.
   • Password: Enter and confirm a strong password.
   • Roles: Assign the desired role(s) (e.g.,

     READ

     ,

     WRITE

     ,

     REPO_MANAGER

     , or

     ADMIN

     ).
5. Click Create to create the new user.

2. Using the REST API

GraphDB exposes its user management via a REST API. You can create a user by sending a POST request to the

/rest/security/users

Example Request (using

curl

)

bash
curl -X POST "http://localhost:7200/rest/security/users" \
  -u admin:admin_password \
  -H "Content-Type: application/json" \
  -d '{
        "userName": "newuser",
        "password": "secure_password",
        "grantedAuthorities": ["READ", "WRITE"]
      }'

Notes: - Replace

admin:admin_password

READ

WRITE

REPO_MANAGER

ADMIN

Example JSON Payload

json
{
  "userName": "newuser",
  "password": "secure_password",
  "grantedAuthorities": ["READ", "WRITE"]
}

3. Programmatic Creation via SQL-like Syntax

GraphDB does not natively support user creation via SQL (such as

CREATE USER

Additional Considerations

• Confirm your changes by listing users via the Workbench or the REST endpoint (

  GET /rest/security/users

  ).
• Always use a secure connection (HTTPS) when managing users on a remote or production server.
• Password reset and further user management actions are available via similar interfaces.

Installing the JDBC Driver

The GraphDB JDBC driver is not distributed with Matillion Data Productivity Cloud by default. This is due to licensing and/or redistribution restrictions. If you wish to connect Matillion Data Productivity Cloud to a GraphDB database via JDBC, you must manually download and install the appropriate driver.

Follow these steps to download and install the GraphDB JDBC driver for use in Matillion Data Productivity Cloud:

1. Download the GraphDB JDBC Driver

1. Navigate to the official GraphDB JDBC connector documentation: https://graphdb.ontotext.com/documentation/jdbc-connector.html
2. On that page, locate and download the latest available Type 4 JDBC driver. Type 4 drivers are strongly recommended as they are platform-independent and do not require additional libraries.
3. Save the downloaded

   .jar

   file to a local directory for later upload.

2. Prepare for JDBC Driver Installation in Matillion

Due to the external nature of this driver, you must manually upload it to your Matillion Data Productivity Cloud Agent.

1. Review the official Matillion documentation for uploading and installing external JDBC drivers: Uploading External Drivers.
2. Follow the instructions provided to upload the

   .jar

   file(s) for the GraphDB JDBC driver to your Matillion Agent environment.
3. This process may differ depending on whether your agent runs in AWS, Azure, or a local environment. Review and follow the details applicable to your deployment model.
4. Ensure that the driver

   .jar

   is properly recognized and available to your Matillion Data Productivity Cloud Agents before proceeding.

3. Configure and Use the Driver

After successful upload and installation of the JDBC driver, consult the following Matillion documentation for step-by-step guidance on connecting to and querying GraphDB from within the product:
Database Query Usage Instructions

This will provide you with details about using the GraphDB JDBC connection in your pipelines or transformation workflows.

Note: Always confirm you are using a compatible JDBC driver version for both GraphDB and Matillion Data Productivity Cloud to ensure connectivity and avoid runtime issues.

Checking network connectivity

To establish successful connectivity between Matillion Data Productivity Cloud and your GraphDB database, it is essential to ensure that the database allows incoming network connections according to your chosen deployment configuration:

• Full SaaS Agent Configuration:
  If you are using the Full SaaS agent, GraphDB must allow incoming connections from the specific IP addresses used by Matillion. You can find the current list of required IP addresses at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS Deployment:
  For Hybrid SaaS configurations, GraphDB must accept incoming connections originating from your own Virtual Private Cloud (VPC). To assist in identifying your VPC’s egress IP addresses and to check connectivity, you can use the utilities available at:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Note:
If your GraphDB instance is referenced by a DNS hostname, ensure that the Matillion agent (whether Full SaaS or Hybrid SaaS) is able to resolve that DNS address successfully. Connectivity will fail if the agent cannot translate the DNS name to an IP address.

Querying Data from GraphDB in a Technical Workflow

This guide explains how to query data from a GraphDB database using SQL SELECT statements and describes an efficient loading strategy for downstream analytics systems (like Databricks). It covers data type conversion specifics and demonstrates best practices for initial and incremental loads.

1. Querying GraphDB with SQL SELECT Statements

Although GraphDB is fundamentally an RDF triple store typically queried with SPARQL, it can often be integrated with systems that express requests as (or translate them to) familiar SQL

SELECT

Example 1: Select all elements from a table/view

SELECT *
FROM Person

Example 2: Select specific columns with filtering

SELECT name, age
FROM Person
WHERE age > 30

Example 3: Aggregation and grouping

SELECT department, COUNT(*) AS staff_total
FROM Employee
GROUP BY department

Example 4: Joining tables/views

SELECT p.name, t.title
FROM Person AS p
JOIN Task AS t ON p.id = t.assigned_to
WHERE t.status = 'Open'

Note: If querying through Databricks or an ETL tool, these queries may be translated or mapped to SPARQL or underlying table equivalents in GraphDB.

2. Datatype Conversion: GraphDB vs. Databricks

When moving data from GraphDB to Databricks, watch for datatype discrepancies. Typical conversion issues include:

xsd:string

STRING

xsd:integer

BIGINT

INT

xsd:decimal

DECIMAL(precision)

xsd:dateTime

TIMESTAMP

xsd:date

DATE

xsd:boolean

BOOLEAN

Tip: Always review field mappings and test sample loads to mitigate conversion errors.

3. Initial vs. Incremental Load Patterns

The best-practice data loading pattern for analytics (particularly in Matillion or Databricks pipelines) is:

• Initial Load: One-time full extraction of the relevant data
• Incremental Loads: Ongoing extraction of only new or changed records

Use the same Database Query component for both.

3.1 Initial Load Example

On the first load, omit all filter conditions so the full dataset is ingested:

SELECT id, name, last_modified
FROM Customer

• No WHERE or filter clause is used.
• Use this only once: during the initial system population.

3.2 Incremental Load Example

For subsequent (incremental) loads, include a filter on a "change tracking" column (such as

last_modified

SELECT id, name, last_modified
FROM Customer
WHERE last_modified > '${last_load_marker}'

• The filter clause ensures only new or updated records since the last load time are selected.

• ${last_load_marker}

  is typically a variable or parameter passed in from ETL orchestration.
• This pattern optimizes data movement by transferring only what has changed.

Further Reading:
For more details and best practices, see Matillion's Incremental Load Data Replication Strategy.

By strictly separating initial from incremental loads and using parameterized filter clauses, you maximize efficiency and reliability when synchronizing GraphDB with analytical targets like Databricks.

Data Integration Architecture

Loading data in advance of integration exemplifies the "divide and conquer" principle by separating the data loading and transformation steps, a key advantage of the ELT (Extract, Load, Transform) architecture. Within this framework, raw data is first loaded into the Databricks environment, allowing practitioners to then focus solely on data integration and transformation tasks as a distinct phase. This separation is efficient because data transformation — an essential aspect of integration — is best managed using data transformation pipelines. These pipelines streamline complex workflows, ensuring data is cleansed, standardized, and prepared before integration. Furthermore, the ELT architecture enables both transformation and integration operations to be performed natively within the target Databricks database. This offers significant benefits: operations are fast, can be triggered on demand, and easily scale to accommodate large data volumes, all without incurring additional expenses for external data processing infrastructure.