CODEWITHSUNDEEP
kettlepentaho-data-integrationpdi
Rio Odestila
Rio Odestila

Reputation: 125

PDI - Update field value in Logging tables

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)

Upvotes: 0

Views: 801

Answers (3)

AlainD
AlainD

Reputation: 6356

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.

Upvotes: 0

AlainD
AlainD

Reputation: 6356

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

Upvotes: 1

Niraj
Niraj

Reputation: 106

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.

Upvotes: 0

Related Questions