(888) 884 6405
Press Enter to see full results

ETL & Reverse ETL
Knowledge Base

Integrate.io ETL Functions

You can use functions in components to further manipulate the results output by a component. See Expression Editor for details on adding functions to an expression. 

The following table lists all available functions. To see detailed information on each function, click the function name.

Cast & Parse Functions
Name Description
ByteArrayToString Converts a byte array to a string based on the given encoding.
GetBytes Returns a byte array for a string input.
ParseDoubleOrDefault Attempts to convert a string representation of a number to a double-precision numeric equivalent. If successful, returns the string as a double numeric value, otherwise returns the default value.
ParseFloatOrDefault Attempts to convert a string representation of a number to a floating-precision numeric equivalent. If successful, returns the string as a double numeric value, otherwise returns the default value.
ParseIntOrDefault Attempts to convert a string representation of a number to a integer-precision numeric equivalent. If successful, returns the string as a double numeric value, otherwise returns the default value.
ParseLongOrDefault Attempts to convert a string representation of a number to a long-precision numeric equivalent. If successful, returns the string as a double numeric value, otherwise returns the default value.
ParseDate Convert input datetime string to datetime data type, using the first format that matches the string.
ToDate Convert datetime string to datetime data type
ToDateCustomFormats Convert input that matches one of multiple formats to datetime data type.
ToHexString Returns a string representation of the integer argument in base 16
ToString Convert datetime value to string data type
ToJson Returns the input as a JSON string.
Complex Data Types Functions
Name Description
BagToString Concatenates a bag into a string.
BagToTuple Flattens a bag into a tuple. Once the bag is flattened into a tuple, items can be extracted by referencing tuple items.
IsEmpty Returns true if the input map or bag is empty, false if the input contains items, or null if the input is null.
MapToBag Transforms a hash map to a bag of hash maps where each map contains two keys: key and value.
MapToQueryString Converts a map to a URL query string (key1=value1&key2=value2).
TOBAG Converts one or more expressions to type bag
TOMAP Converts key/value expression pairs to type map
TOTUPLE Converts one or more expressions to type tuple
Datetime Functions
Name Description
AddDuration Returns the result of a datetime_field plus a duration_field.
ClockTime For each record, returns the datetime value when the function is called.
CurrentTime Returns the datetime value when a job was submitted. Use ClockTime() to get a value per record in the set.
DaysBetween Returns the number of days between the end_datetime and the start_datetime?.
GetDay Returns the day of a month from a datetime field.
GetHour Returns the hour of a day from a datetime field.
GetMilliSecond Returns the millisecond of a second from a datetime field.
GetMinute Returns the minute of a hour from a datetime field.
GetMonth Returns the month of a year from a datetime field.
GetSecond Returns the second of a minute from a datetime field.
GetWeek Returns the week of a week year from a datetime field.
GetWeekYear Returns the week year from a datetime field.
GetYear Returns the year from a datetime field.
HoursBetween Returns the number of hours between the end_datetime and the start_datetime?.
IntervalIntersection Returns the duration in milliseconds of the overlap between all intervals in the function's arguments.
MilliSecondsBetween Returns the number of milliseconds between the end_datetime and the start_datetime?.
MinutesBetween Returns the number of minutes between the end_datetime and the start_datetime?.
MonthsBetween Returns the number of months between the end_datetime and the start_datetime?.
SecondsBetween Returns the number of seconds between the end_datetime and the start_datetime.
SubtractDuration Returns the result of a DateTime object minus a Duration object.
SwitchTimeZone Changes a datetime value's time zone from the value's original time zone to the time zone given in tz_string and returns the value.
ToMilliSeconds Returns the number of milliseconds elapsed since 1970-01-01T00:00:00Z for a datetime value.
ToUnixTime Returns the Unix Time for a datetime value.
WeeksBetween Returns the number of weeks between the end_datetime and the start_datetime
YearsBetween Returns the number of years between the end_datetime and the start_datetime.


ISO 8601 String Functions

Name Description
DateDiffDays Returns the number of days between the end_datetime and the start_datetime.
DateDiffHours Returns the number of hours between the end_datetime and the start_datetime.
DateDiffMinutes Returns the number of minutes between the end_datetime and the start_datetime.
DateDiffMonths Returns the number of months between the end_datetime and the start_datetime.
DateDiffSeconds Returns the number of seconds between the end_datetime and the start_datetime.
DateDiffYears Returns the number of years between the end_datetime and the start_datetime.
IsISODatetime Checks if the datetime_field is in ISO 8601 date time format.
ISOAddDuration Adds the value represented by the duration field to the datetime_field.
ISODatetimeToString Convert datetime string in ISO format to JDBC timestamp string format
ISODatetimeToTimestampFormat  Convert datetime string in ISO format to JDBC timestamp string format
ISOSubtractDuration Subtracts the value represented by the duration field from the datetime_field.
ISOToDay Resets the ISO datetime string to the beginning of the day specified in the string.
ISOToHour Resets the ISO datetime string to the beginning of the hour specified in the string.
ISOToMinute Resets the ISO datetime string to the beginning of the minute specified in the string.
ISOToMonth Resets the ISO datetime string to the beginning of the month specified in the string.
ISOToSecond Resets the ISO datetime string to the beginning of the second specified in the string.
ISOToWeek Resets the ISO datetime string to the beginning of the week (Monday) specified in the string.
ISOToYear Resets the ISO datetime string to the beginning of the year specified in the string.
StringToISODatetime Converts a non-ISO datetime string whose structure is defined by a format string into an ISO 8601 format.
TimestampFormatToISODatetime Convert datetime string in JDBC timestamp string format to ISO format


IP to Location Functions

Name Description
CityNameFromIP Extracts the city name from a given IPv4 string (dot-decimal notation).
ContinentCodeFromIP Extracts the continent code from a given IPv4 string (dot-decimal notation).
ContinentNameFromIP Extracts the continent name from a given IPv4 string (dot-decimal notation).
CountryCodeFromIP Extracts the country code (ISO-3166-1 alpha2) from a given IPv4 string (dot-decimal notation).
CountryNameFromIP Extracts the country name from a given IPv4 string (dot-decimal notation).
LatitudeFromIP Extracts the latitude from a given IPv4 string (dot-decimal notation).
LongitudeFromIP Extracts the longitude from a given IPv4 string (dot-decimal notation).
RegionCodeFromIP Extracts the two character ISO-3166-2 (US or Canada) or FIPS 10-4 code for the state/region from a given IPv4 string (dot-decimal notation).
RegionNameFromIP Extracts the region, state (US) or province (Canada) from a given IPv4 string (dot-decimal notation).
JSON Functions
Name Description
JsonExtract Extracts the value referenced by  jsonpath-like expression json_path from a given JSON json string (a string containing JSON) and returns the result as a JSON string.
JsonExtractScalar Extracts the value referenced by JSONPath-like expression json_path from a given JSON string (a string containing JSON) and returns the result as a string
JsonStringToBag Converts a string field that contains a json array to a bag (array).
JsonStringToMap Converts a string field that contains a Json object to a map (key:value pairs). From   the map you can extract fields using the notation map#'fieldname'.
Query Functions
Name Description
ExecuteBQInsert Starts a query job in Google BigQuery and writes the query results to a destination table.
ExecuteSqlBoolean Execute a SQL query that returns a scalar boolean value.
ExecuteSqlDateTime Execute a SQL query that returns a scalar datetime value.
ExecuteSqlDouble Execute a SQL query that returns a scalar double value.
ExecuteSqlFloat Execute a SQL query that returns a scalar float value.
ExecuteSqlInt Execute a SQL query that returns a scalar int value.
ExecuteSqlLong Execute a SQL query that returns a scalar long value.
ExecuteSqlString Execute a SQL query that returns a scalar string value.
ExecuteSqlNonQuery Execute a SQL statement or a batch of SQL statements that modify the data or the schema (DML or DDL).
XML Functions
Name Description
XPath Extracts part of an XML document using an XPath expression.
XPathToBag Extracts part of an XML document using an XPath expression and returns a bag of all matches.
Math Functions
Name Description
ABS Returns the absolute value of a number
ACOS Returns the arc cosine of a number
ASIN Returns the arc sine of a number
ATAN Returns the arc tangent of a number
CBRT Returns the cube root of a number
CEIL Rounds the expression up to the nearest integer.
COS Returns the trigonometric cosine of a number
COSH Returns the hyperbolic cosine of a number
EXP Returns Euler's number e raised to the power of the expression
FLOOR Rounds the expression down to the nearest integer.
LOG Returns the natural logarithm of a number
LOG10 Returns the base 10 logarithm of a number
POW Returns x raised to the power of y
RANDOM Returns a pseudo-random number between 0.0 and 1.0
ROUND Rounds the expression up or down to the nearest integer according to standard math rules.
ROUND_TO Rounds the expression to a fixed number of decimal digits.
SIN Returns the trigonometric sine of a number
SINH Returns the hyperbolic sine of a number
SQRT Returns the square root of a number
TAN Returns the trigonometric tangent of a number
TANH Returns the hyperbolic tangent of a number
String Functions
Name Description
Base64Decode Decodes a Base64 string into a bytearray.
Base64DecodeToString Decodes a Base64 string into string.
Base64Encode Encodes a byte array argument into a Base64 byte array.
Base64EncodeToString Encodes a byte array argument into a string using Base64 encoding scheme.
CONCAT Concatenates a variable number of strings and returns them as one string.
ExtractQueryStringParam Extracts the value from a specified query string parameter for a url field.
ExtractSearchQuery Returns the search string from a search engine query url.
ExtractUrlPart Extracts a specified part from a URL field.
INDEXOF Returns the position of the first occurrence of a search string in a string field, when searching forward from the start index.
LAST_INDEX_OF Returns the position of the last occurrence of a search string in a string field, when searching backward from the end of the string.
LCFIRST Converts the first character in a string to lower case
Length Returns the number of characters in a string field.
LOWER Converts the characters in a string_field to lower case.
QueryStringToMap Converts a string field that contains a query string (...?key1=value1&key2=value2) to a map of key:value pairs. From the map you can extract fields using the notation map#'fieldname'.
REGEX_EXTRACT Returns the nth occurrence (specified by index) of a string that matches the regular expression.
REGEX_EXTRACT_ALL Returns a tuple with all matched groups.
REGEX_FIND_ALL Return all occurrences of strings that match a regular expression in a bag.
REPLACE In a string field, replaces all occurrences of a string with a different string.
SPRINTF Uses printf-style template to formats a set of values using the native Java formatter library.
STRSPLIT Splits a string around matches of a delimiter
STRSPLITTOBAG Splits a string into a bag around matches of a delimiter.
SUBSTRING Returns part of a string field.
TOKENIZE Generates a bag from delimited strings in string field. If you don't specify a single-character delimiter, tokenize will use any of the following characters: space, double-quote, comma, parenthesis, star
TRIM Removes leading and trailing spaces from a string field.
UCFIRST Converts the first character in a string to upper case
UPPER Converts the characters in a string_field to upper case.
URLDecode Decodes a string field containing an encoded url.
URLEncode URL-encodes a given string expression..
UUID Returns a universally unique   identifier (string)
Hash Functions
Name Description
MD2 Calculates the MD2 hash of a string
MD5 Calculates the MD5 hash of a string
SHA1 Calculates the SHA1 hash of a string
SHA256 Calculates the SHA256 hash of a string
SHA256WithRSA Sign an input string with an RSA private key using the SHA256withRSA signature algorithm. Useful with JWT authentication.
SHA384 Calculates the SHA384 hash of a string
SHA512 Calculates the SHA512 hash of a string
Encrypt / Decrypt Functions
Name Description
Encrypt AES encrypt a string using the specified AWS KMS key ARN and encryption context.
Decrypt Decrypt an Integrate.io ETL encrypted string using the specified AWS KMS key ARN and the encryption context used for the encryption.
User Agent Functions
Name Description
BrowserFamily Extract browser family from user agent string
BrowserFullName Extract browser full name (family+version) from user agent string
BrowserMajor Extract browser major version from user agent string
BrowserMinor Extract browser minor version from user agent string
BrowserPatch Extract browser patch version from user agent string
BrowserVersion Extract browser version from user agent string
DeviceFamily Extract device family from user agent string
ExtractUABrowserID Returns a browser ID for a given user agent string.
ExtractUABrowserName Returns a browser name for a given user agent string.
OsFamily Extract OS family from user agent string
OsFullName Extract OS full name (family+version) from uesr agent string
OsMajor Extract OS major version from user agent string
OsMinor Extract OS minor version from user agent string
OsPatch Extract OS patch version from user agent string
OsPatchMinor Extract OS patch minor version from user agent string
OsVersion Extract OS version from user agent string
PlatformFamily Extract platform family from user agent string
Other Functions
Name Description
CCurl Makes a REST API call and returns the response received from the server. Requests are authenticated using an Integrate.io ETL connection.
BinaryCCurl Makes a REST API call and returns the binary response received from the server. Requests are authenticated using an Integrate.io ETL connection.
CCurlWithPagination Makes one or more REST API calls and returns the responses received from all the pages requested from the server. Requests are authenticated using an Integrate.io ETL connection.
COALESCE Returns the first non-NULL argument
Curl Makes a REST API call and returns the response received from the server.
BinaryCurl Makes a REST API call and returns the binary response received from the server. Requests are made either anonymous or with basic authentication
CurlWithPagination Makes one or more REST API calls and returns the responses received from all the pages requested from the server. Requests are made either anonymous or with basic authentication.
CurlPoll Makes a REST API request continuously until a regular expression is matched or timeout is reached.
CCurlPoll Makes a REST API request continuously until a regular expression is matched or timeout is reached. Requests are authenticated using an Integrate.io ETL connection.
DistInMilesHaversine Computes the distance (in miles) between two latitude-longitude pairs using the Haversine formula, base on Apache DataFu.
Flatten For each item in a bag, flatten creates a new record containing the item and the data of all the other fields in the component.
SIZE Calculates the number of elements based on the data type.
Sleep Causes the process to sleep for a specified number of milliseconds
CSVSPLIT Splits a CSV string into a tuple around matches of a field delimiter.

See Also