Left lookup join

Supported in: Streaming

Joins two datasets together, keeping all rows from the left table and only matching rows from the right dataset.

Transform categories: Join

Declared arguments

Condition for columns to select on the left: All columns in the left input schema will be tested to see if they match this condition. If they match, the column will be selected in the output.
ColumnPredicate
Condition for columns to select on the right: All columns in the right input schema will be tested to see if they match this condition. If they match, the column will be selected in the output.
ColumnPredicate
Join condition: A list of columns from left and right input to join on.
List<Tuple<Column<Boolean | Byte | Date | Double | Float | Integer | Long | Short | String | Timestamp>, Column<Boolean | Byte | Date | Double | Float | Integer | Long | Short | String | Timestamp>>>
Left dataset: Left dataset to use in join.
Table
Max rows to join with a single row: Places a limit on the maximum number of rows from the right input that a single row from the left can be joined with. The highest this limit can go is ten. Setting too high of a limit can have negative performance implications.
Literal<Integer>
Right dataset: Right dataset to use in join.
Table
optional Prefix for columns from right: Prefix to add to all columns on the right hand side.
Literal<String>

Argument values:

Condition for columns to select on the left:
columnNameIsIn(
columnNames: [tail_number, airline],
)
Condition for columns to select on the right:
columnNameIsIn(
columnNames: [home_airport],
)
Join condition: [(tail_number, tail_number)]
Left dataset: ri.foundry.main.dataset.left
Max rows to join with a single row: 10
Right dataset: ri.foundry.main.dataset.right
Prefix for columns from right: null

Inputs: ri.foundry.main.dataset.left

tail_number	airline	miles	factor
XB-123	foundry air	124	2
MT-222	new airline	1123	5
XB-123	foundry airline	335	5
MT-222	new air	565	4
KK-452	new air	222	1
PA-452	new air	212	2
XB-123	foundry airline	1134	2

ri.foundry.main.dataset.right

Output:

Argument values:

Condition for columns to select on the left:
columnNameIsIn(
columnNames: [tail_number, airline, factor],
)
Condition for columns to select on the right:
columnNameIsIn(
columnNames: [home_airport],
)
Join condition: [(tail_number, tail_number), (factor, factor)]
Left dataset: ri.foundry.main.dataset.left
Max rows to join with a single row: 10
Right dataset: ri.foundry.main.dataset.right
Prefix for columns from right: null

Inputs: ri.foundry.main.dataset.left

tail_number	airline	miles	factor
XB-123	foundry air	124	2
MT-222	new airline	1123	5
XB-123	foundry airline	335	5
MT-222	new air	565	4
KK-452	new air	222	1
PA-452	new air	212	2
XB-123	foundry airline	1134	2

ri.foundry.main.dataset.right

Output:

tail_number	airline	factor	home_airport
XB-123	foundry air	2	LHR
MT-222	new airline	5	null
XB-123	foundry airline	5	null
MT-222	new air	4	null
KK-452	new air	1	null
PA-452	new air	2	null
XB-123	foundry airline	2	LHR

Argument values:

Condition for columns to select on the left:
allColumns(

)
Condition for columns to select on the right:
columnNameIsIn(
columnNames: [home_airport],
)
Join condition: [(tail_number, tail_number)]
Left dataset: ri.foundry.main.dataset.left
Max rows to join with a single row: 10
Right dataset: ri.foundry.main.dataset.right
Prefix for columns from right: null

Inputs: ri.foundry.main.dataset.left

tail_number	airline	miles	factor
XB-123	foundry air	124	2
MT-222	new airline	1123	5
XB-123	foundry airline	335	5
MT-222	new air	565	4
KK-452	new air	222	1
PA-452	new air	212	2
XB-123	foundry airline	1134	2

ri.foundry.main.dataset.right

Output:

tail_number	airline	miles	factor	home_airport
XB-123	foundry air	124	2	LHR
XB-123	foundry air	124	2	LGW
MT-222	new airline	1123	5	CPH
XB-123	foundry airline	335	5	LHR
XB-123	foundry airline	335	5	LGW
MT-222	new air	565	4	CPH
KK-452	new air	222	1	JFK
PA-452	new air	212	2	null
XB-123	foundry airline	1134	2	LHR
XB-123	foundry airline	1134	2	LGW