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:
To schedule ETL processes on Linux, we always use the
cronscheduler. The task to do this is very easy, while understanding the syntax of thecronschedules is a bit more complicated.To add a new schedule, you need to edit the
crontabfile; to do this, type the following command from the Linux command line:crontab –eDepending on the distribution, you can also use some graphical tools to do the same, but as I always suggest, it is better to learn the command-line commands to give you complete freedom and portability.
To schedule a process, you need to specify the time at which the command will run. To do this, there is a particular syntax that lets you specify minutes, hours, the month date, and the month year. The format of any of these items is as follows:
Minute: The minute of the hour (0-59)
Hour: The hour of the day (0-23)
Month day: The day of the month (1-31)
Month: The month of the year (1-12)
Weekday: The day of the week (0-6, 0 = Sunday)
You can follow some simple rules to specify ranges or multiple values for any of the preceding items, summarized as follows:
Define ranges by giving the start and end values of the interval and separating them with a hyphen
-Define multiple values by separating the single values with a comma
So, let's suppose that we want to schedule a job in the
<book_samples>/sample1directory calledexport-job.kjbto 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
After you have typed the command, save and quit the
crontabeditor 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 theexport-job.kjbjob. Of course, scheduling a Pan script is just a matter of changing the script name.Further references about how to schedule tasks with the
cronscheduler 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:
Go to Control Panel | Administrative Tools | Task Scheduler.
Select Create a Basic Task from the menu on the right.
The Create Basic Task Wizard dialog will be displayed. Enter the task name (for example,
Example PDI job). Click on Next.
Select the schedule you want to apply. You can either select the schedule based on the calendar or on an event. Let us suppose our schedule is based on the calendar and we want our program to be scheduled daily. Select the Daily radio button and click on Next.
Insert the time you want the event to fire and its recurrence multiplicity (Recur every). We want, for example, the event to fire everyday at 4.00 A.M. Click on Next.
Select the action you want to accomplish; in our case, we select Start a program. Click on Next.
In the Program/script field, insert the full path and the name of the script you want to start (
Kitchen.batorPan.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.
At the end, a summary dialog will open showing you all the parameters you inserted to completely define your schedule.
Click on Finish.
Your job will now appear in the list of waiting jobs.
To schedule ETL processes in Windows using the command line, use the following steps:
The
atcommand works the same ascron, so it becomes very easy to schedule our job with Kitchen or our transformation with Pan.Let's suppose that we want to schedule a job in the
<book_samples>/sample1directory calledexport-job.kjbto 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.kjbAfter you type the command, the schedule immediately becomes available. As you can see, in this case, you need to specify the complete path to the PDI scripts and to the
export-job.kjbjob.
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.
