Data Transmission Service:Configure ETL in a data migration or synchronization task

You can modify the ETL configurations in the following scenarios:

• If the Configure ETL parameter is set to No for an existing data synchronization task, you can set this parameter to Yes and enter DSL statements.

• If the Configure ETL parameter is set to Yes for an existing data synchronization task, you can modify the existing DSL statements or set this parameter to No.

  Important

  • Before you modify the existing DSL statements, you must move the objects to be synchronized from the Selected Objects section to the Source Objects section and then add these objects to the Selected Objects section again.

  • You cannot modify the existing DSL statements in a data migration task.

Usage notes

• For an existing data synchronization task, you cannot modify the destination table schema by modifying the ETL configurations of the task. You can modify the destination table schema only before the data synchronization task is started.

• If you modify the ETL configurations, the data synchronization task may be interrupted. Proceed with caution.

• The modification of the ETL configurations takes effect only on the incremental data generated after the modification.

• DSL scripts are used to implement operations such as data transformation and data cleansing. However, you cannot create a database object by executing the DSL script.

• The fields that are configured in the DSL script must exist in the source database and cannot be the fields that are filtered out by filter conditions. Otherwise, the task becomes abnormal.

• The DSL script is case-sensitive. The names of the databases, tables, and fields that are configured in the DSL script must be the same as those in the source database.

• The DSL script cannot contain multi-line expressions. You can use the e_compose function to combine multiple expressions into one expression.

• If a DML statement for the change of all tables in the source database is executed, the tables must have the same column upon DSL script processing. Otherwise, the task may fail. For example, if you use the e_set function in a DSL script to add a column, you must perform the INSERT, UPDATE, or DELETE operation on a source database to add the column to the destination table. For more information, see Record the time when data is modified.

DSL is a scripting language that is designed to process data in data synchronization scenarios. You can use conditional functions to process data of the string, date, and numeric types. DSL boasts the following characteristics that help you process data in a flexible manner:

• Various functions: DSL provides a variety of functions and supports combined functions.

• Simple syntax: DSL is easy to use. For more information about how to use DSL to filter, transform, and mask data, see the Typical scenarios section of this topic.

• High efficiency: DSL has a minimal effect on the data synchronization performance because DSL uses the code generation mechanism.

Constants and variables

• Constants

  Data type	Example
  int	123
  float	123.4
  string	"hello1_world"
  boolean	true or false
  datetime	DATETIME('2021-01-01 10:10:01')

• Variables

  Variable	Description	Data type	Example
  __TB__	The table name.	string	table
  __DB__	The database name.	string	mydb
  __OPERATION__	The operation type.	string	__OP_INSERT__,__OP_UPDATE__,__OP_DELETE__
  __BEFORE__	The previous image value of the UPDATE operation, which is the column value before the UPDATE operation is performed. Note The DELETE operation has only the previous image value.	Special mark, no type	v(`column_name`,__BEFORE__)
  __AFTER__	The new image value of the UPDATE operation, which is the column value after the UPDATE operation is performed. Note The INSERT operation has only the new image value.	Special mark, no type	v(`column_name`,__AFTER__)
  __COMMIT_TIMESTAMP__	The time when the transaction was committed.	datetime	'2021-01-01 10:10:01'
  `column`	The name of the column.	string	`id`, `name`

Expression functions

• Numeric operations

  Operation	Syntax	Valid value	Return value	Example
  Addition	op_sum(value1, value2)	value1: an integer or a floating-point number. value2: an integer or a floating-point number.	If value1 and value2 are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_sum(`col1`, 1.0)
  Subtraction	op_sub(value1, value2)	value1: an integer or a floating-point number. value2: an integer or a floating-point number.	If value1 and value2 are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_sub(`col1`, 1.0)
  Multiplication	op_mul(value1, value2)	value1: an integer or a floating-point number. value2: an integer or a floating-point number.	If value1 and value2 are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_mul(`col1`, 1.0)
  Division	op_div_true(value1, value2)	value1: an integer or a floating-point number. value2: an integer or a floating-point number.	If value1 and value2 are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_div_true(`col1`, 2.0). In this example, if the value of col1 is 15, 7.5 is returned.
  Modulo	op_mod(value1, value2)	value1: an integer or a floating-point number. value2: an integer or a floating-point number.	If value1 and value2 are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_div_true(`col1`, 2.0). In this example, if the value of col1 is 15, 7.5 is returned.

• Logical operations

  Operation	Syntax	Valid value	Return value	Example
  Equal to	op_eq(value1, value2)	value1: an integer, a floating-point number, or a string. value2: an integer, a floating-point number, or a string.	true or false	op_eq(`col1`, 23)
  Greater than	op_gt(value1, value2)	value1: an integer, a floating-point number, or a string. value2: an integer, a floating-point number, or a string.	true or false	op_gt(`col1`, 1.0)
  Less than	op_lt(value1, value2)	value1: an integer, a floating-point number, or a string. value2: an integer, a floating-point number, or a string.	true or false	op_lt(`col1`, 1.0)
  Greater than or equal to	op_ge(value1, value2)	value1: an integer, a floating-point number, or a string. value2: an integer, a floating-point number, or a string.	true or false	op_ge(`col1`, 1.0)
  Less than or equal to	op_le(value1, value2)	value1: an integer, a floating-point number, or a string. value2: an integer, a floating-point number, or a string.	true or false	op_le(`col1`, 1.0)
  AND	op_and(value1, value2)	value1: a Boolean value. value2: a Boolean value.	true or false	op_and(`is_male`, `is_student`)
  OR	op_or(value1, value2)	value1: a Boolean value. value2: a Boolean value.	true or false	op_or(`is_male`, `is_student`)
  IN	op_in(value, json_array)	value: an arbitrary value. json_array: a JSON string.	true or false	op_in(`id`,json_array('["0","1","2","3","4","5","6","7","8"]'))
  Determine whether the value is empty	op_is_null(value)	value: an arbitrary value.	true or false	op_is_null(`name`)
  Determine whether the value is not empty	op_is_not_null(value)	value: an arbitrary value.	true or false	op_is_not_null(`name`)

• String functions

  Operation	Syntax	Valid value	Return value	Example
  Append strings	op_add(str_1,str_2,...,str_n)	str_1: string. str_2: string. ... str_n: string.	The string after the append operation.	op_add(`col`,'hangzhou','dts')
  Format strings and append strings	str_format(format, value1, value2, value3, ...)	format: a string. Braces ({}) are used as placeholders. Example: "part1: {}, part2: {}". value1: an arbitrary value. value2: an arbitrary value.	The string after the format operation.	str_format("part1: {}, part2: {}", `col1`, `col2`). In this example, if the value of col1 is ab and the value of col2 is 12, "part1: ab, part2: 12" is returned.
  Replace strings	str_replace(original, oldStr, newStr, count)	original: the original string. oldStr: the string to be replaced. newStr: the string after the replacement. count: an integer that indicates the maximum number of times that a string can be replaced. A value of -1 indicates that all oldStr is replaced with newStr.	The string after the replace operation.	Example 1: str_replace(`name`, "a", 'b', 1). In this example, if the name is aba, bba is returned. Example 2: str_replace(`name`, "a", 'b', -1). In this example, if the name is aba, bbb is returned.
  Replace strings in the values of fields of all string types, such as the VARCHAR, TEXT, or CHAR type	tail_replace_string_field(search, replace, all)	search: the string to be replaced. replace: the string after the replacement. all: indicates whether to replace all matched strings. Only a value of true is supported. Note If you do not need to replace all matched strings, use the str_replace() function.	The string after the replace operation.	tail_replace_string_field('\u000f','',true). In this example, all "\u000f" strings in the field values of the string type are replaced with spaces.
  Remove specific characters at the start and end of a string	str_strip(string_val, charSet)	string_val: The original string char_set: the set of the first characters and the last characters of the string.	The string after the remove operation.	str_strip(`name`, 'ab'). In this example, if the name is axbzb, xbz is returned.
  Convert strings to lowercase letters	str_lower(value)	value: a column of the string type or a string constant.	The string after the convert operation.	str_lower(`str_col`)
  Convert strings to uppercase letters	str_upper(value)	value: a column of the string type or a string constant.	The string after the convert operation.	str_upper(`str_col`)
  Convert strings to numbers	cast_string_to_long(value)	value: a string.	The string after the convert operation.	cast_string_to_long(`col`)
  Convert numbers to strings	cast_long_to_string(value)	value: an integer.	The string after the convert operation.	cast_long_to_string(`col`)
  Count strings	str_count(str,pattern)	str: a column of the string type or a string constant. pattern: the substring to query.	The number of times for which the substring appears.	str_count(`str_col`, 'abc'), if str_col="zabcyabcz", it returns 2.
  Query strings	str_find(str, pattern)	str: a column of the string type or a string constant. pattern: the substring to query.	The position in which the substring matches for the first time. If no match is found, -1 is returned.	str_find(`str_col`, 'abc'), if `str_col="xabcy"`, returns `1`.
  Determine whether a string contains only letters	str_isalpha(str)	str: a column of the string type or a string constant.	true or false	str_isalpha(`str_col`)
  Determine whether a string contains only digits	str_isdigit(str)	str: a column of the string type or a string constant.	true or false	str_isdigit(`str_col`)
  Regular expression match	regex_match(str,regex)	str: a column of the string type or a string constant. regex: a regular expression.	true or false	regex_match(__TB__,'user_\\d+')
  Mask part of a string with specific characters. This operation can be used for data masking. For example, mask the last four digits of a mobile phone number with four asterisks (*).	str_mask(str, start, end, maskStr)	str: a column of the string type or a string constant. start: an integer that indicates the start position of the masking. The minimum value is 0. end: an integer that indicates the end position of the masking. The maximum value is the length of the string minus 1. maskStr: a string. The length is 1. Example: #.	The string whose part from start to end is masked with the specified characters.	str_mask(`phone`, 7, 10, '#')
  Truncate a string from the cond string to the last character	substring_after(str, cond)	str: the original string. cond: a string.	The string after the truncate operation. Note The return value does not contain the cond string.	substring_after(`col`, 'abc')
  Extract the part before the string cond	substring_before(str, cond)	str: the original string. cond: a string.	The string after the truncate operation. Note The return value does not contain the cond string.	substring_before(`col`, 'efg')
  Extract the substring between cond1 and cond2	substring_between(str, cond1, cond2)	str: the original string. cond1: a string. cond2: a string.	The string after the truncate operation. Note The return value does not contain the cond1 and cond2 strings.	substring_between(`col`, 'abc','efg')
  Determine whether it is a string type	is_string_value(value)	value: a string or a column name.	true or false	is_string_value(`col1`)
  String type field content replacement; reverse order starting from the end	tail_replace_string_field(search, replace, all)	search: the string to be replaced. replace: the string after the replacement. all: indicates whether to replace all matched strings. Valid values: true and false.	The string after the replace operation.	In the following example, all "\u000f" strings in the field values of the string type are replaced with spaces: tail_replace_string_field('\u000f','',true)
  Get the value of a field in MongoDB	bson_value("field1","field2","field3",…)	field1: the name of the level -1 field. field2: the name of the level -2 field.	The value of the corresponding field in the document.	e_set(`user_id`, bson_value("id")) e_set(`user_name`, bson_value("person","name"))

Global functions

• Flow control functions

  Operation	Syntax	Description	Example
  if statement	e_if(bool_expr, func_invoke)	bool_expr: a Boolean constant or a function. A Boolean constant: true or false. A function: op_gt(`id`, 10). func_invoke: a function. e_drop,e_keep,e_set,e_if,e_compose	e_if(op_gt(`id`, 10), e_drop()). In this example, if an entry whose value of the id column is greater than 10, this entry is dropped.
  if-else statement	e_if_else(bool_expr, func_invoke1, func_invoke2)	bool_expr: a Boolean constant or a function. A Boolean constant: true or false. A function: op_gt(`id`, 10). func_invoke1: a function. Invoke this function if the condition is true. func_invoke2: a function. Invoke this function if the condition is false.	e_if_else(op_gt(`id`, 10), e_set(`tag`, 'large'), e_set(`tag`, 'small')). In this example, if an entry whose value of the id column is greater than 10, the tag column is set to large. Otherwise, the tag column is set to small.
  SWITCH statement that contains multiple conditions and a default operation	s_switch(condition1, func1, condition2, func2, ..., default = default_func)	condition1: a Boolean constant or a function. A Boolean constant: true or false. A function: op_gt(`id`, 10). func_invoke: a function. If condition1 is true, invoke this function and finish the statement. Otherwise, proceed to the next condition. default_func: a function. If the preceding conditions are false, invoke this function.	e_switch(op_gt(`id`, 100), e_set(`str_col`, '>100'), op_gt(`id`, 90), e_set(`str_col`, '>90'), default=e_set(`str_col`, '<=90')).
  Combination of multiple operations	e_compose(func1, func2, func3, ...)	func1: a function. Valid values: e_set, e_drop, and e_if. func2: a function. Valid values: e_set, e_drop, and e_if.	e_compose(e_set(`str_col`, 'test'), e_set(`dt_col`, dt_now())). In this example, the value of the str_col column is set to test, and the value of the dt_col column is set to the current time.

• Data manipulation functions

  Operation	Syntax	Description	Example
  Drop an entry so that it is not synchronized to the destination database	e_drop()	N/A	e_if(op_gt(`id`, 10), e_drop()). This example discards records with an ID greater than 10.
  Retain an entry so that it is synchronized to the destination database	e_keep(condition)	condition: a Boolean expression.	e_keep(op_gt(id, 1)). This example synchronizes only data with an ID greater than 1.
  Specify the value of a column	e_set(`col`, val, NEW)	col: the name of the column. val: a constant or a function. The data type of the value of val must match that of col. NEW: a data type the value of val that is converted from that of col. This is an optional field. Important If NEW is not specified, do not specify the comma (,) before NEW. Make sure that data types are converted in a compatible way. Otherwise, an error occurs.	e_set(`dt_col`, dt_now()). In this example, the value of the dt_col column is set to the current time. e_set(`col1`, `col2` + 1). In this example, the value of the col1 column is set to the value of the col2 column plus 1. e_set(`col1`, 1, NEW). In this example, the col1 column is converted to a numeric type and the value is set to 1.
  Retain fields, drop fields, and use the field name mapping feature during data migration or synchronization between MongoDB databases	e_expand_bson_value('*', 'fieldA'，{"fieldB":"fieldC"})	*: the name of the field to be retained. * indicates all fields. fieldA: the name of the field to be dropped. {"fieldB":"fieldC"}: the field name mapping. fieldB indicates the name of the field in the source database, and fieldC indicates the name of the field in the destination database. Note The field name mapping is an optional expression.	e_expand_bson_value("*", "_id,name"). In this example, the fields other than _id and name are written to the destination database.