Lookup tables

Lookup tables contain data records that are searched.

There are five variants of lookup tables currently supported:

• SimpleLookup - complete lookup table stored in memory, data is read from external text file.
• DBLookup - lookup table base on parametrized SQL query. Results can be cached in memory to limit access to database.
• RangeLookup - elements of lookup are “intervals”: each interval has start and end point(s).
• PersistentLookup - complete lookup table stored in external file. It uses jdbm.
• AspellLookup - an Aspell-like lookup table that enables you to look up data records with similar lookup key.
  

Since 2.6 version all lookup tables can be internal - all attributes and theirs values are stored directly in graph, or external (shared) - attributes and theirs values are stored in separate file and in graph there is only link to the file eg.:

in graph:

 <LookupTable id="LookupTable0" lookupConfig="${LOOKUP_DIR}/MyRangeLookup.cfg"/>

in MyRangeLookup.cfg file:

endFields=dateEnd;timeEnd;nameEnd
endInclude=true;true;true
metadata=${META_DIR}/delimited/schedule.fmt
name=Tour schedule
startFields=dateStart;timeStart;nameStart
startInclude=true;true;true
type=rangeLookup
useI18N=true

All data records stored in the simple lookup table are kept in memory. Therefore, sufficient memory has to be available in order to accommodate all data records to be stored. If the data records are loaded from a data file, the size of available memory should be approximately 6× bigger than the size of the data file. However, this multiplier may vary for different types of data records stored in the data file.

Xml attributes:

Xml definition:
Transformation for ver. 2.5:

  <Global>
    ...
    <LookupTable id="LookupTable0" type="simpleLookup" metadata="Metadata0" 
                 fileURL="data/delimited/employees.dat" dataType="delimited" charset="ISO-8859-1" 
                 key="EmployeeID" name="name" 
    />
  </Global>
 
  <Node id="JOIN" type="REFORMAT">
    <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.*;
        import org.jetel.graph.TransformationGraph;
 
        public class reformatTest extends DataRecordTransform {
	    LookupTable mylookup;
 
	    public boolean init(){
		try {
			mylookup = graph.getLookupTable("LookupTable0");
			if (!mylookup.isInitialized()) {
				mylookup.init();
			}
		} catch (ComponentNotReadyException e) {
			e.printStackTrace();
                        return false;
		}
 
	        // create key 
	        String[] lookupKeyStr={"EmployeeID"};
		RecordKey key=new RecordKey(lookupKeyStr,sourceMetadata[0]);
		key.init();
		mylookup.setLookupKey(key);
		return true;
	    }
 
	    public boolean transform(DataRecord[] source, DataRecord[] target){
		DataRecord employee; 
		employee=mylookup.get(source[0]);
 
		if (employee==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().toString());
		target[0].getField(3).setValue(employee.getField(0).getValue().toString());
		target[0].getField(4).setValue(employee.getField(1).getValue());
 
 
		return true;
	    }
 
	    public void finished(){
		mylookup.free();
	    }
         }
    </attr>
  </Node>

  <Global>
    ...
    <LookupTable id="LookupTable0" type="simpleLookup" metadata="Metadata0" 
                 fileURL="data/delimited/employees.dat" dataType="delimited" charset="ISO-8859-1" 
                 key="EmployeeID" name="name" 
    />
  </Global>
 
  <Node id="JOIN" type="REFORMAT">
    <attr name="transform">
 
import org.jetel.component.DataRecordTransform;
import org.jetel.data.DataRecord;
import org.jetel.data.RecordKey;
import org.jetel.data.lookup.Lookup;
import org.jetel.data.lookup.LookupTable;
import org.jetel.exception.ComponentNotReadyException;
 
public class reformatTest extends DataRecordTransform{
 
	Lookup mylookup;
 
	public boolean init() throws ComponentNotReadyException{
 
		// create key 
	    String[] lookupKeyStr={"EmployeeID"};
		RecordKey key=new RecordKey(lookupKeyStr,sourceMetadata[0]);
		key.init();
 
		LookupTable lt = graph.getLookupTable("LookupTable0");
		lt.init();
		mylookup = lt.createLookup(key);
 
		return true;
	}
 
	public int transform(DataRecord[] source, DataRecord[] target){
 
		mylookup.seek(source[0]);
 
		if (!mylookup.hasNext()) {// skip this one
			errorMessage = "Order: " + source[0].getField(0).getValue() + " - no corresponding record in lookup table";
			return SKIP; 
		}
 
		DataRecord employee = (DataRecord)mylookup.next(); 
 
		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().toString());
		target[0].getField(3).setValue(employee.getField(0).getValue().toString());
		target[0].getField(4).setValue(employee.getField(1).getValue());
 
		return ALL;
	}
}
    </attr>
  </Node>

Lookup definition dialog:

Xml definition:

  <Global>
    ...
    <LookupTable id="LookupTable0" type="dbLookup" metadata="Metadata0" 
                  dbConnection="DBConnection0" name="name" >
                  <attr name="sqlQuery">select * from employee where employee_id=?</attr>
    </LookupTable>
  </Global>
 
  <Node id="JOIN" type="REFORMAT">
    <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.*;
        import org.jetel.graph.TransformationGraph;
 
        public class reformatTest extends DataRecordTransform {
	    LookupTable mylookup;
 
	    public boolean init(){
		try {
			mylookup = graph.getLookupTable("LookupTable0");
			if (!mylookup.isInitialized()) {
				mylookup.init();
			}
		} catch (ComponentNotReadyException e) {
			e.printStackTrace();
                        return false;
		}
 
	        // create key 
	        String[] lookupKeyStr={"EmployeeID"};
		RecordKey key=new RecordKey(lookupKeyStr,sourceMetadata[0]);
		key.init();
		mylookup.setLookupKey(key);
		return true;
	    }
 
	    public boolean transform(DataRecord[] source, DataRecord[] target){
		DataRecord employee; 
		employee=mylookup.get(source[0]);
 
		if (employee==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().toString());
		target[0].getField(3).setValue(employee.getField(0).getValue().toString());
		target[0].getField(4).setValue(employee.getField(1).getValue());
 
		return true;
	    }
 
	    public void finished(){
		mylookup.free();
	    }
         }
    </attr>
  </Node>

Range lookup table contains records, which defines intervals. It means that they must have special structure: first field is the name of the interval, odd fields marks starts of intervals, even fields (from 2) means corresponding ends of intervals, eg: Lookup table defined as follows:

• low_slow,0,10,0,50
• low_fast,0,10,50,100
• high_slow,10,20,0,50
• high_fast,10,20,50,100

has 4 intervals with 2 searching parameters: first from interval 0-10, and second from interval 0-100. Intervals can overlap.
It is possible to use “unlimited” intervals by setting null value to start or end field, eg. interval null,0 “contains” elements smaller then 0.

Xml attributes:

Xml definition:

  <Global>
    ...
    <LookupTable type="rangeLookup" id="LookupTable0" metadata="Metadata0" charset="ISO-8859-1" dataType="delimited">
          <attr name="endInclude">true;true;true</attr>
          <attr name="startInclude">true;true;true</attr>
          <attr name="useI18N">true</attr>
          <attr name="name">Tour schedule</attr>
    </LookupTable>
  </Global>
 
  <Node id="LOOKUP_JOIN0" joinKey="date;time;name" lookupTable="LookupTable0" type="LOOKUP_JOIN">
     <attr name="transform">${out.0.name} = ${in.0.name};
                            ${out.0.date} = ${in.0.date};
                            ${out.0.time} = ${in.0.time};
                            ${out.0.activity} = ${in.1.name};
     </attr>
  </Node>

ETL Version since: 2.6
Complete lookup table stored in external file. It uses jdbm.

Xml attributes:

Xml definition:

<Global>
  ...
   <LookupTable fileURL="${PROJECT}/data-out/persistentLookup" id="LookupTable1" key="EmployeeID" metadata="Metadata0" name="lookupName" type="persistentLookup"/>
</Global>
 
<Node enabled="enabled" id="JOIN" type="REFORMAT">
<attr name="transform"><![CDATA[
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.*;
import org.jetel.graph.TransformationGraph;
 
public class reformatTest extends DataRecordTransform{
 
	LookupTable mylookup;
 
	public boolean init(){
 
		try {
			mylookup = graph.getLookupTable("LookupTable1");
			if (!mylookup.isInitialized()) {
				mylookup.init();
			}
		} catch (ComponentNotReadyException e) {
			e.printStackTrace();
                        return false;
		}
 
		// create key 
  	        String[] lookupKeyStr={"EmployeeID"};
		RecordKey key=new RecordKey(lookupKeyStr,sourceMetadata[0]);
		key.init();
		mylookup.setLookupKey(key);
		return true;
	}
 
	public int transform(DataRecord[] source, DataRecord[] target){
 
		DataRecord employee; 
		employee=mylookup.get(source[0]);
 
		if (employee==null) {// skip this one
			errorMessage = "Input:\n" + source[0] + " - There is no record with corresponding key in lookup table\n";
			return SKIP; 
		}
 
		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().toString());
		target[0].getField(3).setValue(employee.getField(0).getValue().toString());
		target[0].getField(4).setValue(employee.getField(1).getValue());
 
		return ALL;
	}
 
	public void finished(){
		mylookup.free();
	}
}
]]></attr>
</Node>

COMMERCIAL, since version 2.6

An Aspell-like lookup table that enables you to look up data records with similar lookup key. The similarity of two words (keys) is measured in terms of edit operations, their cost and sum of these costs (see below). Such approach may be useful e. g. when looking for a street the name of which is misspelled to a certain extent.

All data records stored in the Aspell lookup table are kept in memory. Therefore, sufficient memory has to be available in order to accommodate all data records to be stored. If the data records are loaded from a data file, the size of available memory should be approximately 7× bigger than the size of the data file. However, this multiplier may vary for different types of data records stored in the data file.

Similarity of Words:

The edit operations and their default costs are as follows:

The edit distance is defined as the minimum sum of costs of edit operations required to transform a misspelled word into a correctly spelled one. The edit distance between two words is actually the similarity of these two words.

Let's assume that the Aspell lookup table contains only the word “Hello”. Then the edit distance of the following queries would be as follows:

XML Attributes:

The editDistanceField attribute may be set if the edit distance should be stored within the looked up data records. In such a case, performance of the lookup table may be reduced because data records have to be duplicated in order to store the edit distance. The editDistanceField has to be present within metadata referenced by the metadata attribute. It is advised to enable autofilling (with default_value) on this field in order to preserve data layout of data records stored in the lookup table data file.

The spellingThreshold attribute defines the maximum allowed edit distance from a correctly spelled word. The higher the threshold, the more mistakes can be present in incorrectly spelled words and vice versa. Words with the edit distance above the spellingThreshold are NOT contained in the query result, unless the includeBestGuesses attribute is set to true.

The editCosts attribute may be used to override the default costs of individual edit operations. User-defined edit costs are specified in the form <edit_operation_1> = <edit_cost_1>; …; <edit_operation_n> = <edit_cost_n> where <edit_operation_x> is the name of an edit operation and <edit_cost_x> is its non-negative cost.

The removeDiacriticalMarks attribute specifies whether or not the diacritical marks should be removed from the lookup key before any put/get operation. Setting this property to true should solve any potential problems you might encounter when using words with diacritical marks. However, this may lead to a problem because similar (but different) words might be indexed using a same key. This should not be an issue when similarity searching is performed.

The includeBestGuesses attribute specifies whether or not the “best guesses” (the closest matches when no suggestions were found) should be included in the result. Setting this flag to true results in returning more suggestions if the spelling threshold is too strict.

XML Definition:

<Global>
    ...
    <LookupTable dataFileCharset="UTF-8" dataFileUrl="${PROJECT}/lookup/streets.dat" id="LookupTable0"
            lookupKeyField="name" metadata="Metadata0" name="streets" type="aspellLookup"/>
</Global>
 
<Node charset="UTF-8" id="LOOKUP_JOIN0" joinKey="street" lookupTable="LookupTable0" type="LOOKUP_JOIN">
    <attr name="transform"><![CDATA[//#TL
 
        // Transforms input record into output record.
        function transform() {
            $0.street := $0.street;
            $0.name := $1.name;
            $0.city := $1.city;
        }
 
    ]]></attr>
</Node>