Flink CDC built-in functions - Realtime Compute for Apache Flink

This topic describes the built-in functions available for expressions in the transform module of Flink CDC data ingestion jobs.

Built-in functions

Flink CDC data ingestion jobs offer a rich set of built-in functions for use in projection and filter expressions within the transform module.

Arithmetic functions

Function	Description
numeric1 + numeric2	Returns the sum of `numeric1` and `numeric2`.
numeric1 - numeric2	Returns the result of subtracting `numeric2` from `numeric1`.
numeric1 * numeric2	Returns the product of `numeric1` and `numeric2`.
numeric1 / numeric2	Returns the result of dividing `numeric1` by `numeric2`.
numeric1 % numeric2	Returns the remainder of dividing `numeric1` by `numeric2`.
ABS(numeric)	Returns the absolute value of `numeric`.
CEIL(numeric)	Rounds `numeric` up to the nearest integer.
FLOOR(numeric)	Rounds `numeric` down to the nearest integer.
ROUND(numeric, int)	Rounds `numeric` to `int` decimal places.
UUID()	Generates a Universally Unique Identifier (UUID) as a string (for example, "3d3c68f7-f608-473f-b60c-b0c44ad4cc4e"). The UUID is generated using the RFC 4122 type 4 pseudo-random method.

Time functions

Function	Description
LOCALTIME	Returns the local time in the current time zone. The return type is `TIME(0)`.
LOCALTIMESTAMP	Returns the local timestamp in the current time zone. The return type is `TIMESTAMP(3)`.
CURRENT_TIME	An alias for `LOCALTIME`.
CURRENT_DATE	Returns the local date in the current time zone.
CURRENT_TIMESTAMP	Returns the local timestamp in the current time zone. The return type is `TIMESTAMP_LTZ(3)`.
NOW()	An alias for `CURRENT_TIMESTAMP`.
DATE_FORMAT(timestamp, string)	Formats the input timestamp according to the specified format string. Note The format string is compatible with Java's `SimpleDateFormat`.
TIMESTAMPADD(timeintervalunit, interval, timepoint)	Adds a specified time `interval` to a `timepoint`. The `timeintervalunit` can be `SECOND`, `MINUTE`, `HOUR`, `DAY`, `MONTH`, or `YEAR`.
TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)	Calculates the difference between `timepoint1` and `timepoint2` in the specified `timepointunit`. The `timepointunit` can be `SECOND`, `MINUTE`, `HOUR`, `DAY`, `MONTH`, or `YEAR`.
TO_DATE(string1[, string2])	Converts the date string `string1` to the `DATE` type using the format specified in `string2`. Note If the format string `string2` is not specified, the default format 'yyyy-MM-dd' is used.
TO_TIMESTAMP(string1[, string2])	Converts the timestamp string `string1` to the `TIMESTAMP` type (without time zone information) using the format specified in `string2`. Note If the format string `string2` is not specified, the default format 'yyyy-MM-dd HH:mm:ss' is used.
FROM_UNIXTIME(numeric[, string])	Converts the first argument, which represents the number of seconds elapsed since 1970-01-01 00:00:00 UTC, into a formatted timestamp string, using the format specified in the second argument. Note If the format string is not specified, the default format 'yyyy-MM-dd HH:mm:ss' is used.
UNIX_TIMESTAMP()	Returns the current UNIX timestamp in seconds.
UNIX_TIMESTAMP(string1[, string2])	Converts the date-time string `string1` to a UNIX timestamp (in seconds), using the format specified by `string2`. If `string2` is not specified, the default format 'yyyy-MM-dd HH:mm:ss' is used.

Note

All sub-expressions calling time functions within projection and filter expressions will share the same time value. For example, in NOW() AS t1, NOW() AS t2, NOW() AS t3, the values of t1, t2, and t3 will be identical, regardless of the evaluation time or order.

String functions

Function	Description
string1 \|\| string2	Concatenates `string1` and `string2`. Note This operator is different from the logical OR operator.
CHAR_LENGTH(string)	Returns the number of characters in `string`.
UPPER(string)	Returns the uppercase version of `string`.
LOWER(string)	Returns the lowercase version of `string`.
TRIM(string1)	Removes leading and trailing whitespace characters from `string1`.
REGEXP_REPLACE(string1, string2, string3)	Replaces all substrings in `string1` matching the pattern `string2` with `string3`. For example, `REGEXP_REPLACE('foobar', 'oo\|ar', '__')` evaluates to `f__b__`.
SUBSTRING(string FROM integer1 [ FOR integer2 ])	Returns a substring of `string` starting at index `integer1` with a length of `integer2`. Note If the `FOR integer2` clause is not provided, the substring extends to the end of the string.
CONCAT(string1, string2,…)	Concatenates two or more strings. For example, `CONCAT('AA', 'BB', 'CC')` evaluates to `AABBCC`.

Type casting functions

Use the CAST(<expression> AS <type>) syntax for explicit type casting. The following conversions are currently supported:

Source type	Target type	Description
Any	`VARCHAR` Important You cannot currently use `STRING` as the target type. Use the equivalent `VARCHAR` type instead.	Converts any data type to a string.
`NUMERIC` `STRING`	`BOOLEAN`	Converts any non-zero numeric literal or a string literal other than 'FALSE' to TRUE.
`NUMERIC` `STRING`	`TINYINT`	Truncates the result to fit within the range of -128 to 127.
`NUMERIC` `STRING`	`SMALLINT`	Truncates the result to fit within the range of -32768 to 32767.
`NUMERIC` `STRING`	`INTEGER`	Truncates the result to fit within the range of -2147483648 to 2147483647.
`NUMERIC` `STRING`	`BIGINT`	Truncates the result to fit within the range of -9223372036854775808 to 9223372036854775807.
`NUMERIC` `STRING`	`FLOAT`
`NUMERIC`	`DOUBLE`
`NUMERIC`	`DECIMAL`
`STRING` `TIMESTAMP_TZ` `TIMESTAMP_LTZ`	`TIMESTAMP`	The string must be in a valid ISO_LOCAL_DATE_TIME format.

Comparison functions

Note

If any input parameter is NULL, these functions return NULL unless specified otherwise.

Function	Description
`value1 = value2`	Returns TRUE if `value1` is equal to `value2`; otherwise returns FALSE.
value1 <> value2	Returns TRUE if `value1` is not equal to `value2`; otherwise returns FALSE.
value1 > value2	Returns TRUE if `value1` is greater than `value2`; otherwise returns FALSE.
value1 >= value2	Returns TRUE if `value1` is greater than or equal to `value2`; otherwise returns FALSE.
value1 < value2	Returns TRUE if `value1` is less than `value2`; otherwise returns FALSE.
value1 <= value2	Returns TRUE if `value1` is less than or equal to `value2`; otherwise returns FALSE.
value IS NULL	Returns TRUE if `value` is NULL; otherwise returns FALSE.
value IS NOT NULL	Returns TRUE if `value` is not NULL; otherwise returns FALSE.
value1 BETWEEN value2 AND value3	Returns TRUE if `value1` is between `value2` and `value3`; otherwise returns FALSE.
value1 NOT BETWEEN value2 AND value3	Returns TRUE if `value1` is not between `value2` and `value3`; otherwise returns FALSE.
string1 LIKE string2	Returns TRUE if `string1` matches the pattern defined in `string2`; otherwise returns FALSE.
string1 NOT LIKE string2	Returns TRUE if `string1` does not match the pattern defined in `string2`; otherwise returns FALSE.
value1 IN (value2 [, value3]* )	Returns TRUE if `value1` exists in the list `[value2, value3, ...]`; otherwise returns FALSE.
value1 NOT IN (value2 [, value3]* )	Returns TRUE if `value1` does not exist in the list `[value2, value3, ...]`; otherwise returns FALSE.

Logical functions

Function	Description
`boolean1 OR boolean2`	Returns TRUE if at least one of `boolean1` or `boolean2` is TRUE.
`boolean1 AND boolean2`	Returns TRUE if both `boolean1` and `boolean2` are TRUE.
NOT boolean	Inverts the boolean value.
boolean IS FALSE	Returns TRUE if `boolean` is FALSE; otherwise returns FALSE.
boolean IS NOT FALSE	Returns TRUE if `boolean` is not FALSE; otherwise returns FALSE.
boolean IS TRUE	Returns TRUE if `boolean` is TRUE; otherwise returns FALSE.
boolean IS NOT TRUE	Returns TRUE if `boolean` is not TRUE; otherwise returns FALSE.

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	Sequentially checks if `value` is equal to the values given in the WHEN clauses and returns the `result` of the first matching clause. If no clause's condition is met, it returns the value specified in the ELSE clause. If no ELSE clause is specified, it returns NULL.
CASE WHEN condition1 THEN result1 (WHEN condition2 THEN result2)* (ELSE result_z) END	Sequentially evaluates the condition in each WHEN clause and returns the `result` from the first `WHEN` clause whose condition is true. If no clause's condition is met, it returns the value specified in the ELSE clause. If no ELSE clause is specified, it returns NULL.
COALESCE(value1 [, value2]*)	Returns the first non-NULL value in the list `[value1, value2, ...]`. If all values are NULL, it returns NULL.
IF(condition, true_value, false_value)	If the `condition` is true, returns `true_value`; otherwise, returns `false_value`.