Skip to main content

Azure Databricks Access

CXM reads the cost, usage and configuration of your Azure Databricks workspaces to recommend optimizations. This guide adds Databricks access to an existing CXM Azure integration. You do not create a new identity: CXM uses the same Service Principal (cxm-asset-crawler).

What CXM Reads​

DataSourceAccess
Databricks usage and list pricessystem.billingSELECT
Clusters, node types and warehouse eventssystem.computeSELECT
Jobs and pipeline runssystem.lakeflowSELECT
SQL query history (duration, size, warehouse)system.querySELECT
Audit eventssystem.accessSELECT
Databricks commit unit (DBCU) reservationsAzure reservationsReservations Reader
Query history and audit events

system.query holds the SQL text and the user of each query. system.access holds the audit events of the account. CXM uses this data only to find cost optimizations. To exclude them, skip the system.query and system.access lines of the grants in Step 2.4. With Terraform, remove query and access from the system_schemas input of the submodule.

Prerequisites​

  • The CXM Azure integration is set up (Terraform or Manual). Keep its Client ID and its Tenant ID at hand.
  • Your workspaces are attached to a Unity Catalog metastore.
  • A workspace admin of one workspace per metastore adds the Service Principal and creates the SQL warehouse.
  • An account admin or a metastore admin enables the system schemas.
  • A user who is both a metastore admin and an account admin runs the grants. A workspace admin cannot make grants on objects that the workspace admin does not own (Admin privileges in Unity Catalog, Grant access to system tables).
  • A metastore that automatic Unity Catalog enablement created may have no metastore admin. In this case, an account admin assigns the metastore admin role first (same two pages).
  • For DBCU reservations: a user with the User Access Administrator role at root scope / (Delegate roles to all reservations). A Global Administrator gets this role through elevated access.

With Terraform​

The Terraform module does Step 1 and Step 2, except the enable of the system schemas. The manual steps below are for a reader without Terraform.

  • Step 1: set enable_reservations_reader = true on the root module github.com/cxmlabs/terraform-azure-cxm-integration, the module of the Terraform setup. If you elevated access for the apply, give the identity that runs Terraform Reservations Reader at /providers/Microsoft.Capacity before you remove the elevated access. Otherwise, later plans cannot read the assignment (reservations section of the root README).
  • Step 2: first enable the system schemas by hand, as Step 2, item 3 says. The module does not enable them. Then call the submodule github.com/cxmlabs/terraform-azure-cxm-integration//terraform-azure-databricks-access once per metastore. The submodule adds the Service Principal, the grants, the cxm warehouse and Can use.
  • Step 3: send the Tenant ID, and the values that terraform output cxm_databricks_values prints for each submodule call.

One identity holds the User Access Administrator role at root scope / and the workspace admin, metastore admin and account admin roles, and runs the apply of this configuration one time. Only if no single identity can hold all these roles, put the submodule in a configuration of its own, with the Client ID as a literal and no depends_on.

terraform {
required_providers {
databricks = {
source = "databricks/databricks"
}
}
}

provider "databricks" {
# The URL of one workspace of the metastore
host = "https://adb-1234567890123456.7.azuredatabricks.net"
}

module "cxm_integration" {
source = "github.com/cxmlabs/terraform-azure-cxm-integration"
# Merge into the main.tf of the Terraform setup
enable_reservations_reader = true
}

module "cxm_databricks_access" {
source = "github.com/cxmlabs/terraform-azure-cxm-integration//terraform-azure-databricks-access"

service_principal_application_id = module.cxm_integration.client_id
depends_on = [module.cxm_integration]
}

output "cxm_databricks_values" {
value = {
workspace_url = module.cxm_databricks_access.workspace_url
sql_warehouse_id = module.cxm_databricks_access.sql_warehouse_id
}
}

The submodule README gives the inputs and the provider alias for a second metastore. If you already did the manual steps:

  • The reservations section of the root README gives the import block of the Reservations Reader assignment.
  • If you created the cxm warehouse by hand, delete it before the apply, or import it into module.cxm_databricks_access.databricks_sql_endpoint.cxm. Otherwise, the apply creates a second cxm warehouse.

Step 1: Give Read Access to Reservations​

A user with the User Access Administrator role at root scope / runs this step. A Global Administrator gets this role through elevated access.

Azure reservations, DBCU reservations included, are outside subscriptions. The subscription Reader role does not cover them. Run:

az role assignment create \
--assignee <client-id> \
--role "Reservations Reader" \
--scope /providers/Microsoft.Capacity

If you are a Global Administrator who elevated access for this step, remove the elevated access after the command (Elevate access).

Step 2: Give Read Access to Each Workspace​

system.billing.usage and system.billing.list_prices hold the whole account. system.compute, system.lakeflow, system.query.history and the workspace events of system.access.audit hold only the region of the metastore. An account has one metastore per region. Do this step in one workspace per metastore (System tables).

  1. As a workspace admin, open Settings > Identity and access > Service principals > Manage > Add service principal > Add new. Choose Microsoft Entra ID managed, and paste the application (client) ID of the Azure integration (Manage service principals). A Databricks managed Service Principal does not map the Entra ID token of CXM. Give it the Databricks SQL access entitlement.

  2. As a workspace admin, open SQL Warehouses > Create SQL warehouse. Name it cxm, set the size to 2X-Small and auto-stop to 10 minutes. Set Scaling to a minimum and a maximum of 1 cluster. In its Permissions, give the CXM Service Principal Can use.

  3. As a metastore admin or an account admin, enable the system schemas (system-schemas commands). databricks metastores current prints the metastore_id of the workspace. Use it as METASTORE_ID. Then list the system schemas:

    databricks metastores current
    databricks system-schemas list METASTORE_ID

    Enable each schema whose state is AVAILABLE, one run for each schema. Skip a schema in ENABLE_COMPLETED or MANAGED, because it is already on. A schema in UNAVAILABLE cannot be enabled. Write each schema name as system-schemas list prints it:

    databricks system-schemas enable METASTORE_ID billing
    databricks system-schemas enable METASTORE_ID compute
    databricks system-schemas enable METASTORE_ID lakeflow
    databricks system-schemas enable METASTORE_ID query
    databricks system-schemas enable METASTORE_ID access
  4. As a user who is both a metastore admin and an account admin, open the SQL editor, select the cxm warehouse or another SQL warehouse that you can use, and run these grants. Replace <client-id> with the Client ID:

    GRANT USE CATALOG ON CATALOG system TO `<client-id>`;
    GRANT USE SCHEMA, SELECT ON SCHEMA system.billing TO `<client-id>`;
    GRANT USE SCHEMA, SELECT ON SCHEMA system.compute TO `<client-id>`;
    GRANT USE SCHEMA, SELECT ON SCHEMA system.lakeflow TO `<client-id>`;
    GRANT USE SCHEMA, SELECT ON SCHEMA system.query TO `<client-id>`;
    GRANT USE SCHEMA, SELECT ON SCHEMA system.access TO `<client-id>`;

    The grants are additive. They do not change the grants of other users.

Step 3: Provide Values to CXM​

Share the following with CXM, for each workspace of Step 2. CXM uses the Tenant ID to choose the Service Principal when your organization has more than one Azure datasource:

Tenant ID: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Workspace URL: https://adb-1234567890123456.7.azuredatabricks.net
SQL Warehouse ID: xxxxxxxxxxxxxxxx

Security Considerations​

  1. What this page grants: Reservations Reader on the reservations, USE CATALOG on the system catalog, USE SCHEMA and SELECT on the system schemas of Step 2.4, and Can use on the cxm warehouse. CXM runs only read queries.
  2. Default workspace access: The Service Principal joins the workspace users group, as each workspace user does. On a workspace with a workspace catalog, workspace users get USE CATALOG on that catalog, and USE SCHEMA, CREATE TABLE, CREATE VOLUME, CREATE MODEL, CREATE FUNCTION and CREATE MATERIALIZED VIEW on its default schema (Admin privileges in Unity Catalog). The Databricks SQL access entitlement gives access to queries, dashboards and SQL warehouses (Entitlements). To see these grants, run SHOW GRANTS ON CATALOG <workspace-catalog>; and SHOW GRANTS ON SCHEMA <workspace-catalog>.default;.
  3. Separate cost: CXM queries run on the cxm warehouse, so their cost stays separate from your other warehouses.
  4. Audit trail: Each CXM query is in system.query.history, under the CXM Service Principal.