Docs

Database Transmission Service

ETL task management

Configuring ETL in a DTS data synchronization or subscription task

Database Transmission Service (DTS) provides streaming data ETL (Extract Transform Load) data processing capabilities. When creating a synchronization or subscription task, you can define data processing logic by editing DSL scripts to extract, transform, process, and load source database data. This topic describes how to configure ETL in a DTS data synchronization or subscription task.

Precautions

When you perform an operation to add a column in the ETL script, you need to manually add the column to the table in the target database. Otherwise, the ETL rules will not take effect.

When you convert the attribute of a column in the ETL script, such as converting bigint to vachar, you need to manually modify the attribute of the target column in the table of the target database. Otherwise, the ETL rules will not take effect.

In the ETL assistant, ETL statements are generated by the AI model. A small number of conversion results may be inaccurate and are for reference only.

ETL assistant capabilities

Database Transmission Service (DTS) supports a natural language-to-ETL feature, which can help you generate standard ETL statements from natural language and lower the threshold for ETL writing and learning.

ETL assistant capabilities	Question example
Field mapping	In the goods table of the product database, rename the 'product_name' field to 'item_name'.
Data concatenation	In the contacts table of the customer database, concatenate the first_name and last_name columns into a full name.
Data filtering	I only need to synchronize mytabl where id > 1000, and the location field is equal to 'hangzhou'.
JSON parsing	Parse the format of the string item_info in the shopping_item table as json, and save the result to the item_info_json column.
Data filtering	For table A or table B, if created_at is earlier than '2024-06-30', the record will be deleted.
Data desensitization	For the user_info table, replace the 8th to 11th digits of the mobile_phone field with asterisks, and also replace the first 2 digits of the uid column with asterisks.
Type conversion + Data mapping	Convert the color field of type SET('red','blue','green','yellow') in the products table to a string, where 1→red, 2→blue, 3→red,blue, and 4→green.
Conditional branch + Field addition	For the user_info table, add the memo column. If 60 > age >= 30, set memo to middle-aged; if age >= 60, set memo to old age; if 30 > age >= 18, set memo to youth; otherwise, set memo to juvenile.
Data filtering + Time analysis	Add a column process_year to the processlog table in the orders database, and the value is the year of the current time. Convert the create_time column from the string format 'yyyyMMddHHmmss' to the standard time format 'yyyy-MM-dd HH:mm:ss'.
Bit operation + Field addition	For the user_info table, add the is_vip and is_svip columns. Bitwise parses user_attr. If the 3rd digit is 1, set is_vip to true. If the 5th digit is 1, set is_svip to true; otherwise, set it to false.
Modify indexes in the database consolidation scenario	For the orders table in the cn_ecommerce and us_ecommerce databases, remove the original primary key id, and use order_uuid as the new primary key.
Modify indexes in the database consolidation scenario		For the user_wallets table in the east and west databases, remove the original primary key id, add a field region where the value of region matches the name of the database, and set region + user_id as the primary key.
Data filtering + Data desensitization	In the orders table of the cn_users database, filter out orders where order_status is not 1, and de-sensitize id_card by retaining only the first three and last four digits, replacing the middle part with *.
Use Hash for data desensitization	Convert the identity card number id_card field in the user table user_info to an MD5 hash value.

Supported databases

The source and target databases supported by ETL are as follows:

Source database and supported versions.

Source database	Version
Volcano Engine RDS for MySQL	MySQL 5.6 MySQL 5.7 MySQL 8.0
Volcano Engine veDB for MySQL	MySQL 8.0
MySQL connected over public network	MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0
MySQL connected over express network	MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0

Target database and supported versions.

Instance type	Target database	Version
MySQL	Volcano Engine RDS for MySQL	MySQL 5.6 MySQL 5.7 MySQL 8.0
		Volcano Engine veDB for MySQL	MySQL 8.0
		MySQL connected over public network	MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0
		MySQL connected over express network	MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0
ElasticSearch	Volcengine Cloud Search Service	6.7.1 7.10.2
		ElasticSearch connected over express network	6.7.1 7.10.2
		Cloud Search connected over public network	6.7.1 7.10.2

Configure ETL when creating a data synchronization or subscription task.

This topic uses the creation of a data synchronization task as an example to explain how to configure ETL rules during task creation. Modifying ETL for existing tasks includes the following two methods:

If ETL is already enabled for an existing task, modifying the DSL script for the task's ETL is supported.

If ETL is not enabled for an existing task, you can enable ETL for the task, and you also have the option to disable ETL for the task. This feature is only available for data synchronization tasks from MySQL to Elasticsearch.

Procedure

Create a synchronization task For more information, refer to Creating and starting a synchronization task.

In the Configure synchronization objects step, when performing Advanced configuration, select Yes for Enable ETL configuration.

Enter the data processing ETL rules, which are DSL scripts, in the Enter ETL statement or use ETL assistant to generate statements… text box. For more information about data processing statements, refer to DSL syntax.

Database Transmission Service (DTS) supports a natural language-to-ETL feature, which can help you generate standard ETL statements from natural language and lower the threshold for ETL writing and learning. The following describes how to generate ETL statements using natural language.

In the upper-right corner of the ETL statement text box, click ETL assistant.

In the ETL assistant dialog box, enter the ETL requirements, and then press Enter. The ETL assistant offers a variety of ETL capabilities. For more information, refer to ETL assistant capabilities.

After the ETL is generated, you can copy the ETL statement to the ETL text box.

Click Syntax verification to verify that the data processing statement is correct.

tip

When an error message is displayed, you need to modify the data processing statement based on the error message.

To verify whether the data processing ETL rules you configured can achieve the expected results in the target table, you can click Debug for verification. For detailed configuration information, refer to Verifying ETL rules using the ETL Playground tool.

Modifying ETL configuration in existing synchronization or subscription tasks

This topic uses modifying ETL configuration in a synchronization task as an example to describe how to modify ETL rules in a task.

Precautions

When you modify an existing ETL rule, you need to move the synchronization objects from the selected object to the synchronization objects, then re-add them to the selected object, and then modify the ETL rule.

Modifying ETL configuration on an existing synchronization task does not currently support changing the table structure of the target table. To change it, you need to change the table structure at the destination before starting the synchronization task.

The modification of ETL rules only takes effect for incremental data after the synchronization task is started, and does not take effect for historical data before the ETL configuration is modified.

The fields configured in the DSL script of the ETL rule can not be those filtered by the filter condition. Otherwise, the task will be abnormal.

The DSL scripts of ETL rules are case-sensitive. The database name, table name, and field name must be exactly the same as those in the source database.

The DSL script of ETL rules does not support multi-line expressions. You can use the e_compose function to combine multiple expressions into one expression.

Modify ETL configuration

In the upper-left corner of the top menu bar, select the project and region.

In the left navigation bar, click Data synchronization.

ETL is enabled for the current task.

On the Data synchronization list page, click the name of the target task.

On the task details page, click Task configuration.

On the Task configuration tab, locate the Advanced configuration area, click Edit.

In the ETL rules debugging and updating control panel, configure ETL rules.

tip

To verify whether the ETL rules you configured can achieve the expected results in the target table, you can click Debug for verification. For detailed configuration information, refer to Verifying ETL rules using the ETL Playground tool.

Click Validate syntax to verify the accuracy of the ETL rules. When an error message is displayed, you need to modify the data processing statement based on the error message.

Click Update ETL.

ETL is disabled for the current task.

Click Configure for the target unidirectional synchronization task.

On the modify synchronization task page, locate the Advanced configuration area, set Enable ETL configuration to Yes to enable the ETL function.

After enabling ETL rules, click Quick configuration field mapping/filtering rules, configure synchronization objects in the Quick configuration field mapping/filtering rules dialog box, and click OK to quickly generate ETL statements.

After enabling the ETL function, you can configure data processing ETL rules by referring to the DSL syntax. Enter the data processing ETL rules, i.e., the DSL script, in the Enter ETL statements or use ETL assistant to generate statements...text box, and click Validate syntax to verify whether the data processing statements are correct.

You can also use the ETL assistant to generate ETL statements in natural language. In the upper-right corner of the ETL statement text box, click ETL assistant to input ETL requirements, and press Enter. After generating the ETL, copy the generated ETL statements into the ETL text box. The ETL assistant offers a variety of ETL statement generation capabilities. For more information, refer to ETL assistant capabilities.

Click Debug to test the effects of the ETL rules you created. For details on debugging, refer to Verifying ETL rules via ETL Playground tool.

Click Start task.

Disable ETL configuration

Currently, only disabling ETL configuration for synchronization tasks from MySQL to Elasticsearch is supported.

In the upper-left corner of the top menu bar, select the project and region.

In the left navigation bar, click Data synchronization.

Click Configure for the target unidirectional synchronization task.

On the modify synchronization task page, locate the Advanced configuration area, set Enable ETL configuration to No to disable the ETL function.

Click Start task.

Last updated: 2026.01.21 17:35:51