In this post, I will walk you through the step-by-step process of creating a custom connector for Azure DataBricks.

I had a project where the biggest challenge was querying data bricks and bringing real-time data to a Power Apps Canvas Application.

I had a look at the available connectors in the Power Platform (https://learn.microsoft.com/en-us/connectors/connector-reference/connector-reference-powerapps-connectors)

Unfortunately, there was no connector for Microsoft Azure Data Bricks. So, I had to build one 😁.

Azure DataBricks end point

Azure DataBricks exposes an API endpoint for all instances (unless you have deactivated it)

Your endpoint has the following format

https://YourTenantGuid.azuredatabricks.net/api/2.0/sql/statements/

 

This allows to send SQL queries and retrieve the results in different formats.

By default, the data is returned in a JSON array format.

Create the connector

Let’s get started !

First, navigate to your Power Apps platform (https://make.powerapps.com) and select Custom Connectors from the left-hand panel.

 

• If not available, click on ‘… More‘ > Discover All > And select Custom Connectors (bottom of the page).

From the custom connectors page, click “+ New custom connector” > “Create from blank”

This will open the following wizard process:

General

In the first tab, provide a:

• Name for your connector
• Icon & Color
• Description
• Connect via on-premises data gateway: Yes/No, it depends whether you want to connect via an on prem gateway or directly to Azure DataBricks.
• Protocol: HTTPS
• Define the custom connector end point:
  • Host: This is your Azure Data Bricks host
  • Base URL: This is the API End Point, set it to /api/2.0/sql/statements/ (If you want to learn more about the SQL Statments for Azure DataBricks, please visit https://docs.databricks.com/en/dev-tools/sql-execution-tutorial.html )

Click Security -> to move to the next section.

Security

It is important to set the security method, this defines how you would authenticate and interact with Azure DataBricks.

An API key is recommended.

Make sure you set the following parameters:

• Name: This is internal only, you can use any name (in my case, I have used API Key)
• Parameter Name: Very important, you have to specify “Authorization”
• Parameter Location: Header.

Generate your Bearer Token

• Navigate to your Azure Data Bricks platform.
• From the top right profile icon, Select Settings.

• Select Developers > Access Tokents > Manage

• Click Generate new token

• Give your token a name and duration (Leave it blank if you don’t want your token to expire)

• Once generated, make sure you copy this token in a safe place (please not in a piece of paper 😩)

Definition

Action

Now, let’s define the action, which will be your custom connector’s action, which will be called in either PowerApps or Power Automate,

Click on the “New action” button from the left side, and provide the following details.

• Summary: This is the name of your actions in the custom connector
• Description: Describe your action.
• Operation ID: This is the tager operation ID from Azure DataBricks; please specify “Run” (which is what the API exposes as an operation).
• Visibility: You can leave it to none; if you want to know more about the different visibility values, visit https://go.microsoft.com/fwlink/?LinkID=851038

Request

In this section, we will define how your request will look.

Let’s import the request’s definition from a sample, click on “Import from sample“, and provide the following JSON structure:

• Verb: Post
• Url: This will be your Azure DataBricks URL with the api/2.0/sql/statements at the end, so the overall value would look like this –>  “https://adb-xxxxxxxxxx.azuredatabricks.net/api/2.0/sql/statements/
• Header:

Content-Type: application-json

• Body:

{
  "warehouse_id": "xxxxxxxxxxx",
  "catalog": "xxxxxxxx",
  "schema": "xxxxxxxx",
  "statement": "your select statement for example select * from XY",
  "wait_timeout": "60s"
}

Once you click import, your request’s structure will look like this:

 

At this stage, you can start testing your custom connector, or if you wish, you can lock specific parameters from being accidentally modified or provided the wrong values.

To do so, you can select the body and edit each individual parameter by changing its visibility and/or providing a static/default value.

Test !

It’s time to test your custom connector.

PS: Save your changes and update your connector before proceeding.

Navigate to the last step (6. Test), and the first thing you need to do is to create a connection which will be used for testing.

 

Once you click on “New connection”, you will see the following pop up:

Make sure to enter your API key in the following format:

Bearer [your key]

e.g. Bearer dapi111111111111111111111111111111

And click create.

Now, go back to your custom connector and navigate to the test section, you should see your connection in the dropdown.

Scroll down to the operations section, and provide your Azure Databricks warehouse ID, Catalog, Schema and SQL Statement

Click “Test operation“.

If you’ve followed all the steps previously explained, you should have a successful response (200).