In DataWorks Data Integration, you can write function expressions that compute a value and write it to a column in the target table during a real-time full-database sync task. This lets you derive new columns — such as formatted timestamps or concatenated strings — directly from source fields without post-processing.
Limitations
Function expressions are supported only in real-time full-database sync tasks where:
Source: MySQL or PolarDB
Destination: MaxCompute or Hologres
Prerequisites
Before you begin, ensure that you have:
A data source configured with MySQL or PolarDB as the source and MaxCompute or Hologres as the destination
A real-time full-database sync task already created
Supported functions
Functions fall into two categories: string functions and date/time functions.
String functions
| Function | Description | Example |
|---|---|---|
| CONCAT | Concatenates one or more strings. Accepts field values (#{col}), built-in variables, or string literals ("string"). All parameters are optional, and you can specify any number of them. | concat(#{name},${table_name_src},"_01") |
| SUBSTRING | Returns a substring starting at a given position. The start position is 0-based and inclusive. If the length is less than or equal to 0, the substring from the start position to the end of the string is returned. | substring(#{name},0,2) |
| LOWER | Converts a string to lowercase. Accepts a text field or a built-in variable. | lower(#{name}) |
| UPPER | Converts a string to uppercase. Accepts a text field or a built-in variable. | upper(#{name}) |
Date and time functions
All three functions use the same date format string syntax: yyyy (year), MM (month), dd (day), HH (24-hour), mm (minute), ss (second), SSS (millisecond), ZZ (time zone).
| Function | Description | Example |
|---|---|---|
| DATE_FORMAT | Converts a timestamp (10-digit UNIX or 13-digit millisecond) to a formatted date string. | date_format(#{timestamp},"millis","Asia/Shanghai","yyyy-MM-dd HH:mm:ss.SSS ZZ") |
| TO_TIMESTAMP | Converts a formatted date string to a 10-digit UNIX or 13-digit millisecond timestamp. | to_timestamp(#{create_time},"yyyy-MM-dd HH:mm:ss","Asia/Shanghai","millis") |
| DATE_CONVERT | Converts a date string from one format and time zone to another format and time zone. | date_convert(#{create_time},"yyyy-MM-dd HH:mm:ss","Asia/Shanghai","yyyyMMdd","UTC") |
DATE_FORMAT parameters
| # | Parameter | Description |
|---|---|---|
| 1 | Source field | A numeric ancestor field containing a UNIX or millisecond timestamp |
| 2 | Timestamp type | unix for 10-digit UNIX timestamps; millis for 13-digit millisecond timestamps |
| 3 | Time zone | The time zone of the source timestamp, e.g., Asia/Shanghai |
| 4 | Output format | The target date string format, e.g., yyyy-MM-dd HH:mm:ss.SSS ZZ |
TO_TIMESTAMP parameters
| # | Parameter | Description |
|---|---|---|
| 1 | Source field | A text ancestor field containing a formatted date string |
| 2 | Input format | The format of the date string in the source field, e.g., yyyy-MM-dd HH:mm:ss |
| 3 | Time zone | The time zone of the source date string, e.g., Asia/Shanghai |
| 4 | Output type | unix for a 10-digit UNIX timestamp; millis for a 13-digit millisecond timestamp |
DATE_CONVERT parameters
| # | Parameter | Description |
|---|---|---|
| 1 | Source field | A text ancestor field containing a formatted date string |
| 2 | Input format | The format of the source date string, e.g., yyyy-MM-dd HH:mm:ss |
| 3 | Source time zone | The time zone of the source date string, e.g., Asia/Shanghai |
| 4 | Output format | The target date string format, e.g., yyyyMMdd |
| 5 | Output time zone | The target time zone, e.g., UTC |
Supported syntax
Function call only: Expressions must be a function call — a function name, parentheses, and parameters. Arithmetic, comparison, and other expression types are not supported. Example:
DATE_FORMAT(#{create_time},"millis","Asia/Shanghai","yyyyMMdd")String literals: Use double quotation marks. Example:
CONCAT("hello","world")Numeric constants: Use plain numbers. Example:
SUBSTRING(#{col1},0,10)Nested calls: A function call can appear as a parameter to another function. Example:
CONCAT(SUBSTRING(#{col1},0,10),"test")Field references: Reference the output column of an ancestor node with
#{}. Example:#{create_time}Built-in variables: Reference built-in variables with
${}. The available variables are the same as those for additional columns in each channel. Example:CONCAT(SUBSTRING(#{col1},0,10),${DATASOURCE_NAME_SRC})Case-insensitive: Function names and built-in variable names are case-insensitive.
Add a function column to a target table
Step 1: Open the sync task configuration page
Log on to the DataWorks console. In the top navigation bar, select the region. In the left-side navigation pane, choose Data Integration > Data Integration. Select the workspace from the drop-down list and click Go to Data Integration.
In the left navigation pane, click
to open the sync task page.Find the real-time full-database sync task. In the Actions column, click More > Edit.
Step 2: Add a function processing column to the target table
The function expression writes its result to a column you add to the target table schema.
On the task configuration page, click Next. In the Target Table Mapping section, click Refresh Mapping.
In the Target Table Name column, click
next to the target table to open the target table schema configuration page.
In the Fields section, click Add Field. Configure the field, then click Apply And Refresh Mapping.
To add function columns to multiple target tables at once, use Target Table Schema - Batch Modify And Add Fields in Batch Operations.
Step 3: Define the function expression
In the Target Table Field Assignment column, click Configure.
In the Target Table Fields section, find the column you added. Set Assignment Method to Function, then enter the expression in the Assignment column.
