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://learn.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
Hello @arsaalandailyUK 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:
- Add the service principle to Databricks using the SCIM API (as Administrator)
- Give this service principle can make and use token permissions (as Administrator)
- Get Azure AD token (as service principle)
- Authenticate using the Azure AD token (as service principle)
- Call Databricks token API to create token (as service principle)
Sign in to comment
@arsaalandailyUK did my response help you? If not ,please let me know how I may better assist.
arsaalandailyUK-7019 if you found your own solution, please share it here with the community.