Connecting to databricks using .Net

Question

Hello Team,

Is it possible to connect to Databricks and perform CRUD operations on catalog schema tables and Delta tables using .NET? If so, what approach is needed to connect to Databricks using JDBC or ODBC? Are there any specific libraries in Visual Studio that can facilitate this connection?

Furthermore, are there any .NET code snippets available that demonstrate how to connect to Databricks and execute queries on a Delta table or catalog schema table for clearer insights?

Regards,

Nagesh CL

Answer 1

It is indeed possible to connect to Databricks and perform CRUD (Create, Read, Update, Delete) operations on catalog schema tables and Delta tables using .NET. The approach involves using JDBC (Java Database Connectivity) or ODBC (Open Database Connectivity) to establish the connection. Below is an in-depth guide on how to achieve this:

Connectivity Approach Using JDBC or ODBC

1. JDBC Connection

To connect to Databricks using JDBC, you would typically use the Databricks JDBC driver. The following steps outline the process:

1. Download the JDBC Driver: Obtain the Databricks JDBC driver from the Databricks website or through Databricks documentation.
2. Configure the Connection String: Formulate a proper JDBC connection string. Below is an example format:

       jdbc:spark://<ServerHostname>:443/default;transportMode=http;ssl=true;httpPath=<HttpPath>;AuthMech=3;UID=token;PWD=<PersonalAccessToken>;
   

   Replace placeholders with your actual server hostname, HTTP path, and personal access token.
3. Use ADO.NET: Leverage ADO.NET to facilitate JDBC connections. A popular library that can help is CData ADO.NET Provider for Databricks.

       using System.Data;
       using CData.Databricks;
   
       public void ConnectToDatabricks()
       {
           string connectionString = "jdbc:spark://<ServerHostname>:443/default;transportMode=http;ssl=true;httpPath=<HttpPath>;AuthMech=3;UID=token;PWD=<PersonalAccessToken>;";
           using (DatabricksConnection connection = new DatabricksConnection(connectionString))
           {
               connection.Open();
               // Perform CRUD operations
           }
       }
   

2. ODBC Connection

For ODBC connections, Databricks provides a dedicated ODBC driver. The steps below walk through the setup:

1. Install the ODBC Driver: Download and install the ODBC driver for Databricks from the official Databricks documentation.
2. Set Up DSN (Data Source Name): Configure the ODBC DSN using the ODBC Data Source Administrator on your system.
3. Create and Open Connection: Use the System.Data.Odbc namespace in your .NET application to open an ODBC connection.

       using System.Data.Odbc;
   
       public void ConnectToDatabricksODBC()
       {
           string dsn = "dsn=Databricks";
           using (OdbcConnection connection = new OdbcConnection(dsn))
           {
               connection.Open();
               // Perform CRUD operations
           }
       }
   

Specific Libraries to Facilitate Connection

• CData ADO.NET Provider for Databricks: Makes it easier to interface with Databricks via ADO.NET.
• System.Data.Odbc: Provides the classes for ODBC connection.

These libraries support seamless integration within Visual Studio, facilitating connection setup and data interaction.

Sample Code Snippets for Performing Queries

Below are sample code snippets for executing queries on a Delta table or catalog schema table using .NET:

Using JDBC with CData ADO.NET Provider

using System;
using System.Data;
using CData.Databricks;

public class DatabricksConnector
{
    public void ExecuteQuery()
    {
        string connectionString = "jdbc:spark://<ServerHostname>:443/default;transportMode=http;ssl=true;httpPath=<HttpPath>;AuthMech=3;UID=token;PWD=<PersonalAccessToken>;";
        using (DatabricksConnection connection = new DatabricksConnection(connectionString))
        {
            connection.Open();
            using (IDbCommand command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM my_delta_table";
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["column_name"]);
                    }
                }
            }
        }
    }
}

Using ODBC

using System;
using System.Data.Odbc;

public class DatabricksODBCConnector
{
    public void ExecuteQuery()
    {
        string dsn = "dsn=Databricks";
        using (OdbcConnection connection = new OdbcConnection(dsn))
        {
            connection.Open();
            using (OdbcCommand command = new OdbcCommand("SELECT * FROM my_delta_table", connection))
            {
                using (OdbcDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["column_name"]);
                    }
                }
            }
        }
    }
}