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

arsaalandailyUK 26 Reputation points
2021-06-23T18:54:55.443+00:00

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

Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
1,947 questions
{count} votes