Lookup Hashmap

The traditional way to process an entry is to intake from the iterator, then use lookup connectors or for each connectors to get additional data.

Feed
  Database_Iterator
Data Flow
  Other_Database_Lookup
  Destination_Database_Update

But that lookup can be slow, sometimes exceedingly slow, even when properly indexed. An alternative is to make an in-memory hashmap of that lookup if your data model supports it.

Create this hashmap by changing your lookup connector to a passive iterator and execute your map in the initialization phase.

Feed
  Other_Database (Passive)
    After Initialize (create hashmap here)
  Database_Iterator
Data Flow   
  Assign_Values
  Destination_Database_Update

Open up the 'After Initialize' section and make a hashmap. 

var sqlStatement = "select SOMETHING from SOME.TABLE where CHECKIN < GETDATE();
var connResult = thisConnector.connector.execSQLSelect(sqlStatement);
if (connResult != "") {
task.logmsg("ERROR","Unable to connect to DB");
system.abortAssemblyLine("Unable to continue");
else { 
// Let's store this in a hashmap for quick retrieveal. Values will be an array so we can handle
// multiple values. 
// We'll start with educated guess of 9000 so we don't invoke a resize right off the bat
var campusMap = new java.util.HashMap(9000)
var key = "";
var value = "";   
var values = [];
var entry = thisConnector.connector.getNextSQLSelectEntry();
while (entry != null) {
key = entry.getAttribute("STUDENTNUMBER").getValue();
value = entry.getAttribute("HALLNAME").getValue();
// Get any existing array there might be and push if there is one
values = campusMap.get(key)
if (values)
values.push(value);
else 
values = [value];
// Store the new array of values
campusMap.put(key,values);
entry = thisConnector.connector.getNextSQLSelectEntry()
}
task.logmsg("INFO","List size "  + campusMap.size());
}



Now, rather than make a SQL query you can simply put in a snippit like this in the - Note the .setValues method that takes an array as input

var key = work.MD_SOURCE_EMPLID_PS.getValue();
var campus = campusMap.get(key);

if (campus != null ) 
work.MD_CAMPUS_ENR.setValues(campus);
Don't forget you may need to flatten your multi-valued Attributes. If you do this in your output map a stray a ret.value = null will disable the update, so it may be best to do it after the snippit above

var attribute = work.MD_CAMPUS_ENR;

var values = attribute.getValues()

// If there are any values, 
if (values.length > 0 ) {
var flatValues= "";
for (val in values) {
if (val != null && val.trim() != "") {
flatValues += val + ";";
}
}
if (flatValues != "") 
flatValues = flatValues.substring(0,flatValues.length -1);

work.MD_CAMPUS_ENR.clear();
work.MD_CAMPUS_ENR.setValue(flatValues);
}

A more elegant solution would be to output the attribute using a built-in parser



Comments