All Forums Aster
protodemos 3 posts Joined 02/13
27 Feb 2013
The coolest Sql-MR Function you'll see today

Hi,
I have to walk a fine line between making a plug and sharing some cool news, so Mods please keep me honest here.
This is the story of a quick-and-dirty little sql-mr function that started out as a simple expression evaluator (add, subtract, multiply, divide) but grew, Frankenstein-like into a full-fledged programming language.
Another programming language?  Doesn't the world have enough of them?  What's so special about this one?
To answer in order: Yes, Probably, and THIS programming language runs IN and AS an sql-mr function.  You pass in the program you want to run AT THE COMMAND LINE and it executes the code, reading in records from the sql-mr function's "ON" clause (and yes, it handles multi-input functions) and passing records back to the database.  And it supports JDBC.  As in read from JDBC via a "cursor" varible, update, delete and insert records and execute arbitrary sql on a JDBC connection.
Basically, we've created a database programming and stored procedure language for nCluster.
Here's an example of using the language:

SELECT * FROM pddbl (
 ON ( SELECT person_id, ssn, last_name, first_name FROM persons)
SCRIPT('
# read in person record, proper-case the names and add a "full_name" column
script format_persons {
    # set up output record definition
    function setup() {
        addoutput("input0.*")
        addoutput("full_name character varying")
    }

    # read in records, reformat the names, then output
    function main() {
        # loop over the input records (the "ON" clause)
        while(consume(input0)) {
            output.person_id=input0.personid
            output.last_name=strproper(input0.last_name)
            output.first_name=strproper(input0.first_name)
            output.ssn=input0.ssn
            output.full_name=input0.last_name + ", "
            output.full_name=output.full_name + input0.first_name
            emitrow()
        }
    }
}'))

The other cool thing it does is that it can execute programs that have been previously stored in the database using the "\install" command (which is why it's effectively a stored procedure language").  So assuming the text of the above program (everything in the "SCRIPT()" option to the "pddbl" sql-mr function) had been saved as a text file named "format_persons.txt" and that file had been uploaded to the cluster, you could run the above as:

SELECT * FROM pddbl (
 ON ( SELECT person_id, ssn, last_name, first_name FROM persons)
SCRIPT('@format_persons.txt')
)

There's a LOT more to it (your usual if/then/else, other types of loops, exception handling via try/catch/finally, array, hashmap and stack variables, user-defined functions, and of course the JDBC functionality), but a forum post really isn't the (for lack of a better word) forum for that sort of thing.
If anyone is interested in seeing it in action, send me a PM to my email address (should be listed in my forum profile, but just in case: protodemos@protodemos.com).
 

Michael Cooper, Vice President for Product Development Proto/demos http://www.protodemos.com
You must sign in to leave a comment.