# All Functions

Function Name | Description |
---|---|

ABS | Computes the absolute value of a numeric expression. |

ACOS | Computes the arccosine (inverse cosine) of a value in radians |

AES_DECRYPT | Decrypts a string produced by AES encryption. |

AES_ENCRYPT | Encrypts a string using AES encryption. |

APPROX_COUNT_DISTINCT | Returns the approximate number of unique, non-null values in a column. |

APPROX_PERCENTILE | Computes the approximate percentile of the given column and quantile. |

ARRAYS_OVERLAP | Compares 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_AGG | Aggregates the provided expression into an array. |

ARRAY_APPEND | Appends an element to the end of an array. |

ARRAY_AVG | Returns the average of all non-null elements of a list. |

ARRAY_CAT | Returns a concatenation of two arrays. |

ARRAY_COMPACT | Returns the input array without null values. |

ARRAY_CONTAINS | Returns whether a list contains a given value. |

ARRAY_DISTINCT | Given an input array, returns an equivalent array that includes only distinct elements. |

ARRAY_GENERATE_RANGE | Returns an array of integers in the specified range. |

ARRAY_INSERT | Returns an array that contains all of the elements from the input array as well as a new element inserted in the specified position. |

ARRAY_MAX | Returns the maximum value of a list. |

ARRAY_MIN | Returns the minimum value of a list. |

ARRAY_POSITION | Returns the index of the first occurrence of an element in an array. |

ARRAY_PREPEND | Prepends an element to the beginning of an array. |

ARRAY_REMOVE | Removes all elements that equal a given value from a list. |

ARRAY_REMOVE_AT | Returns the input array with the element at the specified position removed. |

ARRAY_SIZE | Returns the size of the input array. |

ARRAY_SLICE | Returns an array constructed from the specified subset of elements in the input array. |

ARRAY_SUM | Returns the sum of all non-null elements of a list. |

ARRAY_TO_STRING | Returns a string of the values from the input array, with the values separated by the specified delimiter string. |

ASCII | Returns the ASCII code for the first character of a string. If the string is empty, 0 is returned. |

ASIN | Computes the arcsine (inverse sine) of a value in radians |

ATAN | Computes the Arctangent (inverse Tangent) of a value |

ATAN2 | Computes the Arctangent (inverse Tangent) of the ratio of its two arguments. |

AVG | Computes the average of a set of values. |

BASE64 | Returns the Base64 encoding of a binary string. |

BIN | Returns the the binary representation of an expression. |

BINARY_STRING | Converts the input expression to a binary value. |

BITWISE_AND | Returns the bitwise AND of two operands. |

BITWISE_NOT | Returns the bitwise NOT of the given operand. |

BITWISE_OR | Returns the bitwise OR of two operands. |

BITWISE_XOR | Returns the bitwise XOR of two operands. |

BIT_AND | Returns the bitwise `AND` of non-NULL input values. |

BIT_LENGTH | Returns the length of the bits of the input expression. |

BIT_OR | Returns the bitwise `OR` of non-NULL input values. |

BOOL_AND | Computes 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_OR | Computes 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. |

BROUND | Returns the rounded result of the numeric expression using `HALF_EVEN` rounding mode. |

BTRIM | Trims leading and trailing characters from a string. |

CARDINALITY | Returns the number of elements contained in the specified list or map. |

CASE | Evaluates 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` . |

CAST | Converts a value of one data type to another data type. This function behaves similarly to the TO_<data_type> (i.e. TO_TIMESTAMP) functions. |

CBRT | Computes the cube root of a numeric expression |

CEILING | Returns the nearest equal or larger value of the input expression. Can also be called using CEIL(). |

CHARACTER_LENGTH | Returns the length of an input string. |

CHAR_LENGTH | Returns the character length of the input string |

CHR | Converts 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. |

COALESCE | Evaluates the arguments in order and returns the value of the first expression that does not contain `NULL` . |

COL_LIKE | Tests whether an expression column matches a pattern column. Comparisons are case-sensitive. |

CONCAT | Concatenates two or more strings. `NULL` values are ignored. |

CONCAT_WS | Concatenate 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. |

CONTAINS | Returns documents matching the provided Lucene expression. |

CONVERT_FROM | Converts a binary string from the given data type to a Dremio type. |

CONVERT_REPLACEUTF8 | Converts 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_TIMEZONE | Convert timestamp to the specified timezone |

CONVERT_TO | Converts a value to a binary string of a supported data type. |

CORR | Calculates the Pearson correlation coefficient of the values expression1 and expression2. The function name must be enclosed in double quotes ("CORR"). |

COS | Computes the cosine of a value in radians |

COSH | Computes the hyperbolic cosine of a value in radians. |

COT | Computes the cotangent of a value in radians. |

COUNT | Returns the total number of records for the specified expression. |

COVAR_POP | Returns the population covariance for non-NULL pairs across all input values. |

COVAR_SAMP | Returns the sample covariance for non-NULL pairs across all input values. |

CRC32 | Returns a cyclic redundancy check value of a binary string. |

CUME_DIST | Returns the cumulative distribution of the current row with regard to other values within the same window partition. |

CURRENT_DATE | Returns the current date of the system. |

CURRENT_DATE_UTC | Returns the current date of the system based on the UTC timezone. |

CURRENT_SCHEMA | Returns the path/schema in use by the current session. This function cannot be used in a reflection. |

CURRENT_TIME | Returns the current time for the system. |

CURRENT_TIMESTAMP | Returns the current timestamp for the system in UTC time only. |

DATEDIFF | Compares two dates or timestamps and returns the difference in days. |

DATETYPE | Constructs DATE using the values provided for year, month, and day parameters. |

DATE_ADD | Returns the sum of two expressions of time as another expression of time. |

DATE_DIFF | Returns the difference between two expressions of time as another expression of time. |

DATE_PART | Return subfields such as year or hour from date or timestamp values |

DATE_SUB | Returns the difference of two expressions of time as another expression of time. |

DATE_TRUNC | Truncates the date or timestamp to the indicated precision. |

DAY | Returns the day of month of the date or timestamp. |

DAYOFMONTH | Returns the day of month of the date or timestamp. |

DAYOFWEEK | Returns the day of the week (from 1 to 7) of the date or timestamp. |

DAYOFYEAR | Returns the day of the year (from 1 to 366) of the date or timestamp. |

DEGREES | Converts radians to degrees. |

DENSE_RANK | Returns the rank of the current row within its partition and ordering. Rows that are equal will have the same rank. |

E | Returns Euler's number, a constant approximately equal to 2.718281828459045. |

ENCODE | Encodes the input expression using the specified charSet character encoding. |

ENDS_WITH | Returns whether a string ends with another string. The comparison is case-sensitive. |

EXP | Calculates Euler's number, e, raised to the power of the specified value. |

EXTRACT | Extracts the specified date or time part from the date or timestamp. |

FACTORIAL | Computes the factorial of the numeric expression. The input expression must be an integer from `0` to `20` . |

FIRST_VALUE | Returns the first value within an ordered group of a result set. |

FLATTEN | Explodes 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). |

FLOOR | Returns the value from the specifed expression rounded to the nearest equal or smaller integer. |

FROM_HEX | Returns a binary value for the given hexadecimal string |

GEO_BEYOND | Returns whether or not the two points are beyond the distance specified in meters. |

GEO_DISTANCE | Returns the distance between two points in meters. |

GEO_NEARBY | Returns whether or not the two points are within the distance specified in meters. |

GREATEST | Returns the largest value from a list of expressions. |

HASH | Returns a hash value of the arguments. `HASH` does not return `NULL` , even for `NULL` inputs. |

HASH64 | Returns 64-bit hash of input value, with optional seed. |

HEX | Returns the hexadecimal encoding of an expression. |

HOUR | Extracts the hour number (from 0 to 23) for a given time or timestamp. |

ILIKE | Tests whether an expression matches a pattern. The comparison is case-insensitive. |

IMAXDIR | Returns the name of a subdirectory of a table in HDFS. The subdirectory has the name that ranks highest in case-insensitive alphanumeric order. |

IMINDIR | Returns the name of a subdirectory of a table in HDFS. The subdirectory has the name that ranks lowest in case-insensitive alphanumeric order. |

INITCAP | Returns the input string with the first letter of each word in uppercase and the subsequent letters in the word are in lowercase). |

INSTR | Returns 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 FROM | Compares two expressions to determine whether they have the same or different values. NULLs are considered as comparable values. |

ISDATE | Returns `true` if the input expression can be cast to a date. |

IS [NOT] FALSE | Tests 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] NULL | Determines if an expression is `NULL` or not `NULL` . Alias for the function `ISNULL` /`ISNOTNULL` . |

ISNUMERIC | Determines whether an expression is a valid numeric type (DECIMAL, DOUBLE, INT, BIGINT, VARBINARY). |

IS [NOT] TRUE | Tests 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_BIGINT | Returns TRUE if the input expression is an big integer value. |

IS_INT | Returns TRUE if the input expression is an integer value. |

IS_MEMBER | Returns whether the current user is a member of the specified role. This function cannot be used in a reflection. |

IS_SUBSTR | Returns `true` if a string is contained within another string. The comparison is case-sensitive. |

IS_UTF8 | Returns whether an expression is valid UTF-8 |

IS_VARCHAR | Returns TRUE if the input expression is a varchar value. |

LAG | Returns 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_DAY | Returns the last day of the month for the specified date or timestamp. |

LAST_QUERY_ID | Returns the ID for the most recently executed query in the current session. This function cannot be used in a reflection. |

LCASE | Returns the input expression with all the characters converted to lowercase. |

LEAD | Returns 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` . |

LEAST | Returns the smallest value from a list of expressions. |

LEFT | Returns the left-most substring. The function name must be enclosed in double quotes (\"LEFT\"). |

LENGTH | Returns the length of an input string. If the character encoding isn't specified, it assumes to UTF8. |

LEVENSHTEIN | Computes the Levenshtein distance between two input expressions. |

LIKE | Tests whether an expression matches one or more patterns. Comparisons are case-sensitive. |

LISTAGG | Concatenates a group of rows into a list of strings and places a separator between them. |

LN | Returns the natural logarithm of of the numeric expression. |

LOCALTIME | Returns the current time for the system. |

LOCALTIMESTAMP | Returns the current timestamp for the system. |

LOCATE | Searches 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. |

LOG | Returns the logarithm of the numeric input expression. If no base is specified, the natural log (ln) will be calculated. |

LOG10 | Returns the log base 10 of the numeric input expression. |

LOWER | Returns the input expression with all the characters converted to lowercase. |

LPAD | Left pads a string with spaces or specified characters to reach the number of chracters specified as a parameter. |

LSHIFT | Shifts the bits of the numeric expression to the left. |

LTRIM | Removes leading spaces or characters from a string. |

MAP_KEYS | Returns all keys from a map expression. |

MAP_VALUES | Returns all values from a map expression. |

MASK | Returns a masked version of a string. |

MASK_FIRST_N | Returns 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_HASH | Returns a consistent hash value based on the input string. This function returns `NULL` for non-string types. |

MASK_LAST_N | Returns 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_N | Returns 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_N | Returns 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. |

MAX | Returns the maximum value among the non-NULL input expressions. |

MAXDIR | Returns the name of a subdirectory of a table in HDFS. The subdirectory has the name that ranks highest in case-sensitive alphanumeric order. |

MD5 | Computes the MD5 hash value of a string. |

MEDIAN | Computes the median of the specified column's values based on a continuous distribution. |

MIN | Returns the minimum value among the non-NULL input expressions. |

MINDIR | Returns the name of a subdirectory of a table in HDFS. The subdirectory has the name that ranks lowest in case-sensitive alphanumeric order. |

MINUTE | Extracts the minute number (from 0 to 59) for a given time or timestamp. |

MOD | Returns the remainder of the input expression divided by the second input expression. |

MONTH | Extracts the month number (from 1 to 12) for a given date or timestamp. |

MONTHS_BETWEEN | Returns the number of months between two date or timestamp values. |

NDV | Returns 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_DAY | Returns the date or timestamp of the first specified day of week that occurs after the input date. |

NORMALIZE_STRING | Returns a normalized string in the specified unicode normalization form. |

NOW | Returns the current timestamp (date and time) in UTC timezone. |

NTILE | Equally 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. |

NULLIF | Compares 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. |

NVL | Returns the value of the first expression, if it is not `NULL` . Otherwise, returns the value of the second expression. |

OCTET_LENGTH | Returns the length of the string in bytes. |

OVERLAPS | Returns whether two intervals overlap. |

PARSE_URL | Returns the specified part of the URL or the value for the specified QUERY key. |

PERCENTILE_CONT | Computes a percentile value based on a continuous distribution of the column input. |

PERCENTILE_DISC | Computes a specific percentile for sorted values in a column. |

PERCENT_RANK | Returns 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. |

PI | Returns the value of pi, which is approximately 3.14592654. |

PMOD | Returns the positive remainder after dividend / divisor. Returns an error if the divisor is 0. |

POSITION | Returns the position of the first occurrence of a substring within another string |

POW, POWER | Returns the result of raising the input value to the specified power. |

QUARTER | Extracts the quarter number (from 1 to 4) for a given date or timestamp. |

QUERY_USER | Returns the username of the user that is currently logged in to the system. This function cannot be used in a reflection. |

QUOTE | Returns a result that can be used as a properly escaped data value in a SQL statement. |

RADIANS | Convert a value in degrees to radians |

RANDOM | Each call returns a random generated number between 0 and 1 for each row. |

RANK | Returns 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_LIKE | Returns whether a string matches a regular expression in a column. |

REGEXP_EXTRACT | Extracts the first string in expression that matches the `REGEXP` expression and corresponds to the `REGEX` group index. |

REGEXP_LIKE | Returns whether a string matches a regular expression. |

REGEXP_MATCHES | Returns whether a string matches a regular expression. |

REGEXP_REPLACE | Finds strings that match the given regular expression and replaces the strings with the given string. |

REGEXP_SPLIT | Splits an input string by using a regular expression according to a keyword and an integer value. |

REPEAT | Builds a string by repeating the input for the specified number of times |

REPEATSTR | Repeats the given string n times. |

REPLACE | Removes all occurrences of a specified substring and replaces them with another string. |

REVERSE | Reverses the order of characters in a string. |

RIGHT | Returns the right-most substring. The function name must be enclosed in double quotes ("RIGHT"). |

ROUND | Returns the rounded value for the inputted value. If no scale is specified, the closest whole number is returned. |

ROW_NUMBER | Returns the row number for the current row based on the `ORDER BY` clause within each partition. Rows containing identical values receive different row numbers. |

RPAD | Right pads a string with spaces or specified characters to reach the number of chracters specified as a parameter. |

RSHIFT | Shifts the bits of the numeric expression to he right. |

RTRIM | Removes trailing spaces or characters from a string. |

SECOND | Extracts the second number (from 0 to 59) for a given date or timestamp. |

SESSION_USER | Returns the user that created the current session. This function cannot be used in a reflection. |

SET_UNION | Given two arrays, returns a single array that includes all of the elements in the given arrays, without duplicates. |

SHA, SHA1 | Computes the SHA-1 hash value of a string. |

SHA256 | Computes the 256-bit SHA-2 hash value of a string. |

SHA512 | Computes the 512-bit SHA-2 hash value of a string. |

SIGN | Returns the sign of the input expression. |

SIMILAR_TO | Tests whether the entire expression matches a pattern. |

SIN | Computes the sine of a value. |

SINH | Computes the hyperbolic sine of the input expression. |

SIZE | Returns the number of entries in a map expression. |

SOUNDEX | Returns a string that contains a phonetic representation of the input string. |

SPLIT_PART | Splits a given string at a specified character and returns the requested part. |

SQRT | Returns the square root of the non-negative numeric expression. |

STARTS_WITH | Returns whether a string starts with another string. The comparison is case-sensitive. |

STDDEV | Returns 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_POP | Returns 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_SAMP | Returns 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_BINARY | Returns a string that represents the provided bytes. Escapes non-printable characters as hex values. |

STRPOS | Searches 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_FROMGEOHASH | Returns the latitude and longitude coordinates of the center of the given geohash. |

ST_GEOHASH | Returns the corresponding geohash for the given latitude and longitude coordinates. |

SUBLIST | Returns an array constructed from the specified subset of elements of the input array. |

SUBSTR | Returns the portion of the string from the specified base expression starting at the specified characters. This function supports regular expressions. |

SUBSTRING | Returns the portion of the string from the specified base expression starting at the specified characters. |

SUBSTRING_INDEX | Returns a substring of an expression before the specified number of delimiter occurs. |

SUM | Returns the sum of non-NULL input expressions. |

SYSTEM_USER | Returns the name of the current user. This function cannot be used in a reflection. |

TAN | Computes the tangent of a value in radians. |

TANH | Computes the hyperbolic tangent of the input expression. |

TIMESTAMPADD | Add (or subtract) an interval of time from a date/timestamp value or column. |

TIMESTAMPDIFF | Return the amount of time between two date or timestamp values |

TIMESTAMPTYPE | Constructs a timestamp using the values provided for year, month, day, hour, minute, second, and millisecond parameters. |

TOASCII | Converts a string that is encoded in the specified character set to UTF-8. |

TO_CHAR | Converts the input expression to a character/string using the specified format. |

TO_DATE | Converts the input expressions to the corresponding date. |

TO_HEX | Returns a hexadecimal string for the given binary value. |

TO_NUMBER | Converts a string into a number (double) in the specified format. |

TO_TIME | Converts the input expressions to the corresponding time. |

TO_TIMESTAMP | Converts the input expressions to the corresponding timestamp. |

TRANSACTION_TIMESTAMP | Returns the timestamp in UTC of the current transaction. This function cannot be used in a reflection. |

TRANSLATE | Translates the base expression from the source characters/expression to the target characters/expression. |

TRIM | Removes leading, trailing, or both spaces or characters from a string. |

TRUNCATE | Rounds the input expression down the nearest of equal integer depending on the specified number of places before or after the decimal point. |

TYPEOF | Reports the type (in string format) of the input expression. |

UCASE | Returns the input expression with all the characters converted to uppercase. |

UNBASE64 | Decodes a Base64-encoded string. |

UNHEX | Converts the hexadecimal number into the bytes represented by a number. |

UNIX_TIMESTAMP | Returns the Unix epoch time representation of an ISO 8601 timestamp. |

UPPER | Returns the input expression with all the characters converted to uppercase. |

USER | Returns the user that is currently logged into the system. This function cannot be used in a reflection. |

VAR_POP | Returns the population variance of non-NULL records. |

VAR_SAMP | Returns the sample variance of non-NULL records. |

WEEK | Extracts the week number (from 0 to 53) for a given date or timestamp. |

WEEKOFYEAR | Returns the week of year of the date or timestamp. |

XOR | Returns the bitwise XOR of two integers. |

YEAR | Extracts the year for a given date or timestamp. |