cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
harirajsingh
Frequent 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
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors