Integrate data from Virtuoso to Databricks using Matillion

Creating a User in Virtuoso Database

To create a new user (identity) in your Virtuoso database, you will use SQL commands issued through the Virtuoso Interactive SQL interface (ISQL), the Virtuoso Conductor, or any appropriate database client. The steps outlined below assume you have administrative privileges.

1. Connect to the Database

Access the Virtuoso ISQL shell from your system terminal:

bash
isql 1111 dba your_dba_password

• 1111

  is the default Virtuoso port; replace if your instance runs on a different port.

• dba

  is the default administrator account; provide your password accordingly.

Alternatively, use Virtuoso Conductor (web interface) or another SQL client as appropriate.

2. Create the User Account

Execute an SQL statement in the connected session to create the new user. Replace

username

password

CREATE USER username IDENTIFIED BY 'password';

Example

CREATE USER analyst IDENTIFIED BY 'StrongPassw0rd!';

3. Grant Privileges to the User

After creation, the user has no privileges. Grant specific permissions such as connecting, reading, writing, or administering database objects.

Basic CONNECT privilege

GRANT CONNECT TO username;

Example

GRANT CONNECT TO analyst;

Grant Additional Privileges As Needed

For example, providing the user with ability to select (

SELECT

GRANT SELECT ON SCHEMA schema_name TO username;

Or to grant all privileges on a specific table:

GRANT ALL ON TABLE schema_name.table_name TO username;

4. Verifying the User

List all database users to confirm creation:

SELECT * FROM DB.DBA.SYS_USERS;

Note:
- All identifiers in Virtuoso are case-sensitive unless unquoted, and should follow your security and naming conventions. - Always use secure passwords when creating user accounts.

Installing the JDBC Driver

At the time of writing, the Virtuoso JDBC driver is not bundled with Matillion Data Productivity Cloud by default. This is due to licensing and redistribution restrictions, so you will need to manually download and install it to enable connectivity.

Downloading the Virtuoso JDBC Driver

1. Access the Official Download Page:
   Visit the Virtuoso JDBC driver download page at:
   https://virtuoso.openlinksw.com/dataspace/doc/dav/wiki/Main/VirtuosoJDBC

2. Select the Appropriate Driver:
   Among the options, look specifically for the Type 4 JDBC driver. Type 4 drivers are pure Java implementations and are usually preferred for Matillion integrations due to their portability and performance.

3. Download the Driver File:
   Download the JAR file for the Virtuoso Type 4 JDBC driver to your local machine. Ensure you comply with any license terms presented during the download process.

Installing the Driver into Matillion Data Productivity Cloud

1. Review Installation Documentation:
   For detailed, up-to-date instructions on installing external JDBC drivers, refer to the Matillion documentation here:
   https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

2. Upload the Driver JAR:
   Using your Matillion Agent user interface, navigate to the section for uploading external drivers (sometimes labeled as “Manage Agents” or “Driver Upload”). Follow the prompts to upload the Virtuoso JDBC driver JAR you downloaded earlier.

3. Verify Installation:
   After upload, verify that the driver is listed as available. You may be required to restart the Matillion Agent or service for the new driver to be recognized.

Using the Driver in Matillion Data Productivity Cloud

Once successfully uploaded, you can configure database connections and use the Virtuoso JDBC driver within your Matillion data pipelines and components. For information on creating and managing database queries using external JDBC drivers in Matillion, consult the following resource:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your Virtuoso database, you must allow incoming connections to the Virtuoso database according to your deployment type:

• Full SaaS agent configuration:
  Allow incoming connections from the Matillion-managed IP addresses. Refer to the list of required IP addresses at this URL.

• Hybrid SaaS deployment:
  Allow incoming connections from your own Virtual Private Cloud (VPC) where the agent is running. You can find helpful tools for testing network access at Matillion Exchange: Check Network Access.

Additionally, if your Virtuoso database host is specified using a DNS name rather than an IP address, ensure that the agent (whether Full SaaS or Hybrid SaaS) is able to resolve the DNS address correctly. Network or firewall rules must not block the necessary DNS queries required for hostname resolution.

Querying Data from a Virtuoso Database

This guide explains how to query data from a Virtuoso database using SQL SELECT statements, describes datatype conversion considerations when interacting with systems like Databricks, and details best practices for initial and incremental data loads using the Database Query component.

1. Example Virtuoso SQL SELECT Queries

Virtuoso supports standard SQL along with some extensions. Below are example queries:

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

-- Select specific columns SELECT id, name, email FROM customers;

-- Filtering results with WHERE SELECT id, name FROM customers WHERE signup_date > '2023-01-01';

-- Using aggregation SELECT status, COUNT(*) AS count FROM orders GROUP BY status;

-- Join example 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 = 'COMPLETE'; ```

2. Datatype Conversion Considerations

When moving data between Virtuoso and Databricks (or other systems), datatype differences may arise. Some datatype handling tips:

• Virtuoso’s

  VARCHAR

  ,

  INTEGER

  ,

  DECIMAL

  , and

  DATE

  types generally map well to Databricks data types.
• Complex types such as Virtuoso’s

  VARCHAR(MAX)

  or custom data types may need explicit casting, e.g., using

  CAST(column_name AS VARCHAR(255))

  .
• Timestamps in Virtuoso might need adjustment to Databricks' expected format (such as UTC alignment).

Example:

SELECT id, CAST(comment AS VARCHAR(255)) AS comment FROM feedback;

3. Initial and Incremental Loading Pattern

The recommended ETL pattern is to perform:

1. Once-Off Initial Load:
   The Database Query component runs without filtering, extracting the full dataset.

2. Incremental Loads:
   Subsequent loads only extract data modified or created since the last load, using a filter (e.g., based on a timestamp or incrementing key).

Both scenarios use identical Database Query components, differing only in the WHERE clause.

Initial Load Example

No filter is applied; all data is extracted:

SELECT id, name, signup_date FROM customers;

Incremental Load Example

A filter is applied, referencing a variable for the last loaded timestamp:

SELECT id, name, signup_date
FROM customers
WHERE signup_date > '${last_loaded_date}';

• Replace

  ${last_loaded_date}

  with your ETL orchestration system’s timestamp parameter.

For more guidance, refer to Matillion’s documentation on incremental loading.

Data Integration Architecture

Loading data into Databricks before performing integration is an effective way to divide and conquer the data integration process, as it splits the overall workload into two manageable steps: loading and transforming the data. This method, central to the Extract-Load-Transform (ELT) architecture, offers a significant advantage by enabling organizations to quickly bring raw data into the centralized Databricks database without needing to transform it immediately. Data transformation—a core requirement for effective data integration—is then performed using data transformation pipelines, which provide a systematic and reliable way to process and enrich the data. Another key benefit of the ELT approach is that both the data transformation and integration tasks are executed within the Databricks environment itself. This not only accelerates processing due to Databricks’ highly performant compute capabilities but also ensures transformations are available on demand and at scale. Furthermore, by transforming data directly within Databricks, organizations avoid the cost and complexity of maintaining additional data processing infrastructure, leading to a more streamlined, cost-effective integration solution.