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 Import and export TML files.

Starting in May 2022, ThoughtSpot rebranded pinboards as Liveboards. For backward compatibility, we currently support internal processes and external methods that use the older naming convention.

Syntax of the table TML file

The TML file for 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, SQL 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>
    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 answer answer_columns axis_configs calendar chart chart_columns client_state column columns column_id column_type connection currency_type custom_name data_type db db_column_name db_table description destination display_mode display_headline_column expr	filters format_pattern formulas fqn geo_config guid headline_aggregation id index_priority index_type is_additive is_attribution_dimension is_bypass_rls is_hidden is_one_to_one join join_path join_progressive joins layout locked name on oper ordered_column_ids	phrase pinboard properties rls_rules rules schema search_output_column search_query show_headline size source spotiq_preference sql_output_column sql_query sql_view sql_view_columns synonyms table table_columns table_paths tables type values view view_columns visualizations visualization_id worksheet worksheet_columns

a - e

f - o

p - w

display_headline_column

is_attribution_dimension

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.

See Set up a 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 or column 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 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.

See Set number, date, and currency formats

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.

See Add a geographical data setting

guid: The GUID for the answer, Liveboard, SpotIQ result, table, worksheet, view, or SQL 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.

See Change a column’s suggestion priority.

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

See Index Type Values

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.

See Making an ATTRIBUTE column ADDITIVE.

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

See Change the attribution dimension

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

Possible values: true or false

Default: false

See Privileges that allow users to set, or be exempt from, RLS

is_hidden: The visibility of the column

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

See Hide a column

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

See How the worksheet join rule works

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,, sql_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, view, or SQL view column, a worksheet, view or SQL view itself, or the properties of the output for a formula within an answer, worksheet, view, or SQL 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, and spotiq_preference.

Worksheets, views, and SQL 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: A container for the full definition of row level security rules for the table.

rules: 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.

Refer to Set columns to exlude from SpotIQ analyses.

sql_output_column: Name of the SQL’s output column as per the select clause in the view’s SQL query.

sql_query: A string that represents the SQL query used to create the SQL view.

sql_view: Top-level container for all object definitions within the SQL view.

sql_view_columns: The list of columns in the SQL view.

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

synonyms: Alternate names for the column, used in search

See Define a synonym

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 TML files. You can only update existing tables.
You can only change logical tables using TML files. 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 set of TML files, 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 or export TML files for 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 delete a table join by removing it from the table TML. You must delete it through the UI.
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.
If there is an error in any row-level security (RLS) rule when importing a table, the entire import fails. To import the table, you must fix the RLS rule error.

Was this page helpful?Give us feedback!

ThoughtSpot is the Modern Analytics Cloud company. Our Live Analytics services deliver personalized, actionable insights at the point of impact for every user, at every level.

Product

By Role

By Department

Solutions

About Us

Connect

(800) 508-7008