icCube

Documentation

Joins (ETL)

Allows for joining two tables with different strategies (similar to SQL).

This transformation allows for joining two tables the same way you do it in a relational database. From an implementation point of view the right table is cached in memory and the left table is streamed if possible. So, try defining the small table as the right one.

Join Types available in icCube :

Left with Lookup: All left rows are matched with a single right table row that has to exist. If more than one right table row is matching or no match is found an error is generated.

Left: All left table rows joined with matching right tables rows. If no match the right columns are filled with NULL

Rigth: All right table rows joined with matching left tables rows. If no match the left columns are filled with NULL

Inner: All left table rows joined with matching right tables rows. If no match rows are ignored

Full: All right and left table rows joined with matching rows. If no match columns are filled with NULL

The ignored columns fields allow to remove those fields from the generated table.

The SQL like joins (left,inner, right and full) work as in a relational database. Remember that if multiple rows match on the left and right table a crossjoin will be generated. Therefore the result of a join can be much greater than the size of both tables.

In the enclosed example we will follow the same tables as described in SQL Joins Explained website. Feel free to check this website for further explanations and play with the enclosed model in icCube.

The schema mentioned in this page can be downloaded here. It requires icCube v6.x onwards.

Next chapter: Sort describes how to sort a table.