Step by Step: Connecting to Databricks SQL in Microsoft Power Apps
Microsoft’s PowerApps is a low code business application development platform designed to be friendly for power users who:
- Know their company’s business processes
- Understand the technical and data flows for those processes
- Do not have deep software engineering or application development skills
You can think of it broadly in the same category as Microsoft Excel, Access, and Power BI — providing less-technical users with tooling to develop user interfaces for common workflow and analytical tasks. Most customers use Power Apps’ out-of-the-box UI functionality to build simple for-purpose workflows for narrowly scoped business processes (e.g. an accountant adding commentary to financial transactions, or a marketer assigning campaign targets.)
Although PowerApps offers a few native connectors to Microsoft database engines such as Azure SQL Server and Cosmos DB, the public custom connector SDK only supports web API-based connectors.
Luckily Databricks SQL offers an execution API, which is what we’ll be using in this guide to access data from Databricks and Unity Catalog in a Power App.
A word of warning: the PowerApps web-based development UI is a bit unforgiving if you make mistakes. I strongly advise you to save early and often, and that you proceed carefully and slowly, as even innocuous misclicks and typos can (literally!) permanently break your entire connector development.
Prerequisites
Before starting this tutorial, make sure you have the following already configured and handy:
- A Databricks SQL warehouse
- A personal access token that has permissions to connect to your workspace and use the warehouse
- Access to the Power Apps Studio
Overview
For this walkthrough, we’ll go through 3 steps:
- Defining the queries we want to run from our Power App, and what (if any) user inputs we’ll accept as parameters to customize the queries being sent to Databricks
- Creating a custom connector to the Databricks SQL Execution API with one method, or “action”, for each query we want to run, including our optional parameters. This connector will address:
- Authentication to Databricks
- Parsing the query results into Power App friendly data structures
- Testing and validation of our methods
3. Creating a basic Power App and integrating our connector’s methods into our application’s event behaviors.
Step 1: Defining the queries we want to run from the Power App
Each individual query / result set we want to call from our app will need its own method in our connector, so it’s important to determine them and list them out before proceeding to the connector.
Any valid Databricks SQL query is allowed, not just ones that read data — you can also write data, create objects, set permissions, and even invoke DBSQL’s new AI functions.
In this tutorial, we’ll be using the sample New York Taxi dataset provided in your Databricks workspace, and we’ll just have a single query: getting a count of the taxi trips starting in each New York City zip code for a given day.
In this tutorial, we’ll also allow the user to select the date from a “date picker” UI element in Power Apps.
IMPORTANT: The only valid user inputs are those which do not modify the resultset schema of our query. This means we cannot dynamically select columns or tables as part of our methods.
Some example valid user inputs are:
- Values for a WHERE clause
- Values for a JOIN condition
- Values for a CASE statement condition
In our case we just want to have a WHERE clause taking the user’s selected date as input to filter our results.
Our final query:
select count(1) as trip_count, pickup_zip
from samples.nyctaxi.trips
where date(tpep_pickup_datetime) = :date
group by allStep 2: Creating a custom connector for our queries
From the Power Apps homepage, go to the Custom connectors menu and create a new custom connector “from Blank”. (Once you have created one, you can reuse its Swagger file as a starting point to accelerate development.)
On the General tab:
- Set the Host to your Databricks workspace URL (e.g. adb-1234.4.azuredatabricks.net).
- Set the Base URL to /api/2.0/sql/ (the Databricks SQL Execution API endpoint.)
- Be sure the Scheme is set to its default value HTTPS.
Proceed to the Security tab.
On the Security tab:
You effectively have 3 options for authentication in PowerApps to your Databricks workspace’s API endpoints:
- a personal access token (PAT),
- a Databricks issued OAuth token,
- or (if you use Azure Active Directory as your identity provider for Databricks or your Databricks workspace is in Azure) you can use an Azure Active Directory token.
In this tutorial, we’ll just be using a PAT since it is the easiest to set up and offered in all Databricks workspaces. I will post a subsequent blog showing how to set up authentication using OAuth or AAD.
- Set the Authentication type to API Key.
- Set the Parameter label to API Key.
- Set the Parameter name to Authorization.
- Set the Parameter location to Header.
Proceed to the Definition tab.
We will be adding a method, Get Daily Trips, that connects to our SQL Execution API and sends our query above. As part of the specification we’ll configure default values and hide internal properties of our API call signature, as well as define a parameter for the date input we will use in our application.
While we could use the Power Apps web UI to create our API specification for our method, it’s a bit fiddly and time consuming (AMA), so I’ve shared the method as Swagger-compliant YAML here — for convenience sake we are just going to copy and paste this code into the Swagger file for our connector directly.
Flip the toggle to turn on the Swagger editor view of our API.
We are going to replace the paths object (pictured here on lines 12 and 13) with the following code.
Note that the warehouse_id is set to an invalid “dummy” default value abc123— simply change it to your chosen Databricks SQL warehouse’s ID in the YAML editor after pasting:
paths:
/statements:
post:
responses:
default:
description: default
schema:
type: object
properties:
results:
type: array
items:
type: object
properties:
trip_count:
type: string
description: trip_count
pickup_zip:
type: string
description: pickup_zip
description: results
summary: Get Daily Trips
description: Get Daily Trips
operationId: GetDailyTrips
parameters:
- name: body
in: body
required: false
schema:
type: object
properties:
warehouse_id:
type: string
description: warehouse_id
title: ''
default: abc123
x-ms-visibility: important
statement:
type: string
description: statement
title: ''
default: >-
select count(1) as trip_count,pickup_zip from
samples.nyctaxi.trips where date(tpep_pickup_datetime) = :date
group by all
x-ms-visibility: important
parameters:
type: array
items:
type: object
properties:
name:
type: string
description: name
title: ''
default: date
x-ms-visibility: advanced
type:
type: string
description: type
title: ''
default: DATE
x-ms-visibility: advanced
value:
type: string
description: value
title: DateValue
default: '2016-02-01'
description: parameters
wait_timeout:
type: string
description: wait_timeout
title: ''
default: 50sIf we have pasted the code in properly, we should see our new Get Daily Trips method appear in the Swagger UI on the right hand side of our code.
If we toggle off the Swagger editor, we should also see our new action available in the standard Power Apps connector UI.
Before we proceed with the rest of our development, let’s publish our connector to our Power Apps environment and verify everything is wired up properly.
In the menu at the top, click Create connector (I have already published mine, so my UI says Update connector.)
Assuming we’ve followed directions so far, this should update successfully, and our connector will show up in the list of custom connectors in our Power Apps environment. (Note the pencil icon on the far right — if you accidentally leave the custom connector editor UI, this is how you return to it.)
This however merely validates the API configuration, not that the connector actually works. To test the functionality of our connector and action, first we’ll need to create a Power Apps “connection” instance from our connector.
- Open the Connections list of your Power Apps environment in a new tab.
- Click + New connection at the top of the screen.
- Search for our connector name, and click on it to create a new connection based on the connector.
You’ll be prompted to provide your PAT at this time for authentication. In the API Key field, enter Bearer <your-PAT> — unfortunately at this time there’s no way to parameterize this field to only require a PAT, so be sure to include the “Bearer” keyword!
Click Create.
Now that we have a connection instance, let’s proceed to the Test tab to validate our code.
In the Connections section, if you don’t see your new connection available in the Selected connection dropdown, click the Refresh icon.
- Select your new connection from the dropdown.
- In the GetDailyTrips operation, note the modifiable parameters for our action.
- If you have not set the warehouse ID in your YAML to a valid SQL warehouse ID in your workspace, do so now.
- Set the value parameter to 2016–02–01.
- Click Test operation.
If all goes well, below the operation you should get a 200 Response status code and a body of results from the SQL Execution API.
Common non-200 errors here include:
- 404 — the URL generated for the request is not correct. double check the host, base URL, and path of the action in the Swagger Editor.
- 403 — the PAT provided is not correct, expired, or has insufficient permissions, or you forgot, typoed, etc. the Bearer keyword.
Hooray! Now that we have some results, we need to use Power App’s custom code capabilities to parse our result set into a JSON object Power Apps can use to populate a collection variable in our app.
Proceed to the Code tab.
- Enable the custom code on our connector by toggling the Code disabled switch to Code enabled.
- In the Operations dropdown, select our GetDailyTrips operation so this custom code is triggered when our operation is called
- In the code editor, paste the following code:
public class Script: ScriptBase {
public override async Task < HttpResponseMessage > ExecuteAsync() {
HttpResponseMessage response = await this.Context.SendAsync(
this.Context.Request,
this.CancellationToken);
var responseString = await response.Content.ReadAsStringAsync().ConfigureAwait(
continueOnCapturedContext: false
);
var result = JObject.Parse(responseString);
var _resultSet = new List < Dictionary < string,
Newtonsoft.Json.Linq.JToken >> ();
var _columns = new List < string > ();
foreach(var _c in result["manifest"]["schema"]["columns"]) {
_columns.Add(_c["name"].ToString());
}
foreach(var _r in result["result"]["data_array"]) {
var _newRow = new Dictionary < string,
Newtonsoft.Json.Linq.JToken > ();
var _colIndex = 0;
foreach(var _f in _r) {
_newRow.Add(_columns[_colIndex], _f);
_colIndex++;
}
_resultSet.Add(_newRow);
}
var y = new Dictionary < string,
List < Dictionary < string, Newtonsoft.Json.Linq.JToken >>> () {
{
"results",
_resultSet
}
};
response.Content = CreateJsonContent(JsonConvert.SerializeObject(y));
return response;
}
}Click Update connector to publish our changes to the Power Apps environment.
Once our connector is updated, let’s return to the Test tab to see how our custom code has modified the results.
Re-enter the parameters for our GetDailyTrips action and click Test operation.
We should now see that the shape of the returned JSON is different from before. It now produces an array of objects, where each object represents a row of our dataset, and each element of our object is a column name and a value.
We’re now ready to incorporate this connection into a Power App.
Step 3: Incorporating our connection into a Power App
As this tutorial is focused on demonstrating connectivity to Databricks SQL in a Power App, we will just be developing a “quick and dirty” app with a date picker and a table to hold our data.
In a new browser tab, navigate to the Apps list of your Power Apps environment. Create a new app from page design, and choose Blank canvas.
- Use the Add icon (“+”) in the left ribbon to add Date picker and Data Table components to the Screen1 parent component (i.e. the starting screen of our application.)
- Use the Data icon in the left ribbon to add our connection to the app.
- Click the Layers icon in the left ribbon. Select the App object. Select the OnStart behavior in the formula bar dropdown.
- Paste the following formula into the formula bar, replacing the connection and operation name as needed with your own names.
Collect(trips,
SQLExecDemo2.GetDailyTrips({parameters:
Table({name: "date", type: "DATE", value: "2016–02–16"})}).results)- In the layers menu, click the ellipsis for the App object and click Run OnStart. This is a one-time operation to instantiate the trips collection variable we’ll use to store the results of our operation.
- Select your Data Table component.
- In the properties pane, set Data source to the trips collection.
- In the Fields property, click the “0 selected” link. A subpane will open. Click + Add field and select the two fields from our result set. Click Add.
You should now see the results of our initial load in the data table.
Now we’ll add a simple event to update our data whenever our date picker value is changed.
- Select the Date Picker component. Choose the OnChange behavior from the formula bar dropdown above.
- Paste in the following code into the formula bar, again replacing the collection and operation name as necessary:
ClearCollect(trips,
SQLExecDemo2.GetDailyTrips({parameters:
Table({name: "date", type: "DATE",
value: Text(DateTimeValue(DatePicker1.SelectedDate),
"yyyy-mm-dd", "en-US") }
)}).results)The basics of our app are now complete. We can preview the app by pressing F5 or clicking the preview button in the top menu bar.
Please note the New York Taxi trip sample dataset provided in the Databricks workspace only has data in the date ranges of January 1, 2016 to February 29, 2016. Choosing dates outside of this range will throw an error (in a production environment, this error should properly be handled within our PowerApps behavior code.)
Recap
In this blog, we showed how to create a custom connector in Power Apps to connect to Databricks SQL, pass in user parameters, and then use Power Apps’ event behaviors to create low-code, data-driven applications.
Comments and feedback always welcome!