ETL: Functions Reference

Complete reference of all built-in functions in Integrate.io ETL expressions, covering string, math, date, hash, collection, and API 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 an 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.
ToDateOrNull	Parse a datetime string using a single format; returns null when the input is missing or does not match.
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.

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

Name	Description
AddDuration	Returns the result of a datetime_field plus a duration_field.
AddToDate	Adds a signed amount of a calendar unit (years, months, days, hours, minutes, seconds) to a datetime value.
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 an 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).

Name	Description
JsonExtract	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 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’.

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

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.

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

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.
FUZZY_MATCH	Returns a 0–100 similarity score between two strings using Levenshtein, Jaro-Winkler, or Soundex.
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.
NormalizeDate	Normalizes a date string to ISO 8601 format.
NormalizeLanguage	Normalizes a language name or code to an ISO 639 language code.
NormalizePhone	Normalizes a phone number string to E.164 format.
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.
INITCAP	Capitalizes the first letter of each word in a string and lowercases the rest. Words are delimited by whitespace, hyphen, apostrophe, or period.
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)

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

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.

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

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, based 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.