Snaplogic: How to join two tables with a condition other than equals (like, >, <)

Question

I am trying to join two tables in which column from table 1 "contains" data from one of the columns in table 2 i.e. consider below hypothetical:

• Table error_log:

  id| description
  1 | this is right, bla bla, bla
  2 | this is , bla bla, bla, wrong
  3 | this is , bla bla, bla, a disaster, , bla bla, bla
  4 | bla, bla, bla
  

• Table result_type:

  id|type
   1|Right
   2|Wrong
   3|Disaster
  

Now, I wish to join these two tables and store the results in 3rd table i.e.

• Table analysis:

   id|error_log_id|result_type_id
   1 | 1          | 1
   2 | 2          | 2
   3 | 3          | 3
   4 | 4          | null
  

Normally, in any RDBMS, I can do this pretty easily with a left join with a like condition i.e.

select e.error_log_id, r.result_type_id from error_log e 
left join result_type r on e.description like '%'+ r.type +'%'

but I can't seem to find to do so through snaplogic? I tried Snap Join, but it only provides equals join condition i.e.

any suggestion is highly appreciated.

Answer 1

That is the correct snap, try changing the ‘Join type’ to the relevant value, such as Outer.

Answer 2

Assuming that the number of records in result_type doesn't change that often and is a lot smaller than the number of records in error_log, you could use In-memory Lookup snap instead of the Join snap. That aside, there is no straight forward way of doing this using regular snaps. I would suggest doing complicated queries in the database whenever possible. Following is an implementation using the Script snap.

Sample Pipeline

In-memory Lookup

Add a field called join_id (or whatever) with the value 1 (or whatever) and join based on this field in the lookup. This will add the whole lookup (in your case, the types) in all the incoming documents.

Script

try { load("nashorn:mozilla_compat.js"); } catch(e) { }

importPackage(com.snaplogic.scripting.language);

importClass(java.util.ArrayList);
importClass(java.util.LinkedHashMap);

var impl = {
    
    input : input,
    output : output,
    error : error,
    log : log,

    execute : function () {
       this.log.info("Executing Transform Script");
        while (this.input.hasNext()) {
            try {
                var inDoc = this.input.next();
                var outDocs = new ArrayList();

                var errorLogId = inDoc.id;
                var description = inDoc.description;
                var types = inDoc.types;
                var flag = false;
                for(var idx in types) {
                    var type = types[idx];
                    if(description.toLowerCase().contains(type.type.toLowerCase())) {
                        var outDoc = new LinkedHashMap();
                        outDoc.put('error_log_id', errorLogId);
                        outDoc.put('result_type_id', type.id);
                        outDocs.add(outDoc);
                    }
                }

                if(outDocs.isEmpty()) {
                    var outDoc = new LinkedHashMap();
                    outDoc.put('error_log_id', errorLogId);
                    outDoc.put('result_type_id', null);
                    outDocs.add(outDoc);
                }

                for(var idx in outDocs) {
                    var outDoc = outDocs[idx];
                    this.output.write(inDoc, outDoc);
                }
            }
            catch (err) {
                var errDoc = new LinkedHashMap();
                errDoc.put("error", err);
                this.log.error(err);
                this.error.write(errDoc);
            }
        }
        this.log.info("Script executed");
    },
    
    cleanup : function () {
       this.log.info("Cleaning up")
    }
};

var hook = new com.snaplogic.scripting.language.ScriptHook(impl);

Output