TQL reference

Examples:

SHOW DATABASES;

USE <database>

Examples:

USE "fruit_database";

Lists all schemas within the current database. For example:

SHOW SCHEMAS;

SHOW TABLES

Lists all tables within the current database by schema. For example:

SHOW TABLES;

SHOW TABLE <table>

Lists all the columns for a table. For example:

SHOW TABLE "locations";

SCRIPT SERVER

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

SCRIPT SERVER;

SCRIPT DATABASE <database>

Generates the TQL schema for all tables in a database. For example:

SCRIPT DATABASE "fruit_database";

SCRIPT TABLE <table>

Generates the TQL schema for a table. For example:

SCRIPT TABLE "vendor";

SELECT <cols_or_expr> FROM <table_list> [WHERE <predicates>] [GROUP BY <expr>] [ORDER BY <expr>]

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
• max

For example:

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;

CREATE DATABASE <database>

Creates a database. For example:

CREATE DATABASE "fruit_database";

CREATE SCHEMA <schema>

Creates a schema within the current database. For 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 no KEY is specified, the table will be randomly sharded.

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 your TQL script, after creating your tables. This method guarantees that tables are created before they are referenced in the constraint definitions. For example:

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");

DROP DATABASE <database>

Drops a database and all of its schemas and tables. For example:

DROP DATABASE "fruit_database";

DROP SCHEMA <schema>

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

DROP SCHEMA "fruit_schema";

DROP TABLE <table>

Drops a table. For example:

DROP TABLE "location";

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 TRUNCATE, or all dependent objects (worksheets and pinboards) in ThoughtSpot will become invalid. For example:

TRUNCATE TABLE "location";

ALTER TABLE <table> ADD|DROP|RENAME COLUMN <column>

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

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.

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:

ALTER TABLE "sales" DROP CONSTRAINT PRIMARY KEY;

ALTER TABLE "sales" ADD CONSTRAINT PRIMARY KEY ("PO_number");

ALTER TABLE <table> DROP CONSTRAINT [FOREIGN KEY| RELATIONSHIP] <name>;

Drops the named foreign key or relationship between two tables. For example:

ALTER TABLE "sales_fact" DROP CONSTRAINT FOREIGN KEY "FK_PO_number";

ALTER TABLE "fruit_dim" DROP RELATIONSHIP "REL_dates";

ALTER TABLE <table> DROP [CONSTRAINT FOREIGN KEY [<table_name>] | RELATIONSHIP [WITH <table_name>];

If you drop a foreign key without specifying the referenced table, all foreign keys from the table you are altering will be dropped.

Examples:

ALTER TABLE "shipments" DROP CONSTRAINT FOREIGN KEY "orders";

ALTER TABLE "wholesale_buys" DROP RELATIONSHIP WITH "retail_sales";

Drops all relationships that have wholesale_buys as a source.

ALTER TABLE "wholesale_buys" DROP RELATIONSHIP;

Drops all foreign keys from wholesale_buys.

ALTER TABLE "wholesale_buys" DROP CONSTRAINT FOREIGN KEY;

ALTER TABLE <table> [SET DIMENSION | SET FACT [PARTITION BY HASH [(<shards>)] [KEY(<column>)]]]

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.

Examples of this statement:

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. For example:

ALTER TABLE fact100 MODIFY COLUMN product_id int;

INSERT INTO <table> VALUES ...

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

INSERT INTO "vendor" VALUES ('helen rose', 'jacob norse', 'eileen ruff', 'manny gates');

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. For 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. For 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 optionally provided predicates. Predicates have the form column = value connected by the AND keyword. For example.

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

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 "cart_id";

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:

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";

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:

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";

• VARCHAR(n)

• DOUBLE
• FLOAT

• BOOL

• INT
• BIGINT

BIGINT holds 64 bits.

• DATE
• DATETIME
• TIMESTAMP
• TIME

DATETIME, TIMESTAMP, and TIME are stored at the granularity of seconds

TIMESTAMP is identical to DATETIME, but is included for syntax compatibility.