Suppose that you have a couple of transformations, but you do not want to run all of them. The transformation to be executed will depend on conditions only known at runtime. If you have just two transformations, you could explicitly call one or the other in a simple fashion. On the other hand, if you have several transformations or if you do not even know the names of the available transformations, you must take another approach. This recipe shows you how.
Suppose that you want to run one of the three sample transformations described in the introduction. The transformation to run will be different depending on the time of day:
Hello
transformationHere's how to do it.
You will need the transformations described in the introduction. Make sure you have defined the variable ${OUTPUT_FOLDER}
with the name of the destination folder. Also, make sure that the folder exists.
Also, define a variable named ${COMMON_DIR}
with the path to the folder where you have the sample transformations, for example, c:/my_kettle_work/common
.
Carry out the following steps:
now
with the system date. now
, for Type select String, and for Format, type HH
. Rename the field as hour
. hour
to Integer. hour
and as Output field: type ktr_name
. Fill in the grid, as shown in the following screenshot: KTR_NAME
with the value of the field ktr_name
. For variable scope type, leave the default Valid in the root job. ${COMMON_DIR}/${KTR_NAME}.ktr
and close the window.2010/12/04 15:00:01 - Spoon - Starting job... ... ... - Set Variable ${KTR_NAME}.0 - Set variable KTR_NAME to value [gen_random] ... ... - run the transformation - Loading transformation from XML file [C:/my_kettle_work/common/gen_random.ktr] ... 2010/12/04 15:00:02 - Spoon - Job has ended.
${OUTPUT_FOLDER})
. You should see a new file named random.txt
with ten random numbers in it.When you execute a transformation from a job, you can either type the exact name of the transformation, or use a combination of text and variables instead.
In this recipe, you implemented the second option. As you did not know which of the three transformations you had to run, you created a transformation that set a variable with the proper name. Then, in the job, instead of typing the name of the transformation, you used that variable in combination with a variable representing the path to the .ktr
file. When you ran the job, the first transformation set the name of the transformation to run depending on the current time. Finally, that transformation was executed.
In the recipe, you were sure that no matter the value of the variable ${KTR_NAME}
that transformation exists. If you are not sure, it is recommended that you insert File exist entry before the second Transformation entry. With this entry, you should verify a file with the name of the transformation exists before trying to execute it. This way, you avoid your job crashing.
If instead of files you are working with a repository, you can also verify the existence of the transformation. Instead of verifying the existence of a file, you have to run a SELECT
statement on the repository database to see if the transformation exists or not. If your transformation is in the root directory of the repository, then this is quite simple, but it can become a little more complicated if your transformation is deep in the transformations directory tree.
Finally, all said so far about transformations is valid for jobs as well. In order to run a job, you can either type its exact name or use a combination of text and variables, just as you did in the recipe for running a transformation.
The recipe named Getting information about transformations and jobs (repository based) in Chapter 9, Getting the Most Out of Kettle. With this recipe, you will understand how to know if a transformation or job exists in a repository.