TQL reference

TQL is the ThoughtSpot language for entering SQL commands. This reference lists TQL commands you can use to create a schema, verify a data load, and more.

You can use TQL either through the ThoughtSpot application’s web interface or the command line interface in the Linux shell.

Use --query_results_apply_top_row_count <number> flag to limit the number of result rows that a query returns.

For example:

$ tql --query_results_apply_top_row_count 100`

As a best practice, recommend that you enclose object names (database, schema, table, and column) in double quotes, and use single quotes for column values.

When referring to objects using fully qualified object names, use the following syntax:

"database"."schema"."table"

To get help with SQL when using TQL, enter help on the command line.

You can use TQL to view and modify schemas and data in tables. Remember to add a semicolon after each command.

Commands are not case-sensitive; we capitalized them here here for readability.
Worksheets and pinboards in ThoughtSpot depend on the data in the underlying tables. Be careful when modifying tables directly. If you change or remove a schema on which objects rely, the objects may become invalid.

View schemas and data

The following commands enable you view schemas and data:

SHOW DATABASES

Lists all available databases.

Example
SHOW DATABASES;
USE <database>

Switches the context to the specified database. This is required if queries do not use fully-qualified names (database.schema.table) for specifying tables.

Example
USE "fruit_database";
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";
SELECT <cols_or_expr> FROM <table_list> [WHERE <predicates>] [GROUP BY <expr>] [ORDER BY <expr>]

Shows specified set of table data.

If you do not specify the TOP number of rows to select, the top 50 rows will be returned by default. The number of rows to return can be set using the TQL command line flag:

--query_results apply_top_row_count`

You can use the following aggregation functions: sum, count, count distinct, stddev, avg, variance, min, and max.

You can use the following date functions: absyear, absmonth, absday, absquarter, date, and time.

Examples
SELECT TOP 10 "quantity"
  FROM "sales_fact";

SELECT COUNT(*)
  FROM "vendor";

SELECT "vendor", SUM("quantity")
  FROM "sales_fact"
  GROUP BY "vendor";

SELECT "vendor", SUM("amount")
  FROM "vendor", "sales_fact"
  WHERE "sales_fact"."vendorid" = "vendor"."vendorid"
  AND "amount" > 100
  GROUP BY "vendor"
  ORDER BY "amount" DESC;

SELECT "vendor", SUM("quantity")
  FROM "sales_fact"
  GROUP BY "vendor" LIMIT 10;

Schema creation

The following commands enable you to create schemas:

CREATE DATABASE <database>

Creates a database.

Example
CREATE DATABASE "fruit_database";
CREATE SCHEMA <schema>

Creates a schema within the current database.

Example
CREATE SCHEMA "fruit_schema";
CREATE TABLE <table> (<column_definitions> [<constraints>]) [PARTITION BY HASH (<number>) [KEY ("<column>")]]

Creates a table with the specified column definitions and constraints.

Use PARTITION BY HASH to shard a table across all nodes. If you do not specify a KEY, the table shards randomly.

Do not specify relationship constraints (FOREIGN KEY or RELATIONSHIP) in the CREATE TABLE statement. Instead, define these using ALTER TABLE statements at the end of the TQL script, after creating the tables. This guarantees that tables are created before they are referenced in the constraint definitions.

Examples
CREATE TABLE "vendor" (
  "vendorid" int,
  "name" varchar(255));

CREATE TABLE "sales_fact" (
  "saleid" int,
  "locationid" int,
  "vendorid" int,
  "quantity" int,
  "sale_amount" double,
  "fruitid" int,
  CONSTRAINT PRIMARY KEY("saleid")
  )
  PARTITION BY HASH(96)
  KEY ("saleid");

Schema modification

The following commands enable you to modify schemas:

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";
TRUNCATE TABLE <table>

Removes all data from a table, but preserves its metadata, including all GUIDs, relationships, and so on. Use this command to force a new schema for a table without losing the metadata.

This operation removes all existing data from the table. You must reload the data after a TRUNCATE command, or all dependent objects (worksheets and pinboards) in ThoughtSpot become invalid.
Example
TRUNCATE TABLE "location";
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.

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

ALTER TABLE "cart"
  DROP COLUMN "nickname";

ALTER TABLE "cart"
  RENAME COLUMN "nickname" TO "shortname";
ALTER TABLE <table> DROP CONSTRAINT PRIMARY KEY

Drops the primary key from a table.

If you add a new primary key, the same upsert behavior applies as adding any primary key. This can result in data deletion. Be sure to understand how the upsert affects your data before dropping or adding primary keys.
Examples
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.

Examples
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 the following:

  • 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 the ALTER TABLE...SET FACT PARTITION BY HASH...; command.

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

    Examples
    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 About data type conversion.

Example
ALTER TABLE fact100
  MODIFY COLUMN product_id int;

Modify data

The following commands enable you to modify data:

INSERT INTO <table> VALUES ...

Inserts values into a table. Only use this for testing. Do not use INSERT on a production system.

Example
INSERT INTO "vendor"
  VALUES ('helen rose', 'jacob norse', 'eileen ruff', 'manny gates');
SELECT <cols_or_expr> INTO <table_list> FROM <table_list> [WHERE <predicates>] [GROUP BY <expr>] [ORDER BY <expr>]

Copies data from one table into a new table.

If you do not specify the TOP number of rows to select, the top 50 rows will be returned by default. The number of rows to return can be set using the TQL command line flag:

--query_results apply_top_row_count`

You can use the following aggregation functions: sum, count, count distinct, stddev, avg, variance, min, and max.

You can use the following date functions: absyear, absmonth, absday, absquarter, date, and time.

Examples
SELECT TOP 10 "quantity"
  INTO "revenue_fact"
  FROM "sales_fact";

SELECT COUNT(*)
  INTO "vendor_new"
  FROM "vendor";
SELECT "vendor", SUM("quantity")
  INTO "revenue_fact"
  FROM "sales_fact"
  GROUP BY "vendor";
SELECT "vendor", SUM("amount")
  INTO "revenue_fact", "vendor_new"
  FROM "vendor", "sales_fact"
  WHERE "sales_fact"."vendorid" = "vendor"."vendorid"
  AND "amount" > 100
  GROUP BY "vendor"
  ORDER BY "amount" DESC;
SELECT "vendor", SUM("quantity")
  INTO "sales_fact"
  FROM "sales_fact"
  GROUP BY "vendor" LIMIT 10;
ALTER TABLE <table> SET LOAD PRIORITY <value> <new_data_type>

Sets the load priority for a table. Load priority determines the order in which a table is loaded on a cluster restart. You can set any value from 1-100. The system default for all tables is 50.

Example
ALTER TABLE 'sales_facts'
  SET LOAD PRIORITY 1;
UPDATE <table> ... SET ... [WHERE ...]

Updates rows in a table that match optionally provided predicates. Predicates have the form column = value connected by the AND keyword. Sets the column values to the specified values.

Example
UPDATE "location"
  SET "borough" = 'staten island', "city" = 'new york'
  WHERE "borough" = 'staten isl' AND city = 'NY';
DELETE FROM <table> [WHERE...]

Deletes rows from a table that match optional predicates. Predicates have the form column = value connected by the AND keyword. When specifying dates, use the epoch form. See epoch converter. Other date formats can result in errors and incorrect data deletion from the table.

Example
DELETE FROM "vendor"
  WHERE "name" = 'Joey Smith'
  AND "vendorid" ='19463';

Constraints and relationships

ThoughtSpot uses constraints and relationships to define the relationships between tables, and specify how they can be joined. However, it does not enforce the constraints, because they are in a transactional database.

You can define the following constraints when creating a table with CREATE TABLE, or add them to an existing table using the ADD CONSTRAINT syntax:

PRIMARY KEY

Designates a unique, non-null value as the primary key for a table. This can be one column or a combination of columns. If values are not unique, an upsert will be performed if a row includes a primary key that is already present in the data.

Examples
CREATE TABLE "schools" (
  "schoolID" varchar(15),
  "schoolName" varchar(255),
  "schoolCity" varchar(55),
  "schoolState" varchar(55),
  "schoolNick" varchar(55),
  CONSTRAINT PRIMARY KEY ("schoolID") ) ;

ALTER TABLE "cart"
  ADD CONSTRAINT PRIMARY KEY ("cart_id");

ALTER TABLE "cart"
  DROP CONSTRAINT PRIMARY KEY;
FOREIGN KEY

Defines a relationship where the value(s) in the table are used to join to a second table. Uses an equality operator. The foreign key must match the primary key of the table that is referenced in number, column type, and order of columns.

When creating a foreign key, give it a name. You can reference the foreign key name later, if you want to remove it.

Examples
ALTER TABLE "batting"
  ADD CONSTRAINT "FK_player" FOREIGN KEY ("playerID")
  REFERENCES "players" ("playerID");`

ALTER TABLE "batting"
  ADD CONSTRAINT "FK_lg_team" FOREIGN KEY ("lgID" ,"teamID")
  REFERENCES "teams" ("lgID" ,"teamID");

ALTER TABLE "shipment"
  ADD CONSTRAINT "FK_PO_vendor" FOREIGN KEY ("po_number", "vendor")
  REFERENCES "orders" ("po_number", "vendor");

ALTER TABLE "shipment"
  DROP CONSTRAINT "FK_PO_vendor";
RELATIONSHIP

Defines a relationship where the value(s) in the first table can join to a second table, using a required equality condition and one or more optional range conditions. These conditions act like a WHERE clause when the two tables are joined. The conditions combine using AND logic; all conditions must be met for a row to be in the result.

You may add multiple relationships between tables. When creating a relationship, name it. You can reference the relationship by name later, if you want to remove it.

Examples
ALTER TABLE "wholesale_buys"
  ADD RELATIONSHIP "REL_fruit"
  WITH "retail_sales"
  AS "wholesale_buys"."fruit" = "retail_sales"."fruit"
  AND ("wholesale_buys"."date_order" < "retail_sales"."date_sold"
  AND "retail_sales"."date_sold" < "wholesale_buys"."expire_date");

ALTER TABLE "wholesale_buys"
  DROP RELATIONSHIP "REL_fruit";

Permitted joins and necessary permissions

These are the allowable joins and permissions within ThoughtSpots:

Worksheets

Must have Can edit permission on the source Worksheet. Can create joins with:

Imported tables (UI)

Must have Can manage data permission to load the table.

Views

Must have Can edit permission on the source View. Can create joins with:

Views
Materialized views
Imported tables (UI)

Must have Can manage data permission to load the table.

Tables uploaded from backend (tsload) or through DataFlow

Must have Admin privileges to access tsload.

Materialized views

Must have Can edit permission on the source Materialized view. Can create joins with:

Views
Materialized views
Imported tables (UI)

Must have Can manage data permission to load the table.

Tables uploaded from backend (tsload) or through DataFlow

Must have Admin privileges to access tsload. ThoughtSpot recommends creating this join through the UI, instead of using TQL.

Imported tables (UI)

Must have Can edit permission on the source table. Can create joins with:

Worksheets
Views
Materialized views
Imported tables (UI)

Must have Can manage data permission to load the table.

Tables uploaded from backend (tsload) or through DataFlow

Must have Admin privileges to access tsload.

Tables uploaded from backend (tsload) or through DataFlow

Must have Can edit permission on the source table. Can create joins with:

Views
Materialized views

ThoughtSpot recommends creating this join through the UI instead of using TQL.

Imported tables (UI)

Must have Can manage data permission to load the table.

Table uploaded from backend (tsload) or through DataFlow

Must have Admin privileges to access tsload. This join is often used to create Worksheets.

Tables uploaded through Embrace

Must have Can edit permission on the source table, and Can manage data permission. Can create joins with:

Table uploaded through Embrace

The two tables must be from the same connection.

View on top of table uploaded through Embrace

The View and the table must be from the same connection.

Views on top of table uploaded through Embrace

Must have Can edit permission on the source Views. Can create joins with:

Table uploaded through Embrace

The View and the table must be from the same connection.

View on top of table uploaded through Embrace

The two Views must be from the same connection.

Data types

ThoughtSpot supports a simplified list of data types:

Character

VARCHAR(n)

Specify the maximum number of characters, as in VARCHAR(255). The size limit is 64MB for VARCHAR values.

Floating point

  • DOUBLE ThoughtSpot recommends that you use DOUBLE.

  • FLOAT

Boolean

BOOL

Can be true or false.

Integer

  • INT holds 32 bits

  • BIGINT holds 64 bits

Date or time

  • DATE

  • DATETIME is stored at the granularity of seconds.

  • TIMESTAMP is stored at the granularity of seconds. Identical to DATETIME, here for syntax compatibility.

  • TIME is stored at the granularity of seconds.

  • ThoughtSpot stores date and timestamp values in epoch format.

Was this page helpful?
×