cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
harirajsingh
Regular Visitor

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.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors