Skip to main content

 

ThoughtSpot

ODBC Supported SQL Commands

Overview

Explains what the feature is or what its benefits are to the user or customer.

Feature

The ODBC driver supports a limited set of SQL commands. When developing software that uses the ThoughtSpot ODBC driver, use this reference of supported commands.

This reference is intended for developers using other tools (ETL, etc.) to connect to ThoughtSpot via the ODBC driver.

These SQL commands are supported for ODBC:
Table 1. ODBC supported SQL commands
SQL command Description Example
CREATE TABLE Creates a table with the specified column definitions and constraints. The table is replicated on each node.
CREATE TABLE country_dim (id_number int, country varchar, CONSTRAINT PRIMARY KEY (id_number));
INSERT Creates placeholders in the table to receive the data.
INSERT INTO TABLE country_dim (?, ?);
DELETE FROM <table> Deletes ALL rows from the specified table. Does not support the WHERE clause.
DELETE FROM country_dim;
SELECT <cols_or_expression> FROM <table_list> [WHERE <predicates>] [GROUP BY <expressions>] [ORDER BY <expressions>] Fetches the specified set of table data.
SELECT id_number, country FROM country_dim WHERE id_number > 200;

Best practices for using ODBC

When developing tools that use the ODBC driver, these best practices are recommended:

You should create the parameterized SQL statement outside of ODBC. Using this method, the SQL statement can be sent to ThoughtSpot in batches by the ODBC driver, so you only have to update the memory itself. ETL tools have this implemented already (end users shouldn’t have to actually write the INSERT statement). But as a developer, you may be writing code that leverages the ODBC driver, so this tip can help you write your SQL for the best performance with the driver.

Data can be loaded into a table through multiple parallel connections. This can be achieved by splitting the input data into multiple parts, and loading those individual parts through multiple parallel connections. The parallel loading can be used even while loading to a single table or multiple tables at the same time.

When doing an incremental data load, note that the same UPSERT behavior that occurs via TQL will apply. This means that if you import a row whose primary key matches to an existing row, the existing row will be updated with the new values.

  • Was this article helpful?