Databricks Spark DirectQuery using SQL query

Anonymous · ‎02-20-2020

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.

gbrueckl · ‎08-16-2021

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