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.
- SSH as
admin
or thethoughtspot
user to your ThoughtSpot node. -
Verify the node IP(s).
$ tscli node ls 172.18.231.17 172.18.231.18
- Make a note of each IP; there may be more than one.
- 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
- 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:
- Open a browser on your workstation.
- Navigate to the Downloads page.
- Click ODBC Driver for Linux to download the driver.
- Open a terminal on your workstation.
- Change directory to the location where you downloaded the file.
-
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.
-
Unzip the zip file:
gunzip ThoughtSpot_linux_odbc_<version>.tar.gz
-
Extract the contents of the
tar
file.tar -xvf ThoughtSpot_linux_odbc_<version>.tar
This extracts a subdirectory called
linux
into the current directory. -
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. -
You must know your workstation architecture to continue, confirm your workstation’s architecture.
You can use the
arch
or theuname
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.
-
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 thesudo
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.
-
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!
-
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.
-
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 -
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/
-
Use the
echo
command to verify the path was added correctly.echo $LD_LIBRARY_PATH
-
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. -
Copy the
odbcinst.ini
file to the/etc
directory.$ cp ~/linux/Setup/odbcinst.ini /etc
-
Copy the hidden
.simba.quickstart.ini
file to the/etc
directory, renaming it in the process tosimbaclient.ini
.$ cp ~/linux/Setup/.simba.quickstart.ini /etc/simbaclient.ini
-
Update your environment with the
ODBCSYSINI
andODBCINI
variables.$ export ODBCSYSINI=/etc/ $ export ODBCINI=/etc/odbc.ini
-
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.
- Edit the
/etc/simbaclient.ini
file with your favorite editor. 2. -
Change the
ErrorMessagesPath
property to point to the location where you unzipped the client.[Driver] ErrorMessagesPath=<path_to_error_messages_directory>
- Comment out the
# Generic ODBCInstLib
value. -
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
- 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.
- Open the file
/etc/odbcinst.ini
in your favorite editor. -
Comment out the driver that you don’t need.
For example, if you are using 64-bit, comment out 32-bit.
-
Edit the
Driver
line so that it contains the path to the filelibSimbaClient.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
-
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
- 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.
- Open the file
/etc/odbc.ini
in the editor of your choice. -
Comment out the configuration that you don’t need.
For example, if you are using 64-bit, comment out 32-bit.
- Locate the
Description
section for the type of Linux you are using (32-bit or 64-bit). - Locate the line that begins with
ServerList
. -
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. -
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
- 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.
- Before trying the ODBC connection, make sure you can use this username/password to login into the ThoughtSpot application.
- Confirm the user’s privileges by going to the Data tab.
- Go back to your workstation’s terminal shell.
-
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.