Skip to main content

Connect to Databricks from Microsoft Power Platform

Preview

This feature is in Public Preview.

This page explains how to connect to Databricks from Microsoft Power Platform by adding Databricks as a data connection. When connected, you can use your Databricks data from the following platforms:

  • Power Apps: Build applications that can read from and write to Databricks, while preserving your Databricks governance controls.
  • Power Automate: Build flows and add actions that enable executing custom SQL or an existing Job and get back the results.
  • Copilot Studio: Build custom agents using your Databricks data as a knowledge source.

Before you begin​

Before you connect to Databricks from Power Platform, you must meet the following requirements:

  • You have a premium Power Apps license.
  • You have a Databricks account.
  • You have access to a SQL warehouse in Databricks.

Step 1: Add a Databricks connection to Power Platform​

note

If you're using Copilot Studio, the Databricks connector must be defined using Power Apps or Power Automate. Then it can be used in Copilot Studio.

To add a Databricks connection, do the following:

  1. In Power Apps or Power Automate, from the sidebar, click Connections.

    If you don't see Connections on the sidebar, click More > Connections.

  2. Click + New connection in the upper-left corner.

  3. Search for "Databricks" using the search bar in the upper-right.

  4. Click the Databricks tile.

    Do not click the "Azure Databricks" tile.

  5. Enter your authentication information.

    • For API Key, enter your PAT in the format of “Bearer <personal-access-token>”. To generate a PAT, see Databricks personal access token authentication.
    • For Server Hostname, enter the Databricks SQL warehouse server hostname.
    • For HTTP Path, enter the SQL warehouse HTTP path.

    To find your Databricks SQL warehouse and HTTP path connection details, see Get connection details for a Databricks compute resource.

  6. Click Create.

Step 2: Use the Databricks connection​

After you create a Databricks connection in Power Apps or Power Automate, you can use your Databricks data to create Power canvas apps, Power Automate flows, and Copilot Studio agents.

Use your Databricks data to build Power canvas apps​

To add your Databricks data to your application, do the following:

  1. From the leftmost navigation bar, click Create.
  2. Click Start with a blank canvas and choose your desired canvas size to create a new canvas app.
  3. From your application, click Add data > Connectors > Databricks. Click the Databricks connection you created.
  4. Select a catalog from the Choose a dataset sidebar.
  5. From the Choose a dataset sidebar, select all the tables you want to connect your canvas app to.
  6. Click Connect.

Data operations in Power Apps:

The connector supports create, update, and delete operations, but only for tables that have a primary key defined. When performing create operations you must always specify the primary key.

Databricks supports generated identity columns. In this case, primary key values are automatically generated on the server during row creation and cannot be manually specified.

Use your Databricks data to build Power Automate flows​

The Databricks Statement Execution API and Jobs API are exposed within Power Automate, allowing you to write SQL statements and run existing Jobs. To create a Power Automate flow using Databricks as an action, do the following:

  1. From the leftmost navigation bar, click Create.

  2. Create a flow and add any trigger type.

  3. From your new flow, click + and search for "Databricks" to see the available actions.

  4. To write a SQL query, select one of the following actions:

    • Execute a SQL Statement: Write and run a SQL statement. Enter the following:

      • For Body/warehouse_id, enter the ID of the warehouse upon which to execute the SQL statement.
      • For Body/statement_id, enter the ID of the SQL statement to execute.

      For more about the advanced parameters, see here.

    • Check status and get results: Check the status of a SQL statement and gather results. Enter the following:

      • For Statement ID, enter the ID returned when the SQL statement was executed.

      For more about the parameter, see here.

    • Cancel the execution of a statement: Terminate execution of a SQL statement. Enter the following:

      • For Statement ID, enter the ID of the SQL statement to terminate.

      For more about the parameter, see here.

    • Get result by chunk index: Get results by chunk index, which is suitable for large result sets. Enter the following:

      • For Statement ID, enter the ID of the SQL statement whose results you want to retrieve.
      • For Chunk index, enter the target chunk index.

      For more about the parameters, see here.

  5. To interact with an existing Databricks Job, select one of the following actions:

    • List jobs: Retrieves a list of jobs. For more information, see here.
    • Trigger a new job run: Runs a job and returns the run_id of the triggered run. For more information, see here.
    • Get a single job run: Returns metadata about a run, including run status (e.g., RUNNING, SUCCESS, FAILED), start and end time, run durations, cluster information, and so on. For more information, see here.
    • Cancel a run: Cancels a job run or a task run. For more information, see here.
    • Get the output for a single run: Retrieves the output and metadata of a single job or task run. For more information, see here.

Use Databricks as a knowledge source in Copilot Studio​

To add your Databricks data as a knowledge source to a Copilot Studio agent, do the following:

  1. From the sidebar, click Agent.

  2. Select an existing agent or create a new agent by clicking + New agent.

    • Describe the agent by inputting a message and then click Create.
    • Or, click Skip to manually specify the agent’s information.

  3. In the Knowledge tab, click + Knowledge.

  4. Click Advanced.

  5. Click Databricks as the knowledge source.

  6. Input the catalog name your data is in.

  7. Click Connect.

  8. Select the tables you want your agent to use as a knowledge source and click Add.

Conduct batch updates​

If you need to perform bulk create, update, or delete operations in response to Power Apps inputs, Databricks recommends to implement a Power Automate flow. To accomplish this, do the following:

  1. Create a canvas app using your Databricks connection in Power Apps.

  2. Create a Power Automate flow using the Databricks connection and use Power Apps as the trigger.

  3. In the Power Automate trigger, add the input fields that you want to pass from Power Apps to Power Automate.

  4. Create a collection object within Power Apps to collect all of your changes.

  5. Add the Power Automate flow to your canvas app.

  6. Call the Power Automate flow from your canvas app and iterate over the collection using a ForAll command.

    SQL
    ForAll(collectionName, FlowName.Run(input field 1, input field 2, input field 3, …)

Concurrent writes​

Row-level concurrency reduces conflicts between concurrent write operations by detecting changes at the row-level and automatically resolving conflicts that occur when concurrent writes update or delete different rows in the same data file.

Row-level concurrency is included in Databricks Runtime 14.2 or above. Row-level concurrency is supported by default for the following types of tables:

  • Tables with deletion vectors enabled and without partitioning
  • Tables with liquid clustering, unless deletion vectors are disabled

To enable deletion vectors, run the following SQL command.

SQL
ALTER TABLE table_name SET TBLPROPERTIES ('delta.enableDeletionVectors' = true);

For more information about concurrent write conflicts in Databricks, see Isolation levels and write conflicts on Databricks.

Add Databricks to a data policy​

By adding Databricks to a Business data policy, Databricks can't share data with connectors in other groups. This protects your data and prevents it from being shared with those who should not have access to it. For more information, see Manage data policies.

To add the Databricks connector to a Power Platform data policy:

  1. From any Power Platform application, click the settings gear in the upper-right side, and click Admin Center.
  2. From the sidebar, click Policies > Data Policies.
    • If you are using the new admin center, click Security > Data and Privacy > Data Policy.
  3. Click + New Policy or an existing policy.
  4. If creating a new policy, enter a name.
  5. Select an environment to add to your policy to and click + Add to policy above.
  6. Click Next.
  7. Search for and click the Databricks connector.
  8. Click Move to Business and click Next.
  9. Review your policy and click Create policy.

Limitations​

  • The Power Platform connector does not support government clouds.

Power App limitations​

The following PowerFx formulas calculate values using only the data that has been retrieved locally:

Category

Formula

Table function

  • GroupBy
  • Distinct

Aggregation

  • CountRows
  • StdevP
  • StdevS
Last updated on