Link Search Menu Expand Document

Functions glossary

All Firebolt functions in alphabetical order.

Name Function Type
ABS Calculates the absolute value of an input value. Numeric
ACOS Calculates the arc cosine of a value. Returns NULL if the input value is higher than 1. Numeric
ALL_MATCH Returns 1 (true) when the condition evaluated on all elements of an array evaluate to true. Returns 0 (false) when any one comparison evaluates to false. Lambda
ANY_MATCH Returns 1 if at least one of the elements of an array matches the results of the function provided. Otherwise returns 0. Lambda
ANY_VALUE (ANY) Returns a single arbitrary value from the specified column. Aggregation
APPROX_COUNT_DISTINCT Counts the approximate number of unique or not NULL values. Aggregation
APPROX_PERCENTILE Returns an approximate value for the specified percentile based on the range of numbers returned by the input expression. Aggregation
ARRAY_AGG Concatenates input values into an array. Aggregate array
ARRAY_CONCAT Combines one or more arrays that are passed as arguments. Array
ARRAY_COUNT Counts the number of elements in a boolean array for which function(array[i]) evaluates to true, if a function is provided. If not provided, counts the number of elements in the array that evaluate to true. Lambda
ARRAY_COUNT_GLOBAL Returns the number of elements in the array column accumulated over all rows. Aggregate array
ARRAY_CUMULATIVE_SUM Returns an array of partial sums of elements from the source array (a cumulative sum). If a function input is provided, the values of the array elements are converted by this function before summing. Lambda
ARRAY_DISTINCT Returns an array containing only the unique elements of the given array. Array
ARRAY_FILL Scans through the given array from the first to the last element and replaces array[i] with array[i - 1] if the input function returns 0. Lambda
ARRAY_FIRST Returns the first element in the given array for which the given function returns something other than 0. Lambda
ARRAY_FIRST_INDEX Returns the index of the first element in the indicated array for which the given function returns something other than 0. Index counting starts at 1. Lambda
ARRAY_INTERSECT Evaluates all arrays that are provided as arguments and returns an array of any elements that are present in all the arrays. Array
ARRAY_JOIN (ARRAY_TO_STRING) Concatenates an array of string elements using an optional delimiter. Array
ARRAY_MAX Returns the maximum element in an array. Array
ARRAY_MAX_GLOBAL Returns the maximum element from all the array elements in each group. Aggregate array
ARRAY_MIN Returns the minimum element in an array. Array
ARRAY_MIN_GLOBAL Returns the minimum element taken from all the array elements in each group. Aggregate array
ARRAY_REPLACE_BACKWARDS Scans an array from the last to the first element and replaces each of the elements in that array with array[i + 1] if the specified function returns 0. The last element of the array is not replaced. Lambda
ARRAY_REVERSE Returns an array of the same size and type as the original array, with the elements in reverse order. Array
ARRAY_SLICE Returns a slice of the array based on the indicated offset and length. Array
ARRAY_SORT Returns the elements of the input array in ascending order. If the argument function is provided, the sorting order is determined by the result of applying the function on each element of the array. Lambda
ARRAY_SUM Returns the sum of elements of the input array. If the argument function is provided, the values of the array elements are converted by this function before summing. Lambda
ARRAY_SUM_GLOBAL Returns the sum of elements in the array column accumulated over the rows in each group. Aggregate array
ARRAY_UNIQ Returns the number of different elements in the array if one argument is passed. If multiple arguments are passed, returns the number of different tuples of elements at corresponding positions in multiple arrays. Array
ASIN Calculates the arcsine of a value. Returns NULL if the input value is higher than 1. Numeric
ATAN Calculates the arc tangent of the real number returned by the specified expression. Numeric
ATAN2 Two-argument arc tangent function. Calculates the angle, in radians, between the specified positive x-axis value and the ray from the origin to the point (y,x). Numeric
AVG Calculates the average of an expression. Aggregation
AVG OVER Returns the average value within the requested window. Window
BTRIM Removes all occurrences of optionally specified characters from both sides of a source string. If no trim parameter is specified, all occurrences of common whitespace (ASCII Decimal 32) characters from both sides of the specified source string are removed. String
CASE Conditional expression similar to if-then-else statements. Conditional & miscellaneous
CAST Converts data types into other data types based on specified parameters. Conditional & miscellaneous
CBRT Returns the cubic-root of a non-negative numeric expression. Numeric
CEIL (CEILING) Returns the smallest value that is greater than or equal to the input value. Numeric
CHECKSUM Calculates a hash value known as a checksum operation on a list of arguments. Aggregation
CITY_HASH Takes one or more input parameters of any data type and returns a 64-bit non-cryptographic hash value. Conditional & miscellaneous
COALESCE Checks from left to right for the first non-NULL argument found for each entry parameter pair. Conditional & miscellaneous
CONCAT or || Concatenates the strings listed in the input without a separator. String
CONTAINS Returns 1 if a specified argument is present in the array, or 0 otherwise. Array
COS Trigonometric function that calculates the cosine of a specific value. Numeric
COT Calculates the cotangent. Numeric
COUNT Counts the number of rows or not NULL values. Aggregation
COUNT OVER Count the number of values within the requested window. Window
CUME DIST Calculates the relative rank (cumulative distribution) of the current row in relation to other rows in the same partition within an ordered data set, as ( rank + number_of_peers - 1 ) / ( total_rows ) where rank is the current row’s rank within the partition, number_of_peers is the number of row values equal to the current row value (including the current row), and total_rows is the total number of rows in the partition. The return value ranges from 1/(total_rows) to 1. Window
CURRENT_DATE Returns the current (local) date in the time zone specified in the session’s time_zone setting. Date & time
CURRENT_TIMESTAMP Returns the current (local) timestamp in the time zone specified in the session’s time_zone setting. Date & time
DATE_ADD Calculates a new date or timestamp by adding or subtracting a specified number of time units from an indicated expression. Date & time
DATE_DIFF Calculates the difference between the start and end date by the indicated unit. Date & time
DATE_TRUNC Truncates a date or timestamp value to the selected precision. Date & time
DECODE Decode binary data from a string. Binary
DEGREES Converts a value in radians to degrees. Numeric
DENSE_RANK Rank the current row within the requested window. Window
ELEMENT_AT Returns the element at a location index from the given array. Indexes in an array begin at position 1. Array
ENCODE Encode binary data into a string. Binary
EXP Returns the REAL value of the constant e raised to the power of a specified number. Numeric
EXTRACT Retrieves the time unit from a date or timestamp value. Date & time
EXTRACT_ALL Extracts fragments within a string that match a specified regex pattern. String fragments that match are returned as an array of strings. String
FILTER Returns an array containing the elements from the input array for which the given Lambda function returns something other than 0. The function can receive one or more arrays as its arguments. When multiple arrays are provided to the function, the function will evaluate the current elements from each array as its parameter. Lambda
FIRST_VALUE Returns the first value evaluated in the specified window frame. If there are no rows in the window frame, returns NULL. Window
FLATTEN Converts an array of arrays into a flat array. For every element that is an array, this function extracts its elements into the new array. Array
FLOOR Returns the largest round number that is less than or equal to the input value. Numeric
GEN_RANDOM_UUID Returns a version 4 universally unique identifier (UUID) according to RFC-4122. String
GENERATE_SERIES Generates a single rowset of values from start to stop, with a step size of step - a table-valued function. Conditional & miscellaneous
HLL_COUNT_DISTINCT Counts the approximate number of unique or not NULL values, to the precision specified. Aggregation
IFNULL Compares two expressions. Returns the first input expression if it’s non-NULL, otherwise returns the second. Conditional & miscellaneous
ILIKE Allows matching of strings based on comparison to a pattern, case-insensitively. String
INDEX_OF Returns the index position of the first occurrence of the element in the array (or 0 if not found). Array
JSON_EXTRACT Takes an expression containing a JSON document, a JSON pointer expression, and an expected data type parameter. If the key specified using the JSON pointer expression exists, and its type conforms with the expected data type parameter, returns the value of the data type specified. Otherwise, returns NULL. Semi-structured data
JSON_EXTRACT_ARRAY_RAW Returns a string representation of a JSON array pointed by the supplied JSON pointer. Semi-structured data
JSON_EXTRACT_KEYS Returns an array of strings containing the keys at the nesting level indicated by the specified JSON pointer. Semi-structured data
JSON_EXTRACT_RAW Returns a string representation of the scalar or sub-object under the key indicated by the specified JSON pointer if the key exists. Semi-structured data
JSON_EXTRACT_VALUES Returns an array of string values from a JSON document using the key location specified by the specifed JSON pointer. Semi-structured data
LAG Returns the value of the input expression at the given offset before the current row within the requested window. Window
LEAD Returns values from the row after the current row within the requested window. Window
LENGTH Calculates the length of the input string. String
LENGTH (array) Returns the length of (number of elements in) the given array. Array
LIKE Allows matching of strings based on comparison to a pattern, case-sensitively. String
LOCALTIMESTAMP Returns the current local timestamp in the time zone specified in the session’s time_zone setting. Date & time
LOG Returns the common (base 10) logarithm of a numerical expression, or the logarithm to an arbitrary base if specified as the first argument. Numeric
LOWER Converts the input string to lowercase characters. String
LPAD Adds a specified pad string to the start of the string repetitively up until the length of the resulting string is equivalent to an indicated length. String
LTRIM Removes all occurrences of optionally specified characters from the left side of a source string. If no trim parameter is specified, all occurrences of common whitespace (ASCII Decimal 32) characters from the left side of the specified source string are removed. String
MATCH Checks whether the input expression matches the specified regular expression pattern, which is a RE2 regular expression. Returns 0 if it doesn’t match, or 1 if it matches. String
MATCH_ANY The same as MATCH, but searches for a match with one or more more regular expression patterns. Returns 0 if none of the regular expressions match and 1 if any of the patterns match. String
MAX Calculates the maximum value of an expression across all input values. Aggregation
MAX OVER Returns the maximum value within the requested window. Window
MAX_BY Returns the value of the specified input column at the row with the maximum value in the specified value column. Aggregation
MEDIAN Calculates an approximate median for a given column. Aggregation
MIN Calculates the minimum value of an expression across all input values. Aggregation
MIN OVER Returns the maximum value within the requested window. Window
MIN_BY Returns the value of the specified input column at the row with the minimum value in the specified value column. Aggregation
MOD Calculates the remainder after dividing two values. Numeric
NTH_VALUE Returns the value evaluated of the nth row of the specified window frame (starting at the first row). If the specified row does not exist, NTH_VALUE returns NULL. Window
NTILE Divides an ordered data set equally into the number of buckets specified by the argument value. Buckets are sequentially numbered 1 through the argument value. Window
NULLIF Compares two expressions. Returns NULL if the expressions are equal. Returns the result of the first if they are not equal. Conditional & miscellaneous
PERCENT_RANK Calculates the relative rank of the current row within an ordered data set, as ( rank - 1 ) / ( rows - 1 ) where rank is the current row’s rank within the partition, and rows is the number of rows in the partition. PERCENT_RANK always returns values from 0 to 1 inclusive. The first row in any set has a PERCENT_RANK of 0. Window
PERCENTILE_CONT Calculates a percentile, assuming a continuous distribution of values of the input expression defined. Results are interpolated, rather than matching any of the specific column values. Aggregation
PERCENTILE_CONT OVER Calculates a percentile over a partition, assuming a continuous distribution of values defined. Results are interpolated, rather than matching any of the specific column values. Window
PERCENTILE_DISC Returns a percentile for an ordered data set. The result is equal to a specific column value, the smallest distributed value that is greater than or equal to the percentile value specified. Aggregation
PERCENTILE_DISC OVER Returns a percentile over a partition for an ordered data set. The result is equal to a specific column value, the smallest distributed value that is greater than or equal to the percentile specified. Window
PI Calculates π as a REAL value. Numeric
POW (POWER) Returns a number raised to the specified power. Numeric
RADIANS Converts degrees to radians as a REAL value. Numeric
RANDOM Returns a pseudo-random unsigned value greater than 0 and less than 1 of type DOUBLE PRECISION. Numeric
RANK Rank the current row within the requested window with gaps. Window
REGEXP_EXTRACT Returns the first match of a pattern within the input expression. If the pattern does not match, returns NULL. String
REGEXP_EXTRACT_ALL Returns an array that contains all matches of a pattern within the given input expression. If the pattern does not match, returns an empty array. String
REGEXP_LIKE Checks whether a text pattern matches a regular expression string. Returns 0 if it doesn’t match, or 1 if it matches. String
REGEXP_MATCHES Returns an array that contains either the match or all defined subgroups of the first match of the regular expression pattern. Returns an empty array if the pattern does not match. String
REGEXP_REPLACE Matches a pattern in the input string and replaces the first matched portion (from the left) with the specified replacement. String
REPEAT Repeats the provided string a requested number of times. String
REPLACE Replaces all occurrences of the given pattern substring within the input expression with a replacement substring. String
REVERSE Returns a string of the same size as the original string, with the elements in reverse order. String
ROUND Rounds a value to a specified number of decimal places. Numeric
ROW_NUMBER Returns a unique row number for each row within the requested window. Window
RPAD Adds a specified pad string to the end of the string repetitively up until the length of the resulting string is equivalent to an indicated length. String
RTRIM Removes all occurrences of optionally specified characters from the right side of a source string. If no trim parameter is specified, all occurrences of common whitespace (ASCII Decimal 32) characters from the right side of the specified source string are removed. String
SIGN Returns the sign of a number according to the table below. Numeric
SIN Trigonometric function that calculates the sine of a provided value. Numeric
SPLIT This function splits a given string by a given separator and returns the result in an array of strings. String
SPLIT_PART Divides a string based on a specified delimiter into an array of substrings. The string in the specified index is returned, with 1 being the first index. If the string separator is empty, the string is divided into an array of single characters. String
SQRT Returns the square root of a non-negative numeric expression. Numeric
STDDEV_SAMP Computes the standard deviation of a sample consisting of a numeric expression. Aggregation
STRPOS Returns the position (in bytes) of the substring found in the string, starting from 1. The returned value is for the first matching value, and not for any subsequent valid matches. In case the substring does not exist, functions will return 0. String
SUBSTRING, SUBSTR Returns a substring starting at the character indicated by the start index and including the number of characters defined. Character indexing starts at index 1. String
SUM Calculates the sum of an expression. Aggregation
SUM OVER Calculate the sum of the values within the requested window. Window
TAN Calculates the tangent. Numeric
TO_CHAR Converts a date or timestamp value to a formatted string. Date & time
TO_TIMESTAMP Converts a string to timestamp with time zone. Date & time
TRANSFORM Returns an array by applying the specified on each element of the input array. Lambda
TRIM Removes all specified characters from the start, end, or both sides of a string. By default removes all consecutive occurrences of common whitespace (ASCII character 32) from both ends of a string. String
TRUNC Returns the rounded absolute value of a numeric value. The returned value will always be rounded to less than the original value. Numeric
TRY_CAST Converts data types into other data types based on the specified parameters. If the conversion cannot be performed, returns a NULL. Conditional & miscellaneous
UPPER Converts the input string to uppercase characters. String
URL_DECODE Decodes percent-encoded characters and replaces them with their binary value. String
URL_ENCODE Encodes all characters that are not unreserved using percent-encoding. String
VERSION Returns the version number information for the Firebolt engine. Conditional & miscellaneous