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, if you did not define any conditional formatting, the conditional_formatting
variable does not appear.
You can add that variable in the TML file to specify conditional formatting.
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> 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
, andVARIANCE
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.
- color
-
Color to use for conditional formatting or for the columns of an Answer in chart form, in the form of a HEX value.
- 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 byQuarter
in your search, from theCommit Date
column, thecolumn_id
of the column isQuarter(Commit Date)
.For worksheets and views,
column_id
is in the following format:column_id: <id>::<name>
.id
comes fromWorksheet.table_path
, andname
comes fromTable.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
orATTRIBUTE
For Worksheets, the default is:
MEASURE
For formulas, the default depends on the data_type.
If the data type is
INT
orBIGINT
, the formula output’scolumn_type
defaults toMeasure
.If the data type is
BOOL
,VARCHAR
,DOUBLE
,FLOAT
,DATE
,DATETIME
, orTIME
, the formula output’scolumn_type
defaults toAttribute
- conditional_formatting
-
Conditional formatting for the chart or table of an Answer.
- connection
-
A way to identify the external data warehouse connection that the table resides in. To add tables or columns to an Embrace 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
orBIGINT
, the formula output’scolumn_type
defaults toMeasure
. If the data type isBOOL
,VARCHAR
,DOUBLE
,FLOAT
,DATE
,DATETIME
, orTIME
, the formula output’scolumn_type
defaults toAttribute
. The possible data types areBoolean
,Text
,Date
,Datetime
,Time
,Numeric
, andDecimal
.
- 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
, aworksheet_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
orTABLE_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
- 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
, theexpr
(expression), and an optionalid
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 is34226aaa-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
, orTABLE_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 byQuarter
in your search, from theCommit Date
column, theid
of the column isQuarter(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
, andPREFIX_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
, orINTEGER
) or a date data type (DATE
,DATETIME
,TIMESTAMP
, orTIME
)Possible values:
true
orfalse
.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 trapDefault:
true
- is_bypass_rls
-
Specifies if the worksheet supports bypass of Row-level security (RLS)
Possible values:
true
orfalse
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
withinjoins
definition
- join_path
-
Specification of a composite join as a list of distinct
join
attributes.These
join
attributes list relevant joins, previously defined in thejoins
, 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, andfalse
for all possible joinsDefault:
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 ofsource
,destination
,type
, andis_one_to_one.
- joins_with
-
Contains a list of external joins for which this table is the source.
Each join is identified byname
and optionaldescription
, and the additional attributes ofdestination
,type
,on
, andis_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.
- max
-
Maximum value for conditional formatting.
- min
-
Minimum value for conditional formatting.
- name
-
The name of an object. Applies to
worksheet
,table
,joins
,formula
,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 byQuarter
in your search, from theCommit Date
column, thename
of the column isQuarter(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
, andsearch_iq_preferred
.Worksheets and Views themselves can have the following properties that affect query generation:
is_bypass_rls
, andjoin_progressive
.For Answers, each formula’s output can have the following properties, depending on its definition:
column_type
andaggregation
.
- plotAsBand
-
Specifies whether to plot the chart conditional formatting like a band on the Visualization.
This is the 'fill chart' option in the UI.
- range
-
Range for the conditional formatting to apply to, with a specified
min
andmax
.
- rule
-
A conditional formatting rule.
- 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
, andEXTRA_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
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
withintables
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 oftable
andjoin_path
.
- tables
-
List of tables used by the Worksheet or Answer.
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 Embrace connection type.
Possible values:
Snowflake
,Google BigQuery
,Microsoft Azure
, orAmazon 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
, orCANDLESTICK
.
- 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
andproperties
.
- 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
, andproperties
.
-
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 Embrace connection. You can only add them.