Integrate data from Couchbase to Snowflake using Matillion

Creating a User in Couchbase

This guide explains how to create a user (identity) in a Couchbase Server deployment. You can create users using either the Couchbase Web Console, CLI, or N1QL (SQL-like) queries through the REST API. This example uses N1QL via the Query Workbench or shell.

Prerequisites

• You require Cluster Admin or Security Admin privileges.
• Make sure you have access to the Couchbase Query Workbench or the

  cbq

  shell.

Example: Creating a User via N1QL

Couchbase user management is handled by the Administrator REST API and not directly by N1QL. However, for database scripting, you can use the REST API through tools such as

curl

1. Compose the User Creation Command

The following

curl

Example Command

bash
curl -u Administrator:password \
  -X POST http://localhost:8091/settings/rbac/users/local/john_doe \
  -d 'name=John Doe' \
  -d 'password=s3cretPassw0rd!' \
  -d 'roles=cluster_admin,bucket_admin[travel-sample]'

Parameters

• Administrator:password

  : Replace with your administrator credentials.

• john_doe

  : The new user's username (identity).

• name

  : User’s display name.

• password

  : User’s password.

• roles

  : Assigns roles to the user; for example,

  cluster_admin

  and

  bucket_admin

  for the

  travel-sample

  bucket. Comma-separate for multiple roles.

Example: Creating a User via Couchbase Web Console

1. Log in to the Couchbase Web Console as an administrator.
2. Navigate to Security ➔ Users.
3. Click Add User.
4. Specify the Username, Display Name, and Password.
5. Assign the necessary Roles (e.g.,

   Data Reader

   or

   Query Select

   on relevant buckets).
6. Click Save to create the user.

Notes

• Assign only the minimum required roles for the user in line with security best practices.
• For more information about roles, refer to the Couchbase RBAC documentation.

Installing the JDBC driver

At the time of writing, the Couchbase JDBC driver is not bundled by default within Matillion Data Productivity Cloud due to licensing and redistribution restrictions. To enable connectivity, you'll need to obtain and install the JDBC driver manually before using Couchbase as a data source.

Downloading the Couchbase JDBC Driver

1. Visit the official Simba download page: https://www.simba.com/drivers/couchbase-jdbc-odbc/.
2. On the page, review the available Couchbase drivers and select the Type 4 JDBC driver (also referred to as a "pure Java" implementation) for compatibility and simplicity.
3. Download the appropriate driver package to your local machine, ensuring you comply with the Simba license terms.

Installing the JDBC Driver into Matillion Data Productivity Cloud

The installation process requires uploading the Couchbase JDBC driver to your Matillion Agent. Please follow these steps:

1. Sign in to the Matillion Data Productivity Cloud UI with adequate administrative privileges.
2. Refer to the official instructions for uploading third-party JDBC drivers, located at: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/.

3. Follow the documented procedure to upload the JDBC JAR file you downloaded from the Simba website.

4. Ensure the filename and upload location adheres to documented requirements.

5. After uploading, restart the relevant Matillion Agent to activate the new driver (if indicated in the instructions).

Next Steps: Using the Couchbase JDBC Driver

Once the Couchbase JDBC driver is installed on your Matillion Agent, you can begin configuring and running Couchbase queries.

For detailed steps on connecting to your Couchbase database and issuing queries within Matillion Data Productivity Cloud, consult the database component usage guide at: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/.

By following these instructions and referencing the linked documentation, your Matillion environment will be equipped to leverage Couchbase as a source for data productivity workflows.

Checking network connectivity

To establish connectivity between Matillion Data Productivity Cloud and your Couchbase database, you must ensure that the Couchbase database allows incoming network connections from the appropriate source, depending on your deployment configuration.

• Full SaaS agent configuration:
  Permit inbound connections to your Couchbase database from the public IP addresses used by Matillion Data Productivity Cloud. The current list of IP addresses can be found at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS deployment:
  Allow incoming connections to your Couchbase database from the network range(s) associated with your own virtual private cloud (VPC). To help verify network connectivity in this setup, you can use the utilities and tools available here:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if your Couchbase database is referenced using a DNS name (not a raw IP address), ensure that the Full SaaS or Hybrid SaaS agent can resolve the DNS name correctly. Failure to resolve the DNS address will prevent Matillion agents from connecting to your Couchbase database.

Querying Data from Couchbase Database

To query data from a Couchbase database, you typically use the N1QL query language, which is similar to SQL for JSON data. The process is very familiar to users of relational databases, though you should be aware of potential datatype conversions (for example, when working with platforms such as Snowflake).

Below are instructions and examples to guide you:

Example: Couchbase SQL-Like Queries

Assume you have a bucket named

customers

Full Table Select

To fetch all documents (as in an initial full load):

SELECT *
FROM `customers`;

Selecting Specific Fields

To fetch only the

name

email

SELECT name, email
FROM `customers`;

Filtering Documents

To query records where

country

"USA"

SELECT *
FROM `customers`
WHERE country = "USA";

Suppose you want only records modified after a certain timestamp (using the field

last_modified

SELECT *
FROM `customers`
WHERE last_modified > "2024-05-01T00:00:00Z";

Note on Datatype Conversion

When moving data between Couchbase (with its JSON document model) and another database like Snowflake (tabular SQL model), be mindful of data type conversions, such as:

• JSON

  null

  vs. SQL

  NULL

• Couchbase numeric types vs. Snowflake numeric types
• Couchbase ISO date strings mapped to Snowflake

  TIMESTAMP

Proper mapping may require transformation at query time, or post-load data wrangling.

Recommended Load Pattern

A robust pattern for querying and ETL from Couchbase consists of:

1. Initial Load:
2. Use a Database Query (e.g., in a tool like Matillion) with no filter in the WHERE clause to extract all data.
3. Example (no filter):

SELECT *
     FROM `customers`;

SELECT *
     FROM `customers`
     WHERE last_modified > "2024-06-20T00:00:00Z";

Read more: Incremental Load Data Replication Strategy (Matillion Exchange)

The same Database Query component—changing only the filter—lets you seamlessly switch between full and incremental loads for efficient and manageable data movement.

Data Integration Architecture

Loading data in advance of integration represents a "divide and conquer" strategy, effectively splitting the integration process into two distinct steps. This approach is a key strength of the ELT (Extract, Load, Transform) architecture. Data is first loaded into the target database—in this case, Snowflake—before any integration or transformation occurs. Effective data integration requires robust data transformation, which is best achieved using purpose-built data transformation pipelines that automate and manage these tasks reliably. Furthermore, a major advantage of the ELT architecture is that both transformation and integration take place within the Snowflake environment itself. This ensures that data processing is fast, performed on-demand, and highly scalable, all without the need for external data processing resources or additional infrastructure costs.