[unixODBC][Driver Manager]Can't open lib 'Simba Spark ODBC Driver' : file not found

Question

I have created an API in c# to access Databricks using ODBC connection, which is working fine local after Simba driver installation. Through Azure pipeline,creating docker image to deploy on AWS ECS and linux. Below command create docker image and pushing to ECS successfully.

RUN apt-get install -y apt-utils
RUN apt-get install wget -y  && wget https://databricks.com/wp-content/uploads/drivers-2020/SimbaSparkODBC-2.6.16.1019-Debian-64bit.zip
RUN apt-get install zip -y
RUN apt-get install -y libsasl2-modules-gssapi-mit
RUN unzip SimbaSparkODBC-2.6.16.1019-Debian-64bit.zip && rm SimbaSparkODBC-2.6.16.1019-Debian-64bit.zip
RUN dpkg -i SimbaSparkODBC-2.6.16.1019-Debian-64bit/simbaspark_2.6.16.1019-2_amd64.deb 
RUN apt-get install -y unixodbc unixodbc-dev
RUN export ODBCINI=/root/odbc.ini ODBCSYSINI=/root/odbcinst.ini SIMBASPARKINI=/opt/simba/spark/lib/64/simba.sparkodbc.ini

docker image created successfully. But while trying to call api, getting below error:

Error: System.Data.Odbc.OdbcException (0x80131937): ERROR [01000] [unixODBC][Driver Manager]Can't open lib 'Simba Spark ODBC Driver' : file not found
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, SQLRETURN retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)

Plz help me to optimize the above commands and to resolve the issue. thanks c# code, if required:

using Microsoft.Identity.Client;
using System.Data.Odbc;
using Microsoft.Azure.Databricks.Client;
using Microsoft.Graph.Core;
using System;
using System.Data;
using System.Data.Common;
using System.Net.Http;

using System.Text;

class Program
{
    private static async Task<String> auth()
    {
        var authorityUri = new Uri("https://login.microsoftonline.com/ea80952e-a476-42d4-aaf4-5457852b0f7e");
        IConfidentialClientApplication app;
        app = ConfidentialClientApplicationBuilder.Create("AppID")                                                  
            .WithClientSecret("Client-secret")                                                  
            .WithAuthority(authorityUri)                                                  
            .Build();        
        string[] scopes = new string[] { "2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default" };        
        AuthenticationResult result = null;
        try
        {
            result = await app.AcquireTokenForClient(scopes)
                .ExecuteAsync();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }        
        return result.AccessToken;
    }

    static void Main(string[] args)
    {
        var authToken = auth().Result;    
        Console.WriteLine(authToken);
        OdbcConnectionStringBuilder odbcConnectionStringBuilder =
            new OdbcConnectionStringBuilder()
            {
                Driver = "Simba Spark ODBC Driver"
            };
        odbcConnectionStringBuilder.Add("Host", "adb-host.azuredatabricks.net");
        odbcConnectionStringBuilder.Add("Port", "443");
        odbcConnectionStringBuilder.Add("SSL", "1");
        odbcConnectionStringBuilder.Add("ThriftTransport", "2");
        odbcConnectionStringBuilder.Add("AuthMech", "11");
        odbcConnectionStringBuilder.Add("Auth_Flow", "0");
        odbcConnectionStringBuilder.Add("Auth_AccessToken", authToken);
        odbcConnectionStringBuilder.Add("httpPath", "/sql/1.0/warehouses/id");
        
        using (OdbcConnection connection = new OdbcConnection(odbcConnectionStringBuilder.ConnectionString))
        {
           
            string sqlQuery = "SELECT * FROM table LIMIT 2";     
            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");
            }
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}\t{1}\t{2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
                }
            }              
            Console.Write("\n");   
            reader.Close();         
            command.Dispose();
        }
    }
}

Answer 1

In my case, I was mismatching the DSN. But Here is the complete solution:

When you are connecting through Azure Active Directory. In other cases, you have to specify UID, Password or PAT(Personal Access Token).

To get host, httpPath and configuration details: https://learn.microsoft.com/en-us/azure/databricks/integrations/jdbc-odbc-bi#--install-and-configure-the-odbc-driver-for-linux

Docker Commands :

FROM mcr.microsoft.com/dotnet/aspnet:6.0 AS runtime
WORKDIR /app

RUN apt-get update

RUN apt-get install wget -y  && wget https://databricks-bi-artifacts.s3.us-east-2.amazonaws.com/simbaspark-drivers/odbc/2.6.29/SimbaSparkODBC-2.6.29.1049-Debian-64bit.zip
RUN apt-get install zip libsasl2-modules-gssapi-mit unixodbc gawk -y
RUN unzip SimbaSparkODBC-2.6.29.1049-Debian-64bit.zip && rm SimbaSparkODBC-2.6.29.1049-Debian-64bit.zip
RUN dpkg -i simbaspark_2.6.29.1049-2_amd64.deb 
RUN export ODBCINI=/etc/odbc.ini SIMBASPARKINI=/opt/simba/spark/lib/64/simba.sparkodbc.ini

#To setup DSN in /etc/odbc.ini file
RUN gawk -i inplace '{ print } ENDFILE { print "[ODBC Data Sources]" }'  /etc/odbc.ini
#Below DSN name should be same in below line and code, through this name it finds the driver
RUN gawk -i inplace '{ print } ENDFILE { print "Simba Spark ODBC DSN=Simba Spark ODBC Driver" }'  /etc/odbc.ini
RUN gawk -i inplace '{ print } ENDFILE { print "[Simba Spark ODBC DSN]" }'  /etc/odbc.ini 
RUN gawk -i inplace '{ print } ENDFILE { print "Driver=/opt/simba/spark/lib/64/libsparkodbc_sb64.so" }'  /etc/odbc.ini


COPY . ./
ENTRYPOINT ["dotnet", "ABC.dll"]

And in C#: DSN name should be same as in docker commands:

using Microsoft.Identity.Client;
using System.Data.Odbc;
using Microsoft.Azure.Databricks.Client;
using Microsoft.Graph.Core;
using System;
using System.Data;
using System.Data.Common;
using System.Net.Http;

using System.Text;

class Program
{
    private static async Task<String> auth()
    {
        var authorityUri = new Uri("https://login.microsoftonline.com/ea80952e-a476-42d4-aaf4-5457852b0f7e");
        IConfidentialClientApplication app;
        app = ConfidentialClientApplicationBuilder.Create("AppID")                                                  
            .WithClientSecret("Client-secret")                                                  
            .WithAuthority(authorityUri)                                                  
            .Build();        
        string[] scopes = new string[] { "2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default" };        
        AuthenticationResult result = null;
        try
        {
            result = await app.AcquireTokenForClient(scopes)
                .ExecuteAsync();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }        
        return result.AccessToken;
    }

    static void Main(string[] args)
    {
        var authToken = auth().Result;    
        Console.WriteLine(authToken);
        OdbcConnectionStringBuilder odbcConnectionStringBuilder =
            new OdbcConnectionStringBuilder()
            {
                Dsn= "Simba Spark ODBC DSN"
            };
        odbcConnectionStringBuilder.Add("Host", "adb-host.azuredatabricks.net");
        odbcConnectionStringBuilder.Add("Port", "443");
        odbcConnectionStringBuilder.Add("SSL", "1");
        odbcConnectionStringBuilder.Add("ThriftTransport", "2");
        odbcConnectionStringBuilder.Add("AuthMech", "11");
        odbcConnectionStringBuilder.Add("Auth_Flow", "0");
        odbcConnectionStringBuilder.Add("Auth_AccessToken", authToken);
        odbcConnectionStringBuilder.Add("httpPath", "/sql/1.0/warehouses/id");
        
        using (OdbcConnection connection = new OdbcConnection(odbcConnectionStringBuilder.ConnectionString))
        {
           
            string sqlQuery = "SELECT * FROM table LIMIT 2";     
            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");
            }
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}\t{1}\t{2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
                }
            }              
            Console.Write("\n");   
            reader.Close();         
            command.Dispose();
        }
    }
}