CODEWITHSUNDEEP
jdbcodbcdatabricksazure-databricks
Eugene Goldberg
Eugene Goldberg

Reputation: 15554

ow to properly connect to Azure Databricks Warehouse from C# .NET using JDBC/ODBC driver

We are attempting to set up a connection between a C# .NET console app and Azure Databricks warehouse. We have followed these instructions to set up and configure the ODBC driver:

https://docs.databricks.com/en/integrations/jdbc-odbc-bi.html#odbc-windows

Here is the console app code:

using System;
using System.Data;
using System.Data.Odbc;

namespace DatabricksJdbcExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a connection string to the Databricks warehouse.
           // string connectionString = "odbc:databricks://adb-6817616643364.19.azuredatabricks.net:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/warehouses/827c2f60de39fa0e;";
            string connectionString = "DSN=databricksfiledsn";
            // Create a new OdbcConnection object.
            using (OdbcConnection connection = new OdbcConnection(connectionString))
                
            {
                // Open the connection.
                connection.Open();

                // Create a new OdbcCommand object.
                OdbcCommand command = new OdbcCommand("SELECT * FROM hive_metastore.demos_dlt_loans_eugene_goldberg117.historical_txs", connection);

                // Execute the command and get the results.
                OdbcDataReader reader = command.ExecuteReader();

                // Print the results to the console.
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Console.Write(reader.GetValue(i) + "\t");
                    }
                    Console.WriteLine();
                }

                // Close the reader.
                reader.Close();

                // Close the connection.
                connection.Close();
            }
        }
    }
}

Please, not that we have two variants of the connectionString: the first one has the entire connection string and the one below it is referencing the ODBC DSN which we have set up at the beginning. Interestingly, neither one works for us, as we are getting the same error message while using either connectionString variant:

System.Data.Odbc.OdbcException:  Data source not found and no default driver specified

What is the proper way to configure this to make it work?

Upvotes: 2

Views: 1728

Answers (1)

JayashankarGS
JayashankarGS

Reputation: 8110

Add the DSN setup like below:

enter image description here

In the Password field, provide the Databricks access token you created. It should look something like this: dapi...........

Select HTTP in the Thrift Transport options.

In the HTTP Options, add the HTTP path you have from Databricks. In the SSL Options, enable SSL.

For more details refer to this Stack Overflow solution.

Output:

enter image description here

If you want to use a connection string, format it like below:

Driver=<path-to-driver>;Host=<server-hostname>;Port=443;HTTPPath=<http-path>;ThriftTransport=2;SSL=1;AuthMech=3;UID=token;PWD=<personal-access-token>

Example:

Driver={Simba Spark ODBC Driver};Host=adb-436423333334855.15.azuredatabricks.net;Port=443;HTTPPath=sql/protocolv1/o/234456634855/1111-3456-jdcbhj2;ThriftTransport=2;SSL=1;AuthMech=3;UID=token;PWD=dapi......"

Output:

enter image description here

Make sure to install the ODBC driver.

Upvotes: 3

Related Questions