Integrate data from Apache Druid to Databricks using Matillion

Creating an Identity in Apache Druid

Apache Druid handles user authentication and authorization through its security extensions. To create a user (called an "Identity"), you must have enabled and configured the "Basic Security" extension. These instructions assume the Basic Security extension is in use and you have admin access.

Step 1: Activate the Basic Security Extensions

Ensure you have enabled the following extensions in your Druid

druid.extensions.loadList

json
[
  "druid-basic-security"
]

Step 2: Use the API to Create a User

Druid provides a REST API to manage users. Use a tool such as

curl

Create a User

Replace

<user_name>

bash
curl -u admin:admin_password -X POST \
  -H 'Content-Type: application/json' \
  -d '{"name":"<user_name>"}' \
  http://<COORDINATOR_HOST>:<PORT>/druid-ext/basic-security/authentication/db/login

8081

admin:admin_password

Set the User Password

bash
curl -u admin:admin_password -X POST \
  -H 'Content-Type: application/json' \
  -d '{"password":"<user_password>"}' \
  http://<COORDINATOR_HOST>:<PORT>/druid-ext/basic-security/authentication/db/<user_name>/credentials

Step 3: (Optional) Assign Roles and Permissions

1. Create a Role (if it does not exist):

   bash
       curl -u admin:admin_password -X POST \
         -H 'Content-Type: application/json' \
         -d '{"name":"<role_name>"}' \
         http://<COORDINATOR_HOST>:<PORT>/druid-ext/basic-security/authorization/db/roles

2. Assign Permissions to the Role: See the Druid security documentation to specify resource and actions (e.g., read/write on datasources).

3. Assign Role to the User:

   bash
       curl -u admin:admin_password -X POST \
         -H 'Content-Type: application/json' \
         -d '{}' \
         http://<COORDINATOR_HOST>:<PORT>/druid-ext/basic-security/authorization/db/users/<user_name>/roles/<role_name>

Additional Notes

• All API requests should be performed over HTTPS in a production environment.
• Replace

  <COORDINATOR_HOST>

  and

  <PORT>

  according to your deployment.
• For full details, review Druid's security documentation.

Apache Druid does not support conventional SQL

CREATE USER

Installing the JDBC driver

At the time of writing, the JDBC driver for Apache Druid is not bundled by default with Matillion Data Productivity Cloud. This is due to licensing or redistribution restrictions that require customers to download and install the driver manually. The following instructions will guide you through obtaining and uploading the JDBC driver for use with your Matillion workflows.

Downloading the JDBC Driver

1. Obtain the Driver
   Visit the official Apache Druid SQL documentation at https://druid.apache.org/docs/latest/querying/sql.html.
   Look for a download link for the JDBC driver on the page, and in particular, select a Type 4 JDBC driver (also known as a "fully Java" driver) for maximum compatibility and straightforward deployment.

2. Download and Extract
   Download the JAR file for the driver to your local machine. If the file is compressed (as a .zip or .tar.gz), extract it to obtain the actual

   .jar

   JDBC driver file.

Uploading the Driver to Matillion Data Productivity Cloud

To enable Matillion to connect to Apache Druid via JDBC, you need to upload the

.jar

1. Refer to Matillion Documentation
   Detailed instructions for installing external JDBC drivers can be found in the Matillion Data Productivity Cloud documentation:
   https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

2. Follow the Steps on the Documentation Page:

3. Access your Matillion Agent server as described in the documentation.
4. Locate the correct directory for uploading external JDBC drivers.
5. Place the downloaded Druid JDBC

   .jar

   file into that directory.
6. Restart the relevant agent or service to load the new driver, if required by the platform.

Configuring and Using the Driver in Matillion

After uploading the Apache Druid JDBC driver, refer to the Matillion usage guide for setup details and to begin connecting to your Druid data source:

• Usage instructions are available here:
  https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

From here, you can configure new database endpoints, create queries, and integrate Druid data as part of your Matillion workflows.

Checking network connectivity

To enable secure and reliable network connectivity between Matillion Data Productivity Cloud and your Apache Druid database, you must ensure that Apache Druid is configured to accept incoming connections based on your deployment type:

• Full SaaS Agent Configuration:
  Allow incoming connections from the Matillion Data Productivity Cloud SaaS agents. You will need to add the appropriate IP addresses to your firewall or access control list on the Apache Druid side. The latest list of IP addresses to permit can be found at https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS Deployment:
  In a Hybrid SaaS deployment, ensure that your Apache Druid instance allows network access from your own Virtual Private Cloud (VPC) where the Matillion Hybrid SaaS agent is running. You can use the utilities and information at https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/ to help verify and troubleshoot network access from your VPC to Apache Druid.

Additionally, if your Apache Druid database hostname is referenced using a DNS name, make sure that the relevant agent (Full SaaS or Hybrid SaaS) has network access to resolve this DNS address. This typically means the agent must be able to reach the DNS servers responsible for your database's domain.

Querying Data from Apache Druid

Apache Druid supports querying data using standard SQL, enabling seamless integration for data analysis and transformation workflows. Below are instructions and examples illustrating how to query Druid, recommend best practices for loading data, and considerations when pairing Druid with Databricks.

Example: SQL SELECT Statements for Apache Druid

Here are some standard SQL queries you might run against a Druid datasource:

``` -- Retrieve all rows from a datasource SELECT * FROM "wiki" LIMIT 100;

-- Retrieve aggregated event count per day SELECT FLOOR(__time TO DAY) AS day, COUNT(*) AS event_count FROM "wiki" GROUP BY 1 ORDER BY day DESC;

-- Filter by dimension and aggregate SELECT page, COUNT(*) AS edits FROM "wiki" WHERE "countryName" = 'United States' GROUP BY page ORDER BY edits DESC LIMIT 10; ```

Note:
"wiki" in this example is a Druid datasource (similar to a table).

Datatype Conversions Between Apache Druid and Databricks

When extracting data from Druid to load into Databricks, be aware of datatype conversions:

STRING

STRING

LONG

INTEGER

BIGINT

INTEGER

FLOAT

DOUBLE

FLOAT

DOUBLE

__time

TIMESTAMP

TIMESTAMP

ARRAY

MAP (beta/limited)

ARRAY

MAP

NULL

NULL

Always verify column datatypes after loading and perform explicit casting in your queries if necessary.

Data Loading Pattern: Initial Load Followed by Incremental Loads

The recommended approach for syncing data from Druid uses two load strategies:

1. Initial Load (Full Load)

• Use the Database Query component (or equivalent access method).
• Run a SELECT statement with no filter clause.
  Example:

  SELECT * FROM "wiki"

• This loads all existing data at once.

2. Incremental Load

• Use the same Database Query component.
• Add an appropriate filter clause to load only new/changed data.
  Example, using a timestamp column (usually

  __time

  ):

  SELECT * FROM "wiki"
      WHERE "__time" > TIMESTAMP '2024-06-01 00:00:00'

• Incremental conditions should reference a column that identifies new/modified records. Typically,

  __time

  (

  TIMESTAMP

  ) is used.

Read more on incremental load and replication strategies at the Matillion Exchange Article: Incremental Load Data Replication Strategy.

Tip: Use the same query structure for both initial and incremental loads, varying only the presence of the filtering clause in your

WHERE

Data Integration Architecture

Loading data in advance of integration, as practiced in Extract, Load, Transform (ELT) architectures, allows organizations to divide and conquer the data pipeline by splitting it into two distinct phases: first loading raw data into the target system and then performing integration and transformation as a separate step. This approach not only streamlines the workflow but also facilitates flexibility in data processing. Since data integration inherently requires transformation of data to align disparate sources, transformation pipelines—automated sequences of tasks—provide the most effective and reliable method to execute these operations at scale. Furthermore, a key advantage of ELT architecture within Databricks lies in conducting all transformation and integration activities inside the Databricks database itself. This means that processing can leverage the native scalability and speed of the Databricks platform, allowing for on-demand performance without the cost or complexity of maintaining external processing infrastructure.