When you configure objects for a data migration or data synchronization task in Data Transmission Service (DTS), you can specify SQL conditions to migrate or synchronize only data that meets the specified conditions to the destination database. This feature is applicable to scenarios such as database sharding, table partitioning, and synchronization or migration of partial data.
Prerequisites
The DTS task is being configured in the Configure Objects and Advanced Settings step. For more information about how to configure a DTS task, see the topics listed in Overview of data synchronization scenarios and Overview of data migration scenarios.
Usage notes
You cannot use complex SQL statements as filter conditions. Example:
COLUMN1 IN (SELECT id FROM table1)
.If two columns in the source table or collection have the same name that differ only in capitalization, the data may not be filtered as expected.
If the source is an ApsaraDB for Redis Enhanced Edition (Tair) instance, you can filter data only by using the prefix of keys.
If the source is an MongoDB database, you can specify filter conditions only for full data synchronization or full data migration. You cannot specify filter conditions for incremental data synchronization or incremental data migration.
If the source is not an ApsaraDB for Redis Enhanced Edition (Tair) instance or a MongoDB database, use single quotation marks (') in an SQL condition if necessary.
A filter condition cannot end with a semicolon (;). Otherwise, the DTS task may fail.
Assume that you execute an UPDATE statement in the source database. If the previous image value meets filter conditions whereas the new image value does not meet filter conditions, DTS converts the UPDATE statement to a DELETE statement and then executes the DELETE statement in the destination database.
Procedure
In the Configure Objects and Advanced Settings step, move an object that you want to synchronize or migrate to the Selected Objects section and then right-click the object.
NoteIf the source is a MongoDB database, you can select only collections as objects to be synchronized or migrated. If the source is a ApsaraDB for Redis Enhanced Edition (Tair) instance, you can select only databases as objects to be synchronized or migrated. If the source is not a Tair or Redis instance or a MongoDB database, you can select only tables as objects to be synchronized or migrated.
In the dialog box that appears, enter an SQL condition in the Filter Conditions field.
The syntax of SQL conditions varies based on the type of the source, such as ApsaraDB for Redis Enhanced Edition (Tair), MongoDB, or another type of database.
If the source is an ApsaraDB for Redis Enhanced Edition (Tair) instance:
In the Edit Schema dialog box, configure the Prefixes of Keys to Be Synchronized or Prefixes of Keys to Be Filtered Out parameter.
Sample conditions
Filter type
Example
Description
Synchronize objects identified by a specific prefix
dtsnew
Only objects whose keys are prefixed with
dtsnew
are synchronized to the destination.Filter out objects identified by a specific prefix
dtstest
All objects other than those whose keys are prefixed with
dtstest
are synchronized to the destination.Synchronize objects identified by multiple prefixes
dtsnew,dtsold
Only objects whose keys are prefixed with
dtsnew
ordtsold
are synchronized to the destination.Mixed condition
Set the Prefixes of Keys to Be Synchronized parameter to
dts
.Set the Prefixes of Keys to Be Filtered Out parameter to
dtstest
.Only objects whose keys are prefixed with
dts
and are not prefixed withdtstest
are synchronized to the destination.If the source is a MongoDB database:
In the Edit Table dialog box, specify filter conditions in the Filter Conditions field.
Sample conditions
Filter type
Example
Description
Other type
{"_id":{$gt:"user100844658590795****",$lte:"user101674868045948****"}}
Filter data by user ID. In this example,
$gt
indicates greater than, and$lte
indicates smaller than or equal to. SQL conditions supported by MongoDB databases are different from standard SQL WHERE statements. For more information, see Query and Projection Operators and SQL to MongoDB Mapping Chart in MongoDB documentation.If the source is a database of another type:
In the Edit Table dialog box, specify filter conditions in the Filter Conditions field.
NoteIn the Filter Conditions field, the following operators are supported in the SQL conditions:
=
,!=
,<
,>
, andin
.For more information about how to specify filter conditions, see the FAQ section of this topic.
Sample conditions
Filter type
Example
Description
Number
id > 100 and id <= 200 or id in (1, 2, 3)
Search for data whose
id
value is greater than 100 and smaller than or equal to 200, or whoseid
value is equal to 1, 2, or 3.String
address in('hangzhou','shanghai')
Search for data whose
address
value ishangzhou
orshanghai
.Time
create_time>'2020-01-01'
orcreate_time>'2020-01-01 00:00:00'
Search for data created in 2020 and later.
ImportantYou cannot enter
create_time>'2020'
.DTS uses the UTC+8 time zone. If your time data contains time zone information, you must convert the time in the filter condition to the time in the UTC+8 time zone and then specify the converted filter condition in the Filter Conditions field.
Click OK.
Configure other parameters that are required for the DTS task.
What to do next
View filter conditions
For a DTS task that is being configured, go back to the Configure Objects and Advanced Settings step. In the Selected Objects section, view the objects to be synchronized or migrated, and right-click an object. In the dialog box that appears, view the value in the Filter Conditions field.
For a DTS task that is configured and is running, click Duplicate Task or Reselect Objects in the Actions column of the DTS task to go to the Configure Objects and Advanced Settings step. In the Selected Objects section, view the objects to be synchronized or migrated, and right-click an object. In the dialog box that appears, view the value in the Filter Conditions field. After you view the filter condition, you can directly cancel the task. For more information, see Create a similar instance and Modify the objects to be synchronized.
Modify filter conditions
For a DTS task that is being configured, go back to the Configure Objects and Advanced Settings step. In the Selected Objects section, view the objects to be synchronized or migrated, and right-click an object. In the dialog box that appears, modify the value in the Filter Conditions field.
For a DTS task that is configured and is running:
If the task is a data migration task, you cannot modify the filter condition for the task. You must reconfigure a data migration task.
If the task is a data synchronization task, click Reselect Objects in the Actions column of the DTS task to go to the Configure Objects and Advanced Settings step. In the Selected Objects section, view the objects to be synchronized, and right-click an object. In the dialog box that appears, modify the value in the Filter Conditions field. Then, configure other parameters that are required for the task as prompted. For more information, see Modify the objects to be synchronized.
Common errors and troubleshooting
If the filter condition that you enter is invalid, the following errors may occur. You can fix the reported error based on the error message and the What to do next section of this topic.
The regular expression ((.*)?
) in the error message indicates a variable.
Error code | Error Message | Description |
DTS-RETRY-ERR-0070 | In process of processing data (.*)? failed (.*)? Syntax error in SQL statement | The fields used to filter the objects to be synchronized are specified in an invalid format. |
DTS-RETRY-ERR-0145 | Unknown column (.*)? in | The fields in the filter condition configured for the task do not exist in the source. |
DTS-RETRY-ERR-0147 | You have an error in your SQL syntax | The fields in the filter condition configured for the task do not exist in the source. |
DTS-RETRY-ERR-0188 | null value in column (.*)? violates not-null constraint | A column with the NOT NULL constraint in the destination is filtered out, or the value of the corresponding column in the source is NULL. |
DTS-RETRY-ERR-0276 | if (.*)? is specified it must not be empty | The value mapped to the |
FAQ
Why does a filter condition not take effect?
The filter condition is invalid or is specified for an incremental data synchronization task or an incremental data migration task whose source is a MongoDB database.
What does a filter condition apply to after the filter condition is modified?
For a DTS task that is being configured: If the source is a MongoDB database, the filter condition applies to full data synchronization or full data migration. If the source is a database of another type, the filter condition applies to full data synchronization and incremental data synchronization or full data migration and incremental data migration.
For a DTS task that is configured and is running: If the source is a MongoDB database, the filter condition does not apply to the DTS task. If the source is a database of another type, the filter condition applies to incremental data synchronization or incremental data migration.
Can I specify multiple filter conditions at a time?
No, you can specify filter conditions for multiple objects separately.
Can I filter out the data that meets the specified condition and synchronize or migrate the remaining data to the destination?
Yes. If the source is an ApsaraDB for Redis Enhanced Edition (Tair) instance, you can configure the Prefixes of Keys to Be Filtered Out parameter to filter out data.
If the source is a database of another type, you cannot filter out data. You can use other filter conditions to meet your business requirements. For example, if you want to filter out data in which the value of the
age
field is 25, you can enterage > 25 or age < 25
in the Filter Conditions field.
How do I specify a filter condition if I want to filter data based on multiple keys?
You can use commas (,) to separate multiple keys.
Can I configure both the Prefixes of Keys to Be Synchronized and Prefixes of Keys to Be Filtered Out parameters?
Yes. In this case, the filter conditions specified by the Prefixes of Keys to Be Synchronized and Prefixes of Keys to Be Filtered Out parameters both take effect.
Can I call an API operation to specify filter conditions?
No, you cannot call an API operation to specify filter conditions.
Related API operations
API operation | Description |
Configures a data migration or synchronization task. You can configure the |