Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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 =>
        ConnectionString = [
        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 = "",
    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.

Frequent Visitor

so a new, dedicated connector for Azure Databricks was introduced some time ago:
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 :


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 

New Member

Hey @Anonymous did you manage to solve this?

Not applicable

Hi @Anonymous 


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

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors