Data Transmission Service:Add additional columns

Notes

• You can add additional columns to synchronization or migration instances in the following scenarios:

  • The Database Type for the destination database is DataHub, Lindorm, Kafka, or ClickHouse.

  • The Database Type for the source database is DB2 for LUW or DB2 for iSeries (AS/400), and the Database Type for the destination database is MySQL or PolarDB for MySQL.

  • The Database Type of the source database is MySQL, MariaDB, or PolarDB for MySQL, and the Database Type of the destination database is MySQL, MariaDB, or PolarDB for MySQL.

  • The source database's Database Type is MySQL, and the destination database's Database Type is Tair/Redis, AnalyticDB for PostgreSQL, or AnalyticDB for MySQL 3.0.

  • The Database Type for the source database is PolarDB for PostgreSQL, and the Database Type for the destination database is AnalyticDB for PostgreSQL.

  • The Database Type for the source database is SQL Server, and the Database Type for the destination database is MySQL.

• For synchronization instances, set Synchronization Types to Schema Synchronization. For migration instances, set Migration Types to Schema Migration.

• Before you modify the rules for additional columns in a data synchronization task, evaluate whether the names of the additional columns conflict with existing columns in the destination table.

• If the source database of a synchronization task is MongoDB, the collections in the destination database cannot contain fields named _id or _value. Otherwise, the synchronization fails.

• If you right-click a database in the Selected Objects section, DTS adds the configured additional columns to all tables within the corresponding destination database.

Procedure

This section uses a DTS synchronization instance as an example to describe how to add additional columns.

1. Go to the Data Synchronization Tasks page.

   1. Log on to the Data Management (DMS) console.

   2. In the top navigation bar, click Data + AI.

   3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

   Note

   • Operations may vary based on the mode and layout of the DMS console. For more information, see Simple mode console and Customize the layout and style of the DMS console.

   • You can also go to the Data Synchronization Tasks page of the new DTS console.

2. Click Create Task and configure the source and destination databases.

   Note

   Click Reselect Objects to add additional columns to a Running synchronization instance.

3. Follow the prompts to the Configure Objects step to complete the configuration.

   You can add additional columns in this step.

   1. Set Synchronization Types to Schema Synchronization.

   2. In the Source Objects section, select the databases or tables to synchronize, and then click to move them to the Selected Objects box.

   3. In the Selected Objects section, right-click the database or table that you want to sync.

   4. In the Additional Columns section of the dialog box, click the Add Column button.

   5. Enter the Column Name, Type, and Assign Value for the additional column.

      Note

      For Assign Value, you can click the icon to the right of the text box to customize the expression for the value of the additional column. For more information, see Assignment configuration.

   6. Click OK.

4. Follow the prompts to complete the rest of the data synchronization task configuration.

   Note

   If the extract, transform, and load (ETL) feature is configured for the synchronization task, the data to be synchronized is first processed by the rules for additional columns to generate a value. Then, the ETL script is applied to calculate the final value, which is then synchronized to the destination database.

Assignment configuration

• Constants

  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 value
  __TB__	The name of the table in the database.	string	table
  __DB__	The name of the database.	string	mydb
  __OPERATION__	The type of the operation.	string	__OP_INSERT__,__OP_UPDATE__,__OP_DELETE__
  __COMMIT_TIMESTAMP__	The time when the transaction was committed.	datetime	'2021-01-01 10:10:01'
  `column`	The value of the column for a data record.	string	`id`, `name`
  __SCN__	System Change Number (SCN). It records the version and time of a transaction commit in the database and is unique.	string	22509****
  __ROW_ID__ Note MySQL does not support __ROW_ID__.	The address ID of a data record. It locates the data and is unique.	string	AAAgWHAAKAAJgX****

• Expression functions

  • Numerical operations

    Function	Syntax	Value range	Return value	Example
    Addition (+)	op_sum(value1, value2) value1+value2	value1: an integer or a floating-point number value2: an integer or a floating-point number	If both parameters are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_sum(`col1`, 1.0) `col1`+1.0
    Subtraction (-)	op_sub(value1, value2) value1-value2	value1: an integer or a floating-point number value2: an integer or a floating-point number	If both parameters are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_sub(`col1`, 1.0) `col1`-1.0
    Multiplication (*)	op_mul(value1, value2) value1*value2	value1: an integer or a floating-point number value2: an integer or a floating-point number	If both parameters are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_mul(`col1`, 1.0) `col1`*1.0
    Division (/)	op_div_true(value1, value2) value1/value2	value1: an integer or a floating-point number value2: an integer or a floating-point number	If both parameters are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_div_true(`col1`, 2.0). If col1=15, 7.5 is returned. `col1`/1.0
    Modulo operation	op_mod(value1, value2)	value1: an integer or a floating-point number value2: an integer or a floating-point number	If both parameters are integers, an integer is returned. Otherwise, a floating-point number is returned.	op_mod(`col1`, 10). If col1=23, 3 is returned.

  • Logical operations

    Feature	Syntax	Value range	Return value	Example
    Equals	op_eq(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	boolean: true or false	op_eq(`col1`, 23)
    Greater than	op_gt(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	boolean: true or false	op_gt(`col1`, 1.0)
    Less than	op_lt(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	boolean: true or false	op_lt(`col1`, 1.0)
    Greater than or equal to	op_ge(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	boolean: true or false	op_ge(`col1`, 1.0)
    Less than or equal to	op_le(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	boolean: true or false	op_le(`col1`, 1.0)
    AND operation	op_and(value1, value2)	value1: boolean value2: boolean	boolean: true or false	op_and(`is_male`, `is_student`)
    OR operation	op_or(value1, value2)	value1: boolean value2: boolean	boolean: true or false	op_or(`is_male`, `is_student`)
    IN operation	op_in(value, json_array)	value: any type json_array: a string in the JSON format	boolean: true or false	op_in(`id`,json_array('["0","1","2","3","4","5","6","7","8"]'))
    Is null	op_is_null(value)	value: any type	boolean: true or false	op_is_null(`name`)
    Is not null	op_is_not_null(value)	value: any type	boolean: true or false	op_is_not_null(`name`)

  • String functions

    Feature	Syntax	Value range	Return value	Example
    String concatenation	op_add(str_1,str_2,...,str_n)	str_1: string str_2: string ... str_n: string	The concatenated string	op_add(`col`,'hangzhou','dts')
    String formatting and concatenation	str_format(format, value1, value2, value3, ...)	format: a string that uses curly braces ({}) as placeholders, such as "part1: {}, part2: {}". value1: any value2: any	The formatted string	str_format("part1: {}, part2: {}", `col1`, `col2`). If col1="ab" and col2="12", "part1: ab, part2: 12" is returned.
    String replacement	str_replace(original, oldStr, newStr, count)	original: the original string oldStr: the string to be replaced newStr: the replacement string count: an integer that specifies the maximum number of replacements. If you set this parameter to -1, all occurrences are replaced.	The string after replacement	str_replace(`name`, "a", 'b', 1). If name="aba", "bba" is returned. str_replace(`name`, "a", 'b', -1). If name="aba", "bbb" is returned.
    Replace values in all string-type fields (such as varchar, text, and char)	tail_replace_string_field(search, replace, all)	search: the string to be replaced replace: the replacement string all: specifies whether to replace all matched strings. This parameter supports only true. Note If you do not need to replace all matched strings, use the str_replace function.	The string after replacement	tail_replace_string_field('\u000f','',true). Replaces "\u000f" with a space in the values of all string-type fields.
    Remove specific characters from the beginning and end of a string	str_strip(string_val, charSet)	string_val: the original string char_set: the collection of characters to be removed	The string after the characters are removed from the beginning and end	str_strip(`name`, 'ab'). If name=axbzb, xbz is returned.
    Convert a string to lowercase	str_lower(value)	value: a string column or string constant	A lowercase string	str_lower(`str_col`)
    Convert a string to uppercase	str_upper(value)	value: a string column or string constant	An uppercase string	str_upper(`str_col`)
    Convert a string to a number	cast_string_to_long(value)	value: string	Integer	cast_string_to_long(`col`)
    Convert a number to a string	cast_long_to_string(value)	value: integer	String	cast_long_to_string(`col`)
    Count occurrences of a substring	str_count(str,pattern)	str: a string column or string constant pattern: the substring to find	The number of occurrences of the substring	str_count(`str_col`, 'abc'). If str_col="zabcyabcz", 2 is returned.
    Find a substring	str_find(str, pattern)	str: a string column or string constant pattern: the substring to find	The position of the first match of the substring. If no match is found, `-1` is returned.	str_find(`str_col`, 'abc'). If `str_col="xabcy"`, `1` is returned.
    Check if a string consists of only letters	str_isalpha(str)	str: a string column or string constant	true or false	str_isalpha(`str_col`)
    Check if a string consists of only digits	str_isdigit(str)	str: a string column or string constant	true or false	str_isdigit(`str_col`)
    Regular expression matching	regex_match(str,regex)	str: a string column or string constant regex: a regular expression string column or string constant	true or false	regex_match(__TB__,'user_\\d+')
    Mask a part of a string with a specified character. This can be used for data masking, such as replacing the last four digits of a phone number with asterisks.	str_mask(str, start, end, maskStr)	str: a string column or string constant start: an integer that specifies the start position of the mask. The minimum value is 0. end: an integer that specifies the end position of the mask. The maximum value is the string length minus 1. maskStr: a string of length 1, such as '#'.	The string after the part from the start position to the end position is masked	str_mask(`phone`, 7, 10, '#')
    Get the substring after a specified string	substring_after(str, cond)	str: the original string cond: string	String Note The return value does not include the string cond.	substring_after(`col`, 'abc')
    Get the substring before a specified string	substring_before(str, cond)	str: the original string cond: string	String Note The return value does not include the string cond.	substring_before(`col`, 'efg')
    Get the substring between two specified strings	substring_between(str, cond1, cond2)	str: the original string cond1: string cond2: string	String Note The return value does not include the strings cond1 and cond2.	substring_between(`col`, 'abc','efg')
    Check if the value is a string	is_string_value(value)	value: a string or column name	boolean: true or false	is_string_value(`col1`)
    Replace content in string-type fields, starting in reverse from the end.	tail_replace_string_field(search, replace, all)	search: the string to be replaced replace: the replacement string all: specifies whether to replace all occurrences. The value can be true or false.	The string after replacement	Replace "\u000f" with a space in the values of all string-type fields. 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"))

  • Conditional expressions

    Feature	Syntax	Value range	Return value	Example
    Similar to the ternary operator (? :) in C. Returns a value based on a condition.	(cond ? val_1 : val_2)	cond: a boolean field or expression val_1: return value 1 val_2: return value 2 Note val_1 and val_2 must be of the same type.	If cond is true, val_1 is returned. Otherwise, val_2 is returned.	(id>1000? 1 : 0)

  • Time functions

    Feature	Syntax	Value range	Return value	Example
    Current system time	dt_now()	None	DATETIME, accurate to the second	dts_now()
    	dt_now_millis()	None	DATETIME, accurate to the millisecond	dt_now_millis()
    Convert a UTC timestamp (in seconds) to DATETIME	dt_fromtimestamp(value,[timezone])	value: integer timezone: the time zone. This is an optional parameter.	DATETIME, accurate to the second	dt_fromtimestamp(1626837629) dt_fromtimestamp(1626837629,'GMT+08')
    Convert a UTC timestamp (in milliseconds) to DATETIME	dt_fromtimestamp_millis(value,[timezone])	value: integer timezone: the time zone. This is an optional parameter.	DATETIME, accurate to the millisecond	dt_fromtimestamp_millis(1626837629123); dt_fromtimestamp_millis(1626837629123,'GMT+08')
    Convert DATETIME to a UTC timestamp (in seconds)	dt_parsetimestamp(value,[timezone])	value: DATETIME timezone: the time zone. This is an optional parameter.	Integer	dt_parsetimestamp(`datetime_col`) dt_parsetimestamp(`datetime_col`,'GMT+08')
    Convert DATETIME to a UTC timestamp (in milliseconds)	dt_parsetimestamp_millis(value,[timezone])	value: DATETIME timezone: the time zone. This is an optional parameter.	Integer	dt_parsetimestamp_millis(`datetime_col`) dt_parsetimestamp_millis(`datetime_col`,'GMT+08')
    Convert DATETIME to a string	dt_str(value, format)	value: DATETIME format: a string in the yyyy-MM-dd HH:mm:ss format	String	dt_str(`col1`, 'yyyy-MM-dd HH:mm:ss')
    Convert a string to DATETIME	dt_strptime(value,format)	value: string format: a string in the yyyy-MM-dd HH:mm:ss format	DATETIME	dt_strptime('2021-07-21 03:20:29', 'yyyy-MM-dd hh:mm:ss')
    Modify the time by adding or subtracting a value from the year, month, day, hour, minute, or second	dt_add(value, [years=intVal], [months=intVal], [days=intVal], [hours=intVal], [minutes=intVal] )	value: DATETIME intVal: integer Note A minus sign (-) indicates subtraction.	DATETIME	dt_add(datetime_col,years=-1) dt_add(datetime_col,years=1,months=1)

FAQ