Allow an external user to query SQL table in Databricks

[adityasimplifix]

I have a delta table sitting in a schema inside a catalog. How do I allow an external user to query the SQL table via an API? I scrolled through documentation and a lot of resources but it's all so confusing. The AI assistant is way too naive. Can someone point me towards the correct guide?

[turagittech]

I think you would want Delta Sharing. https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-sharing If you're Azure or GCP you need to find the equivalent. 

[adityasimplifix]

I think Delta sharing would do the trick but is it possible to query the table via this technique based on certain parameters? In open sharing mechanism, it returns you the data in form of pandas dataframe when using python connector (bearer token method). I also looked through the delta-sharing protocol's GitHub README but there is no specific mention about how to query the database based on certain conditions? Also, is the size restriction efficient when compared to SQL Execution API?

[thackman]

We will need a bit more information. Are you asking: A) how an external user who is skilled at code can invoke a sql query via api. or B) how a non-technical external user can run a query via a simple ui?

If it's option A: then you can create a personal access token in the portal and it can be used to call the sql execution rest api. It works well for small and medium queries. Once the result is over 16MB-20MB, i forget the exact limit, you have to retrieve the result in chunks.

https://learn.microsoft.com/en-us/azure/databricks/dev-tools/sql-execution-tutorial

https://docs.databricks.com/api/azure/workspace/statementexecution

This is the example from the docs where they invoke the sql query api from the bash shell.