Is there a way to connect to databricks using ADO.Net?

user_14263036 0
Viewed 1271

I want to build an API which queries the databricks's tables and output the results as a JSON. One way to achieve this is calling the databricks Jobs REST API to execute a job and read the job output but that has data size limitations(max of 5MB and my API result set can exceed beyond 20MB). Instead, can I connect to databricks using the JDBC/ODBC endpoint provided by the cluster using Microsoft.Net or if there's any other way to connect directly? My API layer preferably needs to be built in Microsoft.Net. However, I'm willing to try Python.

azure-databricks databricks .net-core python .net
1 Answers

I found few ways to connect to the databricks cluster

  1. Connect using ODBC connection with Simba drivers (https://pages.databricks.com/ODBC-Driver-Download.html). Also, shared in the comment above by @EdHarper
  2. Use cdata nuget package - https://www.cdata.com/drivers/spark/ado/ but, there's a license cost involved
  3. Using JDBC connection string provided, may need Java code

I went ahead with option #1 and below is a sample c# code.

// Build connection string
OdbcConnectionStringBuilder odbcConnectionStringBuilder = new OdbcConnectionStringBuilder
        {
            Driver = "Simba Spark ODBC Driver"
        };
        odbcConnectionStringBuilder.Add("Host", "adb-xxxxxxxxxxxxx.7.xxxxxxxbricks.net");
        odbcConnectionStringBuilder.Add("Port", "443");
        odbcConnectionStringBuilder.Add("SSL", "1");             
        odbcConnectionStringBuilder.Add("ThriftTransport", "2");
        odbcConnectionStringBuilder.Add("AuthMech", "3");
        odbcConnectionStringBuilder.Add("UID", "token");
        odbcConnectionStringBuilder.Add("PWD", "<Access token generated in databricks>");
        odbcConnectionStringBuilder.Add("HTTPPath", "sql/protocolv1/o/xxxxxxxxxxxxxxx/yyyy8-dfcccf-tyyujjk8");


    using (OdbcConnection connection = new OdbcConnection(odbcConnectionStringBuilder.ConnectionString))
    {
        string sqlQuery = "select * from yourdb.TableName";
        OdbcCommand command = new OdbcCommand(sqlQuery, connection);
        connection.Open();
        OdbcDataReader reader = command.ExecuteReader();

        for (int i = 0; i < reader.FieldCount; i++)
        {
            Console.Write(reader.GetName(i) + "\t");
        }

        Console.Write("\n");

        reader.Close();
        command.Dispose();
    }

Additionally, you connect using a DSN if you prefer, more details here - https://www.simba.com/products/Spark/doc/v1/ODBC_InstallGuide/win/content/odbc/hi/windows/dsn.htm

user_142630360
Related