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 |
| 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 |
| CSVSPLIT | Splits a CSV string into a tuple around matches of a field delimiter. |
