ETL: Using Expressions

Learn how to write and use expressions in Integrate.io ETL pipelines. Build formulas with functions, operators, and field references in components.

• Select fields
• Filter conditions
• Package variables

You can use the expression editor to write expressions. You can also directly enter the expression in the field, without using the editor.

To use the expression editor:

1. Open the required component (from one of the components listed above).

2. In the field you wish to edit, click the edit icon.

3. Type in an expression.

4. A list of field names (available in this component), functions and data types appears.

5. Scroll to the required item and click to add it to the expression.

Read more:

• Data Types
• Nulls
• Functions
• Operators
• Casting
• Conditions

Data Types

You can use the following data types in expressions.

Data Type	Description	Notes/Example
Int	32 bit integer	integer not larger than 2**31 -1. Example: 10
Long	64 bit integer	integer that can be larger than 2**31 -1. Example: 10L or 10l
Float	32 bit floating point	Example: 10.5F or 10.5f or 10.5e2f or 10.5E2F
Double	64 bit floating point	Example: 10.5 or 10.5e2 or 10.5E2
Chararray (String)	Character array	Any character string. Example: ‘hello world’
Binary	Byte array
Boolean	true or false	Example: true/false (case insensitive)
Datetime	Date-time value	ISO 8601 format YYYY-MM-DDThh:mm:ss[.mmm]. Example: 1970-01-01T00:00:00.000+00:00
Tuple	An ordered set of fields	(field1, field2). Example: (19,2)
Bag	A collection of tuples	{(field1, field2), (field1, field2)} . Example: {(19,2), (18,1)}
Map	A set of key value pairs	[key#value]. Example: [integrate.io ETL#rocks]

If you want to convert one data type to another, you can cast it, as long as the conversion is supported. For example, suppose you have an integer field, myint, which you want to convert to a float. You can cast this field from int to float using this syntax: (float)myint. See the Cast Operators section below for more information.

Nulls

Null values represent a value that is unknown or non-existent. Note that is different than an empty string and a null string is not equal to an empty string.

Nulls can occur naturally in the data source or can be the result of an operation, such as division by zero or outer join.

A null value in expression data has these impacts:

Operator	Impact
Comparison or arithmetic operator	If either subexpression is null, the result is null.
is null operator	If the tested value matches the condition, returns true; otherwise, returns false.
Bincond	If the expression results to a null value, bincond returns null,

Null values can be converted to a data type, just like any other expression. For example, int(null) casts to integer.

Functions

To view full list of functions click here.

You can use functions in components to further manipulate the results output by a component. Functions can be added to any component field that can be edited directly (not just selected from a dropdown list).

The best method to add functions is by using the expression editor, which provides onscreen information on functions and performs validation on the completed expression.

These functions are available to transform the field. Use a function that matches the data type of the field. Note the following when using the function.

• Function names are case sensitive and therefore have to be entered exactly as shown in the table.
• Place the cursor at the start of the field you want to apply the function and then select the function from the list or enter the function name directly. Then add parentheses and additional parameters as shown in the syntax.

Operators

Arithmetic operators are applied to any numeric fields and constants to derive a numeric expression. Available operators are:

Operator	Symbol	Notes/Example
Addition	+	Field1 + Field2 + 10
Subtraction	-	field1 - field2
Multiplication	*	field1 * 5
Division	/	field1 / field2
Modulo	%	Returns the remainder of a number divided by another number. Example: field1%5

There are special String functions to derive a string from a string expression. Examples of operators are:

Operator	Function Name	Notes/Example
Concatenation	CONCAT	CONCAT(fieldl1, field2)
Substring	SUBSTRING	SUBSTRING('California', 4, 7)

Comparison operators are used in conditions:

Operator	Symbol	Notes/Example
Equal	==	Field1==Field2
Not equal	!=	field1!=field2
Greater than	>	field1>5
Less than	<	field1<6
Greater than or equal to	>=	field1>=5
Less than or equal to	<=	field1<=5
pattern matching	matches	Expression matches a regular expression Example: field1 matches '.*data.*'

Null operators are used in conditions:

Operator	Symbol	Notes/Example
expression is a null value	is null	field1 is null
expression is not a null value	is not null	field1 is not null

Boolean operators are applied to a condition to derive a different result. Available operators are:

Operator	Symbol	Notes/Example
AND	and	field1==5 and field2>20
OR	or	field1==5 or field2>20
NOT	not	field1==5 and not field2>20

Dereference operators are applied to complex data types to extract values from them:

Operator	Symbol	Notes/Example
Tuple derefence	tuple.$0	Returns fields by ordinal number (starts at 0). For example: STRSPLIT('Mary had a little lamb',' ').$1 returns had
Map dereference	map#‘key’	Returns the value for the key. If the key does not exist, returns empty string. For example: QueryStringToMap('https://someurl/?origin=135&id=321')#'id'

Cast Operators

Cast operators are used to cast or convert data from one data type to another, as long as conversion is supported. For example, suppose you have an integer field, myint, which you want to convert to a float. You can cast this field from int to float using (float)myint.

These are the cast conversions that are supported. Numeric includes the numeric data types: integer, long, float and double.

Data Type	To Data Type
int	Numeric, Chararray
long	Numeric, Chararray
float	Numeric, Chararray
double	Numeric, Chararray
chararray	Numeric, Boolean
binary	All
boolean	Chararray

Implicit casts to convert data to the target data type are performed where the cast conversion is supported. In the statement field1+1, field1 is converted to integer, if that conversion is supported.

In some cases there are functions to cast that data type. These functions are used to cast a datetime field to Chararray (string) or a chararray to datetime:

• ToDate
• ToDateCustomFormats
• ToString

In addition to the string datetime casts, these functions are used to cast a string to one of the numeric formats:

• ParseDoubleOrDefault
• ParseFloatOrDefault
• ParseIntOrDefault
• ParseLongOrDefault

Conditions

Conditions can be specified in expressions using either:

Case

Specifies outcomes based on either the value of an expression or based on conditions.
The syntaxes are:

CASE expression  
  WHEN value then return\_value  
  WHEN ....  
  ELSE return\_value  
ENDCASE   
  WHEN condition THEN return\_value  
  WHEN ....  
  ELSE return\_value  
END

Examples:

First syntax example:

CASE City  
  WHEN 'Paris' THEN 'Baguette'  
  WHEN 'London' THEN 'Fish and chips'  
  ELSE 'Coffee'  
END

If the value of the field City is “Paris” the above example returns the string “Baguette”.
If the value of the field City is “London” the above example returns the string “Fish and chips”.
Any other case, it returns “Coffee”.

Second syntax example:

CASE  
  WHEN A>B THEN 'A is greater than B'  
  WHEN A<B THEN 'B is greater than A'  
  ELSE 'A and B are equal'  
END

If the variable A is larger than B, the string “A is greater than B” is returned.
If the variable B is larger than A, the string “B is greater than A” is returned.
Otherwise, the string “A and B are equal” is returned.

Note:

If a WHEN condition compares a value to NULL, NULL is returned and the following conditions are ignored. Consider using COALESCE to eliminate NULL in a field used in a WHEN condition.

Bincond

Specifies binary outcomes based on the value of an expression, but is more limited and not as readable as the Case statement. The syntax is:

( expression   ?   true result  :   false result  )

Example:

( 10 < 100   ?   'funny but true'  :  'sad and wrong'  )

Returns the string: “funny but true”.

Note:

If an expression compares a value to NULL, NULL is returned.