CODEWITHSUNDEEP
mysqlsqlpentahopdi
erogol
erogol

Reputation: 13624

How to create a new column with an SQL function in Pentaho PDI work-flow?

I have one sql function, let's say theFunction(item_id). It takes an item id and computes one value as its return. I read one table from the DB and I suppose to compute a new value to append for each row by this function given the item_id particular to taht row. Which desing block would do this form me with the following SQL (if not wrong).

select thsFunction(item_id);

I assume that the block gives me item_id of each row as a variable.

Upvotes: 0

Views: 722

Answers (2)

CRConrad
CRConrad

Reputation: 11

An SQL function is probably much more efficient to run in the database, for all rows at once, in stead of making a separate call into the database from PDI for each row to execute the function. So if performance is at all a relevant concern, I'd suggest a whole different strategy:

  1. Write your rows to a table in the database. End your transformation here.
  2. On the job level, first execute your transformation from above, then execute the function in an "Execute SQL script..." component, giving it an SQL command somewhat like "UPDATE my_temp_table set target_col = theFunction(item_id)".
  3. Continue your job with the remaining steps in a new transformation, starting from that table as input.

This of course presupposes that you don't have too many other threads going on, but if your transofrmation is simple and linear -- or at least if it can be made single-linear at this particular step -- it may be possible to split it up into two parts before and after this SQL call.

Upvotes: 1

nsousa
nsousa

Reputation: 4544

You can use another table input step, and have it accept fields from previous steps and execute for every row (both config options are at the bottom of the step's window).

Beware that this is a rather slow implementation. Each query is executed separately and as such each row requires a round trip to the database.

Alternatively, you can use the Row SQL Script. I believe it allows you to pass all SQL statements in a single trip to the database.

Upvotes: 1

Related Questions