Data Transmission Service:Konfigurasikan ETL dalam tugas migrasi atau sinkronisasi DTS

Mengubah konfigurasi ETL pada tugas sinkronisasi yang sudah ada mencakup:

• Jika Anda memiliki tugas sinkronisasi yang sudah ada tanpa konfigurasi ETL—artinya saat membuat tugas sinkronisasi, Configure ETL diatur ke No—Anda dapat mengubah No menjadi Yes dan mengonfigurasi skrip DSL.

• Jika ETL sudah dikonfigurasi, Anda dapat mengubah skrip DSL yang ada atau mengatur Configure ETL ke No.

  Penting

  • Untuk mengubah skrip DSL yang ada, pertama-tama pindahkan objek sinkronisasi dari Selected Objects kembali ke Source Objects, lalu tambahkan kembali ke Selected Objects sebelum mengedit skrip DSL.

  • Tugas migrasi tidak mendukung pengubahan skrip DSL.

Catatan

• Mengubah konfigurasi ETL pada tugas sinkronisasi yang sudah ada tidak mendukung perubahan skema tabel di database tujuan. Untuk mengubah skema, lakukan di database tujuan sebelum memulai tugas sinkronisasi.

• Mengubah konfigurasi ETL dapat mengganggu tautan data. Lakukan dengan hati-hati.

• Perubahan konfigurasi ETL hanya berlaku untuk data inkremental yang diproses setelah tugas dimulai ulang. Perubahan ini tidak memengaruhi data historis yang diproses sebelum perubahan.

• Skrip DSL hanya menangani operasi transformasi dan pembersihan data. Skrip ini tidak mendukung pembuatan objek database.

• Bidang yang dirujuk dalam skrip DSL harus ada di database sumber dan tidak boleh difilter oleh kondisi filter apa pun. Jika tidak, tugas mungkin gagal.

• Skrip DSL bersifat case-sensitive. Nama database, nama tabel, dan nama bidang harus persis sama dengan yang ada di database sumber.

• Skrip DSL tidak mendukung beberapa ekspresi. Gunakan fungsi e_compose untuk menggabungkan beberapa ekspresi menjadi satu.

• Semua perubahan DML dari semua tabel di database sumber harus menghasilkan informasi kolom yang identik setelah pemrosesan DSL. Jika tidak, tugas mungkin gagal. Misalnya, jika Anda menggunakan fungsi e_set untuk menambahkan kolom, pastikan operasi INSERT, UPDATE, dan DELETE dari database sumber semuanya menghasilkan kolom tambahan yang sama di tabel tujuan. Untuk informasi lebih lanjut, lihat Catat waktu modifikasi data.

DSL pemrosesan data adalah bahasa skrip yang dirancang oleh DTS khusus untuk pemrosesan data dalam skenario sinkronisasi data. DSL ini mendukung fungsi kondisional serta menangani string, tanggal, dan nilai numerik, sehingga memungkinkan Anda mendefinisikan logika pemrosesan data secara fleksibel dengan fitur-fitur berikut:

• Fungsionalitas kuat: Menyediakan banyak fungsi dan mendukung komposisi fungsi.

• Sintaks relatif sederhana: Menyertakan contoh untuk skenario umum seperti penyaringan data, transformasi, dan penyamaran. Untuk detailnya, lihat Contoh skenario khas.

• Efisiensi eksekusi tinggi: Menggunakan teknologi generasi kode untuk meminimalkan dampak performa pada proses sinkronisasi.

Konstanta dan variabel

• Konstanta

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

• Variabel

  Variable	Description	Data type	Example value
  __TB__	Table name	string	table
  __DB__	Database name	string	mydb
  __OPERATION__	Operation type	string	__OP_INSERT__, __OP_UPDATE__, __OP_DELETE__
  __BEFORE__	Nilai pre-image untuk operasi UPDATE (nilai sebelum perubahan) Catatan Operasi DELETE hanya memiliki nilai pre-image.	Special marker, no type	v(`column_name`,__BEFORE__)
  __AFTER__	Nilai post-image untuk operasi UPDATE (nilai setelah perubahan) Catatan Operasi INSERT hanya memiliki nilai post-image.	Special marker, no type	v(`column_name`,__AFTER__)
  __COMMIT_TIMESTAMP__	Transaction commit time	datetime	'2021-01-01 10:10:01'
  `column`	Value of the specified column for a record	string	`id`, `name`

Fungsi ekspresi

• Operasi numerik

  Function	Syntax	Value range	Return value	Example
  Addition	op_sum(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_sum(`col1`, 1.0)
  Subtraction	op_sub(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_sub(`col1`, 1.0)
  Multiplication	op_mul(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_mul(`col1`, 1.0)
  Division	op_div_true(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_div_true(`col1`, 2.0); if col1=15, returns 7.5.
  Modulo operation	op_mod(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_mod(`col1`, 10); if col1=23, returns 3.

• Operasi logis

  Function	Syntax	Valid values	Return value	Example
  Equality check	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 check	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)
  is 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 check	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 check	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: JSON-formatted string	Boolean: true or false	op_in(`id`,json_array('["0","1","2","3","4","5","6","7","8"]'))
  Check if value is null	op_is_null(value)	value: any type	Boolean: true or false	op_is_null(`name`)
  Check if value is not null	op_is_not_null(value)	value: any type	Boolean: true or false	op_is_not_null(`name`)

• Fungsi string

  Function	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	Concatenated string	op_add(`col`,'hangzhou','dts')
  String formatting and concatenation	str_format(format, value1, value2, value3, ...)	format: string with braces as placeholders, such as "part1: {}, part2: {}" value1: any type value2: any type	Formatted string	str_format("part1: {}, part2: {}", `col1`, `col2`); if col1="ab" and col2="12", returns "part1: ab, part2: 12".
  String replacement	str_replace(original, oldStr, newStr, count)	original: original string oldStr: string to replace newStr: replacement string count: integer, maximum number of replacements. Set to -1 for all occurrences.	Replaced string	str_replace(`name`, "a", 'b', 1); if name="aba", returns "bba". str_replace(`name`, "a", 'b', -1); if name="aba", returns "bbb".
  Replace values in all string-type fields (such as varchar, text, char)	tail_replace_string_field(search, replace, all)	search: string to replace replace: replacement string all: whether to replace all matches; currently supports only true. Catatan If you do not want to replace all matches, use the str_replace function.	Replaced string	tail_replace_string_field('\u000f', '', true) replaces all occurrences of "\u000f" in string-type field values with a space.
  Remove specified characters from start and end of string	str_strip(string_val, charSet)	string_val: original string char_set: set of characters to remove	String with leading and trailing characters removed	str_strip(`name`, 'ab'); if name=axbzb, returns xbz.
  Convert string to lowercase	str_lower(value)	value: string column or string literal	Lowercase string	str_lower(`str_col`)
  Convert string to uppercase	str_upper(value)	value: string column or string literal	Uppercase string	str_upper(`str_col`)
  Convert string to number	cast_string_to_long(value)	value: string	Integer	cast_string_to_long(`col`)
  Convert number to string	cast_long_to_string(value)	value: integer	String	cast_long_to_string(`col`)
  Count substring occurrences	str_count(str,pattern)	str: string column or string literal pattern: substring to find	Number of times the substring appears	str_count(`str_col`, 'abc'); if str_col="zabcyabcz", returns 2.
  Find substring position	str_find(str, pattern)	str: string column or string literal pattern: substring to find	Position of first match; returns `-1` if not found	str_find(`str_col`, 'abc'); if `str_col="xabcy"`, returns `1`.
  Check if string consists only of letters	str_isalpha(str)	str: string column or string literal	true or false	str_isalpha(`str_col`)
  Check if string consists only of digits	str_isdigit(str)	str: string column or string literal	true or false	str_isdigit(`str_col`)
  Regular expression matching	regex_match(str,regex)	str: string column or string literal regex: regular expression string column or string literal	true or false	regex_match(__TB__,'user_\\d+')
  Mask part of a string with a specified character for data masking, such as replacing the last four digits of a phone number with asterisks	str_mask(str, start, end, maskStr)	str: string column or string literal start: integer, starting position for masking (minimum 0) end: integer, ending position for masking (maximum string length minus one) maskStr: single-character string, such as '#'	String with characters from start to end masked	str_mask(`phone`, 7, 10, '#')
  Extract part of string after cond	substring_after(str, cond)	str: original string cond: string	String Catatan Result does not include cond.	substring_after(`col`, 'abc')
  Extract part of string before cond	substring_before(str, cond)	str: original string cond: string	String Catatan Result does not include cond.	substring_before(`col`, 'efg')
  Extract part of string between cond1 and cond2	substring_between(str, cond1, cond2)	str: original string cond1: string cond2: string	String Catatan Result does not include cond1 or cond2.	substring_between(`col`, 'abc','efg')
  Check if value is a string type	is_string_value(value)	value: string or column name	Boolean: true or false	is_string_value(`col1`)
  Replace content in string-type fields; starts from the end in reverse order	tail_replace_string_field(search, replace, all)	search: string to replace replace: replacement string all: whether to replace all; true or false	Replaced string	Replace "\u000f" with a space in all string field values. tail_replace_string_field('\u000f','',true)
  Get value of a field in MongoDB	bson_value("field1","field2","field3",...)	field1: top-level field name field2: second-level field name	Value of the specified field in the document	e_set(`user_id`, bson_value("id")) e_set(`user_name`, bson_value("person","name"))

Fungsi global

• Fungsi kontrol alur

  Function	Syntax	Parameters	Example
  if statement	e_if(bool_expr, func_invoke)	bool_expr: boolean constant or function call. Constants: true or false. Function call example: op_gt(`id`, 10). func_invoke: function call. Supported: e_drop, e_keep, e_set, e_if, e_compose	e_if(op_gt(`id`, 10), e_drop()); drops the record if ID > 10.
  if-else statement	e_if_else(bool_expr, func_invoke1, func_invoke2)	bool_expr: boolean constant or function call. Constants: true or false. Function call example: op_gt(`id`, 10). func_invoke1: function call executed if condition is true. func_invoke2: function call executed if condition is false.	e_if_else(op_gt(`id`, 10), e_set(`tag`, 'large'), e_set(`tag`, 'small')); sets tag to "large" if ID > 10, otherwise to "small".
  Switch-like statement that evaluates multiple conditions and executes the first matching operation. Executes a default operation if no conditions match.	s_switch(condition1, func1, condition2, func2, ..., default=default_func)	condition1: boolean constant or function call. Constants: true or false. Function call example: op_gt(`id`, 10). func_invoke: function call. Checks condition1; if true, executes this function and exits the switch. If false, checks the next condition. default_func: function call executed if all conditions are false.	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')).
  Combine multiple operations	e_compose(func1, func2, func3, ...)	func1: function call. Can be e_set, e_drop, e_if. func2: function call. Can be e_set, e_drop, e_if.	e_compose(e_set(`str_col`, 'test'), e_set(`dt_col`, dt_now())); sets str_col to "test" and dt_col to the current time.

• Fungsi manipulasi data

  Function	Syntax	Parameters	Example
  Drop this record (do not synchronize)	e_drop()	None	e_if(op_gt(`id`, 10), e_drop()); drops records where ID > 10.
  Keep this record (synchronize to destination)	e_keep(condition)	condition: boolean expression	e_keep(op_gt(id, 1)); synchronizes only records where ID > 1.
  Set column value	e_set(`col`, val, NEW)	col: column name val: constant or function call. Must match col's data type. NEW: converts col to val's data type (optional) Penting If you omit NEW, do not include the preceding comma. Ensure data type compatibility to avoid task errors.	e_set(`dt_col`, dt_now()); sets dt_col to current time. e_set(`col1`, `col2` + 1); sets col1 to col2 + 1. e_set(`col1`, 1, NEW); converts col1 to numeric type and sets it to 1.
  MongoDB field retention, field dropping, and field name mapping	e_expand_bson_value('*', 'fieldA',{"fieldB":"fieldC"})	*: field names to retain; * means all fields. fieldA: field names to drop. {"fieldB":"fieldC"}: field name mapping; fieldB is the source field name, fieldC is the destination field name. Catatan Field name mapping is optional.	e_expand_bson_value("*", "_id,name"); writes all fields except _id and name to the destination.