Kettle using JavaScript steps and FIRETODB functions for custom database queries

Kettle using JavaScript steps and FIRETODB functions for custom database queries

Assuming you need to implement non-traditional database query operations, in order to discuss this scenario, let's say you need to read the regular expression in the database, and then check the number of field match expressions for each row entered.

Executing database queries in JavaScript steps

When the JavaScript step is initialized, query the database for a regular expression recordset. The input data of each row is then looped to check if the expression is matched, if it matches, the record variable is added one, and the result is written to the new field in the forward direction. The idea is clear, but how do you implement database queries in JavaScript steps?

Here is a very useful function firedb (), he needs to define the good one database connection as an argument in advance, and a SQL query statement parameter, where SQL can only be a SELECT query statement, because the Firedb () function has no transaction or error handling, The return value is an array of record rows, each of which is an array with multiple fields.

Here is the code snippet, which executes only once, preferably in initialization tab, which increases the efficiency of the JavaScript step execution.

Read all rules from Scrapbook DB

var Xarr =firetodb ("Scrapbook", "select Rule from rules;");

var regex = [];

For each row, save the returned rulefor later use

for (var i in Xarr) {

Regex.push (Xarr[i][0]);

}

The following code is executed on each line;

var rules_matched = 0;

var jstr = newjava.lang.String (string_to_test);

Count matching rules

for (var i in regex) {

Varrule = Regex[i];

if (jstr.matches (rule)) {

rules_matched++;

}

}

You can download the example here and try the use of the Firedb () function, an example of the SQL statement for MySQL sample data. Where code "Scrapbook" is the name of the connection to the MySQL database as defined in the transformation.

Kettle using JavaScript steps and FIRETODB functions for custom database queries