ETL processes are always batch processes; you will launch them, and then at the very end you expect to get back the results. There is no kind of user interaction during the process's execution. Often, ETL processes can take quite some time to execute because they either work with a lot of data or they implement complex process rules that take a long time to execute. It is always a good idea to schedule them so that they can run whenever the system load is low. This last recipe guides you through how to schedule PDI jobs and transformations on Unix/Linux. As usual, anything is applicable for both jobs and transformations; the only difference is in the name of the script's file that is to be scheduled.
To get ready for this recipe, you need to check that the JAVA_HOME
environment variable is set properly and then configure your environment variables so that the Kitchen script can start from anywhere without specifying the complete path to your PDI home directory. For details about these checks, refer to the recipe Executing PDI jobs from a filesystem (Simple).
To schedule ETL processes on Linux, use the steps that follow:
cron
scheduler. The task to do this is very easy, while understanding the syntax of the cron
schedules is a bit more complicated.crontab
file; to do this, type the following command from the Linux command line:crontab –e
-
<book_samples>/sample1
directory called export-job.kjb
to execute at every half an hour, at 20 and 50 minutes past the hour every day, except on weekends. To do this, you need to add a new schedule by typing the following command:20,50 * * * 1-5 <pdi_home>/kitchen.sh –file <books_samples>/export-job.kjb
crontab
editor by pressing the Esc key; then type :wq
, and then press the Enter key. The schedule will immediately be available. As you can see, in this case, you need to specify the complete path to the PDI scripts and to the export-job.kjb
job. Of course, scheduling a Pan script is just a matter of changing the script name.cron
scheduler can be found on Wikipedia at http://en.wikipedia.org/wiki/Cron.To schedule ETL processes in Windows from Task Scheduler, use the steps that follow:
Example PDI job
). Click on Next.Kitchen.bat
or Pan.bat
). In the Add arguments field, insert the usual information you pass along to the Kitchen or Pan script to start your job or transformation properly. Click on Next.To schedule ETL processes in Windows using the command line, use the following steps:
at
command works the same as cron
, so it becomes very easy to schedule our job with Kitchen or our transformation with Pan.<book_samples>/sample1
directory called export-job.kjb
to be executed everyday at 8:00 AM with the exception of the weekends. To do this, you need to add a new schedule by typing the following command:at 8:00 /every:M,T,W,Th,F <pdi_home>Kitchen.bat /file:<books_samples>export-job.kjb
export-job.kjb
job.For Linux/Mac users, an interesting point is that whenever we execute a problem through a cron
schedule, we get into trouble with our environment variables settings. Let's see how we can apply a little trick to solve all of this easily and without any pain.
For Linux/Mac users, we can get into trouble because a crontab
schedule for our application does not work properly. The reason for this is that crontab
passes a minimal set of environment variables to our application. To look at this, you can add a dummy job and have the output of the environment variables written on a file as suggested by the following example:
* * * * * env > /tmp/env.log
To work around this, a first simple practice would be to remember to specify all of the environment variables used by your script in the script. If you are going to launch an application, wrap it in a little script where you will set all of your needed environment variables.
In case you don't want to redefine all of your environment variables, another option would be to add the following in the crontab
scheduler before your command:
. $HOME/.profile.
An example of this is as follows:
0 5 * * * . $HOME/.profile; /path/to/command/to/run
This could be a simple way to access any environment variable defined for our user without having to respecify them in our script one by one.