Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Databricks Spark DirectQuery using SQL query

I am trying to run SQL against data stored in Azure DataBricks.   Using the existing ODBC connector i am able to use Odbc.Query to execute my SQL script, but this is only available as 'Import' mode...
Need Help
gbrueckl's avatar
gbrueckl
Most Valuable Professional
5 years ago

so a new, dedicated connector for Azure Databricks was introduced some time ago:
https://docs.microsoft.com/en-us/azure/databricks/integrations/bi/power-bi
which allows proper use of import mode and also direct query

however, executing arbitrary SQL or specifying a native SQL query on your own is currently not supported

I also wrote a blog post about this some time ago : https://blog.gbrueckl.at/2020/09/connecting-power-bi-to-azure-databricks/

 

basically, you can only select a SQL table or view directly. If you have some additional transformation steps in Power Query, the engine will try to push them to Databricks and to the heavy lifting there - e.g. projection of columns, WHERE conditions, GROUP BY, and aggregations, etc.

unfortunately the UI does not show whether a query can be pushed to databricks or not so you need to check on your own in the Spark UI of your cluster 

 

FYI DavisBI