Integrate data from DolphinDB to Snowflake using Matillion

Creating a User in DolphinDB

This guide outlines the steps required to create a new user (identity) in a DolphinDB database, using SQL scripts and relevant commands.

Prerequisites

• You must have an account with administrator privileges (

  admin user

  ) in your DolphinDB instance.
• You should be connected to your DolphinDB server via a client session or interactive console.

Step-by-Step Instructions

1. Log in as an Administrator

Ensure you are logged into DolphinDB as an administrator. Only users with admin privileges can create, modify, or delete users.

2. Create a New User

Use the built-in function

createUser

<username>

<password>

``` // Syntax createUser(username, password)

// Example createUser("newuser", "strongpassword123") ```

If you attempt to create a user who already exists, DolphinDB will return an error.

3. Grant Privileges (Optional)

By default, a newly created user has no privileges (except being able to log in). You need to explicitly grant the required privileges. For example, to grant

QUERY

WRITE

/db1

// Grant privilege
grant("newuser", "QUERY,WRITE", "/db1")

Accepted privileges include:

QUERY

READ

WRITE

ADMIN

MAINTAIN

4. Verify the User

To ensure the user has been created, you may use the

userList()

select * from userList()

This will display all users currently in the DolphinDB instance.

Notes

• Password can be a plain string. DolphinDB internally encrypts passwords.
• To change a user’s password, use the

  alterUser()

  function.
• For more information on user and privilege management, refer to the official DolphinDB documentation.

Installing the JDBC driver

At the time of writing, the DolphinDB JDBC driver is not included by default in Matillion Data Productivity Cloud, primarily due to licensing or redistribution restrictions. Therefore, you must manually download and install the driver to enable DolphinDB connectivity.

Step 1: Download the DolphinDB JDBC Driver

1. Visit the official DolphinDB download page: http://www.dolphindb.com/downloads.html
2. Locate the Type 4 JDBC driver for DolphinDB. This type of driver is generally recommended due to its platform independence and direct database communication capabilities.
3. Download the JDBC driver

   .jar

   file to your local system. Be sure to review any accompanying release notes or documentation for important compatibility and version information.

Step 2: Upload the JDBC Driver to Matillion Data Productivity Cloud

1. Follow the installation process detailed in Matillion's documentation here: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/
2. This guide explains how to upload and register external JDBC driver

   .jar

   files into your Matillion Agent environment.
3. After uploading the DolphinDB JDBC driver, ensure it appears in the list of available external drivers and is enabled for use.

Step 3: Configure and Use the Driver

1. Once successfully installed, you can proceed to configure database connections within the Matillion Data Productivity Cloud platform.
2. Refer to the usage instructions at: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/
3. These instructions describe how to set up and test JDBC connections for use within your workflows.

Remember to comply with all licensing terms when downloading and distributing the DolphinDB JDBC driver within your organization.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your DolphinDB database, you must configure your database to allow incoming connections based on your deployment type:

• Full SaaS Agent Configuration:
  If you are using a Full SaaS agent, your DolphinDB database must allow incoming connections from the IP addresses used by Matillion. The complete and up-to-date list of required IP addresses is available at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS Deployment:
  If you are using a Hybrid SaaS deployment, you must allow incoming connections from your own Virtual Private Cloud (VPC). To help check network connectivity from your VPC to the DolphinDB database, you can use the utilities provided at:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if you are referencing your DolphinDB database using a DNS name, ensure that the Full SaaS or Hybrid SaaS agent can resolve the DNS address successfully. This is required for establishing the connection.

Querying Data from a DolphinDB Database

This guide provides technical instructions on querying data from a DolphinDB database, with examples of SQL SELECT statements and best practices for data loading patterns. This approach is relevant when integrating DolphinDB with external systems such as Snowflake, where type conversions and incremental loading strategies may apply.

SQL SELECT Query Examples in DolphinDB

DolphinDB supports both its own query syntax and an ANSI SQL-ish subset. Here are some common SQL SELECT statement examples:

``` -- Selecting all columns and rows from a table SELECT * FROM trades;

-- Selecting specific columns SELECT symbol, price, volume FROM trades;

-- Filtering data with a WHERE clause SELECT symbol, price FROM trades WHERE volume > 10000;

-- Aggregation example SELECT symbol, AVG(price) as avg_price FROM trades WHERE trade_date BETWEEN 2023.01.01 AND 2023.06.30 GROUP BY symbol;

-- Ordering results SELECT * FROM trades ORDER BY trade_date DESC LIMIT 10; ```

Datatype Conversion between DolphinDB and Snowflake

When transferring data between DolphinDB and Snowflake, keep in mind the possibility of datatype conversion. For instance:

• DolphinDB's

  DATE

  type may map to

  DATE

  in Snowflake.

• TIMESTAMP

  formats might need parsing.
• Numeric datatypes (

  INT

  ,

  FLOAT

  ,

  DOUBLE

  ) are usually compatible but confirm scale and precision.
• String encodings should be checked for compatibility (UTF-8 is standard in both).

Careful review and testing of datatype mapping is crucial to avoid unexpected issues during data replication.

Best Pattern: Initial Load and Incremental Loads

The most effective approach for synchronizing DolphinDB data to data warehouses (e.g., Snowflake) is:

1. Initial Load
2. Extract all data at once.
3. Typically performed only once per table.
4. Use a Database Query component without a filter clause to select all records.
5. Incremental Load
6. Extract only new or changed records since the last successful extraction.
7. Use the same Database Query component, this time with a filter clause (e.g., on an

   updated_at

   or

   timestamp

   column).

This strategy ensures efficient extraction and reduces ongoing data transfer costs. See details at Matillion Exchange: Incremental Load Data Replication Strategy.

Example Patterns

Initial Load (No Filter Clause)

SELECT * FROM trades

Incremental Load (With Filter Clause)

Let's suppose

last_extracted_timestamp

SELECT * FROM trades
WHERE trade_time > '${last_extracted_timestamp}'

Both approaches utilize the same Database Query component, with the filter clause applied dynamically for incremental loads.

For more on incremental replication, refer to Matillion's documentation.

Data Integration Architecture

An essential advantage of the ELT (Extract, Load, Transform) architecture is its ability to split the data integration process into two discrete steps by first loading data into Snowflake in advance of integration. This "divide and conquer" approach simplifies the overall workflow, allowing organizations to ingest large volumes of raw data quickly and address complex transformation requirements separately. Data integration inherently involves various data transformations—such as cleansing, standardization, and enrichment—and the most effective way to perform these tasks is through dedicated data transformation pipelines. With ELT, these transformations occur natively within the Snowflake database, leveraging its robust processing power. This design provides several benefits: data transformation and integration are executed quickly and on-demand, the process is highly scalable to match evolving workloads, and organizations do not need to invest in or maintain separate data processing infrastructure, resulting in both operational efficiency and cost savings.