Articles in this section

Variables in Etlworks

  • Updated
Follow

There are 3 types of variables in Etlworks.

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 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

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')

Related articles

Comments

0 comments

Please sign in to leave a comment.