The Join stage is a processing stage. It performs join
operations on two or more data sets input to the stage and then outputs the
resulting data set.
The
Join stage is one of three stages that join tables based on the values of key
columns.
The
three stages differ mainly in the memory they use, the treatment of rows with
unmatched keys, and their requirements for data being input.
In
the Join stage, the input data sets are notionally identified as the
“right” set and the “left” set.
It
has any number of input links and a single output link.
The
data sets input to the Join stage must be key partitioned and sorted in
ascending order.
This
ensures that rows with the same key column values are located in the same
partition and will be processed by the same node.
The
stage can perform one of four join operations:
Inner It joins two or more
tables and returns only those records which satisfy join condition. Records
whose key columns do not contain equal values are dropped.
Left
outer transfers
all values from the left data set but transfers values from the right data set
and intermediate data sets only where key columns match. The stage drops the
key column from the right and intermediate data sets. puts NULL wherever
unmatched record from 2nd (Right) table.
Right
outer transfers
all values from the right data set and transfers values from the left data set
and intermediate data sets only where key columns match. The stage drops the
key column from the left and intermediate data sets. puts NULL wherever
unmatched record from 2nd (Left) table.
Full
outer It
joins two or more tables and returns both matched and unmatched records from
all tables. (Full outer joins do not support more than two input links.)
Options:
Join
Keys/Key – Type: Input Column
Name
of input column you want to join on. Columns with the same name must appear in
both input data sets and have compatible data types.
Join
Type – Type of join operation to perform