The transformation in the Time for action from Chapter 2 that we just talked about reads a file provided by a professor, simply by taking the name of the file from the command line, and appends the file to the global one. Let's enhance that work.
- Copy the examination files you used in Chapter 2 to the input files and folder defined in your
kettle.propertiesfile. If you don't have them, download them from the Packt website. - Open Spoon and create a new transformation.
- Use a Get System Info step to get the first command-line argument. Name the field as
filename. - Add a Filter rows step and create a hop from the Get System Info step to this step.
- From the Flow category drag an Abort step to the canvas, and from the Job category of steps drag a Set Variables step.
- From the Filter rows step, create two hops—one to the Abort step and the other to the Set Variables step. Double-click the Abort step. As Abort message, put
File name is mandatory. - Double-click the Set Variables step and click on Get Fields. The window will be filled as shown here:
- Click on OK.
- Double-click the Filter rows step. Add the following filter:
filename IS NOT NULL. In the drop-down list to the right of Send 'true' data to step, select the Set Variables step, whereas in the drop-down list to the right of Send 'false' data to step, select the Abort step. - The final transformation looks like this:
- Save the transformation in the
transformationsfolder under the namegetting_filename.ktr. - Open the transformation named
examinations.ktrthat was created in Chapter 2 or download it from the Packt website. Save it in thetransformationsfolder under the nameexaminations_2.ktr. - Delete the Get System Info step.
- Double-click the Text file input step.
- In the Accept filenames from previous steps frame, uncheck the Accept filenames from previous step option.
- Under File/Directory in the Selected files grid, type
${FILENAME}. Save the transformation. - Create a new job.
- From the General category, drag a START entry and a Transformation entry to the canvas and link them.
- Save the job as
examinations.kjb. - Double-click the Transformation entry. As Transformation filename, put the name of the first transformation that you created:
${Internal.Job.Filename.Directory}/transformations/getting_filename.ktr. - Click on OK.
- From the Conditions category, drag a File Exists entry to the canvas and create a hop from the Transformation entry to this new one.
- Double-click the File Exists entry.
- Write
${FILENAME}in the File name textbox and click on OK. - Add a new Transformation entry and create a hop from the File Exists entry to this one.
- Double-click the entry and, as Transformation filename, put the name of the second transformation you created:
${Internal.Job.Filename.Directory}/transformations/examinations_2.ktr. - Add a Write To Log entry, and create a hop from the File Exists entry to this. The hop should be red, to indicate when execution fails. If not, right-click the hop and change the evaluation condition to
Follow when result is false. - Double-click the entry and fill all the textboxes as shown:
- Add two entries—an abort and a success. Create hops to these new entries as shown next:
- Save the job.
- Press F9 to run the job.
- Set the logging level to Minimal logging and click on Launch.
- The job fails. The following is what you should see in the Logging tab in the Execution results window:
- Press F9 again. This time set Basic logging as the logging level.
- In the arguments grid, write the name of a fictitious file—for example,
c:/pdi_files/input/nofile.txt. - Click on Launch. This is what you see now in the Logging tab window:
- Press F9 for the third time. Now provide a real examination filename such as
c:/pdi_files/input/exam1.txt. - Click on Launch. This time you see no errors. The examination file is appended to the global file:
You enhanced the transformation you created in Chapter 3 for appending an examination file to a global examination file. This time you embedded the transformation in a job. The first transformation checks that the argument is not null. In that case, it sets a variable with the name provided. The main job verifies that the file exists. If everything is all right, then the second transformation performs the main task—it appends the given file to the global file.
Note that you changed the logging levels just according to what you needed to see—the highlighted lines in the earlier explanation.
So far, you had defined variables only in the kettle.properties file or inside Spoon while you were designing a transformation. In this last exercise, you learned to define your own variables at run time. You set a variable with the name of the file provided as a command-line argument. You used that variable in the main job to check if the file existed. Then you used the variable again in the main transformation. There you used it as the name of the file to read.
This example showed you the how to set a variable with the value of a command-line argument. This is not always the case. The value you set in a variable can be originated in different ways—it can be a value coming from a table in a database, a value defined with a Generate rows step, a value calculated with a Formula or a Calculator step, and so on.
The variables you define with a Set variables step can be used in the same way and the same places where you use any Kettle variable. Just take precautions to avoid using these variables in the same transformation where you have set them.
Modify the job in the tutorial to avoid processing the same file twice. If the file is successfully appended to the global file, rename the original file by changing the extension to processed—for example, after processing the exam1.txt file rename it to exam1.processed.
After verifying if the file exists, also check whether the .processed version exists. If it exists, put a proper message in the log and abort. If someone accidently tries to process a file that is already processed, it will be ignored.
In the Time for action - inserting new products or updating existent ones section in Chapter 8, you read a file with a list of products belonging to the manufacturer Classic DeLuxe. The list was expected as a named parameter. Enhance that process. Create a job that first validates the existence of the provided file. If the file doesn't exist, put the proper error message in the log. If it exists, process the list. Then move the processed file to a folder named processed.
In the hero exercise in Chapter 8 that involves populating the products table, you created different transformations for updating the products—one for each manufacturer. Now you will put all that work together.
Create a job that accepts two arguments—the name of the file to process and the code of the manufacturer to which the file belongs.
Create a transformation that validates that the code provided belongs to an existent manufacturer. If the code exists, set a variable named TRANSFORMATION_FILE with the name of the transformation that knows how to process the file for that manufacturer.
The transformation must also check that the name provided is not null. If it is not null, set a variable named FILENAME with the name supplied.
Then, in the job, check that the file exists. If it exists and the manufacturer code is valid, run the proper transformation. In order to do so, put ${TRANSFORMATION_FILE} as the name of the transformation in the transformation job entry dialog window. Now test your job.