How do I connect to Databricks with .NET and run delta table queries

Question

Is there a way to connect to Databricks and run Delta table queries from .NET? I've seen a few approaches using ODBC/JDBC but is there some other which excludes these types of connection? Maybe managed identity?

If applicable, where can I find some code samples which allow me to connect to Databricks via managed identity and later run queries on a delta table?

Thank you.

Answer 1

Hi @Martin Kostadinov ,

Thanks for using Microsoft Q&A !!
Unfortunately, there is no direct way for you to use .NET to connect to Databricks. Like you said you could use ODBC/JDBC for the connectivity. Also, there are few Databricks REST API's available, however, API's are mostly for managing Clusters, DBFS, Workspace API but nothing to create or manage Delta tables.

Please provide your feedback at Azure Databricks Feedback channel.

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Answer 2

Hi!

Is this still the case with Azure Delta Lake (for Synapse, but probably doesn't matter)? That one needs to go over ODBC/JDBC connection to access delta lake?

Answer 3

Update As of March 2023 You can use Databricks SQL Execution API to execute SQL queries from any language or application without needing to install the ODBC drivrs

Answer 4

<DUPLICATE PLEASE IGNORE>

Answer 5

Hello @Martin Kostadinov! Maybe this :

using Azure.Identity;
using System;
using System.Net.Http;
using System.Net.Http.Headers;

namespace DatabricksManagedIdentity
{
    class Program
    {
        static async System.Threading.Tasks.Task Main(string[] args)
        {
            // Set the Databricks workspace URL and Delta table name
            string workspaceUrl = "https://<databricks-instance>.azuredatabricks.net";
            string tableName = "my_delta_table";

            // Create a ManagedIdentityCredential to obtain an access token
            ManagedIdentityCredential cred = new ManagedIdentityCredential();

            // Create an HttpClient to make REST API calls to Databricks
            HttpClient client = new HttpClient();
            client.BaseAddress = new Uri(workspaceUrl);
            client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", await cred.GetTokenAsync(new Azure.Core.TokenRequestContext(new[] { "https://<databricks-instance>.azuredatabricks.net/.default" })));

            // Define the Delta table query
            string query = $"SELECT * FROM {tableName}";

            // Make a REST API call to run the Delta table query
            HttpResponseMessage response = await client.PostAsync("/api/2.0/sql/endpoints/create", new StringContent($"{{ \"language\": \"sql\", \"query\": \"{query}\" }}"));
            string jobId = await response.Content.ReadAsStringAsync();

            // Poll the job status until it is finished
            bool jobFinished = false;
            while (!jobFinished)
            {
                HttpResponseMessage statusResponse = await client.GetAsync($"/api/2.0/sql/jobs/status?job_id={jobId}");
                string statusJson = await statusResponse.Content.ReadAsStringAsync();
                dynamic status = System.Text.Json.JsonDocument.Parse(statusJson).RootElement.GetProperty("status").GetString();

                if (status == "RUNNING" || status == "PENDING")
                {
                    await System.Threading.Tasks.Task.Delay(TimeSpan.FromSeconds(5));
                }
                else if (status == "SUCCEEDED")
                {
                    jobFinished = true;

                    // Get the query results
                    HttpResponseMessage resultResponse = await client.GetAsync($"/api/2.0/sql/jobs/{jobId}/result");
                    string resultJson = await resultResponse.Content.ReadAsStringAsync();
                    Console.WriteLine(resultJson);
                }
                else
                {
                    jobFinished = true;
                    Console.WriteLine($"Job failed with status: {status}");
                }
            }
        }
    }
}

Links : https://docs.microsoft.com/en-us/azure/databricks/dev-tools/api/latest/aad/service-prin-aad-token https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/data-factory/connector-azure-databricks-delta-lake.md In case this helped kindly mark the answer as accepted ! BR