Official |[Basic] Join

Operation name

Join

Function overview

Joins the two input data.

Data model

The data model of this component is table model type.

= Remarks =

For more details on input/output schema, refer to Table model type .

Properties

Basic settings

Item name	Required/Optional	Use of variables	Description
Name	Required	Not available	Enter a name that is used on the script canvas.
Input data (LEFT)	Required	Not available	Select a component on the script canvas.
Input data (RIGHT)	Required	Not available	Select a component on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
Operation type	Required	Not available	Select a type of operation. INNER JOIN (Default) LEFT OUTER JOIN RIGHT OUTER JOIN	For details, refer to Operation type.
Join key configuration	Required	-	Specify the columns to be used as keys for the Join operation. Each row can be operated with the following buttons: Add Adds a key. Delete Deletes the key.	When multiple columns are specified as keys, the values of the key columns will be compared and the data for which all the compared values are matched will be joined.
Join key configuration/Input data (LEFT)	Required	Not available	Select the column name of Input Data(LEFT) to be used as a key.	The index that shows the position of the column in the order is displayed at the end of the column name. When the value of Input data (LEFT) is selected or changed, the column name will be updated.
Join key configuration/Input data (RIGHT)	Required	Not available	Select the column name of Input data (RIGHT) to be used as a key.	The index that shows the position of the column in the order is displayed at the end of the column name. When the value of Input data (RIGHT) is selected or changed, the column name will be updated.

Output settings

Item name	Required/Optional	Use of variables	Description	Remarks
Configure output manually	Optional	Not available	Select whether or not to manually set columns to be output. Selected Output targets are set manually. Not selected (Default) Output targets are automatically set according to the content of the specified property items.	When it's not selected, the output targets are in the following order: The order set in Join key configuration The order of the input schema of Input data (LEFT) (the columns specified in Join key configuration are excluded) The order of the input schema of Input data (RIGHT) (the columns specified in Join key configuration are excluded)
Output target configuration	Optional	-	Specify columns to be output. Each row can be operated with the following buttons: Up Moves the output target upward by one row in the output order. Down Moves the output target downward by one row in the output order. Add Adds an output target. Delete Deletes the output target.	Enabled when Configure output manually is selected.
Output target configuration/Data source	Required	Not available	Select a data source of the output target column. Join key (LEFT/RIGHT) Input data (LEFT) Input data (RIGHT)
Output target configuration/Column name	Required	Not available	Select the name of a column to be output. The column names corresponding to the value specified in Output target/Data source are displayed as choices. <Column name of input data (LEFT)>/<Column name of input data (RIGHT)> Displayed when Join key (LEFT/RIGHT) is selected. <Column name of input data (LEFT)> Displayed when Input data (LEFT) is selected. <Column name of input data (RIGHT)> Displayed when Input data (RIGHT) is selected.	The index that shows the position of the column in the order is displayed at the end of the column name. When the value of Output target configuration/Data source is selected or changed, column name will be updated. When you want to select a column specified in Join key configuration, select Join key (LEFT/RIGHT) in Output target/Data source.

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this connector.

Schemas

Input schema

Same as the schema of input source component.

Output schema

The number of columns varies depending on the settings for Output Settings.

= Remarks =

For schema structure, refer to Table model type .

Loading schema in Mapper

The output schema is loaded automatically while the input schema needs to be loaded manually.

Specify the schema of the data to be loaded.

= Remarks =

For details, refer to Edit Schema.

Mass data processing

Mass data processing isn't supported.

Parallel Stream Processing

PSP isn't supported.

Available component variables

Component variable name	Description	Remarks
message_category	When an error occurs, the category of the message code corresponding to the error is stored.	The default value is null.
message_code	When an error occurs, the code of the message code corresponding to the error is stored.	The default value is null.
message_level	When an error occurs, the severity of the message code corresponding to the error is stored.	The default value is null.
error_type	When an error occurs, the error type is stored.	The default value is null. The format of the error type is as follows. Example: java.io.FileNotFoundException
error_message	When an error occurs, the error message is stored.	The default value is null.
error_trace	When an error occurs, the trace information for the error is stored.	The default value is null.

Modifying schema of input source component

When the schema of the component specified in Input data (LEFT) or Input data (RIGHT) is modified, to reflect those changes to the Join operation, open the property setting dialog of the Join operation and click Finish button.
When the schema structure of the component specified in Input data (LEFT) or Input data (RIGHT) is modified (such as changes to the order of the schema elements, or deletion of schema elements), settings for Join key configuration/Input data (LEFT), Join key configuration/Input data (RIGHT), and Output target configuration/Column name must be modified accordingly.

The reason is that the Join operation keeps the information of columns by their positions in the order (indices) rather than by their names.

Null and empty strings

When there's a null value for the value of the column which is specified in Join key configuration, the null will be processed as a join key value.
When there's an empty string for the value of the column which is specified in Join key configuration, the behavior of the operation depends on the data type of the other specified column.
- An empty string will be treated as a join key value when the columns are both of String type.
- An error occurs when one of the column is of String type and the other is not.

Operation type

Select one of the following methods of join as the operation type.

Item description

Item name	Description
INNER JOIN (default)	For both tables of Input data (LEFT) and Input data (RIGHT), records are retrieved only if the key values are matched.
LEFT OUTER JOIN	For the table of Input data (LEFT), all records are retrieved. For the table of Input data (RIGHT), records are retrieved only if the key values are matched.
RIGHT OUTER JOIN	For the table of Input data (RIGHT), all records are retrieved. For the table of Input data (LEFT), records are retrieved only if the key values are matched.

Example: Join the below two tables "Products" and "Sales" using "Product number" as a key.

Products

Sales

Product no.	Product name
1	Product A
2	Product B
4	Product D

Product no.	Order amount
1	10
2	30
3	50

In case of INNER JOIN (default)

Product no.	Product name	Order amount
1	Product A	10
2	Product B	30

In case of LEFT OUTER JOIN

Product no.	Product name	Order amount
1	Product A	10
2	Product B	30
4	Product D

In case of RIGHT OUTER JOIN

Product no.	Product name	Order amount
1	Product A	10
2	Product B	30
3		50

Joining columns with different data types

If the data types of the columns specified as the join key are different, the following data type conversion is performed.

Conversion of data types

Data type of the column of Input data (LEFT)	Data type of the column of Input data (RIGHT)
Data type of the column of Input data (LEFT)	byte	short	int	long	float	double	BigDecimal	boolean	String	Date	byte[]
byte	―	short	int	long	BigDecimal	BigDecimal	BigDecimal	×	byte	×	×
short	short	―	int	long	BigDecimal	BigDecimal	BigDecimal	×	short	×	×
int	int	int	―	long	BigDecimal	BigDecimal	BigDecimal	×	int	×	×
long	long	long	long	―	BigDecimal	BigDecimal	BigDecimal	×	long	×	×
float	BigDecimal	BigDecimal	BigDecimal	BigDecimal	―	BigDecimal	BigDecimal	×	BigDecimal	×	×
double	BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	―	BigDecimal	×	BigDecimal	×	×
BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	―	×	BigDecimal	×	×
boolean	×	×	×	×	×	×	×	―	boolean	×	×
String	byte	short	int	long	BigDecimal	BigDecimal	BigDecimal	boolean	―	×	×
Date	×	×	×	×	×	×	×	×	×	×	×
byte[]	×	×	×	×	×	×	×	×	×	×	×

How to read the table

Symbol or text	Description
<Internal data type>	Data type conversion to <Internal data type> is performed.
―	No conversion is performed because they have the same data type.
×	Joining columns of specified data types isn't supported.

= Remarks =

The internal data type char is treated as String.
When converting String to boolean, "true" will be converted to true and strings other than "true" will be converted to false.

Specification limits

Multi-thread processing is supported.

= Remarks =
For the specification limits on multi-thread processing, refer to Specification limitations.
When specifying Join key configuration and Output target configuration, the output schema of input source component must be configured.

For components that require manual schema settings, load schemas with Mapper.
The order of the result data of the Join operation isn't ensured.
Even when mass data processing is performed by the input source component, data is temporarily held in the memory during the execution of the Join operation.

Therefore, when the data amount is huge, OutOfMemoryError may occur even with mass data processing enabled.

Exception messages

Exception name	Cause	Solution
InputDataNotFoundException	Input data isn't specified.	Specify Input data or draw a data flow.
InvalidInputTypeException	Input data isn't table model type.	Check whether the component specified in Input data is table model type.
InvalidPropertyConfigurationException Value is not specified in [<property name>].	A value isn't specified in <property name>.	Specify <property name>.
InvalidPropertyConfigurationException Value is not specified in [<property name2>] of the row [<row number>] in [<property name1>].	A value isn't specified in <property name2>.	Specify <property name2>.
InvalidPropertyConfigurationException The value specified in the row [<row number>] of <property name> is duplicated.	The value specified in <property name> is duplicated.	Check whether the value specified in <property name> is appropriate.
InvalidPropertyConfigurationException The value specified in <property name2> of the row [<row number>] of [<property name1>] is duplicated.	The value specified in <property name2> is duplicated.	Check whether the value specified in <property name2> is appropriate.
ConversionFailedException Column value of <input data> cannot be processed as specified join key.	The contents of <input data> contain some values that can't be processed as per the specification in Join key configuration.	Check whether the contents of <input data> are compatible with the specification in Join key configuration, or whether any empty string is contained.