Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
Arrow up icon
GO TO TOP
Pentaho Data Integration Quick Start Guide

You're reading from   Pentaho Data Integration Quick Start Guide Create ETL processes using Pentaho

Arrow left icon
Product type Paperback
Published in Aug 2018
Publisher Packt
ISBN-13 9781789343328
Length 178 pages
Edition 1st Edition
Languages
Tools
Arrow right icon
Author (1):
Arrow left icon
 Carina Roldán Carina Roldán
Author Profile Icon Carina Roldán
Carina Roldán
Arrow right icon
View More author details
Toc

Defining and using Kettle variables


In PDI, you can define and use variables, just as you do when you code in any computer language. We already defined a couple of variables when we created the kettle.properties file in Chapter 1, Getting Started with PDI. Now, we will see where and how to use them.

It's simple: any time you see a dollar sign by the side of a textbox, you can use a variable:

Sample textboxes that allow variables

You can reference a variable by enclosing its name in curly braces, preceded by a dollar sign (for example, ${INPUT_FOLDER}).

Note

A less used notation for a variable is as follows: %%<variable name>%% (for example, %%INPUT_FOLDER%%).

Let's go back to the transformation created in the previous section. Instead of a fixed value for the location of the output file, we will use variables. The following describes how to do it:

  1. Open the transformation (if you had closed it). You can do this from Main Menu or from Main Toolbar.
  2. Double-click on the Text file output step. Replace the full path for the location of the file with the following: ${OUTPUT_FOLDER}/${FILENAME}.

Note

Note that you can combine variables, and can also mix variable names with static text.

  1. Close the window and press F10 to run the transformation.
  1. In the window that appears, select the Variables tab. You will see the names of both variables – OUTPUT_FOLDER and FILENAME:

Variables in the Execute a Transformation window

The OUTPUT_FOLDER variable already has a value, which is taken from the kettle.properties file. The FILENAME variable doesn't have a value yet.

  1. To the right of the name, type the name that you want to give to the output file, as shown in the following screenshot:

Entering values for variables

  1. Click on Run
  2. Browse the filesystem to make sure that the file with the name provided was generated

Beside the user-defined variables – those created by you, either in the kettle.properties file or inside Spoon – PDI has a list of predefined variables that you can also use. The list mainly includes variables related to the environment (for example, ${os.name}, for the name of the operating system on which you are working, or ${Internal.Entry.Current.Directory}, which references the file directory where the current job or transformation is saved). To see the full list of variables, both predefined and user-defined, just position the cursor inside any textbox where a variable is allowed, and press Ctrl + Spacebar. A full list will be displayed.

 

  • If you click on any of the variables for a second, the actual value of the variable will be shown, as indicated in the following screenshot:

PDI variables

  • If you double-click on a variable name, the name will be transcribed into the textbox.

Using named parameters

In the last exercise, you used two variables: one created in the kettle.properties file, and the other created inside of Spoon at runtime. There are still more ways to define variables. One of them is to create a named parameter. Named parameters are variables that you define in a transformation, and they can have a default value. You only have to supply a value if it differs from the default. Let's look at how it works, as follows:

  1. Open the last transformation (if you had closed it).
  2. Double-click anywhere in the work area excepting over the steps or hops. This will open the Transformation properties window.
  1. Click on the Variables tab. This is where we define the named parameters.
  2. Fill in the grid as shown, replacing the path in the example with the real path where you have PDI installed:

Defining a named parameter

  1.  Close the window.
  2. Double-click on the CSV file input step. Replace the full path of the location of the file with the following: ${SAMPLES_DIR}/Zipssortedbycitystate.csv.
  3. Close the window and save the transformation.
  1. Click F9 to run the transformation. The Parameters tab in the Run Options window will show the named parameter that we just defined:

Running a transformation with a named parameter

  1. Click on Run. PDI will replace the value of the variable, exactly as it did before.

Note that this time, we didn't supply a value for the variable, as it already had a proper value. Now, suppose that we move the samples folder to a different location. The following describes how we can provide the new value:

  • Click F9 to run the transformation. 
  • In the Parameters tab, fill in the Valuecolumn with the proper value, as shown in the following screenshot:

Supplying a value for a named parameter

  • Click on Run. PDI will replace the value of the variable with the value that you provided, and will read the file from that location.
You have been reading a chapter from
Pentaho Data Integration Quick Start Guide
Published in: Aug 2018
Publisher: Packt
ISBN-13: 9781789343328
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $15.99/month. Cancel anytime
Visually different images