Integrate data from CrateDB to Amazon Redshift using Matillion

Creating a User in CrateDB

This guide explains how to create a user identity in CrateDB using SQL statements. The following steps require sufficient privileges, such as access via an administrative account (e.g.,

crate

crash

1. Create a User

Use the

CREATE USER

<username>

CREATE USER <username>;

Example:

CREATE USER alice;

2. Set the User Password

Set the user's password for authentication:

ALTER USER <username> SET (password = '<plaintext-password>');

Example:

ALTER USER alice SET (password = 'securepassword123');

Note: Passwords are stored securely by CrateDB, but choose strong passwords and handle with care.

3. Grant Privileges to the User

New users have no privileges by default. Grant required privileges, such as

DQL

DML

DDL

ALL

Grant all privileges on a specific schema:

GRANT ALL PRIVILEGES ON SCHEMA <schema_name> TO <username>;

Example:

GRANT ALL PRIVILEGES ON SCHEMA doc TO alice;

Or grant specific privileges (e.g.,

DQL

GRANT DQL ON TABLE doc.mytable TO alice;

4. Verify User Creation

To check existing users:

SELECT name FROM sys.users;

Reference

• CrateDB: User Management Documentation

Installing the JDBC driver

To connect Matillion Data Productivity Cloud to CrateDB, you will need to manually download and install the CrateDB JDBC driver. This step is necessary because, at the time of writing, the CrateDB JDBC driver is not included with Matillion Data Productivity Cloud by default; this is due to licensing or redistribution restrictions.

Follow these steps to set up the CrateDB JDBC driver with Matillion Data Productivity Cloud:

1. Download the JDBC Driver

2. Visit the official CrateDB JDBC documentation page.

3. Locate the download option for the JDBC driver, ensuring you select a “Type 4 JDBC driver” for best compatibility.

4. Download the latest available

   .jar

   file for the driver to your local machine.

5. Upload the JDBC Driver to Matillion Data Productivity Cloud

6. Refer to Matillion’s documentation for installing external drivers: Uploading External Drivers.

7. Follow the documented steps to upload the downloaded CrateDB JDBC

   .jar

   file to your Matillion agent.

8. Ensure the agent restarts (or reloads the driver list if supported) in order for the new JDBC driver to become available.

9. Configure Usage in Matillion Data Productivity Cloud

10. Once installation is complete, configure a database connection or job in Matillion Data Productivity Cloud using the newly installed CrateDB JDBC driver.

11. For guidance on how to configure and use the new connection or driver inside the Designer, refer to Matillion’s database query usage documentation.

By following these steps, you will be able to integrate CrateDB as a source or target within your data workflows in Matillion Data Productivity Cloud utilizing the provided JDBC driver.

Checking network connectivity

To ensure successful connectivity between the Matillion Data Productivity Cloud and your CrateDB database, you must configure your firewall or network security rules to allow incoming connections based on your deployment type:

• Full SaaS Agent Configuration:
  If you are using Matillion's Full SaaS agent, you must permit incoming connections to your CrateDB database from the IP addresses specified by Matillion. The current list of authorized source IP addresses is maintained here. Be sure to reference the latest list and allow inbound access from all listed addresses.

• Hybrid SaaS Deployment:
  For a Hybrid SaaS deployment, incoming connections should be allowed from your organization’s own virtual private cloud (VPC) where the Hybrid agent is running. Matillion provides network connectivity verification tools at this link to help you test and validate your setup.

Additionally, if you are referencing your CrateDB database using a DNS hostname rather than a direct IP address, the relevant Matillion agent (Full SaaS or Hybrid SaaS) must be able to resolve this DNS address. Please ensure that DNS resolution is possible from the environment where the agent resides.

Querying Data from CrateDB

This guide demonstrates how to query data from a CrateDB database using SQL

SELECT

Example CrateDB

SELECT

Statements

Below are example queries illustrating typical read operations:

``` -- Select all columns and rows from the "sales" table SELECT * FROM sales;

-- Select specific columns SELECT order_id, customer_name, total_amount FROM sales;

-- Filtering records with a WHERE clause SELECT * FROM sales WHERE order_date >= '2024-01-01';

-- Aggregate functions with GROUP BY SELECT customer_id, COUNT(*) AS order_count FROM sales GROUP BY customer_id;

-- Joining two tables SELECT c.customer_name, o.order_id, o.total_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id; ```

Datatype Conversion: CrateDB ↔ Redshift

When migrating or syncing data from CrateDB to Redshift, be aware that datatype conversions may be necessary due to differences and limitations in each platform's type systems. For example:

• CrateDB's

  OBJECT

  type is not supported natively in Redshift and may need flattening or serialization to text (

  VARCHAR

  ).
• Numeric precision and scale may vary.
• Date and timestamp types may require format conversion.
• Boolean values: Ensure proper mapping (CrateDB

  BOOLEAN

  ↔ Redshift

  BOOLEAN

  ).

Plan and test type conversions during ETL development to avoid data integrity issues.

Data Loading Strategy: Initial and Incremental Loads

The optimal approach to move or replicate data from CrateDB is:

1. Once-off Initial Load
2. Incremental Loads for ongoing syncs

Use the same Database Query component for both, but alter the query filtering logic as follows:

Initial Load

• There is no filter clause.
• The query selects all relevant data from the source table.

-- Initial full load; no filter on last-modified/created
SELECT * FROM sales;

Incremental Loads

• Filter clause applies, usually on a monotonically increasing column (

  id

  ,

  updated_at

  , or

  created_at

  ).
• Only retrieves new or changed records since the last load.

-- Incremental load; filter on "updated_at"
SELECT * FROM sales
WHERE updated_at > '${last_loaded_timestamp}';

• Ensure the filtered column is indexed for optimal performance.
• Store and update the watermark (e.g., last timestamp) after each batch.

For more details and patterns, see Incremental Load Data Replication Strategy - Matillion Exchange.

References

• CrateDB SQL Reference
• CrateDB to Redshift ETL Considerations

Data Integration Architecture

Loading data into Amazon Redshift in advance of integrating it is a practical way to tackle data integration, effectively dividing the process into two distinct steps: loading (Extract and Load phases) and transforming (the Transform phase). This “divide and conquer” strategy is a core benefit of the ELT (Extract, Load, Transform) architecture, making complex data workflows easier to manage. Data integration typically requires extensive data transformation, such as cleaning, joining, and reshaping data, and the most efficient way to perform these operations is by using data transformation pipelines. In the ELT model, these transformations and integrations are carried out directly inside the target Redshift database. This approach offers significant advantages: it is fast, supports on-demand scaling, and leverages Redshift’s parallel processing capabilities. As a result, organizations benefit from reduced infrastructure costs, since there is no need to provision or pay for a separate data processing environment outside the database.