Definition #

Transform module helps users delete and expand data columns based on the data columns in the table.
What’s more, it also helps users filter some unnecessary data during the synchronization process.

Parameters #

To describe a transform rule, the following parameters can be used:

Multiple rules can be declared in one single pipeline YAML file.

Metadata Fields #

Fields definition #

There are some hidden columns used to access metadata information. They will only take effect when explicitly referenced in the transform rules.

Metadata relationship #

Functions #

Flink CDC uses Calcite to parse expressions and Janino script to evaluate expressions with function call.

Comparison Functions #

Logical Functions #

Arithmetic Functions #

String Functions #

Temporal Functions #

Conditional Functions #

Casting Functions #

You can use CAST( <EXPR> AS <T> ) syntax to convert any valid expression <EXPR> to a specific type <T>. Possible conversion paths are:

Example #

Add computed columns #

Evaluation expressions can be used to generate new columns. For example, if we want to append two computed columns based on the table web_order in the database mydb, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    projection: id, order_id, UPPER(product_name) as product_name, localtimestamp as new_timestamp
    description: append calculated columns based on source table

Reference metadata columns #

We may reference metadata column in projection expressions. For example, given a table web_order in the database mydb, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    projection: id, order_id, __namespace_name__ || '.' || __schema_name__ || '.' || __table_name__ identifier_name
    description: access metadata columns from source table

Use wildcard character to project all fields #

A wildcard character (*) can be used to reference all fields in a table. For example, given two tables web_order and app_order in the database mydb, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    projection: \*, UPPER(product_name) as product_name
    description: project fields with wildcard character from source table
  - source-table: mydb.app_order
    projection: UPPER(product_name) as product_name, *
    description: project fields with wildcard character from source table

Notice: When * character presents at the beginning of expressions, an escaping backslash is required.

Add filter rule #

Use reference columns when adding filtering rules to the table web_order in the database mydb, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    filter: id > 10 AND order_id > 100
    description: filtering rows from source table

Computed columns can be used in filtering conditions, too. For example, given a table web_order in the database mydb, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    projection: id, order_id, UPPER(province) as new_province 
    filter: new_province = 'SHANGHAI'
    description: filtering rows based on computed columns

Reassign primary key #

We can reassign the primary key in transform rules. For example, given a table web_order in the database mydb, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    projection: id, order_id
    primary-keys: order_id
    description: reassign primary key example

Composite primary keys are also supported:

transform:
  - source-table: mydb.web_order
    projection: id, order_id, UPPER(product_name) as product_name
    primary-keys: order_id, product_name
    description: reassign composite primary keys example

Reassign partition key #

We can reassign the partition key in transform rules. For example, given a table web_order in the database mydb, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    projection: id, order_id, UPPER(product_name) as product_name
    partition-keys: product_name
    description: reassign partition key example

Specify table creation configuration #

Extra options can be defined in a transform rule, and will be applied when creating downstream tables. Given a table web_order in the database mydb, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    projection: id, order_id, UPPER(product_name) as product_name
    table-options: comment=web order
    description: auto creating table options example

Tips: The format of table-options is key1=value1,key2=value2.

Classification mapping #

Multiple transform rules can be defined to classify input data rows and apply different processing. Only the first matched transform rule will apply. For example, we may define a transform rule as follows:

transform:
  - source-table: mydb.web_order
    projection: id, order_id
    filter: UPPER(province) = 'SHANGHAI'
    description: classification mapping example
  - source-table: mydb.web_order
    projection: order_id as id, id as order_id
    filter: UPPER(province) = 'BEIJING'
    description: classification mapping example

User-defined Functions #

User-defined functions (UDFs) can be used in transform rules.

Classes could be used as a UDF if:

• implements org.apache.flink.cdc.common.udf.UserDefinedFunction interface
• has a public constructor with no parameters
• has at least one public method named eval

It may also:

• overrides getReturnType method to indicate its return CDC type
• overrides open and close method to do some initialization and cleanup work

For example, this is a valid UDF class:

public class AddOneFunctionClass implements UserDefinedFunction {
    
    public Object eval(Integer num) {
        return num + 1;
    }
    
    @Override
    public DataType getReturnType() {
        return DataTypes.INT();
    }
    
    @Override
    public void open() throws Exception {
        // ...
    }

    @Override
    public void close() throws Exception {
        // ...
    }
}

To ease the migration from Flink SQL to Flink CDC, a Flink ScalarFunction could also be used as a transform UDF, with some limitations:

• ScalarFunction which has a constructor with parameters is not supported.
• Flink-style type hint in ScalarFunction will be ignored.
• open / close lifecycle hooks will not be invoked.

UDF classes could be registered by adding a user-defined-function block:

pipeline:
  user-defined-function:
    - name: addone
      classpath: org.apache.flink.cdc.udf.examples.java.AddOneFunctionClass
    - name: format
      classpath: org.apache.flink.cdc.udf.examples.java.FormatFunctionClass

Notice that given classpath must be fully-qualified, and corresponding jar files must be included in Flink /lib folder, or be passed with flink-cdc.sh --jar option.

After being correctly registered, UDFs could be used in both projection and filter expressions, just like built-in functions:

transform:
  - source-table: db.\.*
    projection: "*, inc(inc(inc(id))) as inc_id, format(id, 'id -> %d') as formatted_id"
    filter: inc(id) < 100

Known limitations #

• Currently, transform doesn’t work with route rules. It will be supported in future versions.
• Computed columns cannot reference trimmed columns that do not present in final projection results. This will be fixed in future versions.
• Regular matching of tables with different schemas is not supported. If necessary, multiple rules need to be written.