The Join key is a key according to which the incoming data flows are joined. It is specified as a sequence of mapping expressions for all slaves.

The mapping expressions are separated from each other by hash. Each of these mapping expressions is a sequence of field names from master and slave records (in this order) put together using an equal sign and separated from each other by a semicolon, colon, or pipe.

$CUSTOMERID=$CUSTOMERID#$ORDERID=$ORDERID;$PRODUCTID=$PRODUCTID

The order of these mappings must correspond to the order of the slave input ports. If some of these mappings are empty or missing for some of the slave input ports, the mapping of the first slave input port is used instead.

	Note
	Different slaves can be joined with the master using different master fields!

$master_field1=$slave_field1;$master_field2=$slave_field2;...;$master_fieldN=$slave_fieldN

$first_name=$fname;$last_name=$lname#=$lname;$salary=;$hire_date=$hdate

Join Key Dialog

To create the Join key attribute, use the Join key dialog. When you click the Join key attribute row, a button appears in this row. By clicking this button, you open the dialog.

Figure 58.2. Hash Join Key Dialog

In the dialog, you can see tabs for all of the slave input ports. In each slave tab, there are two panes: Slave fields and Key mappings.

The Slave fields pane is on the left. It contains a list of all the slave field names and their data types.

The Key mapping pane is on the right. It contains three columns: Master fields, Master field type and Slave key field mapped. The left column contains all field names of the driver input port. The middle column contains data types corresponding to the fields in the first column. The right column contains the mapped fields from the slave fields tab.

The component first receives the records incoming through the slave input ports, reads them and creates hash tables from these records. For every slave input port one hash table is created. After that, the component looks up the corresponding records in these hash tables for each driver record incoming through the driver input port.

If such record(s) are found, the tuple of the driver record and the slave record(s) from the hash tables are sent to a transformation class. The transform method is called for each tuple of the master and its corresponding slave records.

The hash tables must be sufficiently small to fit into the main memory.

The incoming records do not need to be sorted.

If the table is 75% full, the size is doubled and the table is recalculated.

The real size of the hash table is nearest power of 2 greater than or equal to the defined parameter value.

The initialization of hash tables is time consuming, therefore it may be a good idea to specify how many records will be stored in hash tables. If you decide to specify the Hash table size attribute, it is wise to consider these facts and set it to the value greater than needed. Nevertheless, for small sets of records, it is not necessary to change the default value.

All slave input data is stored in the memory. However, the master data is not. As for memory requirements, you therefore need to consider only the size of your slave data. In consequence, be sure to always set the larger data to the master and smaller inputs as slaves. ExtHashJoin uses in-memory hash tables for storing slave records.

	Important
	Remember that each slave port can be joined with master using different numbers of various master fields.

A transformation in ExtHashJoin is required.

A transformation in ExtHashJoin lets you define the transformation that sends records to the first output port. Unjoined master records sent to the second output cannot be modified within the ExtHashJoin transformation.

The master port contains metadata fields ProductID and Color. The color is in the RGB code. The slave port contains RGB values and the corresponding color names.

master (port 0)

Product_A|FF0000
Product_B|00FF00
Product_C|00FFFF

slave (port 1)

FF0000|red
00FF00|green
0000FF|blue

Match the product with corresponding color name.

//#CTL2
function integer transform() {
 	$out.0.ProuctID = $in.0.ProductID;
 	$out.0.ColorName = $in.1.ColorName;

 	return ALL;
}

Product_A|red
Product_B|green