Use this procedure to obtain the Linux ODBC driver and install it.

ThoughtSpot’s ODBC connection relies on the SimbaEngine X SDK to connect through ODBC or JDBC to ThoughtSpot’s remote data stores. The instructions on this page explain how to configure the Simba ODBC driver on a Linux workstation.

Make sure you have read the overview material in the ODBC driver overview. This workstation is the same machine where you plan to run your ETL activities.

Check the ThoughtSpot IP and the simba_server status

Before you begin, you need to know the IP address or DNS name of the server you intend to connect your server to.

  1. SSH as admin or the thoughtspot user to your ThoughtSpot node.
  2. Verify the node IP(s).

    $ tscli node ls
    172.18.231.17
    172.18.231.18
    
  3. Make a note of each IP; there may be more than one.
  4. Configure the ThoughtSpot firewall to allow connections from your ETL client, by running the following command on any ThoughtSpot node: tscli firewall open-ports --ports 12345
  5. Exit or close the shell.

Install the Simba client

On your workstation, where you want to connect from, do the following to get the ODBC driver:

  1. Open a browser on your workstation.
  2. Navigate to the Downloads page.
  3. Click ODBC Driver for Linux to download the driver.
  4. Open a terminal on your workstation.
  5. Change directory to the location where you downloaded the file.
  6. Optionally, move the file to a permanent location on your machine.

    When you expand the downloaded file it will create a directory in the location.

  7. Unzip the zip file:

     gunzip ThoughtSpot_linux_odbc_<version>.tar.gz
    
  8. Extract the contents of the tar file.

     tar -xvf ThoughtSpot_linux_odbc_<version>.tar
    

    This extracts a subdirectory called linux into the current directory.

  9. Take a moment to examine the contents of the new directory.

    The structure contains a Simba client library, supporting libraries and setup files for two different architectures. It also continues error messages for multiple languages.

     ├── Bin
     │   ├── Linux_x86
     │   │   └── libSimbaClient.so
     │   └── Linux_x8664
     │       └── libSimbaClient.so
     ├── ErrorMessages
     │   ├── de-DE
     │   │   ├── ... 
     │   ├── en-US
     │   │   ├── CSCommonMessages.xml
     │   │   ├── ClientMessages.xml
     │   │   ├── JNIDSIMessages.xml
     │   │   ├── ODBCMessages.xml
     │   │   ├── SQLEngineMessages.xml
     │   │   └── ServerMessages.xml
     │   ├── es-ES
     │   ├── fr-FR
     │   ├── it-IT
     │   └── ja-JP
     ├── Lib
     │   ├── Linux_x86
     │   │   ├── libcrypto.so
     │   │   ├── libcrypto.so.1.0.0
     │   │   ├── ...
     │   └── Linux_x8664
     │       ├── ...
     └── Setup
         ├── .simba.javaquickstart.ini
         ├── .simba.javaultralight.ini
         ├── .simba.quickstart.ini
         ├── .simba.ultralight.ini
         ├── .simbaclient.ini
         ├── .simbaserver.javaquickstart.ini
         ├── .simbaserver.javaultralight.ini
         ├── .simbaserver.quickstart.ini
         ├── .simbaserver.ultralight.ini
         ├── odbc.ini
         └── odbcinst.ini
    
     14 directories, 92 files
    

    The linux/Setup directory contains the key ODBC configuration files and sample Simba client configurations you can use later in this procedure.

  10. You must know your workstation architecture to continue, confirm your workstation’s architecture.

    You can use the arch or the uname command or both.

     $ arch
       x86_64
     $ uname -a
       Linux nebula-docs-production-4vfnv 4.4.108-1.el7.elrepo.x86_64 #1 SMP Mon Dec 25 09:55:39 EST 2017 x86_64 x86_64 x86_64 GNU/Linux
    

    In previous examples, the workstation is a 64 bit workstation. Your workstation may be 32-bit. You can use this architecture information in the procedures that follow.

(Optional) Install unixODBC tools for testing

The procedures on this page rely on the unixODBC tools to test your configuration and connection. If you are experienced with ODBC and want to skip this, you can. Simply substitute your preferred mechanism in the subsequent procedures where references are made to the unixODBC tools.

  1. Search for the unixODBC tools on your system.

    The yum package manager searches for software already installed or available on your system or from the configured repositories. Depending on your workstation configuration, you may need to use the sudo command with your workstation.

    $ yum search unixODBC
    ...
     * updates: repos-lax.psychz.net
    ================ N/S matched: unixODBC==============================
    opensips-unixodbc.x86_64 : OpenSIPS unixODBC Storage support
    unixODBC-devel.i686 : Development files for programs which will use the unixODBC library
    unixODBC-devel.x86_64 : Development files for programs which will use the unixODBC library
    erlang-odbc.x86_64 : A library for unixODBC support in Erlang
    freeradius-unixODBC.x86_64 : Unix ODBC support for freeradius
    unixODBC.i686 : A complete ODBC driver manager for Linux
    unixODBC.x86_64 : A complete ODBC driver manager for Linux
    

    Make note of the correct package to install for your architecture.

  2. Install the appropriate package for your architecture.

    In this case the command installs the tools for a 64-bit architecture. A 32-bit package needs the unixODBC.i686 package.

     [admin@nebula-docs-odbc-test-cxmrn ~]$ yum install unixODBC.x86_64
     Loaded plugins: fastestmirror, ovl
     Loading mirror speeds from cached hostfile
      * base: mirror.linuxfix.com
      * elrepo: repos.lax-noc.com
      * epel: mirror.hmc.edu
      * extras: centos-distro.cavecreek.net
      * rpmforge: mirror.lstn.net
      * updates: repos-lax.psychz.net
     Resolving Dependencies
     --> Running transaction check
     ---> Package unixODBC.x86_64 0:2.3.1-11.el7 will be installed
     ...
     Complete!
    
  3. Verify the files were installed.

     $ ls /usr/bin/isql
     /usr/bin/isql
     $ ls /usr/bin/odbcinst
     /usr/bin/odbcinst
    

Set up your environment

In this section, you set parameters in your workstation to support your ODBC connection.

  1. Copy the library for your architecture from the Lib directory on your Linux machine.

    Library Architecture
    /linux/Lib/Linux_x86 32-bit
    /linux/Lib/Linux_x8664 64-bit
  2. Add the location’s path to the LD_LIBRARY_PATH environment variable.

    For example if your architecture is 64-bit and you keep the library in your home directory:

    export LD_LIBRARY_PATH=~/linux/Lib/Linux_x8664/
    
  3. Use the echo command to verify the path was added correctly.

    echo $LD_LIBRARY_PATH
    
  4. Copy the odbc.ini file to the /etc directory.

    $ cp ~/linux/Setup/odbc.ini  /etc
    

    If you have trouble making the copy, use the sudo command to make the move.

  5. Copy the odbcinst.ini file to the /etc directory.

     $ cp ~/linux/Setup/odbcinst.ini  /etc
    
  6. Copy the hidden .simba.quickstart.ini file to the /etc directory, renaming it in the process to simbaclient.ini.

     $ cp ~/linux/Setup/.simba.quickstart.ini  /etc/simbaclient.ini
    
  7. Update your environment with the ODBCSYSINI and ODBCINI variables.

     $ export ODBCSYSINI=/etc/
     $ export ODBCINI=/etc/odbc.ini
    
  8. Use the /usr/bin/odbcinst command to confirm your settings:

     $ /usr/bin/odbcinst -j
     unixODBC 2.3.1
     DRIVERS............: /etc/odbcinst.ini
     SYSTEM DATA SOURCES: /etc/odbc.ini
     FILE DATA SOURCES..: /etc/ODBCDataSources
     USER DATA SOURCES..: /etc/odbc.ini
     SQLULEN Size.......: 8
     SQLLEN Size........: 8
     SQLSETPOSIROW Size.: 8
    

Edit the /etc/simbaclient.ini file

When you are ready, follow this procedure to configure the driver.

  1. Edit the /etc/simbaclient.ini file with your favorite editor. 2.
  2. Change the ErrorMessagesPath property to point to the location where you unzipped the client.

     [Driver]
     ErrorMessagesPath=<path_to_error_messages_directory>
    
  3. Comment out the # Generic ODBCInstLib value.
  4. Uncomment the ODBCInstLib property.

    When you are done, your file looks like the following:

     # Generic ODBCInstLib
     #   iODBC
     #ODBCInstLib=libiodbcinst.so
    
     #   SimbaDM / unixODBC
     ODBCInstLib=libodbcinst.so
    
  5. Save and close the /etc/simbaclient.ini file.

Edit the odbcinst.ini file

The odbcinst.ini file is a registry and configuration file for ODBC drivers. Depending on your workstation architecture, you configure the 32-bit or 64-bit driver.

  1. Open the file /etc/odbcinst.ini in your favorite editor.
  2. Comment out the driver that you don’t need.

    For example, if you are using 64-bit, comment out 32-bit.

  3. Edit the Driver line so that it contains the path to the file libSimbaClient.so

    Use the path where you copied the library files. For example, for the 64-bit ODBC driver:

     [ThoughtSpot(x64)]
     APILevel            = 1
     ConnectFunctions    = YYY
     Description         = ThoughtSpot 64bit ODBC driver
     Driver              = /home/admin/linux/Bin/Linux_x8664/libSimbaClient.so
     DriverODBCVer       = 03.52
     SQLLevel            = 1
    
  4. Make sure the remaining driver is named ThoughtSpot without any special characters.

    When you are done, you should see something similar to the following:

     #[ThoughtSpot]
     #APILevel            = 1
     #ConnectFunctions    = YYY
     #Description         = ThoughtSpot 32bit ODBC driver
     #Driver              = /usr/local/scaligent/toolchain/local/simba/odbc/linux/Bin/Linux_x86/libSimbaClient.so
     #DriverODBCVer       = 03.80
     #SQLLevel            = 1
    
     [ThoughtSpot]
     APILevel            = 1
     ConnectFunctions    = YYY
     Description         = ThoughtSpot 64bit ODBC driver
     Driver              = /home/admin/linux/Bin/Linux_x8664/libSimbaClient.so
     DriverODBCVer       = 03.80
     SQLLevel            = 1
    
  5. Save and close the /etc/odbcinst.ini file.

Edit the odbc.ini file

The odbc.ini file is a registry and configuration file for ODBC DSNs (Data Source Names). This file relies on the drivers registered in the /etc/odbcinst.ini file. Depending on your workstation architecture, you configure the 32-bit or 64-bit driver.

  1. Open the file /etc/odbc.ini in the editor of your choice.
  2. Comment out the configuration that you don’t need.

    For example, if you are using 64-bit, comment out 32-bit.

  3. Locate the Description section for the type of Linux you are using (32-bit or 64-bit).
  4. Locate the line that begins with ServerList.
  5. Replace 127.0.0.1 with a comma separated list of the IP addresses of each node on the ThoughtSpot instance.

    The syntax for the ServerList is:

     ServerList = <node1_IP> 12345, <node2_IP> 12345 [, <node3_IP> 12345, ...]
    

    If you need to obtain the IP addresses of the ThoughtSpot cluster nodes, run the command tscli node ls from a Linux shell on a ThoughtSpot appliance.

  6. Do not edit the port number, leave it as 12345.

    When you are done, your entry will look similar to the following (this example is for the 64-bit ODBC driver):

     [ThoughtSpot]
     Description = ThoughtSpot 64-bit ODBC Driver
     Driver = ThoughtSpot
     ServerList =  172.18.231.17 12345
     Locale = en-US
     ErrorMessagesPath = /home/admin/linux/ErrorMessages
     UseSsl = 0
     #SSLCertFile = # Set the SSL certificate file path. The certificate file can be obtained by extracting the SDK tarball
     #LogLevel = 0 # Set log level to enable debug logging
     #LogPath = # Set the debug log files path
     DATABASE = # Set the default database to connect to
     SCHEMA = # Set the default schema to connect to
    
  7. Save and close the odbc.ini file.

Test your ODBC connection

At this point, you can test your ODBC connection to ThoughtSpot. It is important to recall that the username/password you use belongs to a ThoughtSpot application user. Typically, this user is a user with data management or administrative privileges on the application.

  1. Before trying the ODBC connection, make sure you can use this username/password to login into the ThoughtSpot application.
  2. Confirm the user’s privileges by going to the Data tab.
  3. Go back to your workstation’s terminal shell.
  4. Use the /usr/bin/isql and confirm you can connect.

    Specify the ThoughtSpot DSN:

     /usr/bin/isql -v ThoughtSpot tsadmin adminpwd
     +---------------------------------------+
     | Connected!                            |
     |                                       |
     | sql-statement                         |
     | help [tablename]                      |
     | quit                                  |
     |                                       |
     +---------------------------------------+
     SQL>
    

Now, you are ready to begin using the connection you’ve configured.