Built-in Functions
The built-in functions described in this page are usable from DeltaStream SQL streaming Queries.
Aggregate Functions
SQL Function | Description |
---|---|
COUNT([ ALL | DISTINCT] expression) | By default or with ALL, returns the number of distinct input rows for which the |
COUNT(*) | Returns the number of input rows. |
AVG([ ALL | DISTINCT] expression) | |
SUM([ ALL | DISTINCT] expression) | By default or with ALL, returns the sum of the |
MAX([ ALL | DISTINCT] expression) | By default or with ALL, returns the maximum value of the |
MIN([ ALL | DISTINCT] expression) | By default or with ALL, returns the minimum value of the |
STDDEV_POP([ ALL | DISTINCT] expression) | By default or with ALL, returns the population standard deviation of the |
STDDEV_SAMP([ ALL | DISTINCT] expression) | By default or with ALL, returns the sample standard deviation of the |
VAR_POP([ ALL | DISTINCT] expression) | By default or with ALL, returns the population variance (square of the population standard deviation) of the |
VAR_SAMP([ ALL | DISTINCT] expression) | By default or with ALL, returns the sample variance (square of the sample standard deviation) of the |
VARIANCE([ ALL | DISTINCT] expression) | Synonyms for |
FIRST_VALUE(expression) | Returns the first value in an ordered set of values. |
LAST_VALUE(expression) | Returns the last value in an ordered set of values. |
LISTAGG(expression [, separator]) | Concatenates the values of string |
DS_LAG_<TYPE>(value <TYPE>, integer) | Returns the If no such value can be found, Note: if your |
Arithmetic Functions
Function | Description |
---|---|
+ numeric | Returns |
- numeric | Returns negative |
numeric1 + numeric2 | Returns |
numeric1 - numeric2 | Return |
numeric1 * numberic2 | Returns |
numeric1 / numeric2 | Returns |
numeric1 % numeric2 | Returns the remainder (modulus) of |
POWER(numeric1, numeric2) |
|
ABS(numeric) | Returns the absolute value of |
SQRT(numeric) | Returns the square root of |
LN(numeric) | Returns the natural logarithm (base e) of |
LOG10(numeric) | Returns the base 10 logarithm of |
LOG2(numeric) | Returns the base 2 logarithm of |
LOG(numeric1 [, numeric2]) | When called with one argument, returns the natural logarithm of |
EXP(numeric) | Returns e raised to the power of |
CEIL(numeric) CEILING(numeric) | Rounds |
FLOOR(numeric) | Rounds |
SIN(numeric) | Returns the sine of |
SINH(numeric) | Returns the hyperbolic sine of |
COS(numeric) | Returns the cosine of |
TAN(numeric) | Returns the tangent of |
TANH(numeric) | Returns the hyperbolic tangent of |
COT(numeric) | Returns the cotangent of a |
ASIN(numeric) | Returns the arc sine of |
ACOS(numeric) | Returns the arc cosine of |
ATAN(numeric) | Returns the arc tangent of |
ATAN2(numeric1, numeric2) | Returns the arc tangent of a coordinate |
COSH(numeric) | Returns the hyperbolic cosine of |
DEGREES(numeric) | Returns the degree representation of a radian |
RADIANS(numeric) | Returns the radian representation of a degree |
SIGN(numeric) | Returns the signum of |
ROUND(numeric, integer) | Returns a number rounded to |
PI() | Returns a value that is closer than any other values to pi. |
E() | Returns a value that is closer than any other values to e. |
RAND() | Returns a pseudorandom double value in the range [0.0, 1.0) |
RAND(integer) | Returns a pseudorandom double value in the range [0.0, 1.0) with an initial seed |
RAND_INTEGER(integer) | Returns a pseudorandom integer value in the range [0, |
RAND_INTEGER(integer1, integer2) | Returns a pseudorandom integer value in the range [0, |
UUID() | Returns a Universally Unique Identifier (UUID) string (e.g., |
BIN(integer) | Returns a string representation of |
HEX(numeric) HEX(string) | Returns a string representation of an integer |
TRUNCATE(numeric1 [, integer2]) | Returns a This function can also pass in only one |
Collection Functions
Function | Description |
---|---|
| Returns the number of elements in |
Comparison Functions
Function | Description |
---|---|
value1 = value2 | Returns |
value1 <> value2 | Returns |
value1 > value2 | Returns |
value1 >= value2 | Returns |
value1 < value2 | Returns |
value1 <= value2 | Returns |
value IS NULL | Returns |
value IS NOT NULL | Returns |
value1 IS DISTINCT FROM value2 | Returns |
value1 IS NOT DISTINCT FROM value2 | Returns |
value1 BETWEEN value2 AND value3 | Returns |
value1 NOT BETWEEN value2 AND value3 | Returns |
string1 LIKE string2 | Returns |
string1 NOT LIKE string2 | Returns |
value1 IN (value2 [, value3]* ) | Returns |
value1 NOT IN (value2 [, value3]* ) | Returns |
Conditional Functions
Function | Description |
---|---|
CASE value WHEN value1_1 [, value1_2]* THEN RESULT1 (WHEN value2_1 [, value2_2 ]* THEN result_2)* (ELSE result_z) END | Returns |
CASE WHEN condition1 THEN result1 (WHEN condition2 THEN result2)* (ELSE result_z) END | Returns |
NULLIF(value1, value2) | Returns |
COALESCE(value1 [, value2]*) | Returns the first argument that is not If all arguments are |
IF(condition, true_value, false_value) | Returns the |
IFNULL(input, null_replacement) | Returns null_replacement if input is Compared to The function allows to pass nullable columns into a function or table that is declared with a |
IS_ALPHA(string) | Returns |
IS_DECIMAL(string) | Returns |
IS_DIGIT(string) | Returns |
GREATEST(value1[, value2]*) | Returns the greatest value of the list of arguments. Returns |
LEAST(value1[, value2]*) | Returns the least value of the list of arguments. Returns |
Hash Functions
Function | Description |
---|---|
MD5(string) | Returns the MD5 hash of |
SHA1(string) | Returns the SHA-1 hash of |
SHA224(string) | Returns the SHA-224 hash of |
SHA256(string) | Returns the SHA-256 hash of |
SHA384(string) | Returns the SHA-384 hash of |
SHA512(string) | Returns the SHA-512 hash of |
SHA2(string, hashLength) | Returns the hash of |
DS_HASHUUID(string) | Returns the hash of |
Logical Functions
Function | Description |
---|---|
boolean1 OR boolean2 | Returns |
boolean1 AND boolean2 | Returns |
NOT boolean | Returns |
Offset Functions
These functions are used in the scope of the MATCH_RECOGNIZE operator:
Function | Description |
---|---|
LAST(variable.column [, n]) | Returns the value of the If |
FIRST(variable.column [, n]) | Returns the value of the If |
String Functions
Function | Description |
---|---|
string1 || string2 | Returns the concatenation of |
CHAR_LENGTH(string) CHARACTER_LENGTH(string) | Returns the number of characters in |
UPPER(string) | Returns |
LOWER(string) | Returns |
POSITION(string1 IN string2) | Returns the position (start from 1) of the first occurrence of |
TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2) | Returns a string that removes leading and/or trailing characters |
LTRIM(string) | Returns a string that removes the left whitespaces from |
RTRIM(string) | Returns a string that removes the right whitespaces from |
REPEAT(string, integer) | Returns a string that repeats the base |
REGEXP_REPLACE(string1, string2, string3) | Returns a string from |
OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ]) | Returns a string that replaces |
SUBSTRING(string FROM integer1 [ FOR integer2 ]) | Returns a substring of |
REPLACE(string1, string2, string3) | Returns a new string which replaces all the occurrences of |
REGEXP_EXTRACT(string1, string2[, integer]) | Returns a string from The regex match group index starts from |
INITCAP(string) | Returns a new form of |
CONCAT(string1, string2,...) | Returns a string that concatenates |
CONCAT_WS(string1, string2, string3,...) | Returns a string that concatenates |
LPAD(string1, integer, string2) | Returns a new string from |
RPAD(string1, integer, string2) | Returns a new string from |
FROM_BASE64(string) | Returns the base64-decoded result from |
TO_BASE64(string) | Returns the base64-encoded result from |
DS_FROMBASE64(string) | Returns the decoded bytes as a value of the data type |
DS_TOBASE64(bytes) | Returns the base64-encoded |
ASCII(string) | Returns the numeric value of the first character of string. Returns |
CHR(integer) | Returns the ASCII character having the binary equivalent to an |
DECODE(binary, string) | Decodes |
ENCODE(string1, string2) | Encodes |
INSTR(string1, string2) | Returns the position of the first occurrence of |
LEFT(string, integer) | Returns the leftmost |
RIGHT(string, integer) | Returns the rightmost |
LOCATE(string1, string2[, integer]) | Returns the position of the first occurrence of |
PARSE_URL(string1, string2[, string3]) | Returns the specified part from the URL. Valid values for A value of a particular key in |
REGEXP(string1, string2) | Returns |
REVERSE(string) | Returns the reversed string. Returns |
SPLIT_INDEX(string1, string2, integer) | Splits |
STR_TO_MAP(string1[, string2, string3]) | Returns a map after splitting the |
SUBSTR(string, integer1[, integer2]) | Returns a substring of |
Temporal Functions
Function | Description |
---|---|
DATE string | Returns a SQL date parsed from |
TIME string | Returns a SQL time parsed from |
NOW() | Returns the current SQL timestamp in the local time zone; this is a synonym of |
CURRENT_ROW_TIMESTAMP() | Returns the current SQL timestamp in the local time zone; the return type is |
EXTRACT(timeinteravlunit FROM temporal) | Returns a long value extracted from the |
YEAR(date) | Returns the year from the SQL date. Equivalent to |
QUARTER(date) | Returns the quarter of a year (an integer between 1 and 4) from the SQL date. Equivalent to |
MONTH(date) | Returns the month of a year (an integer between 1 and 12) from the SQL date. Equivalent to |
WEEK(date) | Returns the week of a year (an integer between 1 and 53) from the SQL date. Equivalent to |
DAYOFYEAR(date) | Returns the day of a year (an integer between 1 and 366) from the SQL date. Equivalent to |
DAYOFMONTH(date) | Returns the day of a month (an integer between 1 and 31) from the SQL date. Equivalent to |
DAYOFWEEK(date) | Returns the day of a week (an integer between 1 and 7) from the SQL date. Equivalent to |
HOUR(timestamp) | Returns the hour of a day (an integer between 0 and 23) from the SQL timestamp. Equivalent to |
MINUTE(timestamp) | Returns the minute of an hour (an integer between 0 and 59) from the SQL timestamp. Equivalent to |
SECOND(timestamp) | Returns the second of a minute (an integer between 0 and 59) from the SQL timestamp. Equivalent to |
FLOOR(timepoint TO timeintervalunit) | Returns a value that rounds the |
CEIL(timepoint TO timeintervaluntit) | Returns a value that rounds the |
CONVERT_TZ(string1, string2, string3) | Converts a datetime |
FROM_UNIXTIME(numeric[, string]) | Returns a representation of the |
UNIX_TIMESTAMP() | Gets current Unix timestamp in seconds. This function is not deterministic, which means the value would be recalculated for each record. |
UNIX_TIMESTAMP(string1[, string2]) | Converts date time string |
TO_DATE(string1[, string2]) | Converts a date string |
TO_TIMESTAMP_LTZ(numeric, precision) | Converts epoch seconds or epoch milliseconds to a |
TO_TIMESTAMP(string1[, string2]) | Converts date time string |
DS_TOEPOCH(<TIMESTAMP_LTZ>) | Converts a |
Type Conversion Functions
Function | Description |
---|---|
CAST(expression AS datatype) | Returns a new value being cast to |
TRY_CAST(expression AS datatype) | Like |
Value Construction Functions
SQL Function | Description |
---|---|
STRUCT(expression [, expression, ...]) | Returns a new See |
MAP[expression := expression [, expression := expression, ...]] | Returns a |
ARRAY[expression [, expression, ...]] |
We use Apache Flink® as our processing engine, and most of these functions come fully supported with Flink® out of the box. We'd like to thank and give credit to the Flink® contributors and community for providing a great open source project.
Last updated