Integrate Power Apps with Azure Databricks

[Pugalendhi]

Hi Team,

Need sugestions to integrate power apps with Azure Databricks.

In power automate there is no inbuild connector to integrate with Azure Databricks, Need help in building custom connector to integrate power apps with Azure Databricks.

[RaviAchhwa]

Hi @Pugalendhi - Just came through with your post, did you got any way or workaround to connect Databricks connector ?

I have got this link which gives step by step to connect using Customer Connectors and API https://www.cdata.com/kb/tech/databricks-odata-powerapps.rst

Also please vote, this idea way already been added long back in 2020 but still in new state -  https://powerusers.microsoft.com/t5/Power-Apps-Ideas/PowerApp-Apache-Spark-Connector/idi-p/442544#M2...

@Msft @MSMSFT @EtMSFT - Please guide if this can be considered as its already available in Power BI. 

Thanks

[yhan]

One of the ways we used is to create an the API endpoint using AML SDK, and PowerApps can initiate the flow to make the HTTP request to the AML endpoint.

[muhammadh911]

Hi @yhan

Can you provide more details to layman

[santi95]

Has anyone managed to get this done?

[DGTize]

I'm also looking to perform this connection, will be checking back to see if anyone responds. There sure seems to be demand for it.

[shawn_eary]

It seems strange that I'm having a hard time finding ODBC/JDBC connectors in PowerApps. Am I not looking in the right places?

[stanislav-rupec]

We have been strugling with missing of that ODBC connector aswell. But I was able to create custom connector that can be used to call ADB SQL API. If this is something you are interested in here is how you can set it up.

1. Create new application/service pincipal in Entra ID(Azure AD) and create secret for it (save it somewhere safe for later use)
2. Provision that SP also to ADB
3. Assign needed rights on the WS/SQL WH level
4. In PowerApps create new custom connector
   
5. On General tab fill in your ADB WS hostname and SQL API as base URL (/api/2.0/sql/statements/)
   
6. On Security tab:
   select Auth. type: OAuth 2.0
   Identity provider: Azure Active directory
   Mark/Tick Service principal support
   Client ID: client ID of your service principal (you find that in Entra ID/Azure AD)
   Client secret: fill created secret from step I. when you were creating SP
   Authorization URL: https://login.microsoftonline.com
   Tenant ID: common
   Resource URL: 2ff814a6-3304-4ab8-85cb-cd0e6f879c1d  (default ID for ADB in Entra ID/AAD)
   Enable on-behalf-of login: false
   Scope: fill in your ADB WS URL + SQL API example:https://adb-000000000000000.0.azuredatabricks.net/api/2.0/sql/statements/
   Whole security tab example:
   
   
7. On Definition tab fill the first section as you need for example:
   
   In the request section click on  Import from sample:
   URL: ADB WS URL +/api/2.0/sql/statements/
   Headers: Content-Type application/json
   Body (can be modified as reqiured by your needs):
   {
     "warehouse_id": "your warehouse ID",
     "catalog": "name of your catalog",
     "schema": "name of your schema",
     "statement": "your select statement for example select * from XY",
     "wait_timeout": "how long should it wait in case your query is longer or your SQL WH is not running example: 60s"
   }
   
8. AI Plugin - can be skipped
9. Code any custom code you need for example to modify the request or response (optional)
10. Click create the connector
11. Once connector was created click on Test tab and create new connection. There from drop down menu select Service principal connection:
    
12. Fill in details about your SP and Tentant and click create:
    

If there was no issue with your setup you should be redirected to your connections and there will be your connection with state "Connected":

If you want to test the connection go back to custom connector you just created and edit your connetor by clicking on the pencil:

Go to the Test tab select correct connection you just crated:

Fill in details for your request and click "test operation":

Please note your ADB SQL WH should be running at that time or it will start it by that request. In case you have wait_timeout set too low it can return as pending state waiting for SQH WH to start :

If SQL WH is running you should recieve result of your query:

Hope this will help someone 🙂

Stanislav R.

[santi95]

This is helpful! How are you getting the resource URL? Is there any place I can verify it?

All I get is that the resource url was not found in tenant.

[stanislav-rupec]

I am not sure if you already have ADB WS created in your subscription and if so there should be also that default Enterprise application created. You can check it also here for example https://learn.microsoft.com/en-us/azure/databricks/administration-guide/access-control/conditional-a...

When we created our first ADB WS and setup unity catalog + SCIM connetor I was able to see that enterprise application in the Entra ID (Azure AD) automatically. Hope it helps 🙂