cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

 

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.

3 REPLIES 3
gbrueckl
Regular Visitor

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 

kaduservian
New Member

Hey @Anonymous did you manage to solve this?

himavardhan
Helper I
Helper I

Hi @Anonymous 

 

Could you figureout resolution for this issue ? I am facing the same issue.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors