Schema creation examples

These examples demonstrate the steps involved in creating a schema using the ThoughtSpot SQL Command Line (TQL). After the schema is created, you can load data into it with ThoughtSpot Loader.

Simple schema creation example

The example creates a database (tpch) with two tables (customer, transaction). The example does not create a schema explicitly. So it will use the default schema (falcon_default_schema).

In this example:

  • The table customer has a primary key called customer_id. The table customer_transactions has a primary key called transaction_id.

  • The customer table is unsharded.

  • The customer_transactions table is sharded into 96 shards using the transaction_id column.

  • Both tables have referential integrity on customer_id.

$tql
TQL> CREATE DATABASE tpch;
TQL> USE tpch;
TQL> CREATE TABLE customer (
      name  VARCHAR(100),
      address VARCHAR(255),
      zipcode INT,
      customer_id INT,
      CONSTRAINT PRIMARY KEY (customer_id)
      );
TQL> CREATE TABLE customer_transactions (
     transaction_id INT,
     customer_id INT
     amount DOUBLE,
     transaction_date DATETIME,
     CONSTRAINT PRIMARY KEY (transaction_id),
     CONSTRAINT FOREIGN KEY (customer_id) REFERENCES
     customer(customer_id)
     )  PARTITION BY HASH (96) KEY (transaction_id);

More complex schema creation example

The example uses a custom schema called sample_schema to hold the tables. Because of this, every table reference has to be schema qualified.

$ tql
TQL> CREATE DATABASE "sample_db";
TQL> USE "sample_db";
TQL> CREATE SCHEMA "sample_schema";

TQL> CREATE TABLE "sample_schema"."customer" (
  "c_custkey" BIGINT,
  "c_name" VARCHAR(255),
  "c_address" VARCHAR(255),
  "c_city" VARCHAR(255),
  "c_nation" VARCHAR(255),
  "c_region" VARCHAR(255),
  "c_phone" VARCHAR(255),
  CONSTRAINT PRIMARY KEY ("c_custkey")
);

TQL> CREATE TABLE "sample_schema"."supplier" (
  "s_suppkey" BIGINT,
  "s_name" VARCHAR(255),
  "s_address" VARCHAR(255),
  "s_city" VARCHAR(255),
  "s_nation" VARCHAR(255),
  "s_region" VARCHAR(255),
  "s_phone" VARCHAR(255),
  CONSTRAINT PRIMARY KEY ("s_suppkey")
);

TQL> CREATE TABLE "sample_schema"."lineorder" (
  "lo_orderkey" BIGINT,
  "lo_linenumber" BIGINT,
  "lo_custkey" BIGINT,
  "lo_partkey" BIGINT,
  "lo_suppkey" BIGINT,
  "lo_orderdate" DATE,
  "lo_orderpriority" VARCHAR(255),
  "lo_shippriority" VARCHAR(255),
  "lo_quantify" BIGINT,
  "lo_extendprice" BIGINT,
  "lo_ordtotalprice" BIGINT,
  "lo_discount" BIGINT,
  "lo_commitdate" DATE,
  CONSTRAINT PRIMARY KEY ("lo_orderkey","lo_linenumber"),
  CONSTRAINT FOREIGN KEY ("lo_custkey") REFERENCES "sample_schema"."customer" ("c_custkey"),
  CONSTRAINT FOREIGN KEY ("lo_suppkey") REFERENCES "sample_schema"."supplier" ("s_suppkey")
  )  PARTITION BY HASH (96) KEY (lo_orderkey);
Was this page helpful?
×