Before you can load data with ThoughtSpot Loader, you must create a schema to receive it, using the SQL command line interface (TQL).
The TQL syntax is similar to the SQL used in other relational databases, but with some important differences. Use DDL (data definition language) to create the schema into which you can load the data. We recommend combining all DDL statements into a single script for creating the schema.
Before writing your TQL script, you need to understand some basic ThoughtSpot concepts.
About databases and schemas
ThoughtSpot organizes objects in a hierarchical namespace. Databases contain schemas, which contain tables.
ThoughtSpot can contain one or more databases, and each database can have multiple schemas. If you do not specify a schema, the default schema (falcon_default_schema) is used automatically. This makes it easier to add tables to the database without the need to explicitly create a schema.
If you do create an additional schema, you must refer to its objects using the syntax <schemaname>.<objectname>. If you do not qualify the schema when referencing its objects, the default schema (falcon_default_schema) will always be assumed.
By default, ThoughtSpot creates an internal database to host tables corresponding to data that is imported by users from a Web browser.
Review the structure of your data
The schema you create to hold the data needs to be a good fit for your data. First, familiarize yourself with the tables you want to load, and understand their structure. Make note of this information for each table:
- The column names and data types
- Type of table (fact or dimension)
- Primary key column(s)
- The size of the table on disk
- Any other tables it can be joined with (foreign keys)
Here’s what you must take into account in your TQL for creating each table, based on these properties:
| Table type | Table size | To be joined with | Schema recommendations |
|---|---|---|---|
| Fact | Any | Small dimension table(s) | Sharded. Foreign key references the primary key in the dimension table. |
| Fact | Any | Large dimension table(s) | Sharded on the same distribution key as the dimension table it will be joined with. Foreign key references the primary key in the dimension table. |
| Fact | Any | Another fact table | Sharded on the same distribution key as the fact table it will join with. Many-to-many relationship defines how the tables will be joined. |
| Dimension | under 50MB | Fact table(s) | Replicated (not sharded). Has a primary key. |
| Dimension | over 50MB | Fact table(s) | Distributed dimension table, sharded on the same distribution key as the fact table it will be joined with. Primary key must be the same as the distribution key. |
Where to go next
- Data types
ThoughtSpot supports the common data types. Compare these with the data types you want to load, and do any necessary conversion ahead of loading the data. - Constraints
Constraints include primary keys, foreign keys, and relationships. Relationships allow you to create a generic relationship for use when you want to join tables that don’t have a primary key/foreign key relationship. - Sharding
For the best performance, you should split (or shard) very large tables across nodes. If you have a large dimension table, you might choose to co-shard it with the fact table it will be joined with. - Chasm traps
In a complex schema, you may have a fact table with no relationship to another fact table, except that each contains a foreign key to a shared dimension table. This is known as a chasm trap, and ThoughtSpot can handle it!