Use function expressions to assign values to destination table fields-DataWorks(DataWorks)-阿里云帮助中心

DataWorks Data Integration lets you use function expressions to assign values to destination table fields in whole-database real-time synchronization tasks.

Limits

You can use function expressions to define field assignments for destination tables only in whole-database real-time synchronization tasks that use MySQL or PolarDB as the source and MaxCompute or Hologres as the destination.

Supported functions and syntax

Supported functions

Function name	Description	Parameters	Expression example
CONCAT	Concatenates strings.	Parameter 1...n: Can be another field value (`#{col}`), a built-in variable, or a string literal (`"string"`). Optional. No limit on the number of parameters.	`concat(#{name},${table_name_src},"_01")`
DATE_FORMAT	Converts a timestamp to a time string in a specific format.	Parameter 1: A numeric-type ancestor table field containing a 10-digit UNIX timestamp or a 13-digit millisecond timestamp. Parameter 2: Timestamp type. Use "unix" for a 10-digit UNIX timestamp or "millis" for a 13-digit millisecond timestamp. Parameter 3: Time zone used during conversion. Parameter 4: Target date string format, such as `yyyy-MM-dd HH:mm:ss:SSS ZZ`. `yyyy` represents the year, `MM` the month, `dd` the day, `HH` the hour (24-hour format), `mm` the minute, `ss` the second, `SSS` the millisecond, and `ZZ` the time zone.	`date_format(#{timestamp},"millis","Asia/Shanghai","yyyy-MM-dd HH:mm:ss.SSS ZZ")`
TO_TIMESTAMP	Converts a time string into a 10-digit or 13-digit timestamp.	Parameter 1: A text-type ancestor table field containing a time string in a specific format. Parameter 2: Time string format, such as `yyyy-MM-dd HH:mm:ss:SSS ZZ`. `yyyy` represents the year, `MM` the month, `dd` the day, `HH` the hour (24-hour format), `mm` the minute, `ss` the second, `SSS` the millisecond, and `ZZ` the time zone. Parameter 3: Time zone used during conversion. Parameter 4: Output timestamp type. Use "unix" for a 10-digit UNIX timestamp or "millis" for a 13-digit millisecond timestamp.	`to_timestamp(#{create_time},"yyyy-MM-dd HH:mm:ss","Asia/Shanghai","millis")`
DATE_CONVERT	Converts a time string from one format and time zone to another format and time zone.	Parameter 1: A text-type ancestor table field containing a time string in a specific format. Parameter 2: Source time string format, such as `yyyy-MM-dd HH:mm:ss:SSS ZZ`. `yyyy` represents the year, `MM` the month, `dd` the day, `HH` the hour (24-hour format), `mm` the minute, `ss` the second, `SSS` the millisecond, and `ZZ` the time zone. Parameter 3: Source time zone. Parameter 4: Target date string format, such as `yyyy-MM-dd HH:mm:ss:SSS ZZ`. `yyyy` represents the year, `MM` the month, `dd` the day, `HH` the hour (24-hour format), `mm` the minute, `ss` the second, `SSS` the millisecond, and `ZZ` the time zone. Parameter 5: Target time zone.	`date_convert(#{create_time},"yyyy-MM-dd HH:mm:ss","Asia/Shanghai","yyyyMMdd","UTC")`
SUBSTRING	Extracts a substring from a string.	Parameter 1: A text-type ancestor table field. Parameter 2: Starting position of the substring. The substring includes the character at this position. The minimum starting position is 0. Parameter 3: (Optional) Length of the substring. If the length is less than or equal to 0, the substring from the starting position to the end is returned.	`substring(#{name},0,2)`
LOWER	Converts a string to lowercase.	Parameter 1: A text-type ancestor table field or a built-in variable.	`lower(#{name})`
UPPER	Converts a string to uppercase.	Parameter 1: A text-type ancestor table field or a built-in variable.	`upper(#{name})`

Supported syntax

Only function invocation expressions are supported. An expression must consist of a function name, parentheses, and parameters. Other types of expressions are not supported. For example, DATE_FORMAT(#{create_time},"millis","Asia/Shanghai","yyyyMMdd").
String literals in function expression parameters must be enclosed in double quotation marks (""). For example, CONCAT("hello","world").
Numeric literals in function expression parameters can be written directly as numbers. For example, SUBSTRING(#{col1},0,10).
Function calls can be nested within parameters. For example, CONCAT(SUBSTRING(#{col1},0,10),"test").
Use #{} to reference output column values from ancestor nodes.
Use ${} to reference built-in variables. The supported built-in variable types are the same as those available in the additional column settings for each channel. For example, CONCAT(SUBSTRING(#{col1},0,10),${DATASOURCE_NAME_SRC}).
Function names and built-in variable names are case-insensitive.

Prerequisites

A data source is configured. The source must be MySQL or PolarDB, and the destination must be MaxCompute or Hologres. For more information, see Data Source Configuration.
A whole-database real-time sync task is created.

Procedure

Step 1: Go to the whole-database real-time task editing page

Log on to the DataWorks console. In the target region, click Data Integration > Data Integration in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Integration.
In the navigation pane on the left, click to go to the sync task page.
Find the desired whole-database real-time sync task. In the Actions column, choose More > Edit to open the sync task configuration page.

Step 2: Add a function-processed column to the destination table

You must add the function-processed column to the destination table schema before you can write data to it.

On the configuration page of the whole-database real-time task, click Next. In the Target Table Mapping section, click Refresh Mapping.
Find the target table to which you want to add a function-processed column, and click the icon in the Destination Table Name column to go to the table schema configuration page.
In the Field section, click Add Fields. After you configure the field, click Apply and Refresh Mapping.

You can also select Target Table Structure - Batch Modify and Add Fields in Batch Operations to add function-processed columns to multiple destination tables.

Step 3: Define the function expression

In the Value assignment column, click Configure to open the field assignment page.
In the Destination Table Field section, find the function-processed column that you added. Set Assignment Method to Function. Then, enter the function expression in the Assign column. For more information, see Supported functions and syntax.

The Assignment Method parameter provides three options: Manual Assignment, Select Variable, and Function. You can assign values only to fields that do not exist in the source table. Otherwise, the value of the field with the same name from the source table is used.