Create a Join in a Data Flow
Insert a join operator to merge two data sets together using a join definition to match the records.
Context
Procedure
- Click the Join tool, drag it onto the diagram canvas, and release it where you want to create the join.
- Click and drag the port on the right of the first source node that you want to join and drop it onto the join operator.
A flow is created between the source and the join operator.
- Repeat the action for the second source node to create its flow to the join operator.
- Click the join operator to display its properties in the side panel, and complete the properties in the General section:
Property
Description
Label
Provide a suitable name for your join as per your requirement.
Join Type
Choose the type of join. The joins that are currently supported are INNER, LEFT_OUTER, and RIGHT_OUTER.
- In the Join Definition section, click on (Edit) to modify the predefined join mappings.
-
Click on (Auto Map) to automatically create a join that matches column names if possible.
-
To manually create a new join mapping, drag and drop any left column onto any right column.
-
To delete a join mapping, select it and click .
-
To clear all mappings, click (Remove all Mappings).
-
- In the Columns section, specify the mapping of your columns:
-
All unique columns from the input operators are displayed. Hover on a column and click to view its data type.
-
You can reorder the columns by dragging and dropping.
-
You can add any missing columns from input operators using (Add Column) and select the columns.
-
Click (Menu) to rename, remove, or duplicate selected column.
-
To remove multiple columns at once, press the Ctrl key, click on each columns you want to select, and click (Delete Columns).
-
To remove all columns, click Select All and choose (Delete Columns).
-
- In the Advanced Properties section, click on (Edit) to define the join optimization. By default, it is set to Automatic. Manual optimization should be done only when absolutely necessary.
-
Select Join Optimization as Manual.
-
For left and right joins, respectively;
-
Choose Cache as Yes or No.
-
Specify the Rank value within the range 0–100.
-
-
- To complete the definition of the join, create a flow from it to the next operator or the target table, as appropriate.