DataWorks:Real-time MySQL to Elasticsearch synchronization

Actions

Description

Refresh

The system automatically lists the source tables that you selected. However, the properties of the destination index apply only after you refresh and confirm them.

• Select multiple tables to synchronize and click Batch Refresh Mapping Results.

• Destination index name: The destination index name is automatically generated based on the Customize Mapping Rules for Destination Table Names rule. The default name is ${source_database_name}_${table_name}. If an index with this name does not exist in the destination, the system automatically creates it.

Custom Mapping Rule for Destination Index Name (Optional)

The system uses a default rule to generate index names: ${source_database_name}_${table_name}. You can also click the Edit button in the Customize Mapping Rules for Destination Table Names column to add a custom rule.

• Rule name: Define a name for the rule. We recommend that you specify a name that is descriptive and reflects its business purpose.

• Destination index name: You can construct the destination index name by clicking the button and combining values from Manual Input and Built-in Variable. The supported variables include the source data source name, source database name, and source table name.

• Edit built-in variables: You can apply string transformations to the built-in variables.

This feature supports the following scenarios:

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

   Rule configuration	Result
   In the Rule name field, enter pre_table_post. The Destination index name is composed of three parts: the text pre_, the source table name built-in variable, and the text _post.	After you apply the pre_table_post rule, the source table userinfo is mapped to the destination index pre_userinfo_post (to be created), and the source table userinfo1 is mapped to the destination index pre_userinfo1_post (to be created).

2. Perform global string replacement: Replace the string dev_ in all source table names with prd_.

   Rule configuration	Result
   In the Destination index name drop-down list, select the Source table name built-in variable and go to the Edit built-in variables page. On the Source table name tab, configure a string replacement rule to replace the source string dev_ with the destination string prd_. Rules are executed from top to bottom. You can use Move Up, Move Down, and Delete to adjust the order of rules or remove them.	The result shows that the source tables dev_table1 and dev_table2 are mapped to the destination indexes prd_table1 and prd_table2 (to be created) by using the replace rule. The dev_ prefix is replaced with prd_.

3. Write data from multiple tables to a single table: Set the destination index name to a constant value.

   Rule configuration	Result
   In the Destination index name field, enter a fixed value such as my_table to map all source tables to the same destination table.	Result: The source tables table_01 and table_02 from the source database mysql_test2 are both mapped to the same destination index my_table (to be created). This allows you to merge data from multiple tables into a single index.

Edit Field Type Mapping (Optional)

The system provides a default mapping between the Source Type and Destination Type. You can click Edit Mapping of Field Data Types in the upper-right corner of the table to customize the mapping of field data types between the source tables and destination indexes. After you complete the configuration, click Apply and Refresh Mapping.

When you edit field type mappings, make sure that the type conversion rules are valid. Otherwise, type conversion failures can occur, generating dirty data and disrupting the task.

Edit Destination Index (Optional)

Based on the custom index name mapping rules, the system automatically creates new destination indexes or reuses existing ones with matching names.

DataWorks automatically generates the destination index structure based on the source table structure. In most cases, manual intervention is not required.

When the destination index status is To be created, you can add new fields to the destination index based on its original table structure. Perform the following operations:

1. Add fields to the destination index.

   • Add fields to a single index: Click the icon in the Destination Index Name column and add fields by editing the Statement Used to Create Index.

     • Dynamic Mapping Status: Specifies whether to add new fields from the source table to the destination index during data synchronization. Valid values:

       • true: If the system detects new fields in the source table, it adds the fields to the destination index. These fields can then be searched. This is the default value.

       • false: If the system detects new fields in the source table, it adds the fields to the destination index, but these fields are not searchable.

       • strict: If the system detects new fields in the source table, it refuses to add the fields to the destination index and throws an exception. You can view the error details in the logs.

       • runtime: If the system detects new fields in the source table, the new fields are not added to the index mapping. Instead, they are treated as runtime fields at query time. This allows the fields to be used in script calculations and searches.

       For more information about dynamic mapping, see Dynamic mapping.

     • Shards and Replica Shards: The number of primary and replica shards for an index. A complete index is divided into multiple shards and distributed across different Elasticsearch nodes to enable distributed search and improve query performance. For more information, see Basic concepts.

       Note

       The values of the Shards and Replica Shards parameters cannot be changed after the task runs. The default value for both parameters is 1.

   • Batch add fields: Select all tables to be synchronized, and at the bottom of the table, select Batch Modify > Destination Index Structure - Batch Add Fields.

Value assignment

Native fields are automatically mapped based on matching field names between the source and destination. You must manually assign values for the new fields and destination index properties you added. Perform the following operations:

• Assign values for a single table: Click the Configuration button in the Value assignment column to assign values to the destination index fields.

• Assign values in a batch: At the bottom of the list, select Batch Modify > Value assignment to assign values to identical fields across multiple destination indexes at the same time.

You can assign constants and variables by changing the Value Type. The following options are supported:

• Destination index field:

  • Manual assignment: Enter a constant value, such as abc.

  • Source field: Assigns a value from a source table field. You can select either the field value or a time value.

    • Field value: Writes the value of the source field directly to the destination.

    • Time value: If the source field contains a time value, you can process it by using different formats and specify a Destination Format to format the extracted value.

      • Time string: A string that represents a time or date, such as "2018-10-23 02:13:56" or "2021/05/18". The string is parsed into a date or time value by specifying a time format. For example, the strings in the preceding examples can be recognized by using the yyyy-MM-dd HH:mm:ss and yyyy/MM/dd formats.

      • Time object: If the source value is of a time data type such as Date or Datetime, you can directly select this type.

      • Unix timestamp (seconds): A 10-digit timestamp in seconds, which can be provided as a number or string. Examples: 1610529203 and "1610529203".

      • Unix timestamp (milliseconds): A 13-digit timestamp in milliseconds, which can be provided as a number or string. Examples: 1610529203002 and "1610529203002".

  • Select variable: Select a system-provided variable as the value source.

  • Function: Use functions to apply simple transformations to the source field before you assign it as the value. For more information, see Use function expressions to assign values to destination table fields.

• Destination index property assignment: Assign a value to the primary key for the destination index. You can concatenate multiple source fields to create a composite primary key. Make sure that the resulting value is unique.

Source split column

You can select a field from the source table in the Source split column drop-down list or select Not Split. When the synchronization task runs, it is split into multiple subtasks based on this column to read data in batches and in parallel.

We recommend that you use the primary key of the table as the source split column. String, float, and date types are not supported.

The source split column is supported only when the source is MySQL.

Skip full synchronization

If you have configured full synchronization in Step 3, you can choose to skip full synchronization for individual tables. This is useful if you have already synchronized the full data to the destination by using other methods.

Full condition

Applies a filter to the source data during the full synchronization phase. Enter only the filter conditions of a WHERE clause. Do not include the WHERE keyword.

Configure DML Rule

DML message processing applies fine-grained filtering and control to the change data (Insert, Update, and Delete) that is captured from the source before the data is written to the destination. This rule applies only during the incremental sync phase.