Integrate data from Apache Druid to Amazon Redshift using Matillion

Creating an Identity in Apache Druid

By default, Apache Druid does not include built-in SQL commands for user management, but it does support role-based authentication and authorization using external identity providers. User accounts in Druid are typically managed via its SQL-based Basic Security extension. This extension allows for SQL statements to manage users (identities), roles, and permissions at runtime.

The instructions below assume that the Basic Security extension is enabled and properly configured on your Druid cluster.

1. Enable Basic Security (if not already done)

Ensure your

conf/druid/_common/common.runtime.properties

properties
druid.auth.authenticatorChain=["my-basic-authenticator"]
druid.auth.authorizer.name=my-basic-authorizer
druid.extensions.loadList=[..., "druid-basic-security", ...]

Replace

"my-basic-authenticator"

and

"my-basic-authorizer"

with your chosen names.

Restart Druid services after making configuration changes.

2. Create a User

To create a new user (identity), use the

CREATE USER

-- Create a user named 'analyst'
CREATE USER analyst PASSWORD 'SecurePa$$word1';

3. Granting Role(s) to the User

Typically, you will grant privileges via roles. First, create a role if necessary, then add privileges, and finally assign the role to the user.

``` -- Create a new role called 'analyst_role' CREATE ROLE analyst_role;

-- Grant SELECT privilege on all datasources to the role GRANT 'DATASOURCE' ON '.*' TO ROLE analyst_role;

-- Assign the role to the user GRANT analyst_role TO USER analyst; ```

4. Listing Users

To view all users:

SHOW USERS;

5. Altering or Dropping a User

To change a user's password:

ALTER USER analyst PASSWORD 'NewStrongerPassw0rd2';

To remove a user:

DROP USER analyst;

Notes

• Replace usernames, passwords, and privilege patterns to fit your security policy.
• Passwords are hashed using PBKDF2.
• Druid stores user and role data in its metadata storage.
• These commands require appropriate superuser access and a Druid router with SQL support.
• For more complex integration with LDAP, SAML, or other identity backends, consult the Druid Security documentation.

References

• Apache Druid Security Overview
• Druid Basic Security SQL Commands

Installing the JDBC driver

To connect Matillion Data Productivity Cloud to an Apache Druid database, you must manually install the JDBC driver. At the time of writing, this driver is not bundled with Matillion Data Productivity Cloud by default due to licensing or redistribution restrictions. The following steps outline how to download and install the correct JDBC driver and configure Matillion for Druid connectivity.

1. Download the Druid JDBC Driver

1. Visit the official Apache Druid SQL documentation.
2. Locate the section related to JDBC connectivity.
3. Look specifically for a Type 4 JDBC driver (a pure Java driver), as this is generally the preferred and most widely supported driver type for third-party integrations like Matillion.
4. Download the latest stable version of the JDBC driver (typically provided as a

   .jar

   file).

2. Install the JDBC Driver into Matillion

Once you have the Druid JDBC driver

.jar

1. Follow the step-by-step instructions to upload external JDBC drivers to Matillion Data Productivity Cloud found at: Uploading External Drivers
2. Ensure the

   .jar

   file is correctly uploaded and registered, making it available to your Matillion environment.
3. You may need to restart the relevant components or agents in Matillion for the new driver to be recognized.

3. Configure and Use the Connection

After installing the driver, refer to the usage instructions under: Database Query Component Usage for guidance on configuring a Druid connection and running queries from within Matillion.

By following the above steps, you can enable product integrations directly with your Druid cluster using the external JDBC driver.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your Apache Druid database, you must configure your Druid instance to allow incoming connections, depending on the type of deployment. Ensure the following requirements are met:

• Full SaaS Agent Configuration:
  Allow incoming connections to your Druid database from the IP addresses listed at: Matillion Security - Allowing IP Addresses. Configure your firewall or network access controls to permit these IP addresses.

• Hybrid SaaS Deployment:
  Allow incoming connections from the IP ranges associated with your own virtual private cloud (VPC). If you need to verify connectivity or determine which IP addresses are being used, you can find helpful utilities here: Matillion Exchange - Check Network Access.

Additionally, if your Apache Druid database is being referenced by a DNS name instead of a static IP address, ensure that the Full SaaS or Hybrid SaaS agent has network access to resolve the DNS address. This may require allowing outbound DNS (port 53) access and confirming that the relevant DNS servers are reachable from the agent environment.

Querying Data from Apache Druid

This guide describes how to query data from an Apache Druid database using SQL, touches on datatype considerations when syncing with Amazon Redshift, and outlines the recommended approach for initial and incremental loads using a Database Query component.

Apache Druid SQL Query Examples

Apache Druid supports SQL for querying data in a familiar way. Here are some example queries:

Example: Select All Data

SELECT *
FROM "my_datasource"

Example: Select With Filtering and Aggregation

SELECT
  "country",
  COUNT(*) AS "count"
FROM "my_datasource"
WHERE "__time" >= TIMESTAMP '2024-01-01 00:00:00'
GROUP BY "country"
ORDER BY "count" DESC

Example: Select Distinct Values

SELECT DISTINCT "user_id"
FROM "my_datasource"

Datatype Conversion: Druid and Redshift

When moving data between Druid and Redshift, be aware that datatypes may not match exactly and implicit conversions might occur. For example:

• Druid numeric types (

  DOUBLE

  ,

  FLOAT

  ,

  LONG

  ) may be loaded into Redshift as

  DOUBLE PRECISION

  ,

  REAL

  , or

  BIGINT

  .
• Druid's

  STRING

  maps to Redshift's

  VARCHAR

  .
• Druid's

  TIMESTAMP

  type should load as Redshift's

  TIMESTAMP

  .

Validate your schema mappings to avoid issues caused by datatype differences.

Initial vs. Incremental Loads Using Database Query

The best practice is to perform:

1. Once-off Initial Load
   This captures all available records at the current point in time and is usually performed as a full extract with no filters.

-- Initial load: query everything
    SELECT *
    FROM "my_datasource"

1. Incremental Loads
   After the initial load, regularly pull only new or updated records by applying a filter condition (commonly on the timestamp or an update marker). This ensures efficient data synchronization.

-- Incremental load: fetch new data only
    SELECT *
    FROM "my_datasource"
    WHERE "__time" > TIMESTAMP '2024-06-01 00:00:00'

You use the same Database Query component for both scenarios; the difference lies in whether a filter clause is provided. For more details, see the Incremental Load Data Replication Strategy.

Note: Always validate your filter logic for incremental loads to prevent missing or duplicating data during syncs.

Data Integration Architecture

Loading data into Amazon Redshift in advance of integration exemplifies the “divide and conquer” approach inherent to an Extract, Load, Transform (ELT) architecture. By first loading raw data batches into Redshift, and then separately performing the integration and transformation as distinct steps, organizations can manage complexity more effectively and improve reliability. Since data integration tasks always require transformations—such as combining fields, joining datasets, or reformatting data—the best practice is to handle these requirements using data transformation pipelines, which provide structured, repeatable processes. A further key advantage of the ELT model is that these transformations and integration routines occur entirely within the Redshift environment. This not only delivers high performance due to Redshift’s native processing power, but also allows transformations to be executed on-demand and to scale as needed. Most importantly, these benefits are achieved without the need for additional data processing servers or cloud infrastructure, offering both technical efficiency and significant cost savings.