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

by Andrew Cave
January 24, 2018

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

More blog posts

Information Security and the BizCubed Ways and Values

We’ve blogged previously about the BizCubed Ways and Values.  Many companies have versions of this – they may call them values, guiding principles or another phrase.  While many in large organisations struggle to see them as more than “corporate buzzwords”, we rely on them heavily and incorporate them daily.

read more

BizCubed’s Journey to ISO27001 Certification

A year ago, we blogged about Data Security as a Practice. Since then, we have continued the practices we talked about there, integrating it even further into our daily practice, and incorporating new processes and aspects into our existing cadence.

read more