Model, migrate, or restore tables

Use ThoughtSpot Modeling Language to model or update tables in a flat-file format. You can migrate the object to a different cluster, or restore it to the same cluster.

To work with scriptable tables in ThoughtSpot, download these objects to a flat file in TML format, modify it, and subsequently upload this file either to the same cluster, or to a different cluster. To learn how to export, change, and update tables, see Scriptability.

Syntax of the table TML file

The TML file for Scriptable tables has a specific syntax.

See the TML parameters for details about the keywords used in this example.

You may not see each of these parameters in your own TML files, depending on whether each variable is explicitly defined. For example, the fqn parameter is not present in any TML file by default, but you can add it to differentiate a table from another table with the same name.

Refer to join syntax for more information on the functionality and syntax of Worksheet, View, and table joins in TML.

To reduce ambiguity, you may need to add the optional fqn parameter to your TML file when you reference source tables or connections. This is necessary if you have multiple connections or tables with the same name. If you do not add the fqn parameter, and the connection or table you reference does not have a unique name, the file import fails.
guid: <table_guid>
table :
  name: <table_name>
  db: <database_name>
  schema: <schema_name>
  db_table: <database_table_name>
  connection:
    name: <connection_name>
    fqn: <optional_connection_GUID>
  columns:
  - name: <column_name_1>
    db_column_name: <database_column_name>
    data_type: [ BOOL | VARCHAR | DOUBLE | FLOAT | INT32 | INT64 | DATE | TIME ]
    properties:
      column_type: [ MEASURE | ATTRIBUTE ]
      aggregation: [ SUM | COUNT | AVERAGE | MAX | MIN |
                     COUNT_DISTINCT | NONE | STD_DEVIATION | VARIANCE]
      index_type: [ DONT_INDEX | DEFAULT | PREFIX_ONLY |
                    PREFIX_AND_SUBSTRING | PREFIX_AND_WORD_SUBSTRING ]
      index_priority: <index_priority>
      synonyms:
             <synonym_1>
             <synonym_2>
      is_attribution_dimension: [true | false]
      is_additive: [ true | false ]
      calendar: [ default | calendar_name ]
      format_pattern: <format_pattern_string>
      currency_type:
        is_browser : true
          OR
        column : <column_name>
          OR
        iso_code : <valid_ISO_code>
      is_hidden: [ true | false ]
      geo_config:
        latitude : true
          OR
        longitude : true
          OR
        country : true
          OR
        region_name:
        - country : <name_supported_country>
        - region_name : <region_name_in_UI>
      spotiq_preference: <spotiq_preference_string>
      search_iq_preferred: [ true | false ]
    db_column_properties:
      data_type: [ BOOL | VARCHAR | DOUBLE | FLOAT | INT32 | INT64 | DATE | TIME ]
  - name: <column_name_2>
  - name: <column_name_n>
  rls_rules:
    tables:
    - name: <table_name_1>
      id: <optional_table_id>
      fqn: <optional_table_GUID>
    - name: <table_name_2>
    - name: <table_name_n>
    joins:
    - name: <join_name_1>
      source: <source_table_name>
      destination: <destination_table_name>
      type: [RIGHT_OUTER | LEFT_OUTER | INNER | OUTER]
      on: <join_expression_string>
      is_one_to_one: [ false | true ]
    table_paths:
    - id: <table_path_name_1>
      table: <table_name_1>
      join_path:
      - join:
        - <join_name_1>
        - <join_name_n>
   - id: <table_path_name_2>
      table: <table_name_2>
      join_path:
      - join:
        - <join_name_2>
   - id: <table_path_name_n>
      table: <table_name_n>
      join_path:
      - join:
        - <join_name_n>
    rules:
    - name: <rls_rule_name_1>
      expr: <rls_rule_expression_1>
    - name: <rls_rule_name_2>
      expr: <rls_rule_expression_2>
    - name: <rls_rule_name_n>
      expr: <rls_rule_expression_n>
  joins_with:
  - name: <join_name_1>
    description: <optional_join_description_1>
    destination:
      name: <destination_table_name_1>
      fqn: <optional_table_guid_1>
    on: <join_expression_string_1>
    type: [RIGHT_OUTER | LEFT_OUTER | INNER | OUTER]
    is_one_to_one: [ false | true ]
  - name: <join_name_2>
  - name: <join_name_n>

Parameters of TML files

a - e f - o p - w
aggregation

The default aggregation of the Worksheet, View, or table column, or the aggregation of the output for a formula.

Aggregation options depend on the data type.

Possible values are SUM, COUNT, AVERAGE, MAX, MIN, COUNT_DISTINCT, NONE, STD_DEVIATION, and VARIANCE

Default: SUM.

answer

Top-level container for all object definitions within an Answer.

answer_columns

A list of columns generated by the search query.

axis_configs

Specifies the columns for each axis on a chart.

If you are displaying a column chart with a line chart overlaying it, for example, you would need to specify more than one axis_config.

calendar

Specifies the calendar used by a date column.

Can be the Gregorian calendar (default), a fiscal calendar, or any custom calendar.

chart

Contains configuration for the Answer, if it displays in chart format.

chart_columns

A list of columns in the chart.

client_state

A JSON string with more advanced chart and table configuration.

column

The id of the column(s) being filtered on. When a Pinboard contains linked filters, or filters that affect visualizations based on more than one Worksheet, the primary filter column appears first in the list of columns in the TML. The linked filter column appears after the primary filter column.

columns

The columns in the table.

column_id

The id of the worksheet or view column.

For answers, column_id refers to how the column appears in the query. For example, if you sorted by Quarter in your search, from the Commit Date column, the column_id of the column is Quarter(Commit Date).

For worksheets and views, column_id is in the following format: column_id: <id>::<name>. id comes from Worksheet.table_path, and name comes from Table.column. For example: column_id: Customer_Dimension::Customer_Name.

column_type

The type of data the column represents. For a formula, the column_type refers to the output of the formula.

Possible values: MEASURE or ATTRIBUTE

For Worksheets, the default is: MEASURE

For formulas, the default depends on the data_type.

If the data type is INT or BIGINT, the formula output’s column_type defaults to Measure.

If the data type is BOOL, VARCHAR, DOUBLE, FLOAT, DATE, DATETIME, or TIME, the formula output’s column_type defaults to Attribute.

connection

A way to identify the external data warehouse connection that the table resides in. To add tables or columns to an external connection, you must specify this parameter.

currency_type

The source of currency type.

One of:

is_browser : true

infer the currency data from the locale of your browser

column : <column_name>

extracts the currency information from a specified column

iso_code : <valid_ISO_code>

applies currency based on the ISO code;

See ISO 4217 Currency Codes and Set currency type for more information.

custom_name

Optional display name for a column.

data_type

The data type of the formula output or column. If the data type is INT or BIGINT, the formula output’s column_type defaults to Measure. If the data type is BOOL, VARCHAR, DOUBLE, FLOAT, DATE, DATETIME, or TIME, the formula output’s column_type defaults to Attribute. The possible data types are Boolean, Text, Date, Datetime, Time, Numeric, and Decimal.

db

The database that a table resides in. Note that this is not the same as the data warehouse (Falcon, Amazon Redshift, or Snowflake, for example).

db_column_name

The name of the column in the database. Note that this database is not the same as the data warehouse (Amazon Redshift, or Snowflake, for example).

db_column_properties

The properties of the column in the database. Note that this database is not the same as the data warehouse (Amazon Redshift or Snowflake, for example).

db_table

The name of the table in the database. Note that this database is not the same as the data warehouse (Falcon, Amazon Redshift, or Snowflake, for example).

description

The text that describes an object: a worksheet, a worksheet_column, answer, pinboard, view, view_column and so on.

destination

The name of the destination table or View for a join.

display_mode

Determines whether the Answer displays as a chart or a table. Specify either CHART_MODE or TABLE_MODE.

display_headline_column

If the visualization is a headline, this parameter specifies the column the headline comes from.

excluded_visualizations

A list of visualizations the Pinboard editor chose to exclude from the filter. Only appears when using selective filters.

expr

The definition of the formula or, in release 7.1.1 and later, row level security (RLS) rule. For RLS rules, the syntax for variables in TML should be the same as the syntax of the variables in the rule on the table.

filters

Contains specifications for Pinboard, View, and Worksheet filters.

format_pattern

The format pattern string that controls the display of a number, date, or currency column.

formulas

The list of formulas in the Worksheet, View, or Answer.

Each formula is identified by name, the expr (expression), and an optional id attribute.

fqn

The table or connection’s GUID. You can find this string of letters and numbers at the end of the URL for that table.

For example, in https://<company>.thoughtspot.com/#/data/tables/34226aaa-4bcf-4d6b-9045-24cb1e9437cb, the GUID is 34226aaa-4bcf-4d6b-9045-24cb1e9437cb.

Use this optional parameter to reduce ambiguity and identify a specific table or connection, if you have multiple tables or connections with the same name. If you do not add the fqn parameter, and the connection or table you reference does not have a unique name, the file import fails.

geo_config

Specifies the geographic information of a column.

One of:

latitude : true

for columns that specify the latitude

longitude : true

for columns that specify the longitude

country : true

for columns that specify the country

region_name

for specifying a region in a country

Uses two paired parameters:

  • country: <country_name>

  • region_name: <region_name_in_UI>; can be State, Postal Code, District, and so on.

guid

The GUID for the Answer, Pinboard, SpotIQ result, Table, Worksheet, or View.

You can find this string of letters and numbers at the end of the URL for an object.

headline_aggregation

Specifies the type of headline aggregation.

Can be COUNT, COUNT_DISTINCT, SUM, MIN, MAX, AVERAGE, or TABLE_AGGR.

id

Specifies the id of an object, such as table_paths, formula.

For Answers, id refers to how the column appears in the query. For example, if you sorted by Quarter in your search, from the Commit Date column, the id of the column is Quarter(Commit Date).

Refer to Components of a Search Query to understand syntax.

For formulas within Answers, id refers to the display name of the formula.

If you do not give your formula a name, it appears as 'Untitled Formula'.

index_priority

A value (1-10) that determines where to rank a column’s name and values in the search suggestions

ThoughtSpot prioritizes columns with higher values.

index_type

The indexing option of the Worksheet, View, or table column.

Possible values: DONT_INDEX, DEFAULT (see Understand the default indexing behavior), PREFIX_ONLY, PREFIX_AND_SUBSTRING, and PREFIX_AND_WORD_SUBSTRING

Default: DEFAULT

is_additive

Controls extended aggregate options for attribute columns

For attribute columns that have a numeric data type (FLOAT, DOUBLE, or INTEGER) or a date data type (DATE, DATETIME, TIMESTAMP, or TIME)

Possible values: true or false.

Default: true.

is_attribution_dimension

Controls if the column is an attribution dimension.

Used in managing chasm traps.

Possible values: true by default, false to designate a column as not producing meaningful attributions across a chasm trap

Default: true

is_bypass_rls

Specifies if the worksheet supports bypass of Row-level security (RLS)

Possible values: true or false

Default: false

is_hidden

The visibility of the column

Possible values: true to hide the column, false not to hide the column
Default: false

is_one_to_one

Specifies the cardinality of the join. This is an optional parameter.

Possible values: true, false

Default: false

join

Specific join, used in defining higher-level objects, such as table paths

Defined as name within joins definition

join_path

Specification of a composite join as a list of distinct join attributes.

These join attributes list relevant joins, previously defined in the joins, by name.

Default: {}

join_progressive

Specifies when to apply joins on a worksheet

Possible values: true when joins are applied only for tables whose columns are included in the search, and false for all possible joins

Default: true

joins

Contains a list of joins between the tables and Views.

If you edit the joins in the Worksheet or View TML file, you are only editing the joins for that specific Worksheet or View. You are not editing the joins at the table level. To modify table-level joins, you must edit the source table’s TML file.

Each join is identified by name, and the additional attributes of source, destination, type, and is_one_to_one.

joins_with

Contains a list of external joins for which this table is the source.
Each join is identified by name and optional description, and the additional attributes of destination, type, on, and is_one_to_one.

layout

Specifies the Pinboard layout, in the order that a visualization_id is listed.

locked

The 'automatically select my chart' option in the UI. If set to true, the chart type does not change, even when you add items to the query.

name

The name of an object. Applies to worksheet, table,joins, formula, rls_rules, answer, pinboard, view, table, connection, destination, and so on.

For Answers, name refers to how the column appears in the query. For example, if you sorted by Quarter in your search, from the Commit Date column, the name of the column is Quarter(Commit Date). Refer to Components of a Search Query to understand syntax.

on

The join expression: the relationship definition, or the keys that your tables are joined on. For example, [sale::Sale_Last Name] = [employee::Employee_Last Name] AND [sale::Sale_First Name] = [employee::Employee_First Name].
You cannot directly edit a relationship definition. To alter a relationship definition, you must rename the join or create a new join.

oper

The operator of the Pinboard, View or Worksheet filter. Accepted operators are "in", "not in", "between", =<, !=, <=, >=, >, or <.

ordered_column_ids

A list of columns, in the order they appear in the table.

phrase

Phrase associated with a View column.

pinboard

Top-level container for all object definitions within the Pinboard or SpotIQ result.

properties

The list of properties of a Worksheet, table, or View column, a Worksheet or View itself, or the properties of the output for a formula within an Answer, Worksheet, or View.

For Worksheets, Views, and tables, each column can have the following properties, depending on its definition: column_type, aggregation, index_type, is_hidden, index_priority, synonyms, is_attribution_dimension, is_additive, calendar, format_pattern, currency_type, geo_config, spotiq_preference, and search_iq_preferred.

Worksheets and Views themselves can have the following properties that affect query generation: is_bypass_rls, and join_progressive.

For Answers, each formula’s output can have the following properties, depending on its definition: column_type and aggregation.

rls_rules

In release 7.1.1 and later, a container for the full definition of row level security rules for the table.

rules

In release 7.1.1 and later, a container for the names and expressions of row level security rules for the table.

schema

The schema that the table is a part of.

search_output_column

Name of the column generated by the view. To change the name of the column in the view, edit the name parameter.

search_query

A string that represents the fully disambiguated search query. Refer to Components of a Search Query to understand syntax.

show_headline

Determines whether to show the headline for this column. true shows the headline.

size

The size of a visualization in a Pinboard. The options are EXTRA_SMALL, SMALL, MEDIUM, LARGE, LARGE_SMALL, MEDIUM_SMALL, and EXTRA_LARGE.

source

Name of the source table or View for a join.

spotiq_preference

Specifies whether to include a column in SpotIQ analysis. Specify EXCLUDE, or this property defaults to include the column in SpotIQ Analysis.

synonyms

Alternate names for the column, used in search

table

Top-level container for all object definitions within the table.

Specific table, used in defining higher-level objects, such as table paths.

Defined as name within tables definition.

For Answers, this parameter contains configuration for the Answer, if it displays in table format.

table_columns

The columns in an Answer that is being displayed in table format.

table_paths

The list of table paths

Each table path is identified by the id, and additional attributes of table and join_path.

tables

List of tables used by the Worksheet, Answer, or table RLS rule.

Each table is identified by name.

type

For Worksheets and Views, this is the join type. This is an optional parameter.

Possible values: LEFT_OUTER for left outer join, RIGHT_OUTER for right outer join, INNER for inner join, OUTER for full outer join.

Default: INNER

For tables, this is the connection type.

Possible values: Snowflake, Google BigQuery, Microsoft Azure, or Amazon Redshift.

Within the chart section of an Answer TML file, this is the chart type.

Possible values: COLUMN, BAR, LINE, PIE, SCATTER, BUBBLE, STACKED_COLUMN, AREA, PARETO, COLUMN, GEO_AREA, GEO_BUBBLE, GEO_HEATMAP, GEO_EARTH_BAR, GEO_EARTH_AREA, GEO_EARTH_GRAPH, GEO_EARTH_BUBBLE, GEO_EARTH_HEATMAP, WATERFALL, TREEMAP, HEATMAP, STACKED_AREA, LINE_COLUMN, FUNNEL, LINE_STACKED_COLUMN, PIVOT_TABLE, SANKEY, GRID_TABLE, SPIDER_WEB, WHISKER_SCATTER, STACKED_BAR, or CANDLESTICK.

values

The values being filtered (excluded or included) in a Pinboard, View, or Worksheet.

view

Top-level container for all object definitions within the View.

view_columns

The list of columns in the View.

Each column is identified by name, description, column_id, phrase and properties.

visualizations

The visualizations in a Pinboard: tables, charts, and headlines.

visualization_id

The id of a visualization.

Used to specify the Pinboard’s layout.

worksheet

Top-level container for all object definitions within the worksheet

worksheet_columns

The list of columns in the worksheet

Each worksheet is identified by name, description, column_id, and properties.

  • Formulas and columns can either have a new name, or a new expression. You cannot change both, unless migrating or updating the worksheet two times.

  • It is not possible to reverse the join direction in the TML script.

  • You cannot create new tables using Scriptability. You can only update existing tables.

  • You can only change logical tables using Scriptability. You cannot change the physical version of the table that exists in a database. When you change the column_name, for example, the name changes in the application, but not in the physical table in the database.

  • You cannot import manually compressed .zip files. You can only import .zip files that you exported from ThoughtSpot: a custom SpotApp, an object and its associated data sources, or multiple objects of the same type that you exported from the object list page.

  • You cannot create Scriptable representations of R- or Python-powered visualizations.

  • You can only view and modify joins at the table level in the source table TML. You cannot view or modify table-level joins from the destination table’s TML file.

  • You cannot modify joins at the table level from the Worksheet or View TML file. You can only change the joins for that specific Worksheet or View. To modify table-level joins, you must edit the source table’s TML file.

  • You cannot use TML to remove columns or tables from an external connection. You can only add them.


Was this page helpful?