question

arsaalandailyUK-7019 avatar image
0 Votes"
arsaalandailyUK-7019 asked MartinJaffer-MSFT commented

How to connect to Azure Databricks' Hive using a SQLAlchemy from a third party app using a service principal?

I want to connect Superset to a Databricks for querying the tables. Superset uses SQLAlchemy to connect to databases which requires a PAT (Personal Access Token) to access.

It is possible to connect and run queries when I use the PAT I generated on my account through Databricks web UI? But I do not want to use my personal token in a production env. Even so, I was not able to find how to generate a PAT like token for a Service Principal.

 The working SQLAlchemy URI is looks like this:
 databricks+pyhive://token:XXXXXXXXXX@aaa-111111111111.1.azuredatabricks.net:443/default?http_path=sql%2Fprotocolv1%qqq%wwwwwwwwwww1%eeeeeeee-1111111-foobar00

After checking the Azure docs, there are two ways on how to run queries between Databricks and another service:

Create a PAT for a Service Principal to be associated with Superset.
Create a user AD account for Superset.
For the first and preferred method, I was able to advance, but I was not able to generate the Service Principal's PAT: I was able to register an app on Azure's AD. So I got the tenant ID, client ID and create a secret for the registered app. With this info, I was able to curl Azure and receive a JWT token for that app. But all the tokens referred in the docs are JTW's OAUTH2 tokens, which does not seems to work with SQLAlchemy URI.

I know it's possible to generate a PAT for a Service Principal since there is a mention on how to read, update and delete a Service Principal's PAT on the documentation ( https://docs.microsoft.com/en-us/azure/databricks/administration-guide/access-control/tokens ). But it has no information on how to create a PAT for a Service Principal.

I prefer to avoid using the second method (creating an AD user for Superset) since I am not allowed to create/manage users for the AD.

In summary, I have a working SQLAlchemy URI, but I want to use a generated token, associated with a Service Principal, instead of using my PAT. But I can't find how to generate that token (I only found documentation on how to generate OAUTH2 tokens


azure-databricks
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @arsaalandailyUK-7019 and welcome to Microsoft Q&A.

This query took some digging, but I think I have a handle on it now.

Assuming you have Azure Databricks Premium Plan, you need to:

  1. Add the service principle to Databricks using the SCIM API (as Administrator)

  2. Give this service principle can make and use token permissions (as Administrator)

  3. Get *Azure AD* token (as service principle)

  4. Authenticate using the Azure AD token (as service principle)

  5. Call Databricks token API to create token (as service principle)


0 Votes 0 ·

@arsaalandailyUK-7019 did my response help you? If not ,please let me know how I may better assist.

0 Votes 0 ·

arsaalandailyUK-7019 if you found your own solution, please share it here with the community.

0 Votes 0 ·

0 Answers