Integrate data from OrientDB to Amazon Redshift using Matillion

Creating a User in OrientDB

The process of creating a user in an OrientDB database typically involves two steps:

1. Creating an OrientDB User: This allows login to the database with specified credentials and permissions.
2. (Optional) Creating an Application-Level Identity Record: If your application models users or identities as database records (e.g., vertices of a User class), you'll need to create those separately.

Below are step-by-step instructions on how to create a user at both levels using OrientDB's SQL commands.

1. Creating a Database User

OrientDB stores user accounts in the internal

OUser

Example: Creating a read-write user with specific credentials

INSERT INTO OUser SET name = 'alice', password = 'mySecretPassword', status = 'ACTIVE', roles = (SELECT FROM ORole WHERE name = 'writer')

Breakdown: -

name

password

status

ACTIVE

roles

reader

writer

admin

Notes:

• Consider using a secure channel when issuing commands containing passwords.
• Ensure username uniqueness.
• You can assign multiple roles by providing a comma-separated list in the

  roles

  field.

2. (Optional) Creating an Application-Level Identity Record

If your schema defines a class (e.g.,

User

CREATE VERTEX User SET username = 'alice', email = '[email protected]', fullName = 'Alice Smith'

INSERT INTO User SET username = 'alice', email = '[email protected]'

Attributes:
- Add any fields required by your application model (e.g.,

username

email

createdAt

3. Granting Privileges (Optional)

To set custom permissions for the new user, you can create a custom role and assign it. For example:

-- Create a new role with read-only access
CREATE ROLE readOnlyRole MODE = DENY_ALL_BUT
-- Grant SELECT on all classes in the database
GRANT SELECT ON database.class.* TO readOnlyRole
-- Assign the role to the user
UPDATE OUser SET roles = [ #17:0 ] WHERE name = 'alice'
-- (Replace #17:0 with the correct RID of the new role)

Useful SQL: Listing Users

View all defined users:

SELECT FROM OUser

Refer to the OrientDB Security documentation for advanced identity and permission management.

Installing the JDBC Driver

At the time of writing, the JDBC driver for OrientDB is not included by default in Matillion Data Productivity Cloud. This is due to licensing or redistribution restrictions. As a result, users must manually download and install the appropriate JDBC driver to enable connectivity between Matillion and an OrientDB database.

Step 1: Download the OrientDB JDBC Driver

To obtain the driver, visit the official OrientDB download page at:
https://orientdb.com/download/

While browsing the available downloads, look specifically for a Type 4 JDBC driver, as this type is a pure Java implementation and is preferred for use with Matillion Data Productivity Cloud.

Download the latest stable release of the Type 4 JDBC driver and save the resulting JAR file to a location accessible from your workstation.

Step 2: Upload the Driver to Matillion Data Productivity Cloud

After downloading the driver JAR, it needs to be uploaded to your Matillion Data Productivity Cloud agent. Matillion provides a step-by-step guide for this process at:
https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

Follow these instructions, ensuring the OrientDB JDBC driver JAR is placed in the correct directory and that any necessary agent or service restarts are performed as outlined.

Step 3: Configure Usage in Matillion

Once the JDBC driver has been uploaded and the Matillion agents updated, the driver can be used to establish connections to the OrientDB database from within the Matillion Data Productivity Cloud environment. Usage instructions, including how to create connections and execute queries using custom drivers, are provided at:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

Refer to this documentation to configure your connection properties and start working with data in OrientDB via Matillion.

Checking network connectivity

To ensure seamless connectivity between Matillion Data Productivity Cloud and your OrientDB database, you must verify that the OrientDB server allows incoming network connections from the appropriate locations. The necessary configuration depends on your deployment model:

• Full SaaS agent configuration:
  You need to permit incoming connections from the list of IP addresses provided by Matillion. The complete and up-to-date list is available at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/
  Make sure your firewall or network security group allows these IP addresses to access your OrientDB instance.

• Hybrid SaaS deployment:
  In this setup, Matillion connections originate from within your own virtual private cloud (VPC). You must ensure that your OrientDB database is accessible from your VPC’s IP range(s). For assistance with checking network access from your VPC, refer to the tools and resources at:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if your OrientDB database is referenced by a DNS hostname (rather than a static IP address), confirm that the relevant Matillion agent — whether Full SaaS or deployed within your VPC for Hybrid SaaS — can successfully resolve this domain name to the correct IP address. Proper DNS resolution is required for establishing a connection to your database.

Querying Data from an OrientDB Database

OrientDB supports a SQL-like query language which is used to retrieve data from its multi-model database. Below are guidelines and examples for querying data, considerations for datatype conversion when moving data to Amazon Redshift, and batch loading strategies.

1. Example OrientDB Query Statements

OrientDB's SQL is similar to standard SQL with some extensions. Here are some typical examples:

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

-- Select specific fields with a WHERE clause SELECT name, age FROM Person WHERE age > 30;

-- Join data between two classes using embedded records SELECT FROM Person WHERE out('FriendOf').size() > 2;

-- Using aggregate functions SELECT city, COUNT(*) as total FROM Person GROUP BY city;

-- Fetching related documents (traverse edges) SELECT FROM (TRAVERSE out('FriendOf') FROM (SELECT FROM Person WHERE name = 'John')) WHERE @class='Person'; ```

2. Data Type Conversion between OrientDB and Redshift

When migrating or synchronizing data from OrientDB to Amazon Redshift, data type conversions might be necessary because the datatypes supported by both systems differ. Here is a general guideline:

It's best to serialize complex/embedded structures to JSON strings for storage in VARCHAR or SUPER columns in Redshift.

3. Loading Patterns: Initial and Incremental Loads

To efficiently load data from OrientDB into Redshift or any data warehouse, the recommended process is:

Initial Load

• The Database Query component should select all records—no filter is needed.
• Example OrientDB initial load query:

SELECT * FROM Person

• All data is exported to the target system in one batch.

Incremental Load

• After the initial load, future data transfers should only fetch new or updated records.
• This is achieved by adding a filter predicate, such as using a

  last_updated

  timestamp.
• Example OrientDB incremental load query:

SELECT * FROM Person
     WHERE last_updated > :LAST_LOAD_TIME

*(Replace `:LAST_LOAD_TIME` with your variable/system stored value)*

• The same Database Query component is used, but the

  WHERE

  clause efficiently pulls just the incremental data.

For a detailed discussion on incremental load patterns, see this Exchange Matillion article.

By following this pattern, you can efficiently transfer data from OrientDB to Redshift while handling schema and datatype differences and maintaining scalable ETL pipelines.

Data Integration Architecture

Loading data into Amazon Redshift in advance of integration is an effective way to divide and conquer complex data workflows, as it allows you to split the process into two manageable steps: first loading, then integrating. This is a key advantage of the ELT (Extract, Load, Transform) architecture, where raw data from various sources is ingested into Redshift before any complex processing occurs. Because data integration inevitably requires transformation, utilizing data transformation pipelines within Redshift is considered the best-practice approach. Another significant advantage of the ELT approach is that all transformations and integration tasks take place directly inside the target Redshift database. This means data operations are faster, can be run on-demand, and are inherently scalable as your data volumes grow. Furthermore, there is no need to pay for separate data processing infrastructure, since Redshift provides the storage and compute resources necessary for both storing the data and performing required transformations.