Integrate data from OrientDB to Snowflake using Matillion

Creating a User in OrientDB

To create a new user in an OrientDB database, you can use OrientDB's SQL commands within the OrientDB console or via a client that allows SQL execution (such as Studio or through a custom application).

Below are step-by-step instructions to create a user and assign appropriate privileges.

1. Connect to the Database

First, connect to your OrientDB database via the console:

orientdb> CONNECT remote:localhost/YourDatabase admin YourAdminPassword

Replace

YourDatabase

2. Create a New User

You can create a user by inserting a new record into the built-in

OUser

Example SQL Command

INSERT INTO OUser SET name = 'newuser', password = 'userpassword', status = 'ACTIVE', roles = (SELECT FROM ORole WHERE name = 'reader')

name

password

status

'ACTIVE'

roles

reader

writer

admin

reader

3. Assign Roles and Privileges (Optional)

If you want to grant additional privileges, assign other default roles (

writer

admin

Assign Multiple Roles

UPDATE OUser SET roles = (SELECT FROM ORole WHERE name IN ['reader','writer']) WHERE name = 'newuser'

Create a Custom Role

To create a custom role:

INSERT INTO ORole SET name = 'customrole', mode = 0, rules = {}
UPDATE OUser SET roles = (SELECT FROM ORole WHERE name = 'customrole') WHERE name = 'newuser'

You must configure access rules for custom roles. See the OrientDB documentation on security for further details.

4. Verify the User

To confirm user creation:

SELECT FROM OUser WHERE name = 'newuser'

The response should show your new user's document. The user can now authenticate using the provided username and password.

Installing the JDBC Driver

This narrative provides step-by-step instructions for technical users on how to download, install, and configure the OrientDB JDBC driver for use with Matillion Data Productivity Cloud (DPC). Please note that, at the time of writing, the OrientDB JDBC driver is not packaged with Matillion DPC by default. This omission is due to licensing or redistribution restrictions, and thus you are required to manually obtain and upload the driver into your environment.

1. Download the OrientDB JDBC Driver

To begin, download the OrientDB JDBC driver manually from the official OrientDB distribution page. Use the following link to access the downloads:

• https://orientdb.com/download/

On the download page, search for the JDBC driver package. It is recommended to choose the Type 4 JDBC driver, as this type provides a pure Java implementation and eliminates the need for native dependencies or additional libraries.

Download the JAR file and make a note of its location on your workstation.

2. Install the Driver in Matillion Data Productivity Cloud

Next, you need to upload the downloaded JDBC driver to your Matillion Agent so that it is available for use within Matillion DPC. The platform allows BYO (Bring Your Own) JDBC drivers for integrations not supported natively out of the box.

For detailed, product-specific instructions on uploading external drivers to the Matillion agent, refer to the documentation at:

• https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

Follow all steps outlined at the above URL to ensure the driver JAR is placed in the appropriate directory and recognized by your Matillion environment. Remember to restart the agent or refresh its configuration after installing a new external driver, as recommended in the documentation.

3. Configure and Use the Driver within Matillion DPC

Once the OrientDB JDBC driver has been installed on your agent, you can configure database connections and utilize the driver within your Matillion DPC projects. The usage workflow typically includes defining credentials, specifying connection strings, and building queries within the Matillion interface using the uploaded driver.

To understand how to use external JDBC drivers for querying and integration tasks, consult the product usage documentation:

• https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

Refer to this guide for steps on configuring a database query component or other features that rely on your newly installed JDBC driver.

Checking network connectivity

To ensure successful integration between Matillion Data Productivity Cloud and your OrientDB database, you must configure network connectivity appropriately. This includes allowing incoming connections to the OrientDB database based on your deployment type:

• Full SaaS agent configuration:
  You must allow incoming connections from the IP addresses listed in Matillion's public documentation. Refer to the following URL for the current list of IP addresses, and ensure these are permitted by your OrientDB database’s firewall or network rules:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS deployment:
  You should allow incoming connections from your own organization's Virtual Private Cloud (VPC). Typically, this means permitting traffic from your Matillion Hybrid SaaS agent’s internal network. To check whether your configuration allows these connections, or to troubleshoot connectivity, you can use the utilities provided here:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if you are referencing the OrientDB database using a DNS address, it’s essential that the Matillion Full SaaS or Hybrid SaaS agent can resolve this DNS name to the correct IP address. This requires proper DNS configuration and, if applicable, necessary firewall rules to allow DNS lookups.

Querying Data from OrientDB: Technical Instructions

OrientDB supports a SQL-like query language to interact with its multi-model database. Below are instructions and practical examples for querying data in OrientDB, along with relevant considerations for integration and incremental data loading, especially when working with targets like Snowflake.

1. Example OrientDB SQL SELECT Statements

OrientDB uses familiar SQL syntax for most data retrieval tasks:

``` -- Retrieve all documents from a class (table) SELECT * FROM Person

-- Project specific attributes SELECT name, age FROM Person

-- Filtering records SELECT * FROM Person WHERE age > 30

-- Using ORDER BY SELECT name, age FROM Person WHERE city = 'London' ORDER BY age DESC

-- Limiting results SELECT * FROM Person LIMIT 100

-- Pattern matching SELECT * FROM Person WHERE name LIKE 'J%'

-- Traversing graph relationships SELECT expand(out('Friend')) FROM Person WHERE name = 'Alice' ```

2. Datatype Conversion: OrientDB ➔ Snowflake

When extracting data from OrientDB and loading into Snowflake, be alert to automatic or required datatype conversions. Some points to consider:

• OrientDB datatypes: Includes String, Integer, Float, Date, Datetime, Boolean, Embedded, Link, etc.
• Snowflake equivalents: Not all OrientDB datatypes map one-to-one. For example:

• LINK

  ,

  EMBEDDED

  types may need to be flattened or represented via foreign keys in tabular Snowflake schemas.
• Date/datetime formats may need adjustment.
• OrientDB booleans may convert to 1/0 or TRUE/FALSE as dictated by mapping logic or utility in use.
• Use ETL tools' (like Matillion’s) datatype mapping features to control or verify type translations.

3. Patterns for Data Loading: Initial and Incremental

The recommended ETL pattern for moving data from OrientDB to downstream systems (e.g., Snowflake) is:

• Initial Load: Once-off, all data is extracted from the source.

  • Query does not need a restrictive filter; usually a full table/class extract.
  • Example:

    SELECT * FROM Person

  • Configure your Database Query component without a

    WHERE

    clause.

• Incremental Load: After the initial sync, regularly poll for new/updated records.

  • Requires a filter in the Database Query component to only select new/changed records since the last load.
  • Typically uses a column like

    modifiedAt

    (a timestamp or incremental ID).
  • Example:

    SELECT * FROM Person WHERE modifiedAt > :lastSyncTimestamp

  • Replace

    :lastSyncTimestamp

    with your control variable.
  • Read more: Incremental Load: Data Replication Strategy

Both initial and incremental loads use the same Database Query component, with only the presence/absence of the filter clause distinguishing the approach.

References
- OrientDB Documentation – SQL Queries - Matillion: Incremental Load Data Replication Strategy

Data Integration Architecture

Loading data in advance of integration exemplifies the divide-and-conquer approach by splitting the overall data integration process into two manageable steps; this methodology is a core advantage of the Extract, Load, and Transform (ELT) architecture. With ELT, data is first ingested into the Snowflake database in its raw form, and subsequently, integration is performed by transforming and joining this data within the database environment. Data integration inherently requires transformation—such as data cleansing, type casting, or joins—and data transformation pipelines are the most effective way to sequence and automate these tasks efficiently. Importantly, another significant benefit of the ELT architecture is that all transformative work and integration are executed directly inside the target Snowflake environment. This means transformations are not only fast, on-demand, and highly scalable but also eliminate the need to invest in or maintain separate, external data processing resources, as Snowflake’s infrastructure handles the computational workload dynamically.