Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
My need to to somehow be able to:
1. Be able to connect to Azure Databricks (ODBC is fine)
2. Be able to run SQL scripts
3. It should work in DirectQuery mode.
Following are few things which i have tried.
A. Spark Connector + DataQuery allows me to use Tables/View, but i cannot run SQL Query. The Spark connector does not have query option.
B. ODBC Connector + SQL Script allows me to run SQL script, but it works in Import Mode. Does not have option to perform direct query.
C. Custom Connector using Odbc.query, if I force enable 'Direct Query', but as soon as I try and use it shows '
DirectQuery error: DirectQuery may not be used with this data source - please consider moving to a supported data source or upgrading the SQL Server data source to the latest available version.' error message when i try and Apply the query.
// This file contains your Data Connector logic
section TestDataConnector;
/* This is the method for connection to ODBC*/
[DataSource.Kind="TestDataConnector", Publish="TestDataConnector.Publish"]
shared TestDataConnector.Databases = (dsn as text, query as text) as table =>
let
ConnectionString = [
DSN=dsn
],
OdbcDatasource = Odbc.Query(ConnectionString, query)
in OdbcDatasource;
// Data Source Kind description
TestDataConnector = [
// Authentication Type
Authentication = [
Implicit = []
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
TestDataConnector.Publish = [
Category = "Table",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = TestDataConnector.Icons,
SourceTypeImage = TestDataConnector.Icons,
// This is for Direct Query Support
SupportsDirectQuery = true
];
TestDataConnector.Icons = [
Icon16 = { Extension.Contents("TestDataConnector16.png"), Extension.Contents("TestDataConnector20.png"), Extension.Contents("TestDataConnector24.png"), Extension.Contents("TestDataConnector32.png") },
Icon32 = { Extension.Contents("TestDataConnector32.png"), Extension.Contents("TestDataConnector40.png"), Extension.Contents("TestDataConnector48.png"), Extension.Contents("TestDataConnector64.png") }
];
If anyone has done or know of a way, do let me know.
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
Hey @Anonymous did you manage to solve this?
Hi @Anonymous
Could you figureout resolution for this issue ? I am facing the same issue.