PDI - Update field value in Logging tables

0
votes

I'm trying create a transformation that can change field value in DB (postgreSQL what i use).

Case : In postgre db I have table called Monitoring and it has several field like id, date, starttime, endtime, duration, transformation name, status, desc. All those value I get from Transformation Logging.

So, when I run the transformation it will insert into Monitoring table and set value for field status with Running. And when it done it will update the status into Finish. What I'm trying is to define value in table field by myself not take it from Transformation Logging so I can customize the value like I want to.

Goal is Update transformation status value from 'running' to 'finish/error/abort etc' in my db using pentaho and display that status in web app

I have thinking to used Modified Java Script step to do it but if there any other way maybe? A better one. (Just need opinion about this)

3
kettlepentaho-data-integrationpdi
If your Monitoring table is the Transformation Logging table, you'll be trapped in an auto-lock dead loop at the first attempt of updating the value. Additionally, the Logging system has the final word, so it will overwrite your update when the transformation finishes. - AlainD
Is your question related to the fact that, sometimes, -- for example when the system crashes or when the PDI passes the memory limit --, the status of the transformation is not set to Finish properly ? - AlainD
@AlainD yah, that what I mean to, so when the PDI error, crash or something else I can make the trans Abort or stop etc and set the Status field value like I want. And for your info, I use the Transformation Logging data and insert it into my new table called Monitoring (different table from Transformation Logging) so I can change the table when ever I want or need to. - Rio Odestila
And what I don't know is how to update that specific field? - Rio Odestila
I use Update step and update the Status field but it doesn't work, the value in DB not change. - Rio Odestila

3 Answers

1
votes

Apart from my remark, did you try the Value Mapper?

0
votes

modified javascript is not a good idea to use. Ideally, it shouldn't be used due to the performance issue. You can use "add constant" step or "User defined Java Class" for an alternative.

0
votes

You cannot change the values of the built-in Logging tables, for the simple reason that they are reserved for PDI usage. This causes a known issue in case of hard error: for example the status is not set to finish when the data base server crashes, or when a NullException is not catch by the DPI code.

You have some work around.

  • The simplest, the one used in the ETL-Pilot is to test (Status=Finish OR LogDate< 15 minutes ago) is the web app.

  • You can update the table when the transformation is not running. For example, put an hourly (or less) crontab that changes to Finish the status of any transformation whose LogDate is older than 15 mn. This crontab may be a simple SQL or included in a transformation that also check the tables size and/or send an email in case of potential error.

  • You can copy the table (if it is a non locking operation in your DB system), modify the Status column and use this table for your web app.