SQL functions The following Flink SQL operators and functions are supported. If you don’t see something you need, let us know! Aggregate functions SQL Function Description COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*) By default or with ALL, returns the number of input rows for which expression is not NULL. Use DISTINCT for one unique instance of each value. COUNT(*) COUNT(1) Returns the number of input rows. AVG([ ALL | DISTINCT ] expression) By default or with keyword ALL, returns the average (arithmetic mean) of expression across all input rows. Use DISTINCT for one unique instance of each value. SUM([ ALL | DISTINCT ] expression) By default or with keyword ALL, returns the sum of expression across all input rows. Use DISTINCT for one unique instance of each value. MAX([ ALL | DISTINCT ] expression) By default or with keyword ALL, returns the maximum value of expression across all input rows. Use DISTINCT for one unique instance of each value. MIN([ ALL | DISTINCT ] expression ) By default or with keyword ALL, returns the minimum value of expression across all input rows. Use DISTINCT for one unique instance of each value. STDDEV_POP([ ALL | DISTINCT ] expression) By default or with keyword ALL, returns the population standard deviation of expression across all input rows. Use DISTINCT for one unique instance of each value. STDDEV_SAMP([ ALL | DISTINCT ] expression) By default or with keyword ALL, returns the sample standard deviation of expression across all input rows. Use DISTINCT for one unique instance of each value. VAR_POP([ ALL | DISTINCT ] expression) By default or with keyword ALL, returns the population variance (square of the population standard deviation) of expression across all input rows. Use DISTINCT for one unique instance of each value. VAR_SAMP([ ALL | DISTINCT ] expression) By default or with keyword ALL, returns the sample variance (square of the sample standard deviation) of expression across all input rows. Use DISTINCT for one unique instance of each value. COLLECT([ ALL | DISTINCT ] expression) By default or with keyword ALL, returns a multiset of expression across all input rows. NULL values will be ignored. Use DISTINCT for one unique instance of each value. VARIANCE([ ALL | DISTINCT ] expression) Synonyms for VAR_SAMP(). RANK() Returns the rank of a value in a group of values. The result is one plus the number of rows preceding or equal to the current row in the ordering of the partition. The values will produce gaps in the sequence. DENSE_RANK() Returns the rank of a value in a group of values. The result is one plus the previously assigned rank value. Unlike the function rank, dense_rank will not produce gaps in the ranking sequence. ROW_NUMER() Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition. ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5). LEAD(expression [, offset] [, default]) Returns the value of expression at the offsetth row after the current row in the window. The default value of offset is 1 and the default value of default is NULL. LAG(expression [, offset] [, default]) Returns the value of expression at the offsetth row before the current row in the window. The default value of offset is 1 and the default value of default is NULL. 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 expressions and places separator values between them. The separator is not added at the end of string. The default value of separator is ','. APPROX_PERCENTILE(expression, percentile) Returns a given percentile from a distribution of expression across all input rows. The percentile value must be between 0.0 and 1.0 Arithmetic functions SQL Function Description =+ numeric Returns NUMERIC. - numeric Returns negative Numeric numeric1 + numeric2 Returns NUMERIC1 plus NUMERIC2. numeric1 - numeric2 Return NUMERIC1 minus NUMERIC2 numeric1 * numberic2 Returns NUMERIC1 multiplied by NUMERIC2 numeric1 / numeric2 Returns NUMERIC1 divided by NUMERIC2 numeric1 % numeric2 Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative. POWER(numeric1, numeric2) NUMERIC1.power(NUMERIC2) ABS(numeric) Returns the absolute value of numeric. SQRT(numeric) Returns the square root of NUMERIC. LN(numeric) Returns the natural logarithm (base e) of NUMERIC. LOG10(numeric) Returns the base 10 logarithm of numeric. LOG2(numeric) Returns the base 2 logarithm of numeric. LOG(numeric2) LOG(numeric1, numeric2) When called with one argument, returns the natural logarithm of numeric2. When called with two arguments, this function returns the logarithm of numeric2 to the base numeric1. Currently, numeric2 must be greater than 0 and numeric1 must be greater than 1. EXP(numeric) Returns e raised to the power of numeric. CEIL(numeric) CEILING(numeric) Rounds numeric up, and returns the smallest number that is greater than or equal to numeric. FLOOR(numeric) Rounds numeric down, and returns the largest number that is less than or equal to numeric. SIN(numeric) Returns the sine of numeric. SINH(numeric) Returns the hyperbolic sine of numeric. The return type is DOUBLE. COS(numeric) Returns the cosine of numeric. TAN(numeric) Returns the tangent of numeric. TANH(numeric) Returns the hyperbolic tangent of numeric. The return type is DOUBLE. COT(numeric) Returns the cotangent of a numeric. ASIN(numeric) Returns the arc sine of numeric. ACOS(numeric) Returns the arc cosine of numeric. ATAN(numeric) Returns the arc tangent of numeric. ATAN2(numeric1, numeric2) Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2). COSH(numeric) Returns the hyperbolic cosine of NUMERIC. Return value type is DOUBLE. DEGREES(numeric) Returns the degree representation of a radian NUMERIC. RADIANS(numeric) Returns the radian representation of a degree NUMERIC. SIGN(numeric) Returns the signum of NUMERIC. ROUND(NUMERIC, INT) Returns a number rounded to INT decimal places for NUMERIC. 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(INT) Returns a pseudorandom double value in the range [0.0, 1.0) with an initial seed integer. Two RAND functions will return identical sequences of numbers if they have the same initial seed. RAND_INTEGER(INT) Returns a pseudorandom double value in the range [0.0, INT) RAND_INTEGER(INT1, INT2) Returns a pseudorandom double value in the range [0.0, INT2) with an initial seed INT1. Two RAND_INTGER functions will return idential sequences of numbers if they have the same initial seed and bound. UUID() Returns an UUID (Universally Unique Identifier) string (For example: "3c3c68f7-f608-473f-b60c-b0c44ad4cc4e") according to RFC 4122 type 4 (pseudo randomly generated) UUID. The UUID is generated using a cryptographically strong pseudo random number generator. BIN(INT) Returns a string representation of INTEGER in binary format. Returns NULL if INTEGER is NULL. For example: 4.bin() returns "100" and 12.bin() returns "1100". HEX(numeric) HEX(string) Returns a string representation of an integer NUMERIC value or a STRING in hexadecimal format. Returns NULL if the argument is NULL. For example: a numeric 20 leads to "14", a numeric 100 leads to "64", a string "hello,world" leads to "68656C6C6F2C776F726C64". TRUNCATE(numeric1, integer2) Returns a numeric of truncated to integer2 decimal places. Returns NULL if numeric1 or integer2 is NULL.If integer2 is 0,the result has no decimal point or fractional part.integer2 can be negative to cause integer2 digits left of the decimal point of the value to become zero.This function can also pass in only one numeric1 parameter and not set Integer2 to use.If Integer2 is not set, the function truncates as if Integer2 were 0. For example: 42.324.truncate(2) to 42.34. and 42.324.truncate() to 42.0. Collection functions SQL Function Description CARDINALITY(array) Returns the number of elements in an array. array '[' INT ']' Returns the element at position INT in array. The index starts from 1. For example, assume that you have a field called inventory in your records with the array "inventory": ["shampoo", "conditioner", "body wash"]. To access the conditioner element in this list, use array[2]. ELEMENT(array) Returns the sole element of array (whose cardinality should be one); returns NULL if array is empty. Throws an exception if array has more than one element. CARDINALITY(map) Returns the number of entries in a map. map '[' value ']' Returns the value specified by key value in map. For example, assume that you have a field called zoo in your records with the map "zoo":{"mammals":"tiger", "fish":"goldfish"}. Then, zoo['mammals'] would return tiger. array_agg(expression) Accepts a set of values and returns an array in which each value in the set is assigned to an element of the array. This function can be used to aggregate the elements of the many-side of a 1:n join. It can be used with scalar values as well as ROW values. In contrast to JSON_ARRAYAGG(), this function emits a typed array. Unlike other aggregate functions, ARRAY_AGG() does not remove null values. If required, the FILTER clause can be used for removing null values before the aggregation, including NULL rows, which are rows that contain only NULL attributes as these are considered to be NULL themselves. If all values are removed, ARRAY_AGG() will return NULL, rather than an empty array. Example 1: Aggregating scalar values: SELECT po.id, po.customer, array_agg(ol.item) AS items FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id GROUP BY po.id, po.customer Example result: 1, 'Bob', ['hammer', 'nails'] Example 2: Aggregating ROW values: SELECT po.id, po.customer, array_agg(row(ol.id, ol.item)) AS items FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id GROUP BY po.id, po.customer Example result: 1, 'Bob', [ { 11, 'hammer' }, { 12, 'nails'} ] Example 3: Aggregating ROW values, omitting any NULL rows: SELECT po.id, po.customer, array_agg(row(ol.id, ol.item)) FILTER (WHERE ol.id IS NOT NULL) AS items FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id GROUP BY po.id, po.customer` Example result: 1, 'Bob', null Comparison functions SQL Function Description value1 = value2 Returns TRUE if value1 is equal to value2; returns UNKNOWN if value1 or value2 is NULL. value1 <> value2 Returns TRUE if value1 is not equal to value2; returns UNKNOWN if value1 or value2 is NULL. value1 > value2 Returns TRUE if value1 is greater than value2; returns UNKNOWN if value1 or value2 is NULL. value1 >= value2 Returns TRUE if value1 is greater than or equal to value2; returns UNKNOWN if value1 or value2 is NULL. value1 < value2 Returns TRUE if value1 is less than value2; returns UNKNOWN if value1 or value2 is NULL. value1 <= value2 Returns TRUE if value1 is less than or equal to value2; returns UNKNOWN if value1 or value2 is NULL. value IS NULL Returns TRUE if value is NULL. value IS NOT NULL Returns TRUE if value is not NULL. value1 IS DISTINCT FROM value2 Returns TRUE if two values are equal. NULL values are treated as identical here. For example: 1 IS NOT DISTINCT FROM NULL returns FALSE; NULL IS NOT DISTINCT FROM NULL returns TRUE. value1 IS NOT DISTINCT FROM value2 Returns TRUE if two values are equal. NULL values are treated as identical here. For example: 1 IS NOT DISTINCT FROM NULL returns FALSE; NULL IS NOT DISTINCT FROM NULL returns TRUE. value1 BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3 By default (or with the ASYMMETRIC keyword), returns TRUE if value1 is greater than or equal to value2 and less than or equal to value3. With the SYMMETRIC keyword, returns TRUE if value1 is inclusively between value2 and value3. When either value2 or value3 is NULL, returns FALSE or UNKNOWN. For example: 12 BETWEEN 15 AND 12 returns FALSE; 12 BETWEEN SYMMETRIC 15 AND 12 returns TRUE; 12 BETWEEN 10 AND NULL returns UNKNOWN; 12 BETWEEN NULL AND 10 returns FALSE; 12 BETWEEN SYMMETRIC NULL AND 12 returns UNKNOWN. value1 NOT BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3 By default (or with the ASYMMETRIC keyword), returns TRUE if value1 is less than value2 or greater than value3. With the SYMMETRIC keyword, returns TRUE if value1 is not inclusively between value2 and value3. When either value2 or value3 is NULL, returns TRUE or UNKNOWN. For example: 12 NOT BETWEEN 15 AND 12 returns TRUE; 12 NOT BETWEEN SYMMETRIC 15 AND 12 returns FALSE; 12 NOT BETWEEN NULL AND 15 returns UNKNOWN; 12 NOT BETWEEN 15 AND NULL returns TRUE; 12 NOT BETWEEN SYMMETRIC 12 AND NULL returns UNKNOWN. string1 LIKE string2 [ ESCAPE char ] Returns TRUE if string1 matches pattern string2; returns UNKNOWN if string1 or string2 is NULL. An escape character can be defined if necessary. The escape character has not been supported yet. string1 NOT LIKE string2 [ ESCAPE char ] Returns TRUE if string1 does not match pattern string2; returns UNKNOWN if string1 or string2 is NULL. An escape character can be defined if necessary. The escape character has not been supported yet. string1 SIMILAR TO string2 [ ESCAPE char ] Returns TRUE if string1 matches SQL regular expression string2; returns UNKNOWN if string1 or string2 is NULL. An escape character can be defined if necessary. The escape character has not been supported yet. string1 NOT SIMILAR TO string2 [ ESCAPE char ] Returns TRUE if string1 does not match SQL regular expression string2; returns UNKNOWN if string1 or string2 is NULL. An escape character can be defined if necessary. The escape character has not been supported yet. value1 IN (value2 [, value3]* ) Returns TRUE if value1 exists in the given list (value2, value3, …). When (value2, value3, …). contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if value1 is NULL. For example: 4 IN (1, 2, 3) returns FALSE; 1 IN (1, 2, NULL) returns TRUE; 4 IN (1, 2, NULL) returns UNKNOWN. value1 NOT IN (value2 [, value3]* ) Returns TRUE if value1 does not exist in the given list (value2, value3, …). When (value2, value3, …). contains NULL, returns FALSE if value1 can be found and UNKNOWN otherwise. Always returns UNKNOWN if value1 is NULL. For example: 4 NOT IN (1, 2, 3) returns TRUE; 1 NOT IN (1, 2, NULL) returns FALSE; 4 NOT IN (1, 2, NULL) returns UNKNOWN. EXISTS (sub-query) Returns TRUE if sub-query returns at least one row. Only supported if the operation can be rewritten in a join and group operation. For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. value IN (sub-query) Returns TRUE if value is equal to a row returned by sub-query. value NOT IN (sub-query) Returns TRUE if value is not equal to a row returned by sub-query. Conditional functions SQL Function Description CASE value WHEN value1_1 [, value1_2]* THEN RESULT1 (WHEN value2_1 [, value2_2 ]* THEN result_2)* (ELSE result_z) END Returns resultX when the first time value is contained in (valueX_1, valueX_2, …). When no value matches, returns result_z if it is provided and returns NULL otherwise. CASE WHEN condition1 THEN result1 (WHEN condition2 THEN result2)* (ELSE result_z) END Returns resultX when the first conditionX is met. When no condition is met, returns result_z if it is provided and returns NULL otherwise. NULLIF(value1, value2) Returns NULL if value1 is equal to value2; returns value1 otherwise. For example: NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5. COALESCE(value1, value2 [, value3]*) Returns the first value that is not NULL from value1, value2, …. For example: COALESCE(NULL, 5) returns 5. IF(condition, true_value, false_value) Returns the true_value if condition is met, otherwise false_value. For example: IF(5 > 3, 5, 3) returns 5. IFNULL(input, null_replacement) Returns null_replacement if input is NULL; otherwise input is returned. Compared to COALESCE or CASE WHEN, this function returns a data type that is very specific in terms of nullability. The returned type is the common type of both arguments but only nullable if the null_replacement is nullable. The function allows to pass nullable columns into a function or table that is declared with a NOT NULL constraint. For example: IFNULL(nullable_column, 5) returns never NULL. IS_ALPHA(string) Returns true if all characters in string are letter, otherwise false. IS_DECIMAL(string) Returns true if string can be parsed to a valid numeric, otherwise false. IS_DIGIT(string) Returns true if all characters in string are digit, otherwise false. GREATEST(value1[, value2]*) Returns the greatest value of the list of arguments. Returns NULL if any argument is NULL. LEAST(value1[, value2]*) Returns the least value of the list of arguments. Returns NULL if any argument is NULL. Grouping functions SQL Function Description GROUP_ID() Returns an integer that uniquely identifies the combination of grouping keys. GROUPING(expression1 [, expression2]* ) GROUPING_ID(expression1 [, expression2]* ) Returns a bit vector of the given grouping expressions. Hash Functions SQL Function Description MD5(string) Returns the MD5 hash of string as a string of 32 hexadecimal digits; returns NULL if string is NULL. SHA1(string) Returns the SHA-224 hash of string as a string of 56 hexadecimal digits; returns NULL if string is NULL. SHA224(string) Returns the SHA-224 hash of string as a string of 56 hexadecimal digits; returns NULL if string is NULL. SHA256(string) Returns the SHA-256 hash of string as a string of 64 hexadecimal digits; returns NULL if string is NULL. SHA384(string) Returns the SHA-384 hash of string as a string of 96 hexadecimal digits; returns NULL if string is NULL. SHA512(string) Returns the SHA-512 hash of string as a string of 128 hexadecimal digits; returns NULL if string is NULL. SHA2(string, hashLength) Returns the hash using the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, or SHA-512). The first argument string is the string to be hashed and the second argument hashLength is the bit length of the result (224, 256, 384, or 512). Returns NULL if string or hashLength is NULL. JSON Functions SQL Function Description JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]) Determines whether a JSON string satisfies a given path search criteria. If the error behavior is omitted, FALSE ON ERROR is assumed as the default. # Returns TRUE SELECT JSON_EXISTS('{"a": true}', '$.a'); # Returns FALSE SELECT JSON_EXISTS('{"a": true}', '$.b'); # Returns TRUE SELECT JSON_EXISTS('{"a": [{ "b": 1 }]}', '$.a[0].b'); # Returns TRUE SELECT JSON_EXISTS('{"a": true}', 'strict $.b' TRUE ON ERROR); # Returns False SELECT JSON_EXISTS('{"a": true}', 'strict $.b' FALSE ON ERROR); JSON_VALUE(jsonValue, path [RETURNING ] [ { NULL | ERROR | DEFAULT } ON EMPTY ] [ { NULL | ERROR | DEFAULT } ON ERROR ]) STRING.jsonValue(STRING path [, returnType, onEmpty, defaultOnEmpty, onError, defaultOnError]) Extracts a scalar from a JSON string. This method searches a JSON string for a given path expression and returns the value if the value at that path is scalar. Non-scalar values cannot be returned. By default, the value is returned as STRING. Using returningType a different type can be chosen, with the following types being supported: VARCHAR / STRING BOOLEAN INTEGER DOUBLE For empty path expressions or errors a behavior can be defined to either return null, raise an error or return a defined default value instead. When omitted, the default is NULL ON EMPTY or NULL ON ERROR, respectively. The default value may be a literal or an expression. If the default value itself raises an error, it falls through to the error behavior for ON EMPTY, and raises an error for ON ERROR. Returns STRING: "true" JSON_VALUE('{"a": true}', '$.a') Returns BOOLEAN: true JSON_VALUE('{"a": true}', '$.a' RETURNING BOOLEAN) Returns STRING: "false" JSON_VALUE('{"a": true}', 'lax $.b' DEFAULT FALSE ON EMPTY) Returns STRING: "false" JSON_VALUE('{"a": true}', 'strict $.b' DEFAULT FALSE ON ERROR) JSON_VALUE(jsonValue, path [RETURNING ] [ { NULL | ERROR | DEFAULT } ON EMPTY ] [ { NULL | ERROR | DEFAULT } ON ERROR ]) Extracts a scalar from a JSON string. This method searches a JSON string for a given path expression and returns the value if the value at that path is scalar. Non-scalar values cannot be returned. By default, the value is returned as STRING. Using returningType a different type can be chosen, with the following types being supported: VARCHAR / STRING BOOLEAN INTEGER DOUBLE For empty path expressions or errors a behavior can be defined to either return null, raise an error or return a defined default value instead. When omitted, the default is NULL ON EMPTY or NULL ON ERROR, respectively. The default value may be a literal or an expression. If the default value itself raises an error, it falls through to the error behavior for ON EMPTY, and raises an error for ON ERROR. # Returns STRING: "true" JSON_VALUE('{"a": true}', '$.a') # Returns BOOLEAN: true JSON_VALUE('{"a": true}', '$.a' RETURNING BOOLEAN) # Returns STRING: "false" JSON_VALUE('{"a": true}', 'lax $.b' DEFAULT FALSE ON EMPTY) # Returns STRING: "false" JSON_VALUE('{"a": true}', 'strict $.b' DEFAULT FALSE ON ERROR) IS JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ] Determine whether a given string is valid JSON. Specifying the optional type argument puts a constraint on which type of JSON object is allowed. If the string is valid JSON, but not that type, false is returned. The default is VALUE. # Returns TRUE '1' IS JSON '[]' IS JSON '{}' IS JSON # Returns TRUE '"abc"' IS JSON # Returns FALSE 'abc' IS JSON NULL IS JSON # Returns TRUE '1' IS JSON SCALAR # Returns FALSE '1' IS JSON ARRAY # Returns FALSE '1' IS JSON OBJECT # Returns FALSE '{}' IS JSON SCALAR # Returns FALSE '{}' IS JSON ARRAY # Returns TRUE '{}' IS JSON OBJECT JSON_STRING(value) Serializes a value into JSON. This function returns a JSON string containing the serialized value. If the value is NULL, the function returns NULL. # Returns NULL JSON_STRING(CAST(NULL AS INT)) # Returns '1' JSON_STRING(1) # Returns 'true' JSON_STRING(TRUE) # Returns '"Hello, World!"' JSON_STRING('Hello, World!') # Returns '[1,2]' JSON_STRING(ARRAY[1, 2]) JSON_QUERY(jsonValue, path [ { WITHOUT | WITH CONDITIONAL | WITH UNCONDITIONAL } [ ARRAY ] WRAPPER ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON EMPTY ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON ERROR ]) Extracts JSON values from a JSON string. The result is always returned as a STRING. The RETURNING clause is currently not supported. The wrappingBehavior determines whether the extracted value should be wrapped into an array, and whether to do so unconditionally or only if the value itself isn’t an array already. onEmpty and onError determine the behavior in case the path expression is empty, or in case an error was raised, respectively. By default, in both cases null is returned. Other choices are to use an empty array, an empty object, or to raise an error. # Returns '{ "b": 1 }' JSON_QUERY('{ "a": { "b": 1 } }', '$.a') # Returns '[1, 2]' JSON_QUERY('[1, 2]', '$') # Returns NULL JSON_QUERY(CAST(NULL AS STRING), '$') # Returns '["c1","c2"]' JSON_QUERY('{"a":[{"c":"c1"},{"c":"c2"}]}', 'lax $.a[*].c') -- Wrap result into an array # Returns '[{}]' JSON_QUERY('{}', '$' WITH CONDITIONAL ARRAY WRAPPER) # Returns '[1, 2]' JSON_QUERY('[1, 2]', '$' WITH CONDITIONAL ARRAY WRAPPER) # Returns '[[1, 2]]' JSON_QUERY('[1, 2]', '$' WITH UNCONDITIONAL ARRAY WRAPPER) -- Scalars must be wrapped to be returned # Returns NULL JSON_QUERY(1, '$') # Returns '[1]' JSON_QUERY(1, '$' WITH CONDITIONAL ARRAY WRAPPER) -- Behavior if path expression is empty / there is an error # Returns '{}' JSON_QUERY('{}', 'lax $.invalid' EMPTY OBJECT ON EMPTY) # Returns '[]' JSON_QUERY('{}', 'strict $.invalid' EMPTY ARRAY ON ERROR) JSON_OBJECT([[KEY] key VALUE value]* [ { NULL | ABSENT } ON NULL ]) Builds a JSON object string from a list of key-value pairs. Note that keys must be non-NULL string literals, while values may be arbitrary expressions. This function returns a JSON string. The ON NULL behavior defines how to treat NULL values. If omitted, NULL ON NULL is assumed by default. Values which are created from another JSON construction function call (JSON_OBJECT, JSON_ARRAY) are inserted directly rather than as a string. This allows building nested JSON structures. # Returns '{}' JSON_OBJECT() # Returns '{"K1":"V1","K2":"V2"}' JSON_OBJECT('K1' VALUE 'V1', 'K2' VALUE 'V2') -- Expressions as values JSON_OBJECT('orderNo' VALUE orders.orderId) -- ON NULL JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) NULL ON NULL) -- '{"K1":null}' JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) ABSENT ON NULL) -- '{}' # Returns '{"K1":{"K2":"V"}}' JSON_OBJECT( KEY 'K1' VALUE JSON_OBJECT( KEY 'K2' VALUE 'V' ) ) JSON_OBJECTAGG([KEY] key VALUE value [ { NULL | ABSENT } ON NULL ]) Builds a JSON object string by aggregating key-value expressions into a single JSON object. The key expression must return a non-nullable character string. Value expressions can be arbitrary, including other JSON functions. If a value is NULL, the ON NULL behavior defines what to do. If omitted, NULL ON NULL is assumed by default. Note that keys must be unique. If a key occurs multiple times, an error will be thrown. This function is currently not supported in OVER windows. Returns '{"Apple":2,"Banana":17,"Orange":0}' SELECT JSON_OBJECTAGG(KEY product VALUE cnt) FROM orders JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]) Determines whether a JSON string satisfies a given path search criterion. If the error behavior is omitted, FALSE ON ERROR is assumed as the default. SELECT JSON_EXISTS('{"a": true}', '$.a'); SELECT JSON_EXISTS('{"a": true}', '$.b'); SELECT JSON_EXISTS('{"a": [{ "b": 1 }]}', '$.a[0].b'); SELECT JSON_EXISTS('{"a": true}', 'strict $.b' TRUE ON ERROR); SELECT JSON_EXISTS('{"a": true}', 'strict $.b' FALSE ON ERROR); JSON_VALUE(jsonValue, path [RETURNING ] [ { NULL | ERROR | DEFAULT } ON EMPTY ] [ { NULL | ERROR | DEFAULT } ON ERROR ]) Extracts a scalar from a JSON string. This method searches a JSON string for a given path expression and returns the value if the value at that path is scalar. Non-scalar values cannot be returned. By default, the value is returned as STRING. Using returningType a different type can be chosen, with the following types being supported: VARCHAR / STRING BOOLEAN INTEGER DOUBLE For empty path expressions or errors a behavior can be defined to either return null, raise an error or return a defined default value instead. When omitted, the default is NULL ON EMPTY or NULL ON ERROR, respectively. The default value may be a literal or an expression. If the default value itself raises an error, it falls through to the error behavior for ON EMPTY, and raises an error for ON ERROR. JSON_VALUE('{"a": true}', '$.a') JSON_VALUE('{"a": true}', '$.a' RETURNING BOOLEAN) JSON_VALUE('{"a": true}', 'lax $.b' DEFAULT FALSE ON EMPTY) JSON_VALUE('{"a": true}', 'strict $.b' DEFAULT FALSE ON ERROR) IS JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ] Determine whether a given string is valid JSON. Specifying the optional type argument puts a constraint on which type of JSON object is allowed. If the string is valid JSON, but not that type, false is returned. The default is VALUE. -- TRUE '1' IS JSON '[]' IS JSON '{}' IS JSON -- TRUE '"abc"' IS JSON -- FALSE 'abc' IS JSON NULL IS JSON -- TRUE '1' IS JSON SCALAR -- FALSE '1' IS JSON ARRAY -- FALSE '1' IS JSON OBJECT -- FALSE '{}' IS JSON SCALAR -- FALSE '{}' IS JSON ARRAY -- TRUE '{}' IS JSON OBJECT JSON_STRING(value) Serializes a value into JSON. This function returns a JSON string containing the serialized value. If the value is NULL, the function returns NULL. -- NULL JSON_STRING(CAST(NULL AS INT)) -- '1' JSON_STRING(1) -- 'true' JSON_STRING(TRUE) -- '"Hello, World!"' JSON_STRING('Hello, World!') -- '[1,2]' JSON_STRING(ARRAY[1, 2]) JSON_QUERY(jsonValue, path [ { WITHOUT | WITH CONDITIONAL | WITH UNCONDITIONAL } [ ARRAY ] WRAPPER ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON EMPTY ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON ERROR ]) Extracts JSON values from a JSON string. The result is always returned as a STRING. The RETURNING clause is currently not supported. The wrappingBehavior determines whether the extracted value should be wrapped into an array, and whether to do so unconditionally or only if the value itself isn’t an array already. onEmpty and onError determine the behavior in case the path expression is empty, or in case an error was raised, respectively. By default, in both cases null is returned. Other choices are to use an empty array, an empty object, or to raise an error. -- `{ "b": 1 }' JSON_QUERY('{ "a": { "b": 1 } }', '$.a') -- '[1, 2]' JSON_QUERY('[1, 2]', '$') -- NULL JSON_QUERY(CAST(NULL AS STRING), '$') -- '["c1","c2"]' JSON_QUERY('{"a":[{"c":"c1"},{"c":"c2"}]}', 'lax $.a[*].c') -- Wrap result into an array -- '[{}]' JSON_QUERY('{}', '$' WITH CONDITIONAL ARRAY WRAPPER) -- '[1, 2]' JSON_QUERY('[1, 2]', '$' WITH CONDITIONAL ARRAY WRAPPER) -- '[[1, 2]]' JSON_QUERY('[1, 2]', '$' WITH UNCONDITIONAL ARRAY WRAPPER) -- Scalars must be wrapped to be returned -- NULL JSON_QUERY(1, '$') -- '[1]' JSON_QUERY(1, '$' WITH CONDITIONAL ARRAY WRAPPER) -- Behavior if path expression is empty / there is an error -- '{}' JSON_QUERY('{}', 'lax $.invalid' EMPTY OBJECT ON EMPTY) -- '[]' JSON_QUERY('{}', 'strict $.invalid' EMPTY ARRAY ON ERROR) JSON_OBJECT([[KEY] key VALUE value]* [ { NULL | ABSENT } ON NULL ]) Builds a JSON object string from a list of key-value pairs. Note that keys must be non-NULL string literals, while values may be arbitrary expressions. This function returns a JSON string. The ON NULL behavior defines how to treat NULL values. If omitted, NULL ON NULL is assumed by default. Values which are created from another JSON construction function call (JSON_OBJECT, JSON_ARRAY) are inserted directly rather than as a string. This allows building nested JSON structures. -- '{}' JSON_OBJECT() -- '{"K1":"V1","K2":"V2"}' JSON_OBJECT('K1' VALUE 'V1', 'K2' VALUE 'V2') -- Expressions as values JSON_OBJECT('orderNo' VALUE orders.orderId) -- ON NULL JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) NULL ON NULL) -- '{"K1":null}' JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) ABSENT ON NULL) -- '{}' -- '{"K1":{"K2":"V"}}' JSON_OBJECT( KEY 'K1' VALUE JSON_OBJECT( KEY 'K2' VALUE 'V' ) ) JSON_OBJECTAGG([KEY] key VALUE value [ { NULL | ABSENT } ON NULL ]) Builds a JSON object string by aggregating key-value expressions into a single JSON object. The key expression must return a non-nullable character string. Value expressions can be arbitrary, including other JSON functions. If a value is NULL, the ON NULL behavior defines what to do. If omitted, NULL ON NULL is assumed by default. Note that keys must be unique. If a key occurs multiple times, an error will be thrown. This function is currently not supported in OVER windows. -- '{"Apple":2,"Banana":17,"Orange":0}' SELECT JSON_OBJECTAGG(KEY product VALUE cnt) FROM orders JSON_ARRAY([value]* [ { NULL | ABSENT } ON NULL ]) Builds a JSON array string from a list of values. This function returns a JSON string. The values can be arbitrary expressions. The ON NULL behavior defines how to treat NULL values. If omitted, ABSENT ON NULL is assumed by default. Elements which are created from another JSON construction function call (JSON_OBJECT, JSON_ARRAY) are inserted directly rather than as a string. This allows building nested JSON structures. -- '[]' JSON_ARRAY() -- '[1,"2"]' JSON_ARRAY(1, '2') -- Expressions as values JSON_ARRAY(orders.orderId) -- ON NULL JSON_ARRAY(CAST(NULL AS STRING) NULL ON NULL) -- '[null]' JSON_ARRAY(CAST(NULL AS STRING) ABSENT ON NULL) -- '[]' -- '[[1]]' JSON_ARRAY(JSON_ARRAY(1)) Logical Functions SQL Function Description boolean1 OR boolean2 Returns TRUE if BOOLEAN1 is TRUE or BOOLEAN2 is TRUE. Supports three-valued logic. For example: true || Null(BOOLEAN) returns TRUE. boolean1 AND boolean2 Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic. For example: true && Null(BOOLEAN) returns UNKNOWN. NOT boolean Returns TRUE if boolean is FALSE; returns FALSE if boolean is TRUE; returns UNKNOWN if boolean is UNKNOWN. boolean IS FALSE Returns TRUE if boolean is FALSE; returns FALSE if boolean is TRUE or UNKNOWN. boolean IS NOT FALSE Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE. boolean IS TRUE Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN. boolean IS NOT TRUE Returns TRUE if boolean is FALSE or UNKNOWN; returns FALSE if boolean is TRUE. boolean IS UNKNOWN Returns TRUE if boolean is UNKNOWN; returns FALSE if boolean is TRUE or FALSE. boolean IS NOT UNKNOWN Returns TRUE if boolean is TRUE or FALSE; returns FALSE if boolean is UNKNOWN. Stream Conversion Functions SQL Function Description to_append(<change_stream_name>) Convert a change stream into an append stream. See Process a change stream as an append stream for an example. to_change(<append_stream_name>) Convert an append stream into a change stream. You must have a partition key in the append stream in order to convert it into a change stream. When converted, the partition key becomes the change stream’s primary key. See Process an append stream as a change stream for an example. String Functions SQL Function Description ASCII(string) Returns the numeric value of the first character of string. Returns NULL if string is NULL. For example: ascii('abc') returns 97, and ascii(CAST(NULL AS VARCHAR)) returns NULL. CHAR_LENGTH(string) CHARACTER_LENGTH(string) Returns the number of characters in STRING. CHR(integer) Returns the ASCII character having the binary equivalent to integer. If integer is larger than 255, we will get the modulus of integer divided by 255 first, and returns CHR of the modulus. Returns NULL if integer is NULL. For example: chr(97) returns a, chr(353) returns a, and ascii(CAST(NULL AS VARCHAR)) returns NULL. CONCAT(string1, string2, …) Returns a string that concatenates string1, string2, …. Returns NULL if any argument is NULL. For example: CONCAT('AA', 'BB', 'CC') returns "AABBCC". CONCAT_WS(string1, string2, string3, …) Returns a string that concatenates STRING2, STRING3, … with a separator STRING1. The separator is added between the strings to be concatenated. Returns NULL If STRING1 is NULL. Compared with concat(), concat_ws() automatically skips NULL arguments. For example: concat_ws('~', 'AA', Null(STRING), 'BB', '', 'CC') returns "AABB\~CC". GROK(text_field, pattern) Extract values out of text with grok patterns. This function returns a map where the keys are specified in the pattern argument. Grok provides a simplified pattern syntax and uses regular expressions behind the scenes to do the parsing. For example, the statement: GROK('raw_text', '\\[%{TIMESTAMP_ISO8601:timestamp}\\] \"%{DATA:method}\" %{NUMBER:bytes_sent}') for the input record {\"raw_text\":\"[2022-06-10T21:26:22Z] \\\"GET\\\" 6977\"} returns `{\"timestamp\":\"2002-06-10 21:26:22\",\"method\":\"GET\",\"bytes_sent\":6977} INITCAP(string) Returns a new form of STRING with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters. UPPER(string) Returns STRING in uppercase. string1 || string2 Returns the concatenation of STRING1 and STRING2. POSITION(string1 IN string2) Returns the position (start from 1) of the first occurrence of STRING1 in STRING2; returns 0 if STRING1 cannot be found in STRING2. TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2) Returns a string that removes leading and/or trailing characters STRING2 from STRING1. By default, whitespaces at both sides are removed. LOWER(string) Returns string in lowercase. LTRIM(string) Returns a string that removes the left whitespaces from STRING. For example: ' This is a test String.'.ltrim() returns "This is a test String.". RTRIM(string) Returns a string that removes the right whitespaces from STRING.For example: 'This is a test String. '.rtrim() returns "This is a test String.". REPEAT(string, int) Returns a string that repeats the base string integer times. For example: REPEAT('This is a test String.', 2) returns "This is a test String.This is a test String.". REGEXP_REPLACE(string1, string2, string3) Returns a string from STRING1 with all the substrings that match a regular expression STRING2 consecutively being replaced with STRING3. REGEXP_REPLACE('apple banana cherry', 'a\w+', 'fruit') OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ]) Returns a string that replaces INT2 (STRING2’s length by default) characters of STRING1 with STRING2 from position INT1. For example: 'xxxxxtest'.overlay('xxxx', 6) returns "xxxxxxxxx"; 'xxxxxtest'.overlay('xxxx', 6, 2) returns "xxxxxxxxxst". SUBSTRING(string FROM integer1 [ FOR integer2 ]) Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default). REPLACE(string1, string2, string3) Returns a new string which replaces all the occurrences of STRING2 with STRING3 (non-overlapping) from STRING1. For example: 'hello world'.replace('hello', 'salutations') returns 'salutations world'; 'ababab'.replace('abab', 'z') returns 'zab'. REGEXP_EXTRACT(string1, string2[, integer]) Returns a string from string1 which extracted with a specified regular expression string2 and a regex match group index integer. The regex match group index starts from 1 and 0 means matching the whole regex. In addition, the regex match group index should not exceed the number of the defined groups. For example: REGEXP_EXTRACT('foothebar', 'foo(.\*?)(bar)', 2)" returns "bar". LPAD(string1, integer, string2) Returns a new string from string1 left-padded with string2 to a length of integer characters. If the length of string1 is shorter than integer, returns string1 shortened to integer characters. For example: LPAD('hi',4,'??') returns "??hi"; LPAD('hi',1,'??') returns "h". RPAD(string1, integer, string2) Returns a new string from string1 right-padded with string2 to a length of integer characters. If the length of string1 is shorter than integer, returns string1 shortened to integer characters. For example: RPAD('hi',4,'??') returns "hi??", RPAD('hi',1,'??') returns "h". DECODE(binary, string) Decodes the first argument into a String using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. FROM_BASE64(string) Returns the base64-decoded result from string; returns NULL if string is NULL. For example: FROM_BASE64('aGVsbG8gd29ybGQ=') returns "hello world". TO_BASE64(string) Returns the base64-encoded result from string; returns NULL if string is NULL. For example: TO_BASE64('hello world') returns "aGVsbG8gd29ybGQ=". ENCODE(string1, string2) Encodes the string1 into a BINARY using the provided string2 character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. INSTR(string1, string2) Returns the position of the first occurrence of string2 in string1. Returns NULL if any of arguments is NULL. LEFT(string, integer) Returns the leftmost integer characters from the string. Returns EMPTY String if integer is negative. Returns NULL if any argument is NULL. RIGHT(string, integer) Returns the rightmost integer characters from the string. Returns EMPTY String if integer is negative. Returns NULL if any argument is NULL. LCOATE(string1, string2[, integer]) Returns the position of the first occurrence of string1 in string2 after position integer. Returns 0 if not found. Returns NULL if any of arguments is NULL. PARSE_URL(string1, string2[, string3]) Returns the specified part from the URL. Valid values for string2 include 'HOST', 'PATH', 'QUERY', 'REF', 'PROTOCOL', 'AUTHORITY', 'FILE', and 'USERINFO'. Returns NULL if any of arguments is NULL. For example: parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST'), returns 'facebook.com'. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument string3. For example: parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') returns 'v1'. REGEXP(string1, string2) Returns TRUE if any (possibly empty) substring of string1 matches the Java regular expression string2, otherwise FALSE. Returns NULL if any of arguments is NULL. REVERSE(string) Returns the reversed string. Returns NULL if string is NULL. SPLIT_INDEX(string1, string2, integer1) Splits string1 by the delimiter string2, returns the integerth (zero-based) string of the split strings. Returns NULL if integer is negative. Returns NULL if any of arguments is NULL. STR_TO_MAP(string1[, string2, string3]]) Returns a map after splitting the string1 into key/value pairs using delimiters. string2 is the pair delimiter, default is ','. And string3 is the key-value delimiter, default is '='. SUBSTR(string\[, integer1[, integer2]]) Returns a substring of string starting from position integer1 with length integer2 (to the end by default). ip_country(ip_address) Returns the 2 character ISO country code for a given ip address. Returns NULL if a country cannot be found or the ip address is invalid. For example, select ip_country('108.162.167.61') returns CA. Table-Valued Functions Table-Valued Functions create time-bounded windows of your data that allow you to perform aggregations. See the windowing documentation for more information. SQL Function Description tumble(table <data>, descriptor(<timecol>), <size>) Tumble creates adjoining windows of equal size. It counts each input event in exactly one window. hop(table <data>, descriptor(<timecol>), <slide>, <size>) Hop creates windows of equal size which may overlap. For this reason, an input event may be present in more than one window. Hopping windows are also known as "sliding windows". cumulate(table <data>, descriptor(<timecol>), <step>, <size>) Cumulate creates cumulative windows of increasing size from the same start time until a maximum window size is reached. At that point, the previous window is closed and a new one is opened. Input events may be counted in more than one window. Temporal Functions SQL Function Description DATE string Returns a SQL date parsed from string in the form yyyy-MM-dd. TIME string Returns a SQL time parsed from string in the form HH:mm:ss. TIMESTAMP string Returns a SQL timestamp parsed from a string yyyy-MM-dd HH:mm:ss. INTERVAL string range YEAR(date) Creates an interval of months for NUMERIC years. LOCALTIME Returns the current SQL time in the local time zone, the return type is TIME(0). It is evaluated for each record in streaming mode. But in batch mode, it is evaluated once as the query starts and uses the same result for every row. LOCALTIMESTAMP Returns the current SQL timestamp in local time zone. CURRENT_TIME Returns the current SQL time in the local time zone, this is a synonym of LOCAL_TIME. CURRENT_DATE Returns the current SQL date in the local time zone. It is evaluated for each record in streaming mode. But in batch mode, it is evaluated once as the query starts and uses the same result for every row. CURRENT_TIMESTAMP Returns the current SQL timestamp in the local time zone, the return type is TIMESTAMP_LTZ(3). It is evaluated for each record in streaming mode. But in batch mode, it is evaluated once as the query starts and uses the same result for every row. NOW() Returns the current SQL timestamp in the local time zone, this is a synonym of CURRENT_TIMESTAMP. CURRENT_ROW_TIMESTAMP() Returns the current SQL timestamp in the local time zone, the return type is TIMESTAMP_LTZ(3). It is evaluated for each record no matter in batch or streaming mode." EXTRACT(timeintervalunit FROM temporal) Returns a long value extracted from the timeintervalunit part of temporal. For example: EXTRACT(DAY FROM DATE '2006-06-05') returns 5. YEAR(date) Returns the year from SQL date date. Equivalent to EXTRACT(YEAR FROM date). For example: YEAR(DATE '1994-09-27') returns 1994. QUARTER(date) Returns the quarter of a year (an integer between 1 and 4) from SQL date date. Equivalent to EXTRACT(QUARTER FROM date). For example: QUARTER(DATE '1994-09-27') returns 3. MONTH(date) Returns the month of a year (an integer between 1 and 12) from SQL date date. Equivalent to EXTRACT(MONTH FROM date). For example: MONTH(DATE '1994-09-27') returns 9. WEEK(date) Returns the week of a year (an integer between 1 and 53) from SQL date date. Equivalent to EXTRACT(WEEK FROM date). For example: WEEK(DATE '1994-09-27') returns 39. DAYOFYEAR(date) Returns the day of a year (an integer between 1 and 366) from SQL date date. Equivalent to EXTRACT(DOY FROM date). For example: DAYOFYEAR(DATE '1994-09-27') returns 270. DAYOFMONTH Returns the day of a month (an integer between 1 and 31) from SQL date date. Equivalent to EXTRACT(DAY FROM date). For example: DAYOFWEEK(DATE '1994-09-27') returns 3. HOUR(timestamp) Returns the hour of a day (an integer between 0 and 23) from SQL timestamp timestamp. Equivalent to EXTRACT(HOUR FROM timestamp). For example: MINUTE(TIMESTAMP '1994-09-27 13:14:15') returns 14. MINUTE(timestamp) Returns the minute of an hour (an integer between 0 and 59) from SQL timestamp timestamp. Equivalent to EXTRACT(MINUTE FROM timestamp). For example: MINUTE(TIMESTAMP '1994-09-27 13:14:15') returns 14. SECOND(timestamp) Returns the second of a minute (an integer between 0 and 59) from SQL timestamp. Equivalent to EXTRACT(SECOND FROM timestamp). For example: SECOND(TIMESTAMP '1994-09-27 13:14:15') returns 15. FLOOR(timepoint TO timeintervalunit) Returns a value that rounds timepoint down to the time unit timeintervalunit. For example: FLOOR(TIME '12:44:31' TO MINUTE) returns 12:44:00. CEIL(timepoint TO timeintervalunit) Returns a value that rounds timepoint up to the time unit timeintervalunit. For example: CEIL(TIME '12:44:31' TO MINUTE) returns 12:45:00. (timepoint1, temporal1) OVERLAPS (timepoint2, temporal2) Returns TRUE if two time intervals defined by (timepoint1, temporal1) and (timepoint2, temporal2) overlap. The temporal values can be either a time point or a time interval. For example: (TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) returns TRUE; (TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR) returns FALSE. DATE_FORMAT(timestamp, string) Converts timestamp to a value of string in the format specified by the date format string. The format string is compatible with Java’s SimpleDateFormat. TIMESTAMPADD(timeintervalunit, interval, timepoint) The unit for interval is given by the unit argument, which should be one of the following values: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. \n \nFor example: TIMESTAMPADD(WEEK, 1, DATE '2003-01-02') returns 2003-01-09. TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2) Returns the (signed) number of timepointunit between timepoint1 and timepoint2. The unit for the interval is given by the first argument, which should be one of the following values: SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR. CONVERT_TZ(string1, string2, string3) Converts a datetime string1 (with default ISO timestamp format 'yyyy-MM-dd HH:mm:ss') from time zone string2 to time zone string3. The format of time zone should be either an abbreviation such as "PST", a full name such as "America/Los_Angeles", or a custom ID such as "GMT-08:00". For example: CONVERT_TZ('1970-01-01 00:00:00', 'UTC', 'America/Los_Angeles') returns '1969-12-31 16:00:00'. FROM_UNIXTIME(numeric[, string]) Returns a representation of the numeric argument as a value in string format (default is 'yyyy-MM-dd HH:mm:ss'). numeric is an internal timestamp value representing seconds since '1970-01-01 00:00:00' UTC, such as produced by the UNIX_TIMESTAMP() function. The return value is expressed in the session time zone (specified in TableConfig). For example: FROM_UNIXTIME(44) returns '1970-01-01 00:00:44' if in UTC time zone, but returns '1970-01-01 09:00:44' if in 'Asia/Tokyo' time zone. 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 string1 in format string2 (by default: yyyy-MM-dd HH:mm:ss if not specified) to Unix timestamp (in seconds), using the specified timezone in table config. TO_DATE(string1[, string2]) Converts a date string string1 with format string2 (by default 'yyyy-MM-dd') to a date. TO_TIMESTAMP_LTZ(numeric, precision) Converts a epoch seconds or epoch milliseconds to a TIMESTAMP_LTZ, the valid precision is 0 or 3, the 0 represents TO_TIMESTAMP_LTZ(epochSeconds, 0), the 3 represents TO_TIMESTAMP_LTZ(epochMilliseconds, 3). TO_TIMESTAMP(string1[, string2]) Converts date time string string1 with format string2 (by default: 'yyyy-MM-dd HH:mm:ss') under the session time zone (specified by TableConfig) to a timestamp. Type Conversion Functions SQL Function Description CAST(value AS type) Returns a new value being cast to type type. For example: CAST('42' AS INT) returns 42; CAST(NULL AS VARCHAR) returns NULL of type VARCHAR. TRY_CAST(value AS type) Like CAST, but in case of error, returns NULL rather than failing the job. For example: TRY_CAST(‘42’ AS INT) returns 42; TRY_CAST(NULL AS STRING) returns NULL of type STRING; TRY_CAST(’non-number’ AS INT) returns NULL of type INT; COALESCE(TRY_CAST(’non-number’ AS INT), 0) returns 0 of type INT. TYPEOF(input) TYPEOF(input, force_serializable) Returns the string representation of the input expression’s data type. By default, the returned string is a summary string that might omit certain details for readability. If force_serializable is set to TRUE, the string represents a full data type that can be persisted in a catalog. Note that especially anonymous, inline data types have no serializable string representation. In this case, NULL is returned. Value Access Functions SQL Function Description tableName.compositeType.field Returns the value of a field from a Flink composite type (For example: Tuple, POJO) by name. tableName.compositeType.* Returns a flat representation of a Flink composite type (For example: Tuple, POJO) that converts each of its direct subtype into a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (For example: mypojo$mytuple$f0). Value Construction Functions SQL Function Description — implicit constructor with parenthesis (value1 [, value2]*) Returns a row created from a list of values (value1, value2,…). The implicit row constructor supports arbitrary expressions as fields but requires at least two fields. The explicit row constructor can deal with an arbitrary number of fields but does not support all kinds of field expressions well currently. ARRAY ‘[’ value1 [, value2 ]* ‘]’ Returns an array created from a list of values (value1, value2, …). MAP ‘[’ value1, value2 [, value3, value4 ]* ‘]’ Returns a map created from a list of key-value pairs ((value1, value2), (value3, value4), …). XML Functions SQL Function Description xpaths(xml[, key, xPathExpression]*) Extract values out of a XML with xpath expressions. This function returns a map where the keys are the key arguments and the values are the data extracted from the xml by the paired xPathExpression argument. For example, the input xpaths( '<foo><bar id="1">baz</bar></foo>', 'extractedId', '/foo/bar/@id, 'value', '/foo/bar/text()' ) returns {'extractedId': '1', 'value': baz} This product includes GeoLite2 data created by MaxMind, available from https://www.maxmind.com. This page is based on the official Apache Flink® documentation. See credits page for details.