Overview of the ODBC Driver

Use the ODBC driver to bring data in from your ETL tool or database. ThoughtSpot comes packaged with an ODBC (Open Database Connectivity) driver, so that you can transfer data between ThoughtSpot and other databases. Basic knowledge of ODBC data source administration is helpful when setting up ODBC.

Supported operating systems for the ODBC driver are:

  • Microsoft Windows 32-bit

  • Microsoft Windows 64-bit

  • Linux 32-bit

  • Linux 64-bit

Version compatibility and connection parameters

To ensure compatibility, always use the ODBC driver with the same version number as the ThoughtSpot instance to which you are connecting. You can make a secure ODBC connection to the ThoughtSpot database by configuring a user and password combination with the driver. For detailed information about connection parameters, see the ODBC and JDBC configuration properties

Supported Data Types

The ODBC driver supports these data types:

  • INT

  • BIGINT

  • BOOLEAN

  • DOUBLE

  • FLOAT

  • DATE

  • TIME

  • TIMESTAMP

  • DATETIME

  • CHAR

  • VARCHAR

Source and target data compatibility

By default, ThoughtSpot takes a permissive approach to data type compatibility between source and target data in ODBC. In this mode, ThoughtSpot assumes that the incoming data matches exactly with the target data types and loads the table as is.

Alternatively, you can explicitly require that ThoughtSpot match the source data types exactly and, if it can’t find a match, it returns an error and the data load fails. In this mode, for example, if the target ThoughtSpot data type for a column is INT, the source data type for that column must be INT in order for the data load to succeed.

By toggling *strict* and *permissive* true and false options, you can configure settings along a scale of behavior between the permissive, automatic approach and the strictness of the "must match" approach.

Strictness = true, Permissiveness = true

Data types are inferred and automatically converted. ThoughtSpot returns an error in cases where the data conversion is not possible. Data load fails in its entirety if any data contains mismatches. You must correct the problem in the source data and try the load again.

Strictness = true, Permissiveness = false

The source and target data types must match. If any data contains mismatches, ThoughtSpot returns an error to the client a data load fails in its entirety. You must correct the problem in the source data and try the load again.

This is the strictest configuration.

Strictness = false, Permissiveness = true

Data types are inferred and automatically converted. No error is thrown even if source and target data types don’t match. Data load continues even when the source and target data types don’t match. This means your data load may contain data types that you do not intend or that are not helpful. You are responsible for checking and validating the data in this case.

Strictness = false, Permissiveness = false

No data types are inferred and conversion does not check for matches. This is the most permissive configuration.

Your customer support engineer can assist you in configuring custom ODBC behavior. Regardless of the configuration you choose, you must validate that the results of data loading as they appear in ThoughtSpot are what you require.

Data type conversion matrix

The following table describes the conversion matrix between SQL data types and ThoughtSpot data types.

Allowable conversions between data types
Source SQL data types BOOL INT BIGINT DOUBLE FLOAT CHAR DATE TIME DATETIME

SQL_BIT

SQL_TINYINT

SQL_SMALLINT

SQL_INTEGER

SQL_BIGINT

SQL_CHAR

SQL_VARCHAR

SQL_LONGVARCHAR

SQL_BINARY

SQL_VARBINARY

SQL_LONGVARBINARY

SQL_DOUBLE

SQL_REAL

SQL_FLOAT

SQL_NUMERIC

SQL_GUID

SQL_INTERVAL_MINUTE_TO_SECOND

SQL_INTERVAL_HOUR_TO_SECOND

SQL_INTERVAL_HOUR_TO_MINUTE

SQL_INTERVAL_DAY_TO_SECOND

SQL_INTERVAL_DAY_TO_MINUTE

SQL_INTERVAL_DAY_TO_HOUR

SQL_INTERVAL_YEAR

SQL_INTERVAL_MONTH

SQL_INTERVAL_DAY

SQL_INTERVAL_HOUR

SQL_INTERVAL_MINUTE

SQL_INTERVAL_SECOND

SQL_TYPE_TIME

SQL_TYPE_DATE

SQL_TYPE_TIMESTAMP

If a conversion is not possible, ThoughtSpot returns an error. The ETL tool must add a data transformation step if the source column data type does not exactly match the target’s ThoughtSpot column data type. The driver does not do any implicit conversions.