unixODBC is an open-source project that implements the ODBC API. And is widely used as the ODBC Driver manager on Linux platform. Using unixODBC users can connect to an ODBC compliant data source and execute SQL queries on the data source. The pre-requisite is that the unixODBC & the intended ODBC driver is already installed. This blog captures the configurations needed to test Simba Spark ODBC driver on CentOS-7 Linux 64 bit platform. Configurations for other Simba ODBC drivers should be similar.

unixODBC is executed using the ‘isql’ command.  Verify that unixODBC is installed by executing isql without any parameters.

using isql command to test odbc driver

If unixODBC is installed but isql did not execute, specify the driver manager using LD_LIBRARY_PATH env variable. This environment variable must be set to include the paths to the ODBC driver manager libraries. For example, if the libraries are installed in /opt/local/lib, then run the following command to set LD_LIBRARY_PATH for the current user session:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib

By default, ODBC driver managers are configured to use hidden versions of the odbc.ini and odbcinst.ini configuration files (named .odbc.ini and .odbcinst.ini) located in the home directory, as well as the simba.sparkodbc.ini file in the lib subfolder of the driver installation directory. If you store these configuration files elsewhere, then you must set the environment variables described below so that the driver manager can locate the files.

For unixODBC, do the following:

  • Set ODBCINI to the full path and file name of the odbc.ini file.
  • Set ODBCSYSINI to the full path of the directory that contains the odbcinst.ini file.
  • Set SIMBASPARKINI to the full path and file name of the simba.sparkodbc.ini file.

If you are using iODBC, which is a separate driver manager, do the following:

  • Set ODBCINI to the full path and file name of the odbc.ini file.
  • Set ODBCINSTINI to the full path and file name of the odbcinst.ini file.
  • Set SIMBASPARKINI to the full path and file name of the simba.sparkodbc.ini file.

For example, if your odbc.ini and odbcinst.ini files are located in /usr/local/odbc and your simba.sparkodbc.ini file is located in /opt/simba/spark, then set the environment variables as follows:

For unixODBC:
export ODBCINI=/usr/local/odbc/odbc.ini
export ODBCSYSINI=/usr/local/odbc
export SIMBASPARKINI=/opt/simba/spark/simba.sparkodbc.ini

For iODBC:
export ODBCINI=/usr/local/odbc/odbc.ini
export ODBCINSTINI=/usr/local/odbc/odbcinst.ini
export SIMBASPARKINI =/opt/simba/spark/simba.sparkodbc.ini

To locate the simba.sparkodbc.ini file, the driver uses the following search order:

  1. If the SIMBASPARKINI environment variable is defined, then the driver searches for the file specified by the environment variable.
  2. The driver searches the directory that contains the driver library files for a file named simba.sparkodbc.ini.
  3. The driver searches the current working directory of the application for a file named simba.sparkodbc.ini.
  4. The driver searches the home directory for a hidden file named .simba.sparkodbc.ini (prefixed with a period).
  5. The driver searches the /etc directory for a file named simba.sparkodbc.ini.

You configure the Data Source Name (DSN) in the odbc.ini file and the driver in the odbcinst.ini file. The odbc.ini file typically has the data source host, port, authentication params & other config params as needed by the driver. Sample files for Simba Spark ODBC 64 bit Linux driver.

Sample odbc.ini

[ODBC Data Sources]
Simba Spark DSN=Simba Spark ODBC Driver 64-bit
[Simba Spark DSN]
ServiceDiscoveryMode=No Service Discovery
<<Configure Auth mechanism and other options as needed >>

Sample odbcinst.ini

[Simba Spark ODBC Driver 64-bit]
Description=Simba Spark ODBC Driver (64-bit)

The Driver is typically configured in odbcinst.ini. However, you may choose to configure Driver in odbc.ini.

Sample simba.sparkodbc.ini which configures TRACE level logs for the driver.

After the files are configured & the environment variables set correctly, you should be able to execute isql as:  isql ‘Simba Spark DSN’ using the DSN name configured.

If the connection is successful, then the SQL> prompt appears. And you are all set to execute your SQL queries on the Spark instance.

Testing ODBC Driver using unixODBC results

Debugging instructions:

If there are errors executing isql; strace command could be used to understand the cause of the error.

strace -o /root/logs/strace.out -s 1024 isql 'Simba Spark DSN’  

This command will generate a trace file in /root/logs/strace.out which could be used for analyzing the issue.