Using Expressions in Integrate.io ETL

You can write expressions to transform fields or create new fields using the available functions, operators and conditions. 

Expressions can be used in the following components where user entry is allowed:

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

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]

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 drop-down 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:

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

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
END
CASE 
  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 then B, the string "A is greater than B" is returned.
If the variable B is larger then 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.