SQLMost of the "logic" for any transformation should be done in the transformation so the JDBCDriver supports a very limited SQL syntax. It supports such a limited set of SQL that it is easier to describe succinctly what it DOES support rather than what it DOES NOT support. The reader should assume that if it's not described here, that it won't work. SELECTJDBCKettle supports basic SELECT statements. The format of the supported select statement is as follows: SELECT (* or column list)
FROM
OPTIONALSCHEMA.STEPNAME No ORDER BY. No GROUP BY. JDBCKettle does support column aliasing but does NOT support table aliasing. Valid column aliasing: select Year as "Year of My Present Requests" from samples_example.output
Invalid table aliasing: select t1.Year from sample_example.output as "t1"
JDBCKettle does support values in a "where" clause with limitations and unique behavior. The WHERE clause is a way to pass a set of keys / values to Kettle by setting these parameters. Parameters are used through Kettle as a way of making the steps/functionality more dynamic. JDBCKettle supports 0 or more EQUALITY fragments that set these variables. IT DOES NOT do any filter of the received data - it simply sets and passes variables to the KTR step. For instance, the following SQL SELECT *
FROM
"examplevar"."my new table"
WHERE
yeartofilter = "2005" Will set the variable "yeartofilter" to 2005 when executing the transformation. The transformation can then use this variable (like any other Kettle variable) in Table Inputs, filenames, etc as ${yeartofilter}. Multiple variable sets are supported using "AND" between them. where yeartofilter = "2005" and myothervariable = "ANOTHERSTring" AND "var3" = 200 Variable case is preserved. Quotes are optional. "var3" and var3 are the same. ${var3} in kettle. Values can be "quoted" or without quotes but will always be set as a String (this is how Kettle treats variables). You can escape a quote character (so you can actually get a " in the variable using the \ character. DEVELOPERS NOTE: JDBCKettleStatement has a commented location of where and how to set these variables.
|
Sign in to add a comment
