The following is a complete alphabetical list of all Firebolt functions:

NameFunctionType
ABSCalculates the absolute value of an input value.Numeric
ACOSCalculates the arccosine of a value in radians.Numeric
APACHE_DATASKETCHES_HLL_BUILDCreates a new Apache DataSketch HyperLogLog sketch.DataSketches
APACHE_DATASKETCHES_HLL_ESTIMATEExtracts a cardinality estimate of a single Apache DataSketch HyperLogLog sketch.DataSketches
APACHE_DATASKETCHES_HLL_MERGEMerges one or more Apache DataSketch HyperLogLog sketches.DataSketches
ARRAYS_OVERLAPReturns whether all input arrays have at least one common, non-NULL element.Array
ARRAY_ALL_MATCHReturns TRUE if the provided function evaluates to TRUE for all elements in an array, and FALSE otherwise.Lambda
ARRAY_ANY_MATCHReturns TRUE if the provided function evaluates to TRUE for any element in an array, and FALSE otherwise.Lambda
ARRAY_CONCATCombines one or more arrays provided as arguments into a single array.Array
ARRAY_CONTAINSReturns TRUE if a specified argument is present in the array, and FALSE otherwise.Array
ARRAY_COUNTCounts the number of elements in a boolean array for which the function applied to (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_DISTINCTReturns an array containing only the distinct elements of the given array.Array
ARRAY_FIRSTReturns the first element in the given array for which the given function returns TRUE.Lambda
ARRAY_FLATTENConverts a nested array of arrays into a flat array by extracting all the elements from the inner arrays into a single, continuous array.Array
ARRAY_INTERSECTFinds the intersection of the provided argument arrays.Array
ARRAY_TO_STRINGConcatenates the elements of a string array into a single string, using an optional specified delimiter between each element.Array
ARRAY_LENGTHReturns the length, or number of elements, in the given array.Array
ARRAY_MAXReturns the maximum element in an array.Array
ARRAY_MINReturns the minimum element in an array.Array
ARRAY_REVERSEReturns an array of the same size and type as the original array, with the elements in reverse order.Array
ARRAY_REVERSE_SORTReturns the elements of the input array in descending 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_SORTReturns 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_SUMReturns the sum of elements of the input array.Array
ARRAY_TRANSFORMReturns an array constructed by applying a lambda function to each element of the input arrays.Lambda
ASINCalculates the arcsine of a value in radians.Numeric
ATANCalculates the arctangent of a value in radians.Numeric
AVGCalculates the average of an expression.Aggregation
AVG OVERReturns the average value within the requested window.Window
BIT_ANDPerforms a bitwise AND operation on an integer expression.Aggregation
BIT_ORPerforms a bitwise OR operation on an integer expression.Aggregation
BIT_XORPerforms a bitwise XOR operation on an integer expression.Aggregation
BOOL_ANDReturns TRUE if all NOT NULL values in the input are TRUE.Aggregation
BOOL_ORReturns TRUE if any NOT NULL value in the input is TRUE.Aggregation
BTRIMRemoves 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
CASTConverts data types into other data types based on the specified parameters.Conditional & miscellaneous
CEILRounds an input <value> up to the nearest multiple based on the specified precision or decimal place.Numeric
COSCalculates the cosine of a value in radians.Numeric
COTCalculates the cotangent of a value in radians.Numeric
HASH_AGGCalculates a hash value across all rows based on a specified list of arguments.Aggregation
CITY_HASHTakes one or more input parameters of any data type and returns a 64-bit non-cryptographic hash value. Returns NULL if any input value is NULL.Conditional & miscellaneous
COALESCEChecks from left to right for the first NOT NULL argument found for each parameter pair.Conditional & miscellaneous
CONCAT or ||Concatenates the input strings listed in the input together without adding a separator between them.String
CONVERT_FROMConverts a binary string encoded in a specified format to sql TEXT using the database’s default encoding.Binary
CORRReturns the correlation between the values of two variables.Aggregation
COUNTCounts the number of rows or the number of NOT NULL values.Aggregation
COUNT OVERCounts the number of values within a specified window.Window
COVAR_POPReturns the population covariance between the values of two variables.Aggregation
COVAR_SAMPReturns the sample covariance between the values of two variables.Aggregation
CURRENT_ACCOUNTReturns the current account name.Session
CURRENT_DATABASEReturns the current database name. If no database is set, returns account_db.Session
CURRENT_DATEReturns the current (local) date in the time zone specified in the session’s time_zone setting.Date & time
CURRENT_ENGINEReturns the current engine name. Returns system on the system engine.Session
CURRENT_TIMESTAMPReturns the current (local) timestamp in the time zone specified in the session’s time_zone setting.Date & time
DATEConverts a TIMESTAMP, TIMESTAMPTZ, DATE or TEXT value to a DATE value.Date & time
DATE_ADDCalculates a new date or timestamp by adding or subtracting a specified number of time units from an indicated expression.Date & time
DATE_DIFFCalculates the difference between the start and end date based on the specified unit.Date & time
DATE_TRUNCTruncates a date or timestamp value to the selected precision.Date & time
DECODEDecodes binary data from a SQL expression of type TEXT into its original form.Binary
DEGREESConverts a value in radians to degrees.Numeric
DENSE_RANKRanks the current row within the specified window, ensuring that there are no gaps in the ranking sequence.Window
ENCODEEncodes binary data from its original form into a SQL expression of type TEXT.Binary
EXTRACTRetrieves the time unit from a date or timestamp value.Date & time
FIRST_VALUEReturns the first value evaluated in the specified window frame. If there are no rows in the window frame, FIRST_VALUE returns NULL.Window
FLOORRounds an input <value> down to the nearest multiple based on the specified precision or decimal place.Numeric
GENERATE_SERIESA table-valued function that generates a sequence of numeric values or dates, starting from start to stop, incremented by a step value.Table-valued
GREATESTSelects the largest value from a list of expressions.Conditional & miscellaneous
GROUPINGReturns a bitwise integer that uniquely identifies whether columns are part of an aggregation or not.Aggregation
HASHComputes a 64-bit non-cryptographic hash for one or more input values, with NULL values always producing the same hash.Conditional & miscellaneous
HLL_COUNT_BUILDCounts the approximate number of unique NOT NULL values and aggregates them into HLL++ sketches represented as the BYTEA data type.Conditional & miscellaneous
HLL_COUNT_ESTIMATEProvides a cardinality estimate of a single HLL++ sketch that was previously built using the aggregate function HLL_COUNT_BUILD.Conditional & miscellaneous
HLL_COUNT_MERGEMerges one or more HLL++ sketches that were previously built using the aggregate function HLL_COUNT_BUILD into a new sketch.Conditional & miscellaneous
IFEmulates IF-THEN-ELSE statement expression.Conditional & miscellaneous
IFNULLCompares two expressions. Returns the first expression if it is non-NULL, and otherwise returns the second expression.Conditional & miscellaneous
ILIKECase insensitive pattern of strings.String
INDEX_OFReturns the index position of the first occurrence of the element in an array, or NULL if not found.Array
IS_FINITEReturns TRUE if a value is finite, and FALSE otherwise.Numeric
IS_INFINITEReturns TRUE if a value is infinite, and FALSE otherwise.Numeric
JSON_EXTRACTTakes an expression containing a JSON document, a JSON path expression, and an optional path syntax. If the key specified using the JSON path expression exists, JSON_EXTRACT returns the sub JSON document pointed by the given JSON path. Otherwise, returns NULL.Semi-structured data
JSON_EXTRACT_ARRAYTakes an expression containing a JSON document, a JSON path expression, and an optional path syntax. If the key specified using the JSON path expression exists and its value is a JSON array, JSON_EXTRACT_ARRAY returns SQL ARRAY(TEXT) contains all JSON elements as raw text inside the JSON array pointed by the given JSON path. Otherwise, returns NULL.Semi-structured data
JSON_VALUETakes an expression containing a JSON document, removes the outermost quotes and unescapes the value, and extracts a scalar value as SQL TEXT, or NULL if a non-scalar value is selected.Semi-structured data
JSON_VALUE_ARRAYTakes an expression containing a JSON document and extracts an array of scalar values out of it to SQL ARRAY(TEXT).Semi-structured data
LEASTSelects the smallest value from a list of expressions.Conditional & miscellaneous
LENGTHReturns the length of an input string.String
LIKEPerforms case-sensitive pattern matching of strings.String
LIST_OBJECTSReturns an Amazon S3 object metadata and prefixes from the input URL up to the next forward slash (’/’).Table-valued
LNReturns the natural logarithm (base e) of a number.Numeric
LOCALTIMESTAMPReturns the current local timestamp in the time zone specified in the session’s time_zone setting.Date & time
LOGReturns the logarithm of a number using a custom base, defaulting to base 10 if no base is provided.Numeric
LOWERConverts the input string to lowercase characters.String
LPADPads a string from the left with a specified string until it reaches the specified length.String
LTRIMRemoves 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
MAXCalculates the maximum value of an expression across all input values.Aggregation
MAX_BYReturns the value of its first argument for the row that contains the maximum value of the second argument.Aggregation
MAX OVERReturns the maximum value within the requested window.Window
MAX_BYReturns the value of the specified input column at the row with the maximum value in the specified value column.Aggregation
MEDIANCalculates the median for a given column. If number of values are even, it returns the average of the two middle values.Aggregation
MINCalculates the minimum value of an expression across all input values.Aggregation
MIN_BYReturns the value of its first argument for the row that contains the minimum value of the second argument.Aggregation
MIN OVERReturns the maximum value within the requested window.Window
MODCalculates the remainder after dividing two values.Numeric
NGRAMSplits a text sequence into overlapping contiguous subsequences of a specified length.String
NTILEDivides an ordered set into a number of buckets.Window
NULLIFCompares two expressions. Returns NULL if the expressions are equal. Returns the result of the first if they are not equal.Conditional & miscellaneous
OCTET_LENGTHCalculates the length of the input string in bytes.String
PERCENT_RANKCalculates the relative rank of the current row within the requested window.Window
PERCENTILE_CONTCalculates a percentile value for a given expression.Aggregation
PIReturns π as a DOUBLE PRECISION value.Numeric
POSITIONReturns the position 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, position will return 0.String
RANDOMReturns a pseudo-random unsigned value greater than 0 and less than 1 of type DOUBLE PRECISION.Numeric
RANKRank the current row within the requested window with gaps.Window
READ_CSVReturns a table with data from specified CSV file.Table-valued
READ_PARQUETReturns a table with data from specified Parquet file.Table-valued
REGEXP_LIKEChecks whether a text pattern matches a regular expression string. Returns 0 if it doesn’t match, or 1 if it matches.String
REGEXP_LIKE_ANYChecks whether a given string matches any regular expression pattern from a specified list of patterns. Returns FALSE if it doesn’t match, or TRUE if it matches.String
REGEXP_REPLACEMatches a pattern in the input string and replaces the first matched portion (from the left) with the specified replacement.String
REPLACEReplaces all occurrences of the given pattern substring within the input expression with a replacement substring.String
ROUNDRounds a value to a specified number of decimal places.Numeric
ROW_NUMBERCalculate a unique, increasing row number for each row within the window.Window
RPADAdds 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
RTRIMRemoves 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
SESSION_USERReturns the name of the user running the current query.Session
SPLIT_PARTDivides 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 input string is returned at index 1.String
SQRTCalculates square root of a value.Numeric
STRING_TO_ARRAYSplits a given string by a given separator and returns the result in an array of strings.String
STRPOSReturns the position 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, STRPOS will return 0.String
ST_ASBINARYConverts shapes of the GEOGRAPHY data type to the Well-Known Binary (WKB) format for geographic objects.Geospatial
ST_ASEWKBConverts shapes of the GEOGRAPHY data type to the extended Well-Known Binary (EWKB) format using Spatial Reference Identifier (SRID) 4326, which corresponds to the WGS84 coordinate system.Geospatial
ST_ASGEOJSONConverts shapes of the GEOGRAPHY data type to the GeoJSON format.Geospatial
ST_ASTEXTConverts shapes of the GEOGRAPHY data type to the Well-Known Text (WKT) format.Geospatial
ST_CONTAINSThe ST_CONTAINS function determines if one GEOGRAPHY object fully contains another.Geospatial
ST_COVERSThe ST_COVERS function determines if one GEOGRAPHY object fully encompasses another.Geospatial
ST_DISTANCEThe ST_DISTANCE function calculates the shortest distance (measured as a geodesic arc) between two GEOGRAPHY objects, measured in meters.Geospatial
ST_GEOGFROMGEOJSONConstructs a GEOGRAPHY object from a GeoJSON string.Geospatial
ST_GEOGFROMTEXTConstructs a GEOGRAPHY object from a Well-Known Text (WKT) string.Geospatial
ST_GEOGFROMWKBConstructs a GEOGRAPHY object from a Well-Known Binary (WKB) byte string.Geospatial
ST_GEOGPOINTConstructs a Point in the GEOGRAPHY data type created from specified longitude and latitude coordinates.Geospatial
ST_INTERSECTSThe ST_INTERSECTS function determines whether two input GEOGRAPHY objects intersect each other.Geospatial
ST_S2CELLIDFROMPOINTReturns the S2 cell ID, which uniquely identifies the region on Earth that fully contains, or covers, a single Point GEOGRAPHY object.Geospatial
ST_XExtracts the longitude coordinate of a GEOGRAPHY Point.Geospatial
ST_YExtracts the latitude coordinate of a GEOGRAPHY Point.Geospatial
STDDEV_POPReturns the population standard deviation value.Aggregation
STDDEV_SAMPReturns the sample standard deviation value.Aggregation
SUBSTRING, SUBSTRReturns a substring starting at the character indicated by the start index and including the number of characters defined. Character indexing starts at 1.String
SUMCalculates the sum of an expression.Aggregation
SUM OVERCalculate the sum of the values within the requested window.Window
TANCalculates the tangent of a value in radians.Numeric
TO_TIMESTAMPConverts a string to timestamp with time zone.Date & time
TO_YYYYMMExtracts year and month from a DATE, TIMESTAMP, or TIMESTAMPTZ value and combines them into an integer beginning with the four-digit year followed by the two-digit month.Date & time
TO_YYYYMMDDExtracts year, month and day from a DATE, TIMESTAMP, or TIMESTAMPTZ value and combines them into an integer beginning with the four-digit year followed by the two-digit month followed by the two-digit day.Date & time
TRIMRemoves 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
TRY_CASTConverts data types into other data types based on the specified parameters. Returns a NULL if the conversion cannot be performed.Conditional & miscellaneous
UPPERConverts the input string to uppercase characters.String
URL_DECODEDecodes percent-encoded characters.String
URL_ENCODEEncodes characters that are not unreserved using percent encoding.String
VAR_POPReturns the population variance value.Aggregation
VAR_SAMPReturns the sample variance value.Aggregation
VECTOR_COSINE_DISTANCEComputes the cosine distance of two vectors.vector
VECTOR_COSINE_SIMILARITYComputes the cosine similarity of two vectors.vector
VECTOR_EUCLIDEAN_DISTANCEComputes the euclidean distance of two vectors.vector
VECTOR_INNER_PRODUCTComputes the inner product of two vectors.vector
VECTOR_MANHATTAN_DISTANCEComputes the manhattan distance of two vectors.vector
VECTOR_SQUARED_EUCLIDEAN_DISTANCEComputes the squared euclidean distance of two vectors.Array
VERSIONReturns the version number information for the Firebolt engine.Conditional & miscellaneous