Glue

BigData Workflow Engine for Hadoop, Hbase, Netezza, Pig, Hive, Cascalog ...

SQL Module API

TOC

The SQL Module supports any database that has a JDBC driver.

Class: SQLModule

Exceptions: All methods throw RunttimeExceptions when an error happened on the database side.

Method Description Example 
withSql( db:String, closure )
 creates a Groovy SQL object and passes it to the closure as an argument, the sql object is automatically closed after the closure returns 
ctx.sql.withSql 'mydb', { sql -> sql.execute ('create table mytable (test varchar(10) )' }
 
getSql(db):Sql
 returns a Groovy SQL object 
def sql = ctx.sql.getSql('mydb')
 
eachSqlResult(db:String, sql:String, closure)
 Run a query on db and pass each Groovy Result Instance to the closure 
ctx.sql.eachSqlResult 'mydb2', 'select name, age from people', { res -> println "name: ${res.name}, age: ${res.age}" }
 
mysqlImport(db, file:File)
 Only works for MySQL databases and is used to load large files rapidly via the mysql command line. The file must be TSV 
ctx.sql.mysqlImport('mydb', new File('datafile.tsv'))
 
loadSql(db:String, sql:String, delimiter:String = '\t'): String
 writes the results from the sql query into a temporary file 
ctx.sql.loadSql('mydb', 'select * from mytable', ',')
 
loadSql(db:String, sql:String, delimiter:String, quotes:String): String
 writes the results from the sql query into a temporary file 
ctx.sql.loadSql('mydb', 'select * from mytable', ',')
 
loadSqlGZIP(db:String, sql:String, delimiter:String = '\t'): String
 writes the results from the sql query into a temporary gzip file 
ctx.sql.loadSql('mydb', 'select * from mytable', ',')
 
loadSqlGZIP(db:String, sql:String, delimiter:String, quotes:String): String
 writes the results from the sql query into a temporary gzip file 
ctx.sql.loadSql('mydb', 'select * from mytable', ',')
 
updateSql(db:String, sql:String):int
 runs a sql update command 
execSql(db:String, sql:String):String
 executes an arbritary sql command and returns a String response if any
Method Description Example 
(glue/ctx-sql withSql db (fn [sql] ))
 creates a Groovy SQL object and passes it to the closure as an argument, the sql object is automatically closed after the closure returns 
(def sql (glue/ctx-sql getSql db))
 returns a Groovy SQL object 
(glue/ctx-sql eachSqlResult db sql (fn [x] ))
 Run a query on db and pass each Groovy Result Instance to the closure 
(def r-seq (glue/ctx-sql eachSqlResult db sql))
 Same as above but returns a lazy sequence of result sets 
(glue/ctx-sql mysqlImport db file)
 Only works for MySQL databases and is used to load large files rapidly via the mysql command line. The file must be TSV 
(glue/ctx-sql loadSql db sql delimiter)
 writes the results from the sql query into a temporary file 
(glue/ctx-sql updateSql db sql)
 runs a sql update command 
(glue/ctx-sql execSql db  sql)
 executes an arbritary sql command and returns a String response if any
Method Description Example 
ctx.sql().withSql( db, callbackf )
 creates a Groovy SQL object and passes it to the closure as an argument, the sql object is automatically closed after the closure returns 
ctx.sql().withSql('mydb', lambda sql : sql.execute("create table mytable (test varchar(10) )") )
 
ctx.sql().getSql(db)
 returns a Groovy SQL object 
def sql = ctx.sql().getSql('mydb')
 
sql = ctx.sql().eachSqlResult(db, sql, callbacf)
 Run a query on db and pass each Groovy Result Instance to the closure 
ctx.sql().eachSqlResult("mydb2", "select name, age from people", lambda res : "name: ${res.name}, age: ${res.age}" )
 
for rs in ctx.sql().eachSqlResult(db, sql):
    print(str(rs))
 Same as above but returns a lazy sequence of result sets 
ctx.sql().mysqlImport(db, file)
 Only works for MySQL databases and is used to load large files rapidly via the mysql command line. The file must be TSV 
ctx.sql().loadSql(db, sql, delimiter)
 writes the results from the sql query into a temporary file 
ctx.sql().updateSql(db, sql)
 runs a sql update command 
ctx.sql().execSql(db, sql)
 executes an arbritary sql command and returns a String response if any