cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Databricks SQL endpoint as Linked Service in Azure Data Factory

shadowinc
New Contributor III

We have a special endpoint that grants access to delta tables and we want to know if we can use SQL endpoints as a linked service in ADF.

If yes then which ADF-linked service would be suitable for this?

Appreciate your support on this.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Edthehead
Contributor II

I have not tried this first hand however, from what I know, you have 2 options here. 

  1. The easy way is to use Azure Databricks Delta Lake linked service. In the setting you can mention the cluster id but this cluster id should correspond to an interactive cluster. Something like 0409-035326-4alfp636. If you try to point it to a Sql warehouse cluster, you will get an error saying that the cluster cannot be found. So you cannot use a Databricks SQL endpoint with this option but you can still read a Delta table. If the cluster is off, the pipeline execution will bring up the cluster and then execute your query. 
  2. The hard way is to use the ODBC linked service. However, this requires you to use a self hosted integration runtime. You cannot use the standard AzureIR. Install the required ODBC drivers on the SHIR and then use the ODBC linked service to connect to the Databricks SQL endpoint. You can refer to the Microsoft documentation on creating a ODBC linked service here. You can refer to the Databricks ODBC details as mentioned here.

Hope this helps.

View solution in original post

yashrg
New Contributor III

Azure Databricks Delta Lake (Dataset) uses a linked service that can only connect to a All Purpose/Interactive cluster.

If you want to use the SQL Endpoint, you would need a Self Hosted Integration Runtime for ADF with Databricks ODBC driver Installed on it. In ADF, the linked service would be a ODBC Linked Service

View solution in original post

7 REPLIES 7

@Retired_mod Thanks for the information, however, this query was about the Databricks SQL endpoint (which is like an engine to execute queries in Databricks)

RobsonNLPT
Contributor

Hi.

The topic is about databricks sql endpoint as a source using ADF and not Azure SQL.

Best

 

 

 

Edthehead
Contributor II

I have not tried this first hand however, from what I know, you have 2 options here. 

  1. The easy way is to use Azure Databricks Delta Lake linked service. In the setting you can mention the cluster id but this cluster id should correspond to an interactive cluster. Something like 0409-035326-4alfp636. If you try to point it to a Sql warehouse cluster, you will get an error saying that the cluster cannot be found. So you cannot use a Databricks SQL endpoint with this option but you can still read a Delta table. If the cluster is off, the pipeline execution will bring up the cluster and then execute your query. 
  2. The hard way is to use the ODBC linked service. However, this requires you to use a self hosted integration runtime. You cannot use the standard AzureIR. Install the required ODBC drivers on the SHIR and then use the ODBC linked service to connect to the Databricks SQL endpoint. You can refer to the Microsoft documentation on creating a ODBC linked service here. You can refer to the Databricks ODBC details as mentioned here.

Hope this helps.

RobsonNLPT
Contributor

Adf does not support adb sql endpoint as linked server.

 

SANJAYKJ
New Contributor II

I see linked service connect "Azure Databricks Delta Lake" in Azure Data Factory,  isn't it connecting to Databrcicks SQL Endpoint ?

 

yashrg
New Contributor III

Azure Databricks Delta Lake (Dataset) uses a linked service that can only connect to a All Purpose/Interactive cluster.

If you want to use the SQL Endpoint, you would need a Self Hosted Integration Runtime for ADF with Databricks ODBC driver Installed on it. In ADF, the linked service would be a ODBC Linked Service

SouldivPT
New Contributor II
Hi.  This is not my approach. 
The feature I expect is the same used by Power BI. Simply the adb sql serverless endpoint. 
 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group