Kettle JDBC driver

The thin Kettle JDBC driver allows a Java client to query a Kettle transformation remotely using JDBC and SQL.

Architecture

As with most JDBC drivers, there is a server and a client component to the JDBC driver.
The server is designed to run as a Servlet on the Carte server, the Pentaho Data Integration server or Pentaho Business Analytics platform.  At the time of writing only Carte is supported.

Server configuration

The carte configuration file accepts a <services> block that can contain <service> elements with the following sub-elements:

For example:

<services>
  <service>
    <name>Service</name> 
    <filename>/path/to/your/service-transformation.ktr</filename> 
    <service_step>Output</service_step>
   </service>
 </services>

Monitoring

During execution of a query, 2 transformations will be executed on the server:

  1. A service transformation, of human design built in Spoon to provide the service data
  2. An automatically generated transformation to aggregate, sort and filter the data according to the SQL query

These 2 transformations will be visible on Carte or in Spoon in the slave server monitor and can be tracked, sniff tested, paused and stopped just like any other transformation.  However, it will not be possible to restart them manually since both transformations are programatically linked.

The JDBC Client

The JDBC driver uses the following class:

org.pentaho.di.core.jdbc.ThinDriver

The URL is in the following format:

jdbc:pdi://hostname:port/kettle?option=value&option=value

The following standard options are available:

Parameters for the service transformation can be set with the following format:  PARAMETER_name=value (so with the option name prepended with "PARAMETER_")

SQL Support

Support for the SQL is minimal at the moment.

The following things are supported, please consider everything else unsupported:

Literals: 

Limitations

Besides the obviously plentiful limitations in the support for the SQL standard, there are a few noteworthy things to note:

Configuring clients

SQuirreL SQL

Since SQuirrel already contains most needed jar files, configuring it simply done by adding kettle-core.jar as a new driver jar file

BIRT

The following jar files need to be added:

Future

The following things are next on the agenda: