DataWorks:Synchronize an entire MySQL database to Elasticsearch

Actions

Description

Refresh

The system lists the source tables you selected. You must refresh and confirm the destination index properties for them to take effect.

• Select the 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 is ${source_database_name}_${inedx_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}_${index_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 giving the rule a descriptive name that 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. 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: Add a prefix or suffix to the source table name by setting a constant.

   Rule Configuration	Result

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

   Rule Configuration	Result

3. Write multiple tables to a single index: Set the destination index name to a constant.

   Rule Configuration	Result

Edit field type mapping (Optional)

The system provides a default mapping between 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 field type mapping between source tables and destination indexes. After configuration, click Apply and Refresh Mapping.

When editing field type mappings, ensure that type conversion rules are correct. Incorrect rules can cause conversion failures, generate dirty data, and disrupt task execution.

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 fields to the destination index on top of its original structure. Follow these steps:

1. Add fields to the destination index

   • Add fields to a single index: Click the button 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 them to the destination index, making them searchable. This is the default value.

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

       • strict: If the system detects new fields in the source table, it refuses to add them 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, which allows them 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 Shards and Replica Shards parameters cannot be changed after the task runs. The default value is 1 for each.

   • 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 to the new fields and destination index properties added in the previous step. Follow these steps:

• 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 batch: At the bottom of the list, select Batch Modify > Value assignment to assign values to identical fields across multiple destination indexes in batch.

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

• Destination index field:

  • Manual assignment: Directly 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 source field's value directly to the destination.

    • Time value: If the source field contains a time value, you can process it 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 and 2021/05/18. The string is parsed into a date/time value by specifying a time format. For example, the strings shown above can be recognized by using the formats yyyy-MM-dd HH:mm:ss and yyyy/MM/dd.

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

      • Unix timestamp (seconds): A 10-digit timestamp in seconds, provided as a number or string, such as 1610529203 or "1610529203".

      • Unix timestamp (milliseconds): A timestamp in milliseconds. It also supports 13-digit numbers or strings, such as 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 assigning 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 index key. Ensure that the resulting value is unique.

Source split column

You can select a field from the source table from the source split column dropdown or select Not Split. When the synchronization task runs, it splits into multiple sub-tasks based on this column to read data concurrently in batches.

We recommend using the table's primary key as the source split column. String, float, and date types are not supported.

Currently, the source split column is supported only when the source is MySQL.

Skip full synchronization

You can choose to skip full synchronization for individual tables. This is useful if you have already synchronized the full data to the destination using other methods.

Full condition

Applies a filter to the source data during the full synchronization phase. Enter the filter conditions that would normally follow 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, Delete) captured from the source before the data is written to the destination. This rule applies only during the incremental phase.