TML for joins

Joins do not exist as a separate type of TML file. Instead, they are present in Worksheets, tables, views, and SQL views. To modify joins using TML, download the objects that contain these joins to flat files in .TML format, modify the files, and subsequently upload the files either to the same cluster, or to a different cluster.

The syntax and functionality of joins in the table TML file differs from the syntax and functionality for worksheet and view TML files.

When you edit the information in the joins section of the TML for a worksheet or view, you override the table join(s) from the table the worksheet or view comes from. However, you only override the join(s) for the specific worksheet or view you are editing, not for the source table.

When you edit the information in the joins_with section of the TML for a table, worksheet, view, or SQL view, you edit the join information for the source object, the destination object, and any dependents, such as worksheets and views. Note that you can only edit joins for which the object is the source.

See the TML parameters for joins for details about the keywords used in these examples.

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

Syntax for joins a Worksheet or view is built on

For the joins a worksheet or view is built on, the join syntax is the following. The joins section defines the joins that the worksheet or view was built on, and the joins_with section defines the joins from that worksheet, view, or SQL view to another object. Note that SQL views and tables do not have the joins section, but they may have the joins_with section.

joins syntax

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_2>

joins_with syntax

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>

Functionality and limitations of the joins section

You can accomplish the following tasks:

  • Add new joins at the worksheet, view, or SQL view level

  • Modify existing joins at the worksheet, view, or SQL view level

  • Delete existing joins at the worksheet, view, or SQL view level

You have the following limitation:

  • You cannot modify joins at the table level from the worksheet, view, or SQL view TML file. You can only override the joins for that specific worksheet, view, or SQL view.

Syntax for joins from a table, Worksheet, view, or SQL view to another object

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.

The joins_with section defines the joins from the data source to another object. It may appear in tables, worksheets, views, and SQL views.

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>

Functionality and limitations of the joins_with section

You can accomplish the following tasks:

  • Add new joins at the table level. Joins only appear in the table TML file of the source table in a join, or the table on the Many side of a Many-to-One join. You can only add and edit table joins from the TML file of the table on the Many side of the join.

  • Create generic or range joins at the table level, such as Table1.date = Table2.date AND Table1.date > Table2.start_date AND Table1.date < Table2.end_date.

    Note that you must have an 'equals' clause before you can have a 'greater than/less than' clause. Table1.date = Table2.date AND Table1.date > Table2.start_date AND Table1.date < Table2.end_date is valid, but Table1.date < Table2.end_date is not.

    Joins only appear in the table TML file of the source table in a join, or the table on the Many side of a Many-to-One join. You can only add and edit table joins from the TML file of the table on the Many side of the join.

    Generic/ range join example:

    name: LO to Date Range Join
    destination:
    name: DATE
    "on": "(([LINEORDER::Order Date] = [DATE::Datekey]) AND ([LINEORDER::Commit Date] < [DATE::Datekey]))"
    type: INNER

  • Edit existing joins by changing the name of the join and modifying it to your specifications. Changing the name of the join creates a new join; you must then delete the old join. Joins only appear in the table TML file of the source table in a join, or the table on the Many side of a Many-to-One join. You can only add and edit table joins from the TML file of the table on the Many side of the join.

You have the following limitations:

  • You cannot directly edit an existing join; you must rename it, edit it to your specifications, and then delete the old join.

  • Renaming a join creates a new join with that name and does not delete the old join with the original name.

  • When creating generic or range joins, you must have an 'equals' clause before you can have a 'greater than/less than' clause. Table1.date = Table2.date AND Table1.date > Table2.start_date AND Table1.date < Table2.end_date is valid, but Table1.date < Table2.end_date is not.

Generic joins

The functionality for generic joins in TML files allows the following elements:

  • Constants: int, double, bool, date, and string

  • Comparison operators: =, !=, <, >, <=, and >=

  • Columns

  • Boolean operators: AND and NOT. TML does not support use of OR in generic joins.

TML parameters for joins

These parameters and their definitions are specific to joins; 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.

d - i j - n o - t

description

destination

fqn

id

is_one_to_one

join

join_path

joins

joins_with

name

on

source

table

table_paths

type
description

The text that describes a join.

destination

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

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.

id

Specifies the id of a table path.

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 object 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 and destination.

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.

source

Name of the source table or view for a join.

table

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

Defined as name within tables definition.

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

Limitations of working with TML files

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

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

  • You can only change logical tables using TML files. You can’t 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 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.

  • Joins only appear in the table TML file of the source table in a join, or the table on the Many side of a Many-to-One join. You can only add and edit table joins from the TML file of the table on the Many side of the join. You can’t view or modify table-level joins from the destination table’s TML file.

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

Was this page helpful?
×