When you select objects for a Data Transmission Service (DTS) task, you can specify SQL conditions to filter data. Only the data that meets the specified conditions is synchronized or migrated to the destination database. This feature is applicable to scenarios such as regular data synchronization or migration and table partitioning.
Prerequisites
The task is being configured. The current step is Configure Objects and Advanced Settings. For more information about how to create and configure a DTS task, see Configure a data synchronization task.
Limits
You can filter the fields only in the current table. Cross-table filtering is not supported.Procedure
- In the Configure Objects and Advanced Settings step, move a table to the Selected Objects section and then right-click the table.
- In the Edit Table Name dialog box, enter one or more SQL conditions in the Filter Conditions field. Note
- An SQL condition is a standard SQL WHERE statement. The following operators are supported:
=
,!=
,<
,>
, andin
. Only the data that meets the WHERE condition is synchronized or migrated to the destination database. In this example, enterorder>100
. - You can specify a time condition in an SQL WHERE statement. However, you must make
sure that the specified time condition is valid. For example, to filter incremental
data created since 2020, you must enter
create_time>'2020-01-01'
orcreate_time>'2020-01-01 00:00:00'
. You cannot entercreate_time>'2020'
. - You can use single quotation marks (') in an SQL condition if necessary. For example,
you can enter
address in('hangzhou','shanghai')
. - Filter conditions are case-insensitive. If a table in the source database contains
Column A and Column a, you can use an SQL WHERE statement to filter only Column A.
In this case, you can enter
WHERE A=10
rather thanWHERE a=10
. - Filter statements supported by ApsaraDB for MongoDB instances are different from standard
SQL WHERE statements. For example, if you want to use user ID as the filter condition,
enter
{"_id":{$gt:"user100844658590795****",$lte:"user101674868045948****"}}
. In this statement,gt
indicates greater than andlte
indicates less than.
- An SQL condition is a standard SQL WHERE statement. The following operators are supported:
- Click OK.
- Configure other parameters that are required for the DTS task.