The simplest way to replace variables in a string in Pentaho Spoon

Spoon: variable substitution in string A in the Calculator step

This post is going to look at using the Calculator step to do variable or parameter interpolation into a string. In other words, we can use the Calculator step to do variable substitution instead of laboriously using a ‘get variable’ step followed by one (or more) ‘replace string’ step(s). This has been available in Pentaho since version 4.01.

First off, let’s make a new transformation in Spoon (Pentaho Data Integration) and add in a ‘Data Grid’ step, a Calculator step, and a ‘Dummy’ step. Join them up with hops.

In the transformation properties, add in the two parameters P_TOKEN and P_URL.

Open the Edit dialogue for the Data Grid step (I’ve renamed it to ‘set the string’) and add in the field ‘string_with_vars’ and make it a ‘String’ type.

In the data tab, we’ll give ‘string_with”vars the value “http://${P_URL}/get.messages?taken=${P_TOKEN}”. The syntax ${VARIABLE_OR_PARAMETER_NAME} is the standard way of referencing variables or parameters in Pentaho.

Now open the Calculator step for editing and add the new filed ‘uri_string’ in the first row. Select ‘variable substitution in string A’ as the Calculation type (tip: type ‘var’ in the Filter box and hit to filter).

Now choose ‘string_with_vars’ from the drop-down in ‘Field A’ then click ‘OK’ and close..

To have a look at what the results (without having to save our changes yet), we’ll preview the output. Select the ‘Dummy’ step, right-click and choose ‘Preview…’ from the mouse-menu.

In the ‘Debug Dialog’ box, choose ‘Configure’

Now for each parameter, type in a value (whatever you please) and click ‘Run’

After a few seconds, the ‘Examine preview data’ screen pops up and we can see that in the new field ‘uri_string’ the variable/parameter names in ‘string_with_vars’ have been replaced with the values we typed into the Parameter value field.

Here’s a zip with the worked example.

worked transformation