TQL is the ThoughtSpot language for entering SQL commands. This reference lists TQL commands you can use to do things like creating a schema or verifying a data load.
About using TQL
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 returned by a query. For example:
$ tql --query_results_apply_top_row_count 100
As a best practice, you should enclose object names (database, schema, table, and column) in double quotes, and column values in single quotes. When referring to objects using fully qualified object names, the syntax is:
"database"."schema"."table"
To get help on 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 but are capitalized here for readability.
View schemas and data
Syntax | Description |
---|---|
SHOW DATABASES
|
Lists all available databases.
Examples:
|
|
Switches the context to the specified database. This is required if queries
do not use fully qualified names (database.schema.table) for specifying
tables.
Examples:
|
SHOW SCHEMAS
|
Lists all schemas within the current database. For example:
|
|
Lists all tables within the current database by schema. For example:
|
|
Lists all the columns for a table. For example:
|
|
Generates the TQL schema for all tables in all databases on the server. For example:
|
|
Generates the TQL schema for all tables in a database. For example:
|
|
Generates the TQL schema for a specified table. Use the physical table name, not the logical table name from the ThoughtSpot UI. For example:
|
|
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:
You can use the following aggregation functions:
You can use the following date functions:
For example:
|
Schema creation
Syntax | Description |
---|---|
|
Creates a database. For example:
|
|
Creates a schema within the current database. For example:
|
|
Creates a table with the specified column definitions and constraints.
Use
Do not specify relationship constraints (
|
Schema modification
Syntax | Description |
---|---|
|
Drops a database and all of its schemas and tables. For example:
|
|
Drops a schema within the current database, and drops all of the tables in the schema. For example:
|
|
Drops a table. For example:
|
TRUNCATE TABLE
<table>
|
Removes all data from a table, but preserves its metadata, including all GUIDs, relationships, etc. This can be used to force a new schema for a table without losing the metadata. However, this operation removes all existing data
from the table and must be used with caution. You must reload the data following
a
|
|
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. For example:
|
|
Drops the primary key from a table. Note that if you then add a new primary key, the same upsert behavior will be applied as with adding any primary key. This can result in data deletion, so make sure you understand how the upsert will affect your data ahead of time. For example:
|
|
Drops the named foreign key or relationship between two tables. For example:
|
|
Changes the partitioning on a table by doing one of:
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 To make a sharded table into a dimension table (replicated on every node), use
Examples of this statement:
|
|
Changes the data type of a column. This can have implications on sharding and primary key behavior. See About data type conversion. For example:
|
Modify data
Syntax | Description |
---|---|
|
Inserts values into a table. Only use this for testing. Do not use
|
|
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
|
|
Updates rows in a table that match optionally provided predicates. Predicates
have the form
|
|
Deletes rows from a table that match optionally provided predicates.
Predicates have the form
|
Constraints and relationships
Constraints and relationships in ThoughtSpot are used to define the
relationships between tables (how they can be joined). However, constraints
are not enforced, as they would be 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:
Syntax | Description |
---|---|
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. Some examples:
</p>
|
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 of this statement:
|
RELATIONSHIP |
Defines a relationship where the value(s) in the table can be used to join to a second table, using an equality condition (required) and one or more range conditions (optional). These conditions act like a WHERE clause when the two tables are joined. They are applied using AND logic, such that all conditions must be met for a row to be included. You may add multiple relationships between tables. When creating a relationship, give it a name. You can reference the relationship name later, if you want to remove it. Examples of this statement:
|
Permitted joins and necessary permissions
See this matrix for information about which joins you can create, and what permissions these joins require.
Worksheets | View | Materialized View | Imported table (UI) | Table uploaded from backend (tsload) or through DataFlow | Table uploaded through Embrace | View on top of table uploaded through Embrace | ||
---|---|---|---|---|---|---|---|---|
Necessary permissions: | None | None | None | Can manage data permission to load the table | Admin privileges to access tsload |
None | None | |
Worksheets | Can edit permission on the source Worksheet | ✗ | ✗ | ✗ | ✓ | ✗ | ✗ | ✗ |
View | Can edit permission on the source View | ✗ | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ |
Materialized View | Can edit permission on the source Materialized View | ✗ | ✓ | ✓ | ✓ | ✓ Note: It is a best practice to create this join through the UI, rather than using TQL . |
✗ | ✗ |
Imported table (UI) | Can edit permission on the source table | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ |
Table uploaded from backend (tsload) or through DataFlow | Can edit permission on the source table | ✗ | ✓ | ✓ Note: It is a best practice to create this join through the UI, rather than using TQL . |
✓ | ✓ Note: This join is often used to create Worksheets. |
✗ | ✗ |
Table uploaded through Embrace | Can edit permission on the source table, and can manage data permission | ✗ | ✗ | ✗ | ✗ | ✗ | ✓ Note: The two tables must be from the same connection. |
✓ Note: The View and the table must be from the same connection. |
View on top of table uploaded through Embrace | Can edit permission on the source View | ✗ | ✗ | ✗ | ✗ | ✗ | ✓ Note: The View and the table must be from the same connection. |
✓ Note: The two Views must be from the same connection. |
Data types
ThoughtSpot supports a simplified list of data types:
Syntax | Description | Examples |
---|---|---|
Character |
|
Specify the maximum number of characters, as in VARCHAR(255). The size limit is 64MB for VARCHAR values. |
Floating point |
|
DOUBLE is recommended. |
Boolean |
|
Can be true or false . |
Integer |
|
INT holds 32 bits.
BIGINT holds 64 bits. |
Date or time |
|
DATETIME, TIMESTAMP, and TIME are stored at the granularity of seconds. TIMESTAMP is identical to DATETIME, but is included for syntax compatibility. ThoughtSpot stores date and timestamp values in epoch format. |