TML for views

Use TML to modify a ThoughtSpot object in a flat-file format. Then, migrate the object to a different cluster, or restore it to the same cluster.

To work with TML files for views, you can download these objects to flat files in .TML format, modify the files, and subsequently upload the files either to the same cluster, or to a different cluster. To learn how to export, change, and update views, see Import and export TML files.

The syntax examples in this article contain every possible parameter in TML files for views. Some of these parameters are not in these files by default. If you want to use them, you must add them yourself. 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.

As you work with TML files, keep in mind that changing elements of the TML file, such as the name of a column or table, may affect dependents. This is specifically true if you are editing TML files outside ThoughtSpot. When you change the name of a table in a TML file, and then import that file into ThoughtSpot, ThoughtSpot automatically updates that table name in any dependents, such as Answers that use the table as a data source. However, if you download multiple TML files from one ThoughtSpot cluster, then change the table name in TML, and upload all the files to a brand-new cluster, ThoughtSpot doesn’t know that the dependents should use that table. You must also change the table name in the dependents.

Syntax of the view TML file

The TML file for views has a specific syntax.

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

See Limitations of working with TML files for more information about actions you can’t perform using TML.

You may not see each of these parameters in your own TML files, depending on whether each variable is explicitly defined. For example, if you don’t have any filters on your view, the filters variable does not appear. You can add that variable in the TML file to add values to include or exclude.

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.

Refer to TML for joins for more information on the functionality and syntax of view joins in TML.

If you edit the joins in the view TML file, you are only editing the joins for that specific 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.
guid: <view_guid>
view:
  name: <view_name>
  description:
    This is a multi-line description of the View.
    Description line 2
  tables:
  - name: <table_name_1>
    id : <optional_table_id>
    fqn : <optional_GUID_of_table_name>
  - 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>
  formulas:
  - id: <formula_id_1>
    name: <formula_name_1>
    expr: <formula_definition_1>
    properties: <formula_properties_1>
      column_type: [ MEASURE | ATTRIBUTE ]
      data_type: [ BOOL | VARCHAR | DOUBLE | FLOAT | INT | BIGINT | DATE | DATETIME | TIMESTAMP | TIME ]
      aggregation: [ SUM | COUNT | AVERAGE | MAX | MIN |
                         COUNT_DISTINCT | NONE | STD_DEVIATION | VARIANCE]
  - id: <formula_id_n>
    name: <formula_name_n>
    expr: <formula_definition_n>
    properties: <formula_properties_n>
  filters:
  - column: <filtered_column_name_1>
    oper: <filter_operator>
    values: <filtered_values>
    - value 1
    - value 2
  - column: <filtered_column_name_n>
  search_query: <query_string>
  view_columns:
  - name: <column_name_1>
    description: <optional_column_description>
    search_output_column: <query_column_name>
    column_id: <table_path>::<column_id_1>
    phrase: <phrase_string_1>
    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>
  - name: <column_name_2>
    description : <column_description>
    search_output_column: <query_column_name>
    column_id : <table_path>::<column_id_2>
  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>

TML parameters for views

These parameters and their definitions are specific to views; this list doesn’t contain every parameter in every TML file. Similarly, the parameter definitions may vary from object to object, since the parameters may have different roles in different objects.

a - g i - o p - v
aggregation

The default aggregation of the view 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.

calendar

Specifies the calendar used by a date column.

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

column

The id of the column(s) being filtered on.

column_id

The id of the view column.

column_id is in the following format: column_id: <id>::<name>. id comes from View.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 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.

currency_type

The source of the 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.

data_type

The data type of the formula output or column. If the data type is INT32 or INT64, 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.

description

The text that describes an object: a view, view_column and so on.

destination

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

expr

The definition of the formula.

filters

Contains specifications for view 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 view.

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 or connection.

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, if you have multiple tables with the same name. When exporting a TML file, you have the option to Export FQNs of referenced objects, which ensures that the TML files you export contain FQNs for the underlying tables and connections. 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. See Add a geographical data setting

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 view.

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

id

Specifies the id of an object, such as table_paths, 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 view 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_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: {}

joins

Contains a list of joins between the tables and views.

If you edit the joins in the view TML file, you are only editing the joins for that specific 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 view 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.

name

The name of an object. Applies to joins, formula, view, and so on.

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].

To alter a relationship definition, edit this parameter.

oper

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

phrase

Phrase associated with a view column.

properties

The list of properties of a view column or the properties of the output for a formula within a view.

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.

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.

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

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

Defined as name within tables definition.

tables

List of tables used by the View.

Each table is identified by name.

table_columns

The columns in an Answer or View 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.

type

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

values

The values being filtered (excluded or included) in a view.

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.

Limitations of working with TML files

There are certain limitations to the changes you can apply by editing views through TML.

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

  • It isn’t possible to reverse the join direction in the TML script.

  • You can’t create or export TML files for R- or Python-powered visualizations.

  • You can’t 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 can’t modify joins at the table level from the Worksheet, view, or Answer TML file. You can only override the joins for that specific Worksheet, view, or Answer. To modify table-level joins, you must edit the source table’s TML file.

  • When deleting columns, you only delete ThoughtSpot’s record of the column. You don’t delete the column in your external database.