atoti.Table.join()#

Table.join(target, mapping=None, /, *, target_optionality='optional')#

Define a join between this source table and the target table.

There are two kinds of joins:

  • full join if all the key columns of the target table are mapped and the joined tables share the same locality (either both Table or both ExternalTable).

  • partial join otherwise.

Depending on the cube creation mode, the join will also generate different hierarchies and measures:

  • manual: No hierarchy is automatically created. For partial joins, creating a hierarchy for each mapped key column is necessary before creating hierarchies for the other columns. Once these required hierarchies exist, hierarchies for the un-mapped key columns of the target table will automatically be created.

  • no_measures: All the key columns and non-numeric columns of the target table will be converted into hierarchies. No measures will be created in this mode.

  • auto: The same hierarchies as in the no_measures mode will be created. Additionally, columns of the base table containing numeric values (including arrays), except for columns which are keys, will be converted into measures. Columns of the target table with these types will not be converted into measures.

Parameters:

  • target (Table) – The other table to join.

  • mapping (Condition[ColumnIdentifier, Literal['eq'], ColumnIdentifier, ~typing.Literal['and'] | None] | None) – An equality-based condition from columns of this table to columns of the target table. If None, the key columns of the target table with the same name as columns in this table will be used.

  • target_optionality (Literal['mandatory', 'optional']) –

    The relationship optionality on the target table side.

    • "optional" declares no constraints: a row in the source table does not need to have a matching row in the target table.

    • "mandatory" declares that every row in the source table has at least one matching row in the target table at all time. In the future, this hint will enable some optimizations when incrementally refreshing DirectQuery data.

Example

>>> sales_table = session.create_table(
...     "Sales",
...     types={"ID": tt.STRING, "Product ID": tt.STRING, "Price": tt.INT},
... )
>>> products_table = session.create_table(
...     "Products",
...     types={"ID": tt.STRING, "Name": tt.STRING, "Category": tt.STRING},
... )
>>> sales_table.join(
...     products_table, sales_table["Product ID"] == products_table["ID"]
... )