Databricks SQL endpoint as Linked Service in Azure Data Factory

[shadowinc]

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.

[Kaniz_Fatma]

Hi @shadowinc, You can use private endpoints in Azure Data Factory (ADF) to securely access Delta tables. These private endpoints allow communication between ADF and services like Azure Blob storage and Azu...¹.

Here are the steps to set it up:

1. Create a Data Factory:

   • If you haven’t already, create an Azure Data Factory instance.
   • You can use the Azure Data Factory user interface (UI) to create a pipeline.

2. Prepare Your Blob Storage and SQL Database:

   • Create a source blob in Azure Blob storage.
   • Upload data (e.g., an emp.txt file) to the blob storage.
   • Create a sink SQL table in your Azure SQL Database (e.g., dbo.emp).

3. Configure Linked Services:

   • Go to the Manage tab in ADF.
   • Under Linked services, select + New.
   • Choose the SQL Server tile and input the FQDN of your on-premises SQL Server, along with the username and password. Test the connection².

4. Or Use Azure SQL Managed Instance:

   • Alternatively, you can create a linked service for Azure SQL Database Managed Instance.
   • Input the host of your SQL Managed Instance, along with the username and password³.

Let me know if you need further assistance! 😊

[shadowinc]

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

[RobsonNLPT]

Hi.

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

Best

[Edthehead]

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]

Adf does not support adb sql endpoint as linked server.