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.
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. |
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 |
xplenty_rb.bags_difference | Returns the difference between two bags. The result is the items in bag2 that do not appear in bag1. |
xplenty_rb.bags_intersect | Returns the intersection between two bags. The result is the items that appear in both bag1 and bag2. |
xplenty_rb.bags_union | Returns the union of two bags. The result is the distinct items that appear in either bag1 or bag2. |
xplenty_rb.concat_bag | Returns a string of the concatenated values in an input bag (array) |
xplenty_rb.transform_bag | Transforms a bag (array) of values into a bag of other values using the lambda expression. The lambda expression must be Ruby compliant. |
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). |
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'. |
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. |
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) |
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 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 |
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 |
xplenty_rb.transform | Transforms an input value using a Ruby compliant lambda expression. |
CSVSPLIT | Splits a CSV string into a tuple around matches of a field delimiter. |