Supported SQL commands for ODBC and JDBC

The ThoughtSpot connection drivers support a limited set of SQL commands. The ODBC and JDBC drivers support a limited set of SQL commands. When developing software that uses a ThoughtSpot ODBC driver, use this reference of supported commands. This reference is intended for developers using other tools (ETL, and so on) to connect to ThoughtSpot through the ODBC or JDBC driver.

ThoughtSpot displays VARCHAR fields using lower case, regardless of what the original case of the loaded data.

Supported commands

ThoughtSpot supports these SQL commands for both ODBC and JDBC:

CREATE TABLE

Creates a table with the specified column definitions and constraints. The table is replicated on each node.

Example:
CREATE TABLE country_dim (id_number int, country varchar, CONSTRAINT PRIMARY KEY (id_number));
CREATE DATABASE

Creates a database.

Example:
CREATE DATABASE "fruit_database";
CREATE SCHEMA

Creates a schema within the current database.

Example:
CREATE SCHEMA "fruit_schema";
ALTER TABLE <table> ADD|DROP|RENAME COLUMN <column>

Alters a table to add, drop, or rename a column.

When you add a column to an existing table, you must provide a default value to use for existing rows.

Example:
ALTER TABLE "cart" ADD COLUMN "nickname" varchar(255) DEFAULT 'no nickname';

ALTER TABLE "cart" DROP COLUMN "nickname";

ALTER TABLE "sales" ADD CONSTRAINT PRIMARY KEY ("PO_number");
ALTER TABLE <table> DROP CONSTRAINT PRIMARY KEY

Drops the primary key from a table.

If you add a new primary key, ThoughtSpot uses the same upsert behavior it does when adding any primary key. This can result in deletion of your data, so make sure that you understand how the upsert changes your data before issuing this command.
Example:
ALTER TABLE "sales" DROP CONSTRAINT PRIMARY KEY;

ALTER TABLE "sales" ADD CONSTRAINT PRIMARY KEY ("PO_number");
ALTER TABLE <table> DROP [CONSTRAINT | RELATIONSHIP] <name>

Drops the named foreign key or relationship between two tables.

Example:
ALTER TABLE "sales_fact" DROP CONSTRAINT "FK_PO_number";

ALTER TABLE "fruit_dim" DROP RELATIONSHIP "REL_dates";
ALTER TABLE <table> [SET DIMENSION | SET FACT [PARTITION BY HASH [(<shards>)] [KEY(<column>)]]]

Changes the partitioning on a table by doing one of:

  • re-sharding a sharded table

  • changing a replicated table to a sharded table

  • changing a sharded table to a replicated (unsharded) table

    By default, ThoughtSpot does not shard dimension tables.

    To change the partitioning on a table, or to change a dimension table to a sharded table, use ALTER TABLE…​SET FACT PARTITION BY HASH…​;

    To make a sharded table into a dimension table (replicated on every node), use ALTER TABLE…​SET DIMENSION; command.

    Example:
    ALTER TABLE "sales_fact" SET FACT PARTITION BY HASH (96) KEY ("PO_number");
ALTER TABLE "fruit_dim" SET DIMENSION;
ALTER TABLE <table> MODIFY COLUMN <column> <new_data_type>;

Changes the data type of a column.

This can have implications on sharding and primary key behavior. See Data type conversion behavior.

Example:
ALTER TABLE fact100 MODIFY COLUMN product_id int;
INSERT

Creates placeholders in the table to receive the data.

Example:
INSERT INTO TABLE country_dim (?, ?);
DELETE FROM <table>

Deletes ALL rows from the specified table.

Use the WHERE clause to specify only certain rows to be deleted.

Example:

You could remove all data for sales before a certain date to free up space in ThoughtSpot.

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.

Example:
SELECT id_number, country FROM country_dim WHERE id_number > 200;
TRUNCATE TABLE <table>

Removes all data from a table, but preserves its metadata, including all GUIDs, relationships, and so on.

This can be used to force a new schema for a table without losing the metadata.

You must reload the data following a TRUNCATE, or all dependent objects (worksheets and pinboards) in ThoughtSpot become invalid.

Example:
TRUNCATE TABLE "location";
This operation removes all existing data from the table.
DROP DATABASE <database>

Drops a database and all of its schemas and tables.

Example:
DROP DATABASE "fruit_database";
DROP SCHEMA <schema>

Drops a schema within the current database, and drops all of the tables in the schema.

Example:
DROP SCHEMA "fruit_schema";
DROP TABLE <table>

Drops a table.

Example:
DROP TABLE "location";
SHOW DATABASES

Lists all available databases.

Example:
SHOW DATABASES;
SHOW SCHEMAS

Lists all schemas within the current database.

Example:
SHOW SCHEMAS;
SHOW TABLES

Lists all tables within the current database by schema.

Example:
SHOW TABLES;
SHOW TABLE <table>

Lists all the columns for a table.

Example:
SHOW TABLE "locations";
SCRIPT SERVER

Generates the TQL schema for all tables in all databases on the server.

Example:
SCRIPT SERVER;
SCRIPT DATABASE <database>

Generates the TQL schema for all tables in a database.

Example:
SCRIPT DATABASE "fruit_database";
SCRIPT TABLE <table>

Generates the TQL schema for a specified table. Use the physical table name, not the logical table name from the ThoughtSpot UI.

Example:
SCRIPT TABLE "physical-table-name";

Related information

Was this page helpful?
×