Thursday, June 12, 2014

Setting a variable value dynamically in a Pentaho Data Integration job

Setting a variable value dynamically in a Pentaho Data Integration job

On some occasions you might have to set a variable value dynamically in a job so that you can pass it on to the Execute SQL Script job entry in example. In this blog post we will take a look at how to create an integer representation of the date of 30 days ago. And we want to achieve this without using an additional transformation!

The way to achieve this in a simple fashion on the job level is to use the Evaluate JavaScript job entry [Pentaho Wiki]. While this job entry is not really intended to do this, it currently offers the easiest way to accomplish just this. Just add this job entry to your Kettle job and paste the following JavaScript:

date = new java.util.Date();
date.setDate(date.getDate()-30); //Go back 30 full days
var date_tk_30_days_ago = new java.text.SimpleDateFormat("yyyyMMdd").format(date);
parent_job.setVariable("VAR_DATE_TK_30_DAYS_AGO", date_tk_30_days_ago);
true; // remember that this job entry has to return true or false

To test this let's add a Log job entry:

Add this to the log message to the job entry settings:

The date 30 days ago was: ${VAR_DATE_TK_30_DAYS_AGO}

And then run the job. You should see something similar to this:

Certainly you could just pass the value as parameter from the command line to the job, but on some occasions it is more convenient to create the value dynamically inside the job.

Software used:

  • pdi-4.4.0-stable

4 comments:

  1. Nice recipe!! It's a more simple way that using a transformation

    ReplyDelete
  2. usefull post...Thank you

    ReplyDelete
  3. I am setting up a new Pentaho DI 5.3 Installation. The Server is installed in Linux Box. Dev Team have Installed Pentaho Client Tools in Windows 7 Virtual Machine. Created Central Repository in Client Machine connecting to Server
    and all development are done in Repository.

    This is the issue I am facing:- I have a File Location in UNIX Server /xxx/xxx where I will get and place all files from FTP Server. How do I connect to this Unix Location from Windows Client?

    I tried reading the files using Text File Input giving the Unix Location directly and using Kettle Variables, But getting Error:- ****"Could not list the contents of "file:///C:/xxx/xxx" because it is not a folder."**** Does not recognize it as a Unix location.

    I know I can use SSHGet and write a shell script, what are the other options that I have? && If I write a shell script, how will I give the location of script (if I am placing the script in UNIX Server /xxx/xxx).

    Could you pls help me with this?

    ReplyDelete
  4. Thank you for this. Was looking for a simple way to set a variable date

    ReplyDelete