Skip to main content

All Functions

Function NameDescription
ABSComputes the absolute value of a numeric expression.
ACOSComputes the arccosine (inverse cosine) of a value in radians
AES_DECRYPTDecrypts a string produced by AES encryption.
AES_ENCRYPTEncrypts a string using AES encryption.
APPROX_COUNT_DISTINCTReturns the approximate number of unique, non-null values in a column.
APPROX_PERCENTILEComputes the approximate percentile of the given column and quantile.
ARRAYS_OVERLAPCompares whether two arrays have at least one element in common. Returns true if the arrays have one or more elements in common; otherwise returns false.
ARRAY_AGGAggregates the provided expression into an array.
ARRAY_APPENDAppends an element to the end of an array.
ARRAY_AVGReturns the average of all non-null elements of a list.
ARRAY_CATReturns a concatenation of two arrays.
ARRAY_COMPACTReturns the input array without null values.
ARRAY_CONTAINSReturns whether a list contains a given value.
ARRAY_DISTINCTGiven an input array, returns an equivalent array that includes only distinct elements.
ARRAY_FREQUENCYReturns a map of key-value pairs: keys are the unique elements in the input array and values specify how many times the keys appear in the input array.
ARRAY_GENERATE_RANGEReturns an array of integers in the specified range.
ARRAY_INSERTReturns an array that contains all of the elements from the input array as well as a new element inserted in the specified position.
ARRAY_MAXReturns the maximum value of a list.
ARRAY_MINReturns the minimum value of a list.
ARRAY_POSITIONReturns the index of the first occurrence of an element in an array.
ARRAY_PREPENDPrepends an element to the beginning of an array.
ARRAY_REMOVERemoves all elements that equal a given value from a list.
ARRAY_REMOVE_ATReturns the input array with the element at the specified position removed.
ARRAY_SIZEReturns the size of the input array.
ARRAY_SLICEReturns an array constructed from the specified subset of elements in the input array.
ARRAY_SUMReturns the sum of all non-null elements of a list.
ARRAY_TO_STRINGReturns a string of the values from the input array, with the values separated by the specified delimiter string.
ASCIIReturns the ASCII code for the first character of a string. If the string is empty, 0 is returned.
ASINComputes the arcsine (inverse sine) of a value in radians
ATANComputes the Arctangent (inverse Tangent) of a value
ATAN2Computes the Arctangent (inverse Tangent) of the ratio of its two arguments.
AVGComputes the average of a set of values.
BASE64Returns the Base64 encoding of a binary string.
BINReturns the the binary representation of an expression.
BINARY_STRINGConverts the input expression to a binary value.
BITWISE_ANDReturns the bitwise AND of two operands.
BITWISE_NOTReturns the bitwise NOT of the given operand.
BITWISE_ORReturns the bitwise OR of two operands.
BITWISE_XORReturns the bitwise XOR of two operands.
BIT_ANDReturns the bitwise AND of non-NULL input values.
BIT_LENGTHReturns the length of the bits of the input expression.
BIT_ORReturns the bitwise OR of non-NULL input values.
BOOL_ANDComputes the boolean AND of two boolean expressions. Returns TRUE if both expressions evaluate to TRUE. Returns FALSE if one or both expression(s) evaluate(s) to FALSE.
BOOL_ORComputes the boolean OR of two boolean expressions. Returns TRUE if one or both expressions evaluate to TRUE. Returns FALSE if both expressions evaluate to FALSE.
BROUNDReturns the rounded result of the numeric expression using HALF_EVEN rounding mode.
BTRIMTrims leading and trailing characters from a string.
CARDINALITYReturns the number of elements contained in the specified list or map.
CASEEvaluates a list of conditions and returns the first resulting true expression. If a true expression is not found, will return the ELSE statement, if present, or else will return NULL.
CASTConverts a value of one data type to another data type. This function behaves similarly to the TO_<data_type> (i.e. TO_TIMESTAMP) functions.
CBRTComputes the cube root of a numeric expression
CEILINGReturns the nearest equal or larger value of the input expression. Can also be called using CEIL().
CHARACTER_LENGTHReturns the length of an input string.
CHAR_LENGTHReturns the character length of the input string
CHRConverts a Unicode code point into the character that matches the input Unicode character. If an invalid code point is specified, an empty string is returned.
COALESCEEvaluates the arguments in order and returns the value of the first expression that does not contain NULL.
COL_LIKETests whether an expression column matches a pattern column. Comparisons are case-sensitive.
CONCATConcatenates two or more strings. NULL values are ignored.
CONCAT_WSConcatenate with separator. Returns a string resulting from the joining of two or more string values in an end-to-end manner. Uses the first argument as the separator between each string.
CONVERT_FROMConverts a binary string from the given data type to a Dremio type.
CONVERT_REPLACEUTF8Converts a binary string to a UTF-8 value and replaces all characters that cannot be converted to UTF-8 with the specified replacement character.
CONVERT_TIMEZONEConvert timestamp to the specified timezone
CONVERT_TOConverts a value to a binary string of a supported data type.
CORRCalculates the Pearson correlation coefficient of the values expression1 and expression2. The function name must be enclosed in double quotes ("CORR").
COSComputes the cosine of a value in radians
COSHComputes the hyperbolic cosine of a value in radians.
COTComputes the cotangent of a value in radians.
COUNTReturns the total number of records for the specified expression.
COVAR_POPReturns the population covariance for non-NULL pairs across all input values.
COVAR_SAMPReturns the sample covariance for non-NULL pairs across all input values.
CRC32Returns a cyclic redundancy check value of a binary string.
CUME_DISTReturns the cumulative distribution of the current row with regard to other values within the same window partition.
CURRENT_DATEReturns the current date of the system.
CURRENT_DATE_UTCReturns the current date of the system based on the UTC timezone.
CURRENT_SCHEMAReturns the path/schema in use by the current session. This function cannot be used in a reflection.
CURRENT_TIMEReturns the current time for the system.
CURRENT_TIMESTAMPReturns the current timestamp for the system in UTC time only.
DATEDIFFCompares two dates or timestamps and returns the difference in days.
DATETYPEConstructs DATE using the values provided for year, month, and day parameters.
DATE_ADDReturns the sum of two expressions of time as another expression of time.
DATE_DIFFReturns the difference between two expressions of time as another expression of time.
DATE_PARTReturn subfields such as year or hour from date or timestamp values
DATE_SUBReturns the difference of two expressions of time as another expression of time.
DATE_TRUNCTruncates the date or timestamp to the indicated precision.
DAYReturns the day of month of the date or timestamp.
DAYOFMONTHReturns the day of month of the date or timestamp.
DAYOFWEEKReturns the day of the week (from 1 to 7) of the date or timestamp.
DAYOFYEARReturns the day of the year (from 1 to 366) of the date or timestamp.
DEGREESConverts radians to degrees.
DENSE_RANKReturns the rank of the current row within its partition and ordering. Rows that are equal will have the same rank.
EReturns Euler's number, a constant approximately equal to 2.718281828459045.
ENCODEEncodes the input expression using the specified charSet character encoding.
ENDS_WITHReturns whether a string ends with another string. The comparison is case-sensitive.
EXPCalculates Euler's number, e, raised to the power of the specified value.
EXTRACTExtracts the specified date or time part from the date or timestamp.
FACTORIALComputes the factorial of the numeric expression. The input expression must be an integer from 0 to 20.
FIRST_VALUEReturns the first value within an ordered group of a result set.
FLATTENExplodes compound values into multiple rows. The FLATTEN function takes a LIST column and produces a lateral view (that is, an inline view that contains correlation referring to other tables that precede it in the FROM clause).
FLOORReturns the value from the specifed expression rounded to the nearest equal or smaller integer.
FROM_HEXReturns a binary value for the given hexadecimal string
GEO_BEYONDReturns whether or not the two points are beyond the distance specified in meters.
GEO_DISTANCEReturns the distance between two points in meters.
GEO_NEARBYReturns whether or not the two points are within the distance specified in meters.
GREATESTReturns the largest value from a list of expressions.
HASHReturns a hash value of the arguments. HASH does not return NULL, even for NULL inputs.
HASH64Returns 64-bit hash of input value, with optional seed.
HEXReturns the hexadecimal encoding of an expression.
HOURExtracts the hour number (from 0 to 23) for a given time or timestamp.
ILIKETests whether an expression matches a pattern. The comparison is case-insensitive.
IMAXDIRReturns the name of a subdirectory of a table in HDFS. The subdirectory has the name that ranks highest in case-insensitive alphanumeric order.
IMINDIRReturns the name of a subdirectory of a table in HDFS. The subdirectory has the name that ranks lowest in case-insensitive alphanumeric order.
INITCAPReturns the input string with the first letter of each word in uppercase and the subsequent letters in the word are in lowercase).
INSTRReturns the position of the first occurrence of a string when it is contained in another string. If no such occurrence is found, a zero is returned. The comparison is case-sensitive.
IS [NOT] DISTINCT FROMCompares two expressions to determine whether they have the same or different values. NULLs are considered as comparable values.
ISDATEReturns true if the input expression can be cast to a date.
IS [NOT] FALSETests whether the input expression is either false or not false. If true in either case, returns a value of true. Alias for the function ISFALSE/ISNOTFALSE.
IS [NOT] NULLDetermines if an expression is NULL or not NULL. Alias for the function ISNULL/ISNOTNULL.
ISNUMERICDetermines whether an expression is a valid numeric type (DECIMAL, DOUBLE, INT, BIGINT, VARBINARY).
IS [NOT] TRUETests whether the input expression is either true or not true. If true in either case, returns a value of true. Alias for the function ISTRUE/ISNOTTRUE.
IS_BIGINTReturns TRUE if the input expression is an big integer value.
IS_INTReturns TRUE if the input expression is an integer value.
IS_MEMBERReturns whether the current user is a member of the specified role. This function cannot be used in a reflection.
IS_SUBSTRReturns true if a string is contained within another string. The comparison is case-sensitive.
IS_UTF8Returns whether an expression is valid UTF-8
IS_VARCHARReturns TRUE if the input expression is a varchar value.
LAGReturns the row before the current one in a partition based on the ORDER BY clause without the need for a self-join. If there are no rows, this function returns NULL.
LAST_DAYReturns the last day of the month for the specified date or timestamp.
LAST_QUERY_IDReturns the ID for the most recently executed query in the current session. This function cannot be used in a reflection.
LCASEReturns the input expression with all the characters converted to lowercase.
LEADReturns the row after the current one in the same result set without the need for a self-join. If there are no rows, this function returns NULL.
LEASTReturns the smallest value from a list of expressions.
LEFTReturns the left-most substring. The function name must be enclosed in double quotes (\"LEFT\").
LENGTHReturns the length of an input string. If the character encoding isn't specified, it assumes to UTF8.
LEVENSHTEINComputes the Levenshtein distance between two input expressions.
LIKETests whether an expression matches one or more patterns. Comparisons are case-sensitive.
LISTAGGConcatenates a group of rows into a list of strings and places a separator between them.
LNReturns the natural logarithm of of the numeric expression.
LOCALTIMEReturns the current time for the system.
LOCALTIMESTAMPReturns the current timestamp for the system.
LOCATESearches for the first occurrence of the first argument in the second argument and if found, returns the position the of the first argument in the second argument. The first character in a string is position 1. Returns 0 if the substring isn't found in the expression.
LOGReturns the logarithm of the numeric input expression. If no base is specified, the natural log (ln) will be calculated.
LOG10Returns the log base 10 of the numeric input expression.
LOWERReturns the input expression with all the characters converted to lowercase.
LPADLeft pads a string with spaces or specified characters to reach the number of chracters specified as a parameter.
LSHIFTShifts the bits of the numeric expression to the left.
LTRIMRemoves leading spaces or characters from a string.
MAP_KEYSReturns all keys from a map expression.
MAP_VALUESReturns all values from a map expression.
MASKReturns a masked version of a string.
MASK_FIRST_NReturns a masked version of a string with the first num_chars characters masked. By default, if you do not provide a mask value, the first four characters are masked.
MASK_HASHReturns a consistent hash value based on the input string. This function returns NULL for non-string types.
MASK_LAST_NReturns a masked version of a string with the last num_chars characters masked. By default, if you do not provide a mask value, the last four characters are masked.
MASK_SHOW_FIRST_NReturns a masked version of a string with the first num_chars characters unmasked. By default, if you do not provide a value, the first four characters are shown.
MASK_SHOW_LAST_NReturns a masked version of a string with the last num_chars characters unmasked. By default, if you do not provide a value, the last four characters are shown.
MAXReturns the maximum value among the non-NULL input expressions.
MAXDIRReturns the name of a subdirectory of a table in HDFS. The subdirectory has the name that ranks highest in case-sensitive alphanumeric order.
MD5Computes the MD5 hash value of a string.
MEDIANComputes the median of the specified column's values based on a continuous distribution.
MINReturns the minimum value among the non-NULL input expressions.
MINDIRReturns the name of a subdirectory of a table in HDFS. The subdirectory has the name that ranks lowest in case-sensitive alphanumeric order.
MINUTEExtracts the minute number (from 0 to 59) for a given time or timestamp.
MODReturns the remainder of the input expression divided by the second input expression.
MONTHExtracts the month number (from 1 to 12) for a given date or timestamp.
MONTHS_BETWEENReturns the number of months between two date or timestamp values.
NDVReturns an approximate distinct value number, similar to COUNT(DISTINCT col). NDV can return results faster than using the combination of COUNT and DISTINCT while using a constant amount of memory, resulting in less memory usage for columns with high cardinality.
NEXT_DAYReturns the date or timestamp of the first specified day of week that occurs after the input date.
NORMALIZE_STRINGReturns a normalized string in the specified unicode normalization form.
NOWReturns the current timestamp (date and time) in UTC timezone.
NTILEEqually splits the rows in each partition into ranked parts specified by the integer value and starting from 1. This function requires the ORDER BY clause.
NULLIFCompares two expressions. If the values in each expression are equal, returns NULL and, if they are not equal, returns the value of the first expression.
NVLReturns the value of the first expression, if it is not NULL. Otherwise, returns the value of the second expression.
OCTET_LENGTHReturns the length of the string in bytes.
OVERLAPSReturns whether two intervals overlap.
PARSE_URLReturns the specified part of the URL or the value for the specified QUERY key.
PERCENTILE_CONTComputes a percentile value based on a continuous distribution of the column input.
PERCENTILE_DISCComputes a specific percentile for sorted values in a column.
PERCENT_RANKReturns the relative rank of the current row in the partition based on the ORDER BY clause. The displayed percentage ranges from 0.0 to 1.0.
PIReturns the value of pi, which is approximately 3.14592654.
PMODReturns the positive remainder after dividend / divisor. Returns an error if the divisor is 0.
POSITIONReturns the position of the first occurrence of a substring within another string
POW, POWERReturns the result of raising the input value to the specified power.
QUARTERExtracts the quarter number (from 1 to 4) for a given date or timestamp.
QUERY_USERReturns the username of the user that is currently logged in to the system. This function cannot be used in a reflection.
QUOTEReturns a result that can be used as a properly escaped data value in a SQL statement.
RADIANSConvert a value in degrees to radians
RANDOMEach call returns a random generated number between 0 and 1 for each row.
RANKReturns the rank of the current row within its partition and placement order. Rows that are equal have the same rank. However, the count of tied rows is added to the next rank, instead of being incremented by one. The rank value starts at 1 and increases sequentially.
REGEXP_COL_LIKEReturns whether a string matches a regular expression in a column.
REGEXP_EXTRACTExtracts the first string in expression that matches the REGEXP expression and corresponds to the REGEX group index.
REGEXP_LIKEReturns whether a string matches a regular expression.
REGEXP_MATCHESReturns whether a string matches a regular expression.
REGEXP_REPLACEFinds strings that match the given regular expression and replaces the strings with the given string.
REGEXP_SPLITSplits an input string by using a regular expression according to a keyword and an integer value.
REPEATBuilds a string by repeating the input for the specified number of times
REPEATSTRRepeats the given string n times.
REPLACERemoves all occurrences of a specified substring and replaces them with another string.
REVERSEReverses the order of characters in a string.
RIGHTReturns the right-most substring. The function name must be enclosed in double quotes ("RIGHT").
ROUNDReturns the rounded value for the inputted value. If no scale is specified, the closest whole number is returned.
ROW_NUMBERReturns the row number for the current row based on the ORDER BY clause within each partition. Rows containing identical values receive different row numbers.
RPADRight pads a string with spaces or specified characters to reach the number of chracters specified as a parameter.
RSHIFTShifts the bits of the numeric expression to he right.
RTRIMRemoves trailing spaces or characters from a string.
SECONDExtracts the second number (from 0 to 59) for a given date or timestamp.
SESSION_USERReturns the user that created the current session. This function cannot be used in a reflection.
SET_UNIONGiven two arrays, returns a single array that includes all of the elements in the given arrays, without duplicates.
SHA, SHA1Computes the SHA-1 hash value of a string.
SHA256Computes the 256-bit SHA-2 hash value of a string.
SHA512Computes the 512-bit SHA-2 hash value of a string.
SIGNReturns the sign of the input expression.
SIMILAR_TOTests whether the entire expression matches a pattern.
SINComputes the sine of a value.
SINHComputes the hyperbolic sine of the input expression.
SIZEReturns the number of entries in a map expression.
SOUNDEXReturns a string that contains a phonetic representation of the input string.
SPLIT_PARTSplits a given string at a specified character and returns the requested part.
SQRTReturns the square root of the non-negative numeric expression.
STARTS_WITHReturns whether a string starts with another string. The comparison is case-sensitive.
STDDEVReturns the standard deviation of non-NULL values in a column with a numeric data type. If all records inside a group are NULL, returns NULL.
STDDEV_POPReturns the population standard deviation (square root of variance) of non-NULL values in a column with a numeric data type. If all records inside a group are NULL, returns NULL.
STDDEV_SAMPReturns the sample standard deviation (square root of sample variance) of non-NULL values in a column with a numeric data type. If all records inside a group are NULL, returns NULL.
STRING_BINARYReturns a string that represents the provided bytes. Escapes non-printable characters as hex values.
STRPOSSearches for the first occurence of the substring in the given expression and returns the position of where the substring begins. Searching binary values is also supported.
ST_FROMGEOHASHReturns the latitude and longitude coordinates of the center of the given geohash.
ST_GEOHASHReturns the corresponding geohash for the given latitude and longitude coordinates.
SUBLISTReturns an array constructed from the specified subset of elements of the input array.
SUBSTRReturns the portion of the string from the specified base expression starting at the specified characters. This function supports regular expressions.
SUBSTRINGReturns the portion of the string from the specified base expression starting at the specified characters.
SUBSTRING_INDEXReturns a substring of an expression before the specified number of delimiter occurs.
SUMReturns the sum of non-NULL input expressions.
SYSTEM_USERReturns the name of the current user. This function cannot be used in a reflection.
TANComputes the tangent of a value in radians.
TANHComputes the hyperbolic tangent of the input expression.
TIMESTAMPADDAdd (or subtract) an interval of time from a date/timestamp value or column.
TIMESTAMPDIFFReturn the amount of time between two date or timestamp values
TIMESTAMPTYPEConstructs a timestamp using the values provided for year, month, day, hour, minute, second, and millisecond parameters.
TOASCIIConverts a string that is encoded in the specified character set to UTF-8.
TO_CHARConverts the input expression to a character/string using the specified format.
TO_DATEConverts the input expressions to the corresponding date.
TO_HEXReturns a hexadecimal string for the given binary value.
TO_NUMBERConverts a string into a number (double) in the specified format.
TO_TIMEConverts the input expressions to the corresponding time.
TO_TIMESTAMPConverts the input expressions to the corresponding timestamp.
TRANSACTION_TIMESTAMPReturns the timestamp in UTC of the current transaction. This function cannot be used in a reflection.
TRANSLATETranslates the base expression from the source characters/expression to the target characters/expression.
TRIMRemoves leading, trailing, or both spaces or characters from a string.
TRUNCATERounds the input expression down the nearest of equal integer depending on the specified number of places before or after the decimal point.
TYPEOFReports the type (in string format) of the input expression.
UCASEReturns the input expression with all the characters converted to uppercase.
UNBASE64Decodes a Base64-encoded string.
UNHEXConverts the hexadecimal number into the bytes represented by a number.
UNIX_TIMESTAMPReturns the Unix epoch time representation of an ISO 8601 timestamp.
UPPERReturns the input expression with all the characters converted to uppercase.
USERReturns the user that is currently logged into the system. This function cannot be used in a reflection.
VAR_POPReturns the population variance of non-NULL records.
VAR_SAMPReturns the sample variance of non-NULL records.
WEEKExtracts the week number (from 0 to 53) for a given date or timestamp.
WEEKOFYEARReturns the week of year of the date or timestamp.
XORReturns the bitwise XOR of two integers.
YEARExtracts the year for a given date or timestamp.