DataWorks:Synchronize MySQL to MaxCompute using a full and incremental task

Action

Description

Refresh

The system automatically lists the source tables you selected, but the destination table attributes take effect only after you refresh and confirm them.

• Select the tables to synchronize in batch and click Batch Refresh Mapping.

• Destination table name: The destination table name is automatically generated based on the Customize Mapping Rules for Destination Table Names rules. The default format is ${source_database_name}_${table_name}. If a table with the same name does not exist in the destination, the system automatically creates one.

Customize Mapping Rules for Destination Table Names (optional)

The system has a default table name generation rule: ${source_database_name}_${table_name}. You can also click Edit in the Customize Mapping Rules for Destination Table Names column to add custom destination table name rules.

• Rule name: Define a rule name. We recommend that you specify a name with clear business meaning.

• Destination table name: You can click the button to select Manual Input and Built-in Variable to concatenate the destination table name. The supported variables include the source data source name, source database name, and source table name.

• Edit built-in variables: You can perform string transformations on built-in variables.

The following scenarios are supported:

1. Add prefixes or suffixes to table names: Add a prefix or suffix to the source table name by configuring constants.

2. Uniform string replacement: Replace the string dev_ in source table names with prd_.

3. Write multiple tables to a single table.

Edit column type mapping (optional)

The system provides a default mapping between source and destination column types. You can click Edit Mapping of Field Data Types in the upper-right corner of the table to customize the column type mapping between source and destination tables. After configuration, click Apply and Refresh Mapping.

When you edit column type mapping, ensure that the type conversion rules are correct. Incorrect rules cause type conversion failures that generate dirty data and affect task execution.

Edit the destination table schema (optional)

The system automatically creates destination tables that do not exist based on the custom table name mapping rules, or reuses existing tables with the same name.

DataWorks automatically generates the destination table schema based on the source table schema. Manual intervention is not required in common scenarios. You can also modify the table schema in the following ways:

• Add columns to a single table: Click the button in the Destination Table Name column to add columns.

• Add columns in batch: Select all tables to synchronize and choose Batch Modify > Destination Table Schema - Batch Modify and Add Field at the bottom of the table.

• Renaming column names is not supported.

For existing tables, you can only add columns. For new tables, you can add columns, partition columns, and set the table type or table properties. See the editable areas on the page for details.

Value assignment

Native columns are automatically mapped based on columns with the same name in the source and destination tables. The newly added columns in the preceding steps require manual value assignment. Perform the following operations:

• Single-table assignment: Click the Configuration button in the Value assignment column to assign values to destination table columns.

• Batch assignment: At the bottom of the list, choose Batch Modify > Value assignment to assign values in batch for columns with the same name in destination tables.

You can assign constants or variables to columns. Switch the type in Value Type. The following methods are supported:

• Table columns

  • Manual input: Enter a constant value or scheduling parameter directly, such as abc or ${bizdate}.

  • Select variable: Select a system-provided variable as the column value. For variable types, see the descriptions on the page.

  • Source column: Use the value of a source table column as the column value. The value type can be a column value or a time value.

    • Column value: If the source column is not a time type, you can directly write the source column value to the destination column.

    • Time value: If the source column value is a time value, you can process it according to different formats and specify the Destination Format to format the partition value.

      • Time string: A string that represents a time, such as 2018-10-23 02:13:56 or 2021/05/18. Serialize it as a time by specifying the source and destination time formats. For the examples above, you can use the formats yyyy-MM-dd HH:mm:ss and yyyy/MM/dd respectively for serialization.

      • Time object: If the source value is a time type such as Date or Datetime, select the corresponding type directly.

      • Unix timestamp (seconds): A second-level timestamp. Numbers or strings in a 10-digit timestamp format are also supported, such as 1610529203 or "1610529203".

      • Unix timestamp (milliseconds): A millisecond-level timestamp. Numbers or strings in a 13-digit timestamp format are also supported, such as 1610529203002 or "1610529203002".

  • Function: Apply simple transformations to source columns using functions to generate partition values. For more information, see Function reference.

• Partition columns: Dynamically create partitions based on the enumerated values or event time of source columns.

  • Manual input: Enter a constant value or a scheduling parameter that has been configured in Configure Scheduling Parameters for Periodical Scheduling, such as abc or ${bizdate}.

Source Split Key

In the source split key column, select a column from the source table in the drop-down list or select Not Split. During task execution, the data is split into multiple tasks based on this column for concurrent, batch data reading.

We recommend that you use a column with evenly distributed data, such as the table primary key, as the source split key. String, floating-point, and date types are not supported.

The source split key is currently supported only when the source is MySQL.

Skip Full Synchronization

If full synchronization is configured in step 3, you can cancel the full data synchronization for individual tables. This is applicable when you have already synchronized full data to the destination through other methods.

Full condition

Apply condition-based filtering to the source during the full load phase. Specify only the WHERE clause here without the WHERE keyword.

Configure DML Rule

DML message processing filters and controls the change data (Insert, Update, Delete) captured from the source before the data is written to the destination during real-time synchronization. This rule takes effect only during the incremental phase.

Full Merge Cycle

Currently, only daily merges are supported. You can configure the specific scheduling time of the merge task in Custom Merge Time.

Merge Primary Key

You can define a primary key by selecting one or more columns in the table.

• Single-column primary key: Select a single column with unique values (such as id) as the primary key.

• Composite primary key: If no single column is unique, select a combination of multiple columns. The combined values of these columns must uniquely identify each row to ensure data uniqueness during writes and prevent overwrites.