Joiners

Joiners are components of graph that join data from all connected input ports based on defined key and propagate the result to output port.

In either of these components, you must define some transformation using the transform() function. In addition to this required function, you can define other two functions: init() and finished(). If you want to declare and initialize some variables, if you want to anything what should be done at the beginning of data processing by the component, you should do it within the init() function. If you want to free memory, delete some temporary files, you should define it within the finished() function. Either of these functions is called only once. The init() function is called at the beginning, the finished() function is called at the end. Unlike them, the required transform() function is called many times. It is called after init() and before finished(). All joiners implement a RecordTransform interface or inherit from a DataRecordTransform class.

Common attributes

Joins records on port 0 (Master records) with records on port 1 (Slave records). Joining is based on specified key fields and their similarity - with defined maximum disimilarity/distance - computed as edit distance - “Levenshtein distance”

Input ports: * 0 - driver records

• 1 - slave records

Output ports:

• 0 - joined records with conformity greater then given limit. If in output metadada there are fields called “_total_conformity_” or “_keyName_conformity_”, where keyName is one of field's names from joinKey parameter, to these fields will be assigned computed conformity. These fields have to be of type “numeric”
• 1 - joined records with conformity smaller then given limit but with the same matching key. Output metadata can have additional fields (see above)
• 2 - (optional) driver records for which there is not matching slave
• 3 - (optional) slave records for which there is not matching driver

Xml attributes:

joinKey - comparison strength:

• identical - letters equals if they are identical
• tertiary - comparison does not depend on upper or lower case
• secondary - diacritic letters and theirs latin equivalents are equals
• primary - letters with additional features (e.g.:penduncle, pick, circle) and theirs latin equivalents are equals

Example:

  <Node id="APROX_MERGE_JOIN0" type="APROX_MERGE_JOIN">
    <attr name="matchingKey">key</attr>
    <attr name="transform">${out.0.lname} = ${in.0.lname};
          ${out.0.fname} = ${in.0.fname};
          ${out.0.birthdate} = ${in.0.birthdate};
          ${out.0.gender} = ${in.0.gender};
          ${out.0.first_name} = ${in.1.first_name};
          ${out.0.last_name} = ${in.1.last_name};
          ${out.0.birth_date} = ${in.1.birth_date};
          ${out.0.gender1} = ${in.1.gender};
    </attr>
    <attr name="conformity">0.8</attr>
    <attr name="slaveOverrideKey">last_name;first_name</attr>
    <attr name="joinKey">lname 2 0.75 false true true true;fname 4 0.25 false false false true</attr>
    <attr name="transformForSuspicious">${out.1.lname} = ${in.0.lname};
          ${out.1.fname} = ${in.0.fname};
          ${out.1.first_name} = ${in.1.first_name};
          ${out.1.last_name} = ${in.1.last_name};
    </attr>
  </Node>
 
  <Node conformity="0.81" id="APROX_MERGE_JOIN1" 
        joinKey="$fname=$first_name(3 0.8 true false false false);$lname=$last_name(4 0.2 true false false false);" 
        matchingKey="$key=$key;#" transformClass="customizedTransformExample" type="APROX_MERGE_JOIN">
       <attr name="transformForSuspicious"><![CDATA[${out.0.lname} = ${in.0.lname};
           ${out.0.fname} = ${in.0.fname};
           ${out.0.birthdate} = ${in.0.birthdate};
           ${out.0.gender} = ${in.0.gender};
           ${out.0.first_name} = ${in.1.first_name};
           ${out.0.last_name} = ${in.1.last_name};
           ${out.0.birth_date} = ${in.1.birth_date};
           ${out.0.gender1} = ${in.1.gender};]]></attr>
  </Node>

Joins records from input port and database based on specified key. It expects that on port [0], there is a driver and from database is a slave. For each driver record, slave record is looked up in database. Pair of driver and slave records is sent to transformation class. The method transform is called for every pair of driver&slave. It skips driver records for which there is no corresponding slave (if there is connected output port 1, thees records are sent to it) - unless outer join (leftOuterJoin option) is specified, when only driver record is passed to transform method (no records is sent to output port 1).

Input ports: * one input port defined/connected.

Output ports:

• [0] - joined records
• [1] - (optional) skipped driver records

Xml attributes:

Example:

  <Node id="dbjoin0" type="DBJOIN">
        <attr name="metadata">Metadata3</attr>
        <attr name="transformClass">TransformTransformdbjoin0</attr>
        <attr name="sqlQuery">select * from employee where Employee_ID=?</attr>
        <attr name="joinKey">EmployeeID</attr>
        <attr name="dbConnection">DBConnection0</attr>
  </Node>

Joins two records from two different input flows based on specified key. The flow on port 0 is the driver, the flow on port 1 is the slave. First, all records from slave flow are read and stored in hash table. Then for every record from driver flow, corresponding record from slave flow is looked up (if it exists)

Description:

Joins records on input ports. It expects driver stream at port [0] and slave streams at other input ports. Slave streams are expected to be small enough to store all the slave records in hashtable.
Each driver record is joined with corresponding slave records according to join keys specification.

• For each driver record, slave records are looked up in Hashtables which are created from all records on slave inputs.
• Tuple of driver and slave records is sent to transformation class.
• The method transform is called for every tuple of driver and corresponding slaves.
  

There are three join modes available: inner, left outer, full outer.

• Inner mode processess only driver records for which all associated slaves are available.
• Left outer mode furthermore processes driver records with missing slaves.
• Full outer mode additionally calls transformation method for slaves without driver.
  

In case you use outer mode, till 2.4 version be sure your transformation code is able to handle null input records. From 2.5 version it doesn't happen any more, because for lacking record there is used special null record - all getValue methods returns null instead of throwing exception. If you want to realise some action for such record you can compare it with NullRecord.NULL_RECORD (see example below). Hash join does not require input data to be sorted. But it spends some time at the beginning initializing hashtable of slave records. It is generally good idea to specify how many records are expected to be stored in each hashtable (there is one hashtable per each slave input), especially when you expect the number to be really great. It is better to specify slightly greater number to ensure that rehashing won't occure. For small record sets - up to 512 records, there is no need to specify the size.

Input ports: * 0 - driver record input.

• 1..n - slave record inputs.

Output ports:

• one output port defined/connected.

Xml attributes:

Example:

  <Node id="JOIN" type="EXT_HASH_JOIN" joinKey="CustomerID" transformClass="org.jetel.test.reformatOrders"/>
 
  <Node id="JOIN" type="EXT_HASH_JOIN" joinKey="EmployeeID" joinType="inner">
        <attr name="transform">//#TL
              function transform(){
                 $OrderID := $OrderID;
                 $CustomerID := $CustomerID;
                 $EmployeeID := $EmployeeID;
                 $EmployeeID2 := num2str($1.EmployeeID);
                 $LastName := concat($1.LastName,":",$1.FirstName);
              }
        </attr>
  </Node>
 
 In-line transformation for 2.4 and lower ver.:
 <Node id="JOIN" type="EXT_HASH_JOIN" joinKey="EmployeeID=EmployeeID#CustomerID=CUSTOMERID" joinType="leftOuter">
        <attr name="transform">
 
            import org.jetel.component.DataRecordTransform;
            import org.jetel.data.*;
 
            public class reformatJoinTest extends DataRecordTransform{
 
                public boolean transform(DataRecord[] source, DataRecord[] target){
                    if (source[0] != null) {
                        target[0].getField(0).setValue(source[0].getField(0).getValue());	// OrderID
                        target[0].getField(1).setValue(source[0].getField(1).getValue());	// CustomerID
                        target[0].getField(2).setValue(source[0].getField(2).getValue());	// EmployeeID
                    } else {
                        target[0].getField(1).setValue("*MISSING*");
                    }
                    if (source[1]!=null){
                        target[0].getField(4).setValue(source[1].getField(1) + ", " + source[1].getField(2));	// Name
                    } else {
                        target[0].getField(4).setValue("*MISSING*");
                    }
                    if (source[2]!=null){
                        target[0].getField(3).setValue(source[2].getField(1).getValue());	// Company
                    } else {
                        target[0].getField(3).setValue("*MISSING*");
                    }
                    target[0].getField(5).setValue("*MISSING*");
                    return true;
                }
            }
        </attr>
  </Node>
 
  In-line transformation since 2.5 ver.:
  <Node id="JOIN" type="EXT_HASH_JOIN" joinKey="$EmployeeID=$EmployeeID#$CustomerID=$CUSTOMERID" joinType="leftOuter">
        <attr name="transform">
            import org.jetel.component.DataRecordTransform;
            import org.jetel.data.*;
 
            public class reformatJoinTest extends DataRecordTransform{
 
                public boolean transform(DataRecord[] source, DataRecord[] target){
                    target[0].getField(0).setValue(source[0].getField(0).getValue());	// OrderID
                    target[0].getField(1).setValue(source[0].getField(1).getValue());	// CustomerID
                    target[0].getField(2).setValue(source[0].getField(2).getValue());	// EmployeeID
                    target[0].getField(3).setValue(source[2].getField(1).getValue());	// Company
                    target[0].getField(4).setValue(source[1].getField(1) + ", " + source[1].getField(2));	// Name
                     if (source[0] == NullRecord.NULL_RECORD){
                        target[0].getField(4).setValue("driver missing");
                     }
                     if (source[1] instanceof NullRecord.NULL_RECORD){
                        target[0].getField(4).setValue("1st slave missing");
                     }
                      if (source[2].equals(NullRecord.NULL_RECORD)){
                        target[0].getField(4).setValue("2nd slave missing");
                     }
                  return true;
                }
            }
        </attr>
  </Node>

Joins records on input port and from lookup table. It expects that on port [0], there is a driver and from lookup table is a slave. For each driver record, slave record is looked up in lookup table. Pair of driver and slave records is sent to transformation class. The method transform is called for every pair of driver&slave. It skips driver records for which there is no corresponding slave (if there is connected output port 1, theese records are sent to it) - unless outer join (leftOuterJoin option) is specified, when only driver record is passed to transform method (no records is sent to output port 1).
Note: In transformation, records from lookup table are treated as records from 1st source, while records from input port are treated as 0th source (see example).

Input ports: * one input port defined/connected.

Output ports:

• [0] - joined records
• [1] - (optional) skipped driver records

Xml attributes:

Example:

  <Node id="JOIN" type="LOOKUP_JOIN">
       <attr name="lookupTable">LookupTable0</attr>
       <attr name="joinKey">EmployeeID</attr>
       <attr name="transform">//#TL
             function transform(){
                 $OrderID := $OrderID;
                 $CustomerID := $CustomerID;
                 $EmployeeID := $EmployeeID;
                 $EmployeeID2 := num2str($1.EmployeeID);
                 $LastName := concat($1.LastName,":",$1.FirstName);
              }
       </attr>
  </Node>
 
  In-line transformation for 2.4 ver. and lower:
  <Node id="JOIN" type="LOOKUP_JOIN">
       <attr name="lookupTable">LookupTable0</attr>
       <attr name="joinKey">EmployeeID</attr>
       <attr name="transform">
             import org.jetel.component.DataRecordTransform;
             import org.jetel.data.DataRecord;
             import org.jetel.data.RecordKey;
             import org.jetel.data.lookup.LookupTable;
             import org.jetel.exception.JetelException;
             import org.jetel.graph.TransformationGraph;
 
             public class reformatTest extends DataRecordTransform{
 
                 public boolean transform(DataRecord[] source, DataRecord[] target){
 
                     if (source[1]==null) return false; // skip this one
 
                     target[0].getField(0).setValue(source[0].getField(0).getValue());
                     target[0].getField(1).setValue(source[0].getField(1).getValue());
                     target[0].getField(2).setValue(source[0].getField(2).getValue());
                     target[0].getField(3).setValue(source[1].getField(0).getValue());
                     target[0].getField(4).setValue(source[1].getField(1).getValue());
 
                     return true;
                 }
             }
       </attr>
  </Node>
 
  In-line transformation since 2.5 ver.:
  <Node id="JOIN" type="LOOKUP_JOIN">
       <attr name="lookupTable">LookupTable0</attr>
       <attr name="joinKey">EmployeeID</attr>
       <attr name="transform">
             import org.jetel.component.DataRecordTransform;
             import org.jetel.data.DataRecord;
             import org.jetel.data.RecordKey;
             import org.jetel.data.lookup.LookupTable;
             import org.jetel.exception.JetelException;
             import org.jetel.graph.TransformationGraph;
 
             public class reformatTest extends DataRecordTransform{
 
                 public boolean transform(DataRecord[] source, DataRecord[] target){
 
                     if (source[1]==NullRecord.NULL_RECORD) {
                        errorMessage = "Slave record not found";
                     }
 
                     target[0].getField(0).setValue(source[0].getField(0).getValue());
                     target[0].getField(1).setValue(source[0].getField(1).getValue());
                     target[0].getField(2).setValue(source[0].getField(2).getValue());
                     target[0].getField(3).setValue(source[1].getField(0).getValue());
                     target[0].getField(4).setValue(source[1].getField(1).getValue());
 
                     return true;
                 }
             }
       </attr>
  </Node>

Joins sorted records on input ports. It expects driver stream at port [0] and slave streams at other input ports. Each driver record is joined with corresponding slave records according to join keys specification. The method transform is every composed of driver and corresponding slaves. There are three join modes available: inner, left outer, full outer.

Inner mode processess only driver records for which all associated slaves are available.
Left outer mode furthermore processes driver records with missing slaves.
Full outer mode additionally calls transformation method for slaves without driver.

In case you use outer mode, till 2.4 version be sure your transformation code is able to handle null input records. From 2.5 version it doesn't happen any more, because for lacking record there is used special null record - all getValue methods returns null instead of throwing exception. If you want to realise some action for such record you can compare it with NullRecord.NULL_RECORD (see example below).

Input ports: * 0 - sorted driver record input

• 1..n - sorted slave record inputs

Output ports:

• one output port defined/connected.

Xml attributes:

Example:

  <Node id="JOIN" type="EXT_MERGE_JOIN" joinKey="CustomerID" transformClass="org.jetel.test.reformatOrders"/>
 
  <Node id="JOIN"
        type="EXT_MERGE_JOIN" 
        joinKey="EmployeeID=SlaveID;EmployeeType=SlaveType#EmployeeID=SlaveID;EmployeeType=SlaveType2" 
        joinType="inner">
        <attr name="transform">
              function transform() {
                 ${out.0.car} = ${in.0.car};
                 ${out.0.seqKey} = ${in.0.seqKey};
                 ${out.0.mobilephone} = ${in.0.mobilephone};
                 ${out.0.financial_bonus} = ((${in.1.monthly_bonus})*12)+(${in.1.yearly_bonus})
              }
        </attr>
  </Node>
 
  In-line transformation for 2.4 and lower ver.:
  <Node id="JOIN" type="EXT_MERGE_JOIN" joinKey="EmployeeID;EmployeeType" leftOuterJoin="true">
        <attr name="transform">
            import org.jetel.component.DataRecordTransform;
            import org.jetel.data.*;
 
            public class reformatJoinTest extends DataRecordTransform{
 
                int recNo = 0;
 
                public boolean transform(DataRecord[] source, DataRecord[] target){
 
                    target[0].getField(0).setValue(source[0].getField(0).getValue());
                    target[0].getField(1).setValue(source[0].getField(1).getValue());
                    target[0].getField(2).setValue(source[0].getField(2).getValue());
                    if (source[1]!=null){
                        target[0].getField(3).setValue(source[1].getField(0).getValue());
                        target[0].getField(4).setValue(source[1].getField(1).getValue());
                    }else{
			System.out.println("Slave record not found for " + recNo + "th record.");
                    }
                    recNo++;
                    return true;
                }
            }
        </attr>
  </Node>
 
  In-line transformation since 2.5 ver.:
  <Node id="JOIN" type="EXT_MERGE_JOIN" joinKey="$EmployeeID;$EmployeeType" leftOuterJoin="true">
        <attr name="transform">
            import org.jetel.component.DataRecordTransform;
            import org.jetel.data.*;
 
            public class reformatJoinTest extends DataRecordTransform{
 
                int recNo = 0;
 
                public boolean transform(DataRecord[] source, DataRecord[] target){
 
		    if (source[1] == NullRecord.NULL_RECORD) {
			System.out.println("Slave record not found for " + recNo + "th record.");
		    }
                    target[0].getField(0).setValue(source[0].getField(0).getValue());
                    target[0].getField(1).setValue(source[0].getField(1).getValue());
                    target[0].getField(2).setValue(source[0].getField(2).getValue());
                    target[0].getField(3).setValue(source[1].getField(0).getValue());
                    target[0].getField(4).setValue(source[1].getField(1).getValue());
                    recNo++;
                    return true;
                }
            }
        </attr>
  </Node>

COMMERCIAL, since version 2.8

Joins data records that are not equal with respect to a given join key and operator.

The RelationalJoin component has two input ports. The first one is referred to as the master port, the second one is referred to as the slave port. Each data record from the master port is joined with corresponding data records from the slave port. Data records to be joined are specified by a join key and a relational operator. Any of the following relational operators may be used: !=, <, <=, >, >=. The < operator for example means that each data record from the master port is joined with all data records from the slave port that are lexicographically greater.

Data records coming through the master port are processed one by one. On the other hand, data records coming through the slave port need to be buffered. In the worst case, all the slave data records need to be buffered. Thus the number of data records on the slave port should be as small as possible.

All data records coming through connected input ports have to be sorted in the following way:

In case the ordering is incorrect, execution of the component fails.

Input ports: * 0 – master record input

• 1 – slave record input

Output ports:

• one output port defined/connected

XML attributes: