You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@groovy.apache.org by Péter Gergely Horváth <pe...@gmail.com> on 2016/11/08 16:45:37 UTC
Using Groovy in Business Intelligence applications via dyna4JDBC driver
Hi Groovy Users,
There is one more area where the power of Groovy can now be utilized: I
developed a JDBC driver called dyna4JDBC (http://dyna4jdbc.org/), which
allows Groovy (or any JSR-223 compatible scripting language) to be called
via the JDBC API. The standard output echoed by the script is parsed to a
JDBC Result Set, so that the caller application can process it further.
The goal of this driver was to allow JDBC-compatible reporting and ETL
applications calling dynamic JVM language scripts as if they were databases
and exposing the script output as a result set for further processing
(e.g.: advanced visualizations: charts etc.)
For example, you can now use Eclipse BIRT [1] reporting application and
create reports, which are backed by a Groovy script: You can simply
configure a JDBC datasource in Eclipse BIRT and write a Groovy script
against that datasource instead of SQL. The output the script prints to the
standard output will appear as the result set of the "query".
You can get a quick introduction on the project home page [2]. The
documentation is on GitHub Wiki [3], and the binary version of the driver
can be downloaded from GitHub [4] or from Maven Central [5].
I am sharing it here in the hope that it might be useful for some people
and that I could potentially get some feedback you. :)
Cheers,
Peter
[1] http://www.eclipse.org/birt/
[2] http://dyna4jdbc.org/
[3] https://github.com/peter-gergely-horvath/dyna4jdbc/wiki
[4] https://github.com/peter-gergely-horvath/dyna4jdbc/releases
[5] http://search.maven.org/#search%7Cga%7C1%7Cg%3A%22com.
github.peter-gergely-horvath%22%20a%3A%22dyna4jdbc%22
Re: Using Groovy in Business Intelligence applications via dyna4JDBC driver
Posted by Péter Gergely Horváth <pe...@gmail.com>.
Hi Paul,
Thank you very much for giving it a try!! :) I've pushed a new, patched
version (1.0.1) to Maven Central, which solves this issue.
I hope someone else might find this tool also useful. For me, having the
ability to run Groovy in a JDBC reporting application would have been a
huge help in some of my previous jobs.
Cheers,
Peter
On Wed, Nov 16, 2016 at 8:16 PM, Paul King <pa...@asert.com.au> wrote:
> I finally got around to giving this a try. I noticed some minor issues
> with the pom which require me to have some @GrabExclude statements
> that I would prefer not to need. I created an issue for your
> consideration.
>
> Anyway, it was fun to play with:
>
> @Grab('com.github.peter-gergely-horvath:dyna4jdbc:1.0.0')
> @GrabExclude('org.scala-lang:scala-compiler')
> @GrabExclude('org.renjin:renjin-script-engine')
> @GrabExclude('org.scala-lang:scala-library')
> @GrabConfig(systemClassLoader=true)
> import groovy.sql.Sql
>
> def script = '''
> def fields = [t: 'Ticker', name: 'Name', l_cur: 'Close']
> def base = 'http://www.google.com/finance/info?infotype=infoquoteall&q'
> def url = new URL("$base=NASDAQ:AAPL,IBM,MSFT,GOOG")
> def json = url.text.replaceFirst("//", "")
> def data = new groovy.json.JsonSlurper().parseText(json)
> println fields.values().collect{ "$it::" }.join('\t')
> data.each { row -> println fields.keySet().collect{ row[it] }.join('\t') }
> '''
>
> def url = 'jdbc:dyna4jdbc:scriptengine:groovy'
> Sql.withInstance(url) { sql ->
> sql.eachRow(script){ row -> println "$row.Ticker $row.Close" }
> }
>
> Cheers, Paul.
>
>
> On Wed, Nov 9, 2016 at 2:45 AM, Péter Gergely Horváth
> <pe...@gmail.com> wrote:
> > Hi Groovy Users,
> >
> > There is one more area where the power of Groovy can now be utilized: I
> > developed a JDBC driver called dyna4JDBC (http://dyna4jdbc.org/), which
> > allows Groovy (or any JSR-223 compatible scripting language) to be called
> > via the JDBC API. The standard output echoed by the script is parsed to a
> > JDBC Result Set, so that the caller application can process it further.
> >
> > The goal of this driver was to allow JDBC-compatible reporting and ETL
> > applications calling dynamic JVM language scripts as if they were
> databases
> > and exposing the script output as a result set for further processing
> (e.g.:
> > advanced visualizations: charts etc.)
> >
> > For example, you can now use Eclipse BIRT [1] reporting application and
> > create reports, which are backed by a Groovy script: You can simply
> > configure a JDBC datasource in Eclipse BIRT and write a Groovy script
> > against that datasource instead of SQL. The output the script prints to
> the
> > standard output will appear as the result set of the "query".
> >
> > You can get a quick introduction on the project home page [2]. The
> > documentation is on GitHub Wiki [3], and the binary version of the driver
> > can be downloaded from GitHub [4] or from Maven Central [5].
> >
> > I am sharing it here in the hope that it might be useful for some people
> and
> > that I could potentially get some feedback you. :)
> >
> > Cheers,
> > Peter
> >
> > [1] http://www.eclipse.org/birt/
> > [2] http://dyna4jdbc.org/
> > [3] https://github.com/peter-gergely-horvath/dyna4jdbc/wiki
> > [4] https://github.com/peter-gergely-horvath/dyna4jdbc/releases
> > [5]
> > http://search.maven.org/#search%7Cga%7C1%7Cg%3A%22com.
> github.peter-gergely-horvath%22%20a%3A%22dyna4jdbc%22
>
Re: Using Groovy in Business Intelligence applications via dyna4JDBC driver
Posted by Paul King <pa...@asert.com.au>.
I finally got around to giving this a try. I noticed some minor issues
with the pom which require me to have some @GrabExclude statements
that I would prefer not to need. I created an issue for your
consideration.
Anyway, it was fun to play with:
@Grab('com.github.peter-gergely-horvath:dyna4jdbc:1.0.0')
@GrabExclude('org.scala-lang:scala-compiler')
@GrabExclude('org.renjin:renjin-script-engine')
@GrabExclude('org.scala-lang:scala-library')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql
def script = '''
def fields = [t: 'Ticker', name: 'Name', l_cur: 'Close']
def base = 'http://www.google.com/finance/info?infotype=infoquoteall&q'
def url = new URL("$base=NASDAQ:AAPL,IBM,MSFT,GOOG")
def json = url.text.replaceFirst("//", "")
def data = new groovy.json.JsonSlurper().parseText(json)
println fields.values().collect{ "$it::" }.join('\t')
data.each { row -> println fields.keySet().collect{ row[it] }.join('\t') }
'''
def url = 'jdbc:dyna4jdbc:scriptengine:groovy'
Sql.withInstance(url) { sql ->
sql.eachRow(script){ row -> println "$row.Ticker $row.Close" }
}
Cheers, Paul.
On Wed, Nov 9, 2016 at 2:45 AM, Péter Gergely Horváth
<pe...@gmail.com> wrote:
> Hi Groovy Users,
>
> There is one more area where the power of Groovy can now be utilized: I
> developed a JDBC driver called dyna4JDBC (http://dyna4jdbc.org/), which
> allows Groovy (or any JSR-223 compatible scripting language) to be called
> via the JDBC API. The standard output echoed by the script is parsed to a
> JDBC Result Set, so that the caller application can process it further.
>
> The goal of this driver was to allow JDBC-compatible reporting and ETL
> applications calling dynamic JVM language scripts as if they were databases
> and exposing the script output as a result set for further processing (e.g.:
> advanced visualizations: charts etc.)
>
> For example, you can now use Eclipse BIRT [1] reporting application and
> create reports, which are backed by a Groovy script: You can simply
> configure a JDBC datasource in Eclipse BIRT and write a Groovy script
> against that datasource instead of SQL. The output the script prints to the
> standard output will appear as the result set of the "query".
>
> You can get a quick introduction on the project home page [2]. The
> documentation is on GitHub Wiki [3], and the binary version of the driver
> can be downloaded from GitHub [4] or from Maven Central [5].
>
> I am sharing it here in the hope that it might be useful for some people and
> that I could potentially get some feedback you. :)
>
> Cheers,
> Peter
>
> [1] http://www.eclipse.org/birt/
> [2] http://dyna4jdbc.org/
> [3] https://github.com/peter-gergely-horvath/dyna4jdbc/wiki
> [4] https://github.com/peter-gergely-horvath/dyna4jdbc/releases
> [5]
> http://search.maven.org/#search%7Cga%7C1%7Cg%3A%22com.github.peter-gergely-horvath%22%20a%3A%22dyna4jdbc%22