Articles in this section

Variables in Etlworks

  • Updated
Follow

Scope

Variables in Etlworks are defined within the scope of a single execution unit, such as a running flow and any of its sub-flows. There are three distinct types of variables:

Global variables

Global variables are key/value pairs, where both the key and value are strings. Developers can reference a global variable in connections and transformations by using the syntax {variable_name}.

Global variables are accessible throughout the entire nested flow hierarchy, meaning they become available to all flows within that hierarchy immediately after they are set.

Common Use Cases for Global Variables

Global variables can be used in various scenarios to make your workflows more dynamic and flexible. Common use cases include:

Programmatically Set and Get Global Variables

The developer can set the global variable programmatically as

com.toolsverse.config.SystemConfig.instance().getProperties().put('name', 'value')

The value must be a string

The value of the global variable can be accessed programmatically as

com.toolsverse.config.SystemConfig.instance().getProperties().get('name')

This expression returns either String or null if the variable is not set.

Set and get Global variables in parallel loop

This technique is used to set global variables in parallel loops and parallel transformations using a thread-safe container. These variables are only "visible" to the current ETL thread, so do not use this technique to set variables that can be accessed in other threads.  

com.toolsverse.config.SystemConfig.instance().
addPropertyInCurrentEtlThread(key, value);

This technique is used to access the value of the variable set in a parallel loop or parallel transformation. If the variable is not found in the current ETL thread this method will return the value set in the main thread.

// props is a java.util.HashMap<String, String>
var props = com.toolsverse.config.SystemConfig.instance().getContextProperties();

// use the same key you stored in props.put(...)
var someValue = props.get("unique key");

Log global variables

Here is an example of the JavaScrip code which logs global variables. You can use this fragment of code anywhere in JavaScript.

// Get the properties map
var properties = com.toolsverse.config.SystemConfig.instance().getProperties();

// Use forEach to iterate over the entry set
for each (var entry in properties.entrySet()) {
    var key = entry.getKey();
    var value = entry.getValue();

    // Log each key-value pair
    etlConfig.log(key + '=' + value);
}

Set Global Variables

Variables set programmatically or using configuration:

Flow variables

Flow variables are key/value pairs, where both the key and value are strings. Developers can reference flow variables in SQL statements, JavaScript and Python scripts using the syntax {variable_name}.

Unlike Global variables, Flow variables are individually set for each flow within a nested flow hierarchy. However, there is one exception: when the main (parent) flow in a nested hierarchy starts, all flows within the hierarchy inherit the value of any flow variable set in the main flow.

Common Use Cases for Flow Variables

Parameterizing SQL Statements

Flow variables are often used to dynamically parameterize SQL queries. Instead of hardcoding values, you can use flow variables to inject dynamic data into your SQL statements, such as filter conditions, limits, or values for insert and update operations. This makes SQL queries flexible and adaptable to varying inputs during flow execution.

Example:

SELECT * FROM orders WHERE order_date >= '{start_date}' AND order_date <= '{end_date}'

Parameterizing Scripts

Flow variables can be embedded into scripts across different scripting languages supported by Etlworks, allowing you to customize and control flow behavior programmatically. These variables enable dynamic adjustments in your scripts depending on flow parameters or conditions at runtime.

Example JavaScript:

var recordCount = {RECORD_COUNT};
if (recordCount > {A_FEW}) {
    // code
}

Example Python:

recordCount = {RECORD_COUNT};
if recordCount > {A_FEW}) 
    # code

Example bash:

record_count=${RECORD_COUNT}
if [ "$record_count" -gt {A_FEW} ]; then
   # code
fi

Programmatically Set and Get Flow Variables

The developer can set the flow variable programmatically as

scenario.getVariable('name', value)

The value must be a string.

The flow variable can be accessed programmatically as

scenario.getVariable('name')

This expression returns either Variable or null if the variable is not set.

To get the value the developer can use the following code:

var variable = scenario.getVariable('name'); 
var value = variable != null ? variable.getValue() : null

Log flow variables

Here is an example of the JavaScrip code which logs flow variables:

// Get variables
var vars = scenario.getVariables();

// check if there are variables
if (vars != null && !vars.isEmpty()) {
   // iterate the variables by index
   for (var index = 0; index < vars.size(); index++) {
      // get the variable 
      var theVar = vars.getList().get(index);

      // log name and value
      etlConfig.log(theVar.getName() + '=' + theVar.getValue());
   } 
}

Set Flow Variables

Here are the different ways to set and use flow variables:

Key/value pairs in a flow storage

Key/value pairs in flow storage are a map of objects where the key is a string and the value can be any type of object, such as a number, boolean, or other data types.

These key/value pairs become available throughout the entire nested flow hierarchy as soon as they are set. However, unlike global or flow variables, they cannot be referenced using the {variable_name}syntax.

Common Use Cases for Key/value pairs in a flow storage

One of the most common use cases for key/value pairs in flow storage is to use them in Conditions—to execute a step in a nested flow conditionally—or in Loop scripts—to repeatedly execute a step in a nested flow until the loop script returns a non-null value.

Another common use case is using key/value pairs as a cache. Developers can store any object or variable (such as a record counter) in the key/value storage and later retrieve and use it within the same flow or across other flows in the nested hierarchy. This allows for efficient reuse of data without reprocessing or recalculating values, improving flow performance and logic.

Programmatically Set and Get Key/value pairs

The key/value pairs can be set programmatically as

etlConfig.setValue('name', value)

The value can be an object with any data type, including number, boolean, Java class, etc.

The value of the key/value pair can be accessed programmatically as

etlConfig.getValue('name')

Access to etlConfig

Variable etlConfig is directly accessible within:

• Mapping transformations

• Scripting transformations

• The dedicated Scripting Flow

var param = etlConfig.getValue('name');

Preprocessors (e.g., Format Preprocessor, HTTP Connection Preprocessor, etc.) do not have access to etlConfig. In these cases, use:

var etlConfig = com.toolsverse.config.SystemConfig.instance().getEtlThreadContext().getData();

var param = etlConfig.getValue('name');

How Etlworks Resolves {tokens} at Runtime

When Etlworks encounters placeholders in the form {varname}, it resolves them based on the execution context. The resolution rules differ slightly depending on where the tokens appear.

In connections and transformations

When replacing {tokens} inside connection properties, format settings, or transformation configuration values, Etlworks evaluates them in the following order:

  1. Replace tokens using static variables ({app.data}).

  2. If tokens remain, replace them using global variables bound to the current ETL thread (thread-local globals).

  3. If tokens remain, replace them using global variables not bound to the current thread (regular globals).

  4. Flow variables and key-value pairs stored in flow storage are not used for token resolution in this context.

In SQL statements and code scripts (JavaScript, Python, etc.)

When replacing {tokens} inside SQL scripts or code scripts:

  1. Replace tokens using flow variables.

  2. If tokens remain, replace them using global variables bound to the current ETL thread.

  3. If tokens remain, replace them using global variables not bound to the current thread.

Thread Safety of Variables in Parallel Execution

Etlworks can run many parts of a flow in parallel, including parallel steps in the nested flow, parallel loops, parallel source-to-destination transformations, parallel wildcard transformations, and parallel file operations. Each parallel execution type uses its own thread pool. The default limit is 5 active threads per pool, though this can be changed.

Because flows may run in parallel threads, different types of variables behave differently when accessed and modified at runtime.

Thread safety of Global variables

Etlworks maintains two containers for global variables: a regular container and a thread-safe container.

When setting global variables programmatically inside code that may run in parallel, developers must use the thread-safe container:

com.toolsverse.config.SystemConfig.instance().addPropertyInCurrentEtlThread(key, value);

Parallel loops use this automatically. 

When reading global variables programmatically, developers must be aware of whether they want thread-local values:

var props = com.toolsverse.config.SystemConfig.instance().getContextProperties();

var someValue = props.get('name');

or the shared non-thread-safe values:

var someValue = com.toolsverse.config.SystemConfig.instance().getProperties().get('name')

Thread safety of Flow variables

Flow variables are inherently thread-safe. Each parallel execution receives its own copy of the flow object, and therefore its own independent set of flow variables. Updates inside one thread do not affect flow variables in another thread.

Thread safety of Key-value pairs in flow storage

Key-value pairs set through etlConfig.setValue are thread-safe in terms of concurrent access, but they are shared across all threads. Only one instance of each key exists for the entire nested flow hierarchy. Multiple threads can read and write these values, but developers should not expect isolation. A value changed in one thread may be visible in another, which may or may not be desirable depending on the flow design.

Related articles