Hitachi Vantara Pentaho Community Wiki

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

<services>
  <service>
    <name>Service</name> 
    <filename>/home/matt/svn/kettle/trunk/testfiles/sql-transmeta-test-data.ktr</filename> 
    <service_step>Output</service_step>
   </service>
 </services>

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

The following standard options are available:

  public static final String ARG_WEBAPPNAME="webappname";
  
  public static final String ARG_PROXYHOSTNAME = "proxyhostname";
  public static final String ARG_PROXYPORT =  "proxyport"; 
  public static final String ARG_NONPROXYHOSTS = "nonproxyhosts";

  • webappname : the name of the web app (future feature to support running on the DI server)
  • proxyhostname : the proxy server for the HTTP connection(s)
  • proxyport : the port of the proxy server
  • nonproxyhosts : the hosts (comma seperator) for which not to use a proxy

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:

  • SELECT:
    • *
    • COUNT(field)
    • COUNT(*)
    • COUNT(DISTINCT field)
    • IIF( condition, true-value or field, false-value or field)
    • Aggregates: SUM, AVG, MIN, MAX
    • Alias both with the "AS" keyword and with one or more spaces seperated, for example SUM(sales) AS "Total Sales" or SUM(sales) TotalSales
  • FROM
    • Strictly one service name
  • WHERE
    • nested brackets
    • AND, OR, NOT if preceded by brackets, for example: NOT ( A = 5 OR C = 3 )
    • precedence taken into account
    • Literals (String, Integer)
    • PARAMETER('parameter-name')='value'  (always evaluates to TRUE in the condition)
  • GROUP BY
    • Group on fields, not IIF() function
  • HAVING
    • Conditions should be placed on the aggregate construct, not the alias
  • ORDER BY
    • You can order on any column in the result. (to be fixed later to allow you to also sort on non-selected columns in the service)