User Tools

Site Tools


8. Variables

Variables can be used throughout Spatialytics ETL, including in transformation steps and job entries. You define variables by setting them with the Set Variable step in a transformation or by setting them in the Spatialytics_ETL.properties file in the directory:

  $HOME/.Spatialytics_ETL(Unix/Linux/OSX)
  C:\Documents and Settings\<username>\.Spatialytics_ETL\ (Windows)
  C:\Users\<username>\.Spatialytics_ETL\ (Windows Vista)

The way to use them is either by grabbing them using the Get Variable step or by specifying meta-data strings like:

  • ${VARIABLE}

or:

  • VARIABLE

Both formats can be used and even mixed, the first is a UNIX derivative, the second is derived from Microsoft Windows. Dialogs that support variable usage throughout Spatialytics ETL are visually indicated using a red dollar sign. You can use <CTRL>+ space hot key to select a variable to be inserted into the property value. Mouse over the variable icon to display the shortcut help.

Note: Whenever it is possible to use variables, it is also possible to use special characters (e.g. CHAR ASCII HEX01). This can be set with the format $[hex value], e.g. $[01] (or $[31,32,33] equivalent to 123). These Hex numbers can be looked up at an ASCII conversion table.

Note: The feature of special characters makes it possible to escape the variable syntax. E.g. when you want to use ${foobar} really in your data stream, then you can escape it like this: $[24]{foobar}. $[24] is then replaced by '$' what results in ${foobar} without resolving the variable. See also feature request PDI-6188.

Note: Recursive usage of variables is possible by alternating between the Unix and Windows style syntax. For example you want to resolve a variable that is itself depending on another variable then you could use this example: ${inner_var}

Other ways to set and access variables:

  • There are also System parameters, including command line arguments. These can be accessed using the Get System Info step in a transformation.
  • You can also specify values for variables in the “Execute a transformation/job” dialog in Spoon or the Scheduling perspective. If you include the variable names in your transformation they will show up in these dialogs.
  • It is also possible to set variables by Named Parameters.

The following topics are covered in this section:

Variable Scope

Internal Variables

8.1 Variable scope

The scope of a variable is defined by the place in which it is defined.

Environment variables

The first usage (and only usage in previous Spatialytics ETL versions) was to set an environment variable. Traditionally, this was accomplished by passing options to the Java Virtual Machine (JVM) with the -D option. It's also an easy way to specify the location of temporary files in a platform independent way, for example using variable ${java.io.tmpdir}. This variable points to directory /tmp on Unix/Linux/OSX and to C:\Documents and Settings\<username\Local Settings\Temp on Windows machines. The only problem with using environment variables is that the usage is not dynamic and problems arise if you try to use them in a dynamic way. For example, if you run two or more transformations or jobs run at the same time on an application server you get conflicts. Changes to the environment variables are visible to all software running on the virtual machine.

Spatialytics ETL variables

Because the scope of an environment variable is too broad, Spatialytics ETL variables were introduced to provide a way to define variables that are local to the job in which the variable is set. The “Set Variable” step in a transformation allows you to specify in which job you want to set the variable's scope (i.e. parent job, grand-parent job or the root job).

8.2 Internal variables

The following variables are always defined:

Variable Name Sample value
Internal.Spatialytics_ETL.Build.Date 2007/05/22 18:01:39
Internal.Spatialytics_ETL.Build.Version 2045
Internal.Spatialytics_ETL.Version 2.5.0

These variables are defined in a transformation:

Variable Name Sample value
Internal.Transformation.Filename.Directory D:\Spatialytics_ETL\samples
Internal.Transformation.Filename.Name Denormaliser - 2 series of key-value pairs.ktr
Internal.Transformation.Name Denormaliser - 2 series of key-value pairs sample
Internal.Transformation.Repository.Directory /

These are the internal variables that are defined in a Job:

Variable Name Sample value
Internal.Job.Filename.Directory /home/matt/jobs
Internal.Job.Filename.Name Nested jobs.kjb
Internal.Job.Name Nested job test case
Internal.Job.Repository.Directory /

These variables are defined in a transformation running on a slave server, executed in clustered mode:

Variable Name Sample value
Internal.Slave.Transformation.Number 0..<cluster size-1> (0,1,2,3 or 4)
Internal.Cluster.Size <cluster size> (5)

From the Pentaho website

8.3 How to pass parameters from a job to a transformation and retrieve parameters values?

Named parameters are a system that allows you to parameterize your transformations and jobs. Named parameters form a special class of ordinary Spatialytics ETL variables and are intended to clearly and explicitly define for which variables the caller should supply a value when executing your job or transformation. A default value can also be provided.

Defining a named parameter in the Transformation job entry

In both the Job and Transformation job entries in a job you can define how parameters are set and/or passed:

Here, we define in the Parameters tab of the job entry, a parameter named myparam with value 'test'. You can either set the value of a parameter by giving it a value or by looking up the value in a Result row from a previous transformation. Simply specify a column name in that case.

Please note that the “Pass all parameter values down to the sub-transformation” option is checked. With this option checked, the value of the myparam parameter is transmitted to the transformation settings.

Retrieving the value of the parameter in the transformation

In order to retrieve the value of the parameter, you have first to define the parameter in the settings of the transformation. Remember with the “Pass all parameter values down to the sub-transformation” option checked, you enable the transmission of the parameter but in order to have its value set you have to define it at the transformation level.

To do that, please go to the sub-transformation tab and then click on menu Transformation > Settings, and then visit the Parameters tab.

You can specify a default value and a description. The description is especially useful when you invoke a transformation from the command line. As a reminder, the -listparam of the pan command line tool provides a listing of all the transformation parameters with their names, default values and descriptions.

From this point, you can retrieve the value of the myparam parameter in any steps by typing ${myparam} in any fields that accept the use of variables. Please note that on Windows OS, the syntax becomesmyparam.

Retrieving parameter values in rows or in scripts

To retrieve the parameter value in a row, you can use the “Get Variable” step.

Here the myparam value set in the Transformation job entry will be accessible in the parametre field of the stream.

Retrieving parameter values in scripts

The Modified Java Script Value step provides a very flexible and powerful way to define specific data processes. But how to retrieve value of the myparam parameter in such a step as the ${myparam} is not very friendly with the javascript interpreter?

In conjunction with the Get Variable step, you can access the value of the parameter through the parametre field of the input step as illustrated in the figure below.

But you can also retrieve it directly through the getVariable function.

What is the better way? It depends on what you want to do in your transformation. If you need to modify the value of the parameter, maybe it would be interesting to have the value stored in the stream and have it modified during the transformation by one or more steps.

Interested in knowing more about named parameters and variables, and especially on how to substitute variables in Kettle Parameter values? Please have a look at this interesting blog post on the Roland Bouman's blog.

en/spatialytics_etl/008_variables.txt · Last modified: 2013/02/06 17:42 by sbedard

Page Tools