Formula functions
There are three actions that accept custom formulas entered by users:
- Create a new column with a formula
- Transform columns with a formula
- Filter rows with a formula
The following set of functions can be used in the expressions accepted by any of the above three actions:
| # | Name | Description | Type signatures |
|---|---|---|---|
| 1 | IF | When condition (first argument) is true, evaluates the second argument. When condition is not true, evaluates the third argument. | (Boolean, Numeric, Numeric) -> Numeric (Boolean, Text, Text) -> Text (Boolean, Boolean, Boolean) -> Boolean (Boolean, DateTime, DateTime) -> DateTime (Boolean, JSONObject, JSONObject) -> JSONObject (Boolean, JSONArray, JSONArray) -> JSONArray |
| 2 | IS_NUMERIC | Indicates whether string value is parsable to numeric | (Text) -> Boolean |
| 3 | IS_DATETIME | Indicates whether string value is parsable to datetime of given strftime format | (Text, STRING literal) -> Boolean |
| 4 | TO_NUMERIC | Casts string values to numeric, returns null if value is not parsable | (Text) -> Numeric |
| 5 | TO_DATETIME | Casts string values to datetime taking an strftime format string as second argument. Returns null if value is not parsable. | (Text, STRING literal) -> DateTime |
| 6 | TO_TIMESTAMP | Casts string values to datetime taking a Java Datetime format string as second argument. | (Text, STRING literal) -> DateTime |
| 7 | DAY | The day of month as a number | (DateTime) -> Numeric |
| 8 | MONTH | The month of year as a number | (DateTime) -> Numeric |
| 9 | YEAR | The year as a number | (DateTime) -> Numeric |
| 10 | HOUR | The hour of the day | (DateTime) -> Numeric |
| 11 | MINUTE | The minute of the hour | (DateTime) -> Numeric |
| 12 | SECOND | The second of the minute | (DateTime) -> Numeric |
| 13 | WEEKDAY | The day of week as a number from 0 = Monday to 6 = Sunday | (DateTime) -> Numeric |
| 14 | WEEKOFYEAR | The week of year as a number from 1 to 52 | (DateTime) -> Numeric |
| 15 | QUARTER | The quarter in the year as a number from 1 to 4 | (DateTime) -> Numeric |
| 16 | DATETIME_VALUE | Returns datetime for an ISO-format string literal | (STRING literal) -> DateTime |
| 17 | WEEKDAY_NAME | Name of the day in week ("Sunday" etc.) | (DateTime) -> Text |
| 18 | MONTH_NAME | Name of the month ("January" etc.) | (DateTime) -> Text |
| 19 | WEEKDAY_NAME_ABBREV | Abbreviated name of the day in week ("Sun" etc.) | (DateTime) -> Text |
| 20 | MONTH_NAME_ABBREV | Abbreviated name of the month ("Jan" etc.) | (DateTime) -> Text |
| 21 | E | Returns the value of "e", the base of natural logarithm | () -> Numeric |
| 22 | PI | Returns the value of pi | () -> Numeric |
| 23 | RAND | Generates uniform random numbers between 0 and 1 | () -> Numeric (INT literal) -> Numeric |
| 24 | RANDN | Generates random numbers from the standard normal distribution | () -> Numeric (INT literal) -> Numeric |
| 25 | ABS | Absolute value of a number | (Numeric) -> Numeric |
| 26 | ACOS | Inverse cosine function. Computes the angle (in radians) whose cosine equals the input value. Returns null if input is out of [-1, 1] range | (Numeric) -> Numeric |
| 27 | ACOSH | Inverse hyperbolic cosine of a given input value. Returns null if input is negative. | (Numeric) -> Numeric |
| 28 | ASIN | Inverse sine function. Computes the angle (in radians) whose sine equals the input value. Returns null if input is out of [-1, 1] range | (Numeric) -> Numeric |
| 29 | ASINH | Inverse hyperbolic sine of a given value. | (Numeric) -> Numeric |
| 30 | ATAN | Arc tangent function. Computes the angle (in radians) whose tangent equals the input value. Returns null if input is out of [-1, 1] range | (Numeric) -> Numeric |
| 31 | ATANH | Inverse hyperbolic tangent of a given input value. Returns null if input is not between -1 and 1. | (Numeric) -> Numeric |
| 32 | COS | Trigonometric cosine | (Numeric) -> Numeric |
| 33 | COSH | Hyperbolic cosine | (Numeric) -> Numeric |
| 34 | CEIL | Ceiling of a number, i.e. the smallest integer not less than the input | (Numeric) -> Numeric |
| 35 | DEGREES | Computes the angle in degrees given value in radians | (Numeric) -> Numeric |
| 36 | EXP | Computes the exponential of a given value. | (Numeric) -> Numeric |
| 37 | FACTORIAL | Factorial of a given value. Fractional parts are truncated and negative input yields null. | (Numeric) -> Numeric |
| 38 | FLOOR | Floor of a number, i.e. the largest integer not greater than the input | (Numeric) -> Numeric |
| 39 | INT | Truncates a number by removing fractional parts | (Numeric) -> Numeric |
| 40 | LOG10 | Logarithm with base 10 | (Numeric) -> Numeric |
| 41 | LOG | Natural logarithm (base = Napier's constant) | (Numeric) -> Numeric |
| 42 | LOG2 | Logarithm with base 2 | (Numeric) -> Numeric |
| 43 | RADIANS | Computes the angle in radians given value in degrees. | (Numeric) -> Numeric |
| 44 | ROUND | Rounds the given input to the nearest integer. | (Numeric) -> Numeric |
| 45 | SIGN | Returns -1 for negative numbers and +1 for positive numbers | (Numeric) -> Numeric |
| 46 | SIN | Trigonometric sine | (Numeric) -> Numeric |
| 47 | SINH | Hyperbolic sine | (Numeric) -> Numeric |
| 48 | SQRT | Square root | (Numeric) -> Numeric |
| 49 | TAN | Trigonometric tangent | (Numeric) -> Numeric |
| 50 | TANH | Hyperbolic tangent | (Numeric) -> Numeric |
| 51 | ATAN2 | Angle (in radians) of a line segment in two dimensions with respect to the x-axis, with origin as one end point and the given inputs as the (y, x) coordinates. Returns a value in the range [0, 2 * pi). | (Numeric, Numeric) -> Numeric |
| 52 | POW | Raises the first value to the power of the second value. | (Numeric, Numeric) -> Numeric |
| 53 | MOD | Computes the remainder after dividing the first value by the second value. | (Numeric, Numeric) -> Numeric |
| 54 | HYPOT | Computes the hypotenuse length of a right-triangle given the lengths of its shorter sides. Equivalent to computing the magnitude of a vector in two dimensions. Together with ATAN2, this function can be used to convert two-dimensional cartesian coordinates into polar coordinates. | (Numeric, Numeric) -> Numeric |
| 55 | LENGTH | Number of characters in the string | (Text) -> Numeric |
| 56 | LOWER | Convert string to lower case | (Text) -> Text |
| 57 | UPPER | Convert string to upper case | (Text) -> Text |
| 58 | INITCAP | Change the first character in each string to upper case | (Text) -> Text |
| 59 | TRIM | Remove leading and trailing whitespaces | (Text) -> Text |
| 60 | LTRIM | Remove leading whitespaces | (Text) -> Text |
| 61 | RTRIM | Remove trailing whitespaces | (Text) -> Text |
| 62 | LEFT | First n characters of the string | (Text, Numeric) -> Text |
| 63 | RIGHT | Last n characters of the string | (Text, Numeric) -> Text |
| 64 | SUBSTRING | The part of the string starting at the given position to the end or up to the specified length. | (Text, Numeric) -> Text (Text, Numeric, Numeric) -> Text |
| 65 | FORMAT_NUMBER | Format the number to a string to specified decimal places, commas separating 1000's | (Numeric, INT literal) -> Text |
| 66 | RLIKE | Indicates whether column or value (first argument) matches the given regular expression (second argument). Second argument must be a string literal and not a column. | (Text, STRING literal) -> Boolean |
| 67 | SPLIT_DELIM | Splits strings by a literal delimiter to produce a JSON-serialized array. The delimiter is interpreted as a literal rather than as a regular expression. The delimiter can be a maximum of 32 characters long. | (Text, STRING literal) -> JSONArray |
| 68 | SPLIT_PATTERN | Splits strings by a regex pattern to produce a JSON-serialized array. The pattern is interpreted as a regex pattern rather than as a literal delimiter. The pattern can be a maximum of 256 characters long. | (Text, STRING literal) -> JSONArray |
| 69 | JSON_ARRAY_LENGTH | Computes the lengths of arrays that are JSON-serialized. Null values and invalid strings in input result in -1. | (Text) -> Numeric (JSONArray) -> Numeric |
| 70 | JSON_ARRAY_NDISTINCT | Computes the number of unique items in arrays that are JSON-serialized. Null values and invalid strings in input result in -1. | (Text) -> Numeric (JSONArray) -> Numeric |
| 71 | JSON_ARRAY_SORT | Given the input JSON-serialized array, this function sorts the contents of the array and outputs it as a new JSON-serialized array | (Text) -> JSONArray (JSONArray) -> JSONArray |
| 72 | JSON_ARRAY_UNIQUE | Given the input JSON-serialized array, this function outputs a new JSON-serialized array with the elements of the input array, but without repetitions. Output will be sorted. | (Text) -> JSONArray (JSONArray) -> JSONArray |
| 73 | JSON_ARRAY_CONTAINS | Returns whether the input JSON array contains the specified string as one of its elements. | (JSONArray, Text) -> Boolean (Text, Text) -> Boolean (JSONArray, Numeric) -> Boolean (Text, Numeric) -> Boolean (JSONArray, Boolean) -> Boolean (Text, Boolean) -> Boolean |
| 74 | JSON_ARRAY_OCCURRENCES | Returns the number of times a specified string value occurs in a JSON array. Invalid strings and null values in input result in an output of 0. | (JSONArray, Text) -> Numeric (Text, Text) -> Numeric (JSONArray, Numeric) -> Numeric (Text, Numeric) -> Numeric (JSONArray, Boolean) -> Numeric (Text, Boolean) -> Numeric |
| 75 | JSON_ARRAY_INTERSECT | Returns the elements common to first and second arrays, removing duplicates. Result will be sorted as strings. | (JSONArray, JSONArray) -> JSONArray |
| 76 | JSON_ARRAY_UNION | Returns the elements found in either the first or the second array. Duplicates will be removed and the result will be sorted as strings. | (JSONArray, JSONArray) -> JSONArray |
| 77 | JSON_ARRAY_CONCAT | Concatenates the first and second arrays, order is retained and duplicates are not removed. | (JSONArray, JSONArray) -> JSONArray |
| 78 | JSON_OBJ_KEYS | Gets the keys of the JSON object as a JSON array. | (JSONObject) -> JSONArray |
| 79 | IS NULL | A predicate/postfix operator indicating presence of null value | (Numeric) -> Boolean (Text) -> Boolean (Boolean) -> Boolean (DateTime) -> Boolean (Categorical) -> Boolean (JSONObject) -> Boolean (JSONArray) -> Boolean |
| 80 | IS NOT NULL | A predicate/postfix operator indicating absence of null value | (Numeric) -> Boolean (Text) -> Boolean (Boolean) -> Boolean (DateTime) -> Boolean (Categorical) -> Boolean (JSONObject) -> Boolean (JSONArray) -> Boolean |