Connect Pigment with Databricks SQL

This article explains how to connect Databricks data with Pigment using the Databricks SQL connector.

As with all native Pigment integrations, you must first configure a connection on the Integration page using the guidance below. Once configured, the connection is available to the Applications you select under Application access.

Before you begin

Pigment offers two Databricks connectors. Choose the connector that matches how you want to access your data.

This native integration lets you run a SQL query against a Databricks SQL warehouse to select, filter, join and aggregate the data you want to import into Pigment. Unlike the Databricks OpenSharing connector, which imports a pre-selected shared table, the Databricks SQL connector queries your warehouse directly, so you decide exactly which data to import.

To set up the integration, you'll create a service principal in Databricks and a connection in Pigment. The sections below walk through both.

⚠️ Important

The service principal needs permissions at two separate levels in Databricks: workspace entitlements and a direct grant on the specific SQL warehouse. Missing either causes the connector test and any import to fail with a permission error. See Step 1 for details.

Access and Permissions

Before configuring the Databricks SQL integration, ensure you have the following requirements:

• A Databricks workspace and SQL warehouse. The warehouse is used to run the queries you configure in Pigment.

• A Databricks-managed service principal. Pigment authenticates using OAuth M2M with a service principal you create and manage in Databricks.

• Workspace entitlements and warehouse permission for the service principal. Required before the connection will work. See Step 1 for the specific entitlements and warehouse permission needed.

• Permissions to create service principals and manage warehouse permissions. You must have the required Databricks permissions to create a service principal, generate OAuth secrets and grant warehouse access. Contact your Databricks administrator if you do not have the necessary permissions.

You do not need a catalog or schema before you begin. Your SQL query specifies the data to retrieve at import time.

Step 1: Set up the service principal in Databricks

Prepare a Databricks service principal for Pigment to connect to your warehouse.

1. In Databricks, go to Settings, then Identity and access.

2. Select Service principals and select Add service principal (or select an existing one). Choose Databricks managed rather than Microsoft Entra ID managed, then name the service principal.

3. Under Entitlements, confirm the following are enabled:

   • Databricks SQL access

   • Workspace access

   • Consumer access

4. Go to the Secrets tab and select Generate OAuth secret. Choose a lifetime between 1 and 730 days, then copy the Secret and Client ID shown.

   ℹ️ Note

   The secret value is shown only once, immediately after generation. Copy it right away, since Databricks won't display it again. If you lose it, generate a new secret and update the connection in Pigment.

5. Go to SQL Warehouses, select the warehouse you want Pigment to query, open Permissions, and grant the service principal at least Can use on that specific warehouse.

   ⚠️ Important

   This step is required even with the entitlements above already enabled. Entitlements alone don't grant access to a specific warehouse. If you skip it, Pigment surfaces an error such as:

   The Databricks SQL workspace refused the request: PERMISSION_DENIED: You do not have permission to use the SQL warehouse.

6. Note the workspace URL and the warehouse's Warehouse ID (shown on the warehouse's overview page). You'll need both in Step 2.

7. Select Done.

Step 2: Set up the connector in Pigment

When you have your Databricks credentials set up, you can set up the connector in Pigment.

To do this:

1. In your Workspace, go to Integrations and locate Databricks SQL.

2. Select + Add or if Databricks SQL is already installed, select Manage, then + Set up a new connection.

3. Fill in the connection details:

   • Name. Enter the name of your connection.

   • Application access. Select the Applications that can use this connection.

   • Workspace URL. Paste the full Databricks workspace URL. Pigment reads only the host portion.

   • Application (Client) ID. The client ID of the service principal from Step 1, this is the same value as the service principal's UUID.

   • Client OAuth secret. The OAuth secret generated in Step 1. Stored encrypted.

   • SQL warehouse ID. The warehouse ID from Step 1.

4. Select Setup.

Pigment validates the connection by executing a simple statement against the configured warehouse. A successful test shows a Connection successful confirmation in the dialog. If the workspace URL, warehouse or credentials are incorrect, or if the service principal is missing a permission from Step 1, Pigment displays the underlying error.

Step 3: Load Databricks data into Pigment

After the Databricks SQL connection is configured, access an Application linked to this connection and open the Import Data panel for the relevant Block.

For example, if you are importing data into a Transaction List, you would open the List and select Import data.

1. Select Import, then select Setup.

2. Select Integrations, then select the Databricks SQL connection you just configured.

   ⚠️ Important

   If you don't see the connection, go to Integrations and confirm that the Application is on the connector’s Application access list.

3. Enter a SQL query that returns the data you want to import. Use fully qualified table names in the form catalog.schema.table, since no catalog or schema is preset on the connection:

   SELECT
     customer_id,
     order_date,
     amount
   FROM catalog_name.schema_name.orders;
   

   You can use SQL to filter rows, join tables and aggregate data before importing it into Pigment, provided the service principal has the required permissions on those catalogs and schemas.

4. Select Set up Import and complete the import configuration.

   ⚠️ Important

   The import preview retrieves a limited number of rows from your query results, regardless of how many rows the full query would return.

Working with complex data types

The Databricks SQL connector does not import arrays, maps or structs directly. If your query returns a column with one of these types, its values appear as empty cells in Pigment.

To import these values as text, convert them to strings in your SQL query. For example:

SELECT
  CAST(array_col AS STRING) AS array_col,
  CAST(map_col AS STRING) AS map_col,
  CAST(struct_col AS STRING) AS struct_col
FROM catalog_name.schema_name.table_name;

Replace the table and column names with your own. Pigment imports the converted values as text.

Understand the resulting text format

Casting to STRING does not necessarily produce JSON or preserve field names. For example:

SELECT
  CAST(array(1, 2, 3) AS STRING) AS array_col,
  CAST(map('a', 1, 'b', 2) AS STRING) AS map_col,
  CAST(
    named_struct('name', 'Ada', 'age', 37)
    AS STRING
  ) AS struct_col;

This query produces the following text values:

In this example, the struct's values are retained but its field names are not. If you need a particular text format, perform that conversion in your SQL query before importing.

⚠️ Important

The OpenSharing connector automatically converts complex values to text. With Databricks SQL, you must explicitly convert these values in your query. The resulting text format may differ between connectors.