Joins
Join one or more datasets together using SQL joins. Supported join types include INNER, LEFT, RIGHT, FULL OUTER, and CROSS.
Parameters
Example
Source Code
Last updated
Join one or more datasets together using SQL joins. Supported join types include INNER, LEFT, RIGHT, FULL OUTER, and CROSS.
Last updated
Name | Type | Description | Is Optional |
---|---|---|---|
join_dicts
join_dicts
List of 'join_dict' dictionaries which specify how to perform each join. Each Dictionary will have the following values 1. join_type: Literal['LEFT', 'RIGHT', 'INNER', 'FULL OUTER', 'CROSS'] - Type of Join to Perform 2. table_a: str dataset.FQTN (only needed if not first 'join_dict' in list) - Specfies the table/fqtn to join in the FROM ...
clause - This would be the source_table
by default if the first dictionary 3. table_b: str ( dataset.FQTN) - Specifies the table/fqtn to join in the <join_type> JOIN ...
clause 4. join_on: Dict[str, str] - Dictionary for which columns to join on between table A and table B - Key value pairs determine the ON part of the clause, like the string below ON <table_A_col_name_dict_key> = table_B_col_name_dict_value>
- join_on dict can contain mutiple values Example: python internet_sales = rasgo.get.dataset(74) customer = rasgo.get.dataset(55) product = rasgo.get.dataset(75) ds = product.join(join_dicts=[ { 'table_b': internet_sales.fqtn, 'join_type':'LEFT', 'join_prefix_b':'product', 'join_on':{'PRODUCTKEY':'PRODUCTKEY'} }, { 'table_a':internet_sales.fqtn, 'table_b':customer.fqtn, 'join_on':{'CUSTOMERKEY':'CUSTOMERKEY', 'DUEDATE': 'BIRTHDATE'}, 'join_type':'INNER', 'join_prefix_b':'sales' } ]) print(ds.sql)
Will produce the following SQL: sql SELECT ... FROM ADVENTUREWORKS.PUBLIC.DIMPRODUCT LEFT JOIN ADVENTUREWORKS.PUBLIC.FACTINTERNETSALES ON DIMPRODUCT.PRODUCTKEY = FACTINTERNETSALES.PRODUCTKEY INNER JOIN ADVENTUREWORKS.PUBLIC.DIMCUSTOMER ON FACTINTERNETSALES.CUSTOMERKEY = DIMCUSTOMER.CUSTOMERKEY AND FACTINTERNETSALES.DUEDATE = DIMCUSTOMER.BIRTHDATE