---
title: "Connect Pigment with Databricks SQL"
slug: "connect-pigment-with-databricks-sql"
description: "Learn to connect Databricks data with Pigment using the SQL connector, enabling precise data import and management for your applications."
tags: ["Databricks Integration", "SQL Query Setup", "Pigment Data Import"]
updated: 2026-10-26T16:25:54Z
published: 2026-10-26T16:25:54Z
canonical: "kb.pigment.com/connect-pigment-with-databricks-sql"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://kb.pigment.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Connect Pigment with Databricks SQL

![Pigment and Databricks logos combined, representing a partnership in data solutions.](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/databricksSQL.png?sv=2026-02-06&spr=https&st=2026-11-09T19%3A10%3A30Z&se=2026-11-09T20%3A10%3A30Z&sr=c&sp=r&sig=fDr58Wijqq1Xr09m7cHgfpENZ3mZXBrN7ZspK3gSrhM%3D)

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.

| Connector | How it works |
| --- | --- |
| Databricks SQL | Run a SQL query against a warehouse to select and prepare the data you want to import. |
| Databricks OpenSharing | Select a shared table to import using an OpenSharing profile. For more information, see [Connect Pigment with Databricks OpenSharing](/v1/docs/connect-pigment-with-databricks). |

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.

> [!WARNING]
> **⚠️ Important**
> 
> The service principal needs permissions at three separate levels in Databricks: workspace entitlements, warehouse grant, and catalog/schema grants. Missing any of these causes imports to fail. The connection test only checks credentials and the warehouse ID, so a successful test doesn’t mean permissions are complete. See [Step 1](/v1/docs/connect-pigment-with-databricks-sql#step-1-set-up-the-service-principal-in-databricks1) 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](/v1/docs/connect-pigment-with-databricks-sql#step-1-set-up-the-service-principal-in-databricks1) 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.

No catalog or schema is set on the connection itself, but the service principal needs read access to every catalog and schema your queries use (see [Step 1](/v1/docs/connect-pigment-with-databricks-sql#step-1-set-up-the-service-principal-in-databricks1)).

## 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**. Under scope, select **all-apis**. Don’t use the BI Tools preset (`sql` scope only): the connection will fail with *“Access denied by Databricks. The service principal may be missing required permissions.”* Choose a lifetime between 1 and 730 days, then copy the **Secret** and **Client ID** shown.

> [!NOTE]
> **ℹ️ 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.

> [!WARNING]
> **⚠️ 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, the connection test in Step 2 still succeeds, but imports fail with an error such as:
> 
> `The Databricks SQL workspace refused the request: PERMISSION_DENIED: You do not have permission to use the SQL warehouse.`
6. Grant the service principal read access to the data you want to import. In a Databricks SQL editor, run:

```sql
GRANT USE CATALOG ON CATALOG `<catalog_name>` TO `<client_id>`;
GRANT USE SCHEMA ON SCHEMA `<catalog_name>`.`<schema_name>` TO `<client_id>`;
GRANT SELECT ON SCHEMA `<catalog_name>`.`<schema_name>` TO `<client_id>`;
```

`SELECT` on the schema covers all current and future tables and views in it. Repeat for each catalog and schema your queries use. For more details, see the Databricks documentation on Unity Catalog privileges.
7. Note the **workspace URL** and the warehouse's **Warehouse ID** (shown on the warehouse's overview page). You'll need both in Step 2.
8. 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 authenticating with the service principal credentials and checking that the configured SQL warehouse exists and is accessible. A successful test does **not** confirm that the service principal can run queries on the warehouse or read your tables. These permissions are checked when you run an import.

## 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.

> [!WARNING]
> **⚠️ 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:

```sql
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.

> [!NOTE]
> **ℹ️ Note**
> 
> If your SQL warehouse is stopped, Databricks starts it when the import runs. This can take a minute or more, and the preview or import will wait until the warehouse is running. Any permission error is returned only after that.
4. Select **Set up Import** and complete the import configuration.

> [!WARNING]
> ⚠️ **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:

```sql
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:

```sql
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:

| Column | Imported text |
| --- | --- |
| array_col | [1, 2, 3] |
| map_col | {a -> 1, b -> 2} |
| struct_col | {Ada, 37} |

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.

> [!WARNING]
> **⚠️ 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.

## Troubleshooting

| Error / symptom | Likely cause | Fix |
| --- | --- | --- |
| “Access denied by Databricks. The service principal may be missing required permissions.” when importing | OAuth secret without `all-apis` scope | Regenerate the secret with `all-apis` |
| `PERMISSION_DENIED: You do not have permission to use the SQL warehouse` when importing | Missing warehouse grant | Grant **Can use** on the warehouse |
| Table/schema not found or insufficient privileges at import | Missing Unity Catalog grants | Apply the step 1.6 grants |
| Import preview hangs for a minute or more | Warehouse was stopped and is starting | Wait, or start the warehouse beforehand |