Help Center/ Data Replication Service/ API Reference/ APIs V5.0 (in OBT)/ Data Processing/ Verifying Data Filtering Rules

Updated on 2024-05-27 GMT+08:00

Online Debugging

CLI Examples

View PDF

Verifying Data Filtering Rules

Function

This API is used to verify data filtering rules.

URI

POST /v5/{project_id}/job/{job_id}/data-filtering/check

**Table 1** Path parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Project ID of a tenant in a region. For details, see Obtaining a Project ID.
job_id	Yes	String	Task ID.

Request Parameters

**Table 2** Request header parameters
Parameter	Mandatory	Type	Description
Content-Type	Yes	String	MIME type of the request body. Use the default value application/json. For APIs used to upload objects or images, the value varies depending on the flow type. The default value is application/json.
X-Auth-Token	Yes	String	User token obtained from IAM. It is a response to the API for obtaining a user token. This API is the only one that does not require authentication. The token is the value of X-Subject-Token in the response header.
X-Language	No	String	Request language type. Default value: en-us Enumerated values: en-us zh-cn

**Table 3** Request body parameters
Parameter	Mandatory	Type	Description
data_process_info	No	Array of objects	Request body for data processing rules for a specified task. For details, see Table 4.

**Table 4** Data structure description of field **data_process_info**
Parameter	Mandatory	Type	Description
filter_conditions	No	Array of objects	Request body for data processing rules for a specified task. For details, see Table 5.
is_batch_process	No	Boolean	The value is true for database-level and batch table-level data processing and false for single-table data processing.
add_columns	No	Array of objects	Additional column. This parameter is mandatory when adding additional columns is required. NOTE: You must use additional columns to avoid data conflicts in many-to-one operations. For details, see Table 6.
ddl_operation	No	Map<String,String>	DDL operations to be migrated or synchronized in an incremental task. The value can be: If this parameter is set to table, DDL operations including CREATE TABLE, ALTER TABLE, DROP TABLE, and RENAME TABLE can be migrated or synchronized. If this parameter is left blank, DDL operations cannot be migrated or synchronized.
dml_operation	No	String	DML operations to be migrated or synchronized. i: INSERT u: UPDATE d: DELETE If this parameter is left blank, DML operations cannot be migrated or synchronized in an incremental task.
db_object_column_info	No	Object	Column mapping and filtering information. This parameter is mandatory when column mapping and filtering information is required. For details, see Table 7.
db_or_table_rename_rule	No	Object	Database table mapping rule. For details, see Table 9.
db_object	No	Object	Data processing object information, which is mandatory when column mapping and data filtering conditions are verified. For details, see Table 10.
is_synchronized	No	Boolean	Whether the rule has been synchronized to the destination database.
source	No	String	Comparison source. job: indicates the data filtering during synchronization. compare: indicates the data filtering during comparison. Enumerated values: job compare

**Table 5** Data structure description of field **filter_conditions**
Parameter	Mandatory	Type	Description
value	No	String	Filtering criteria. If filtering_type is set to configConditionalFilter, value is set to config by default. When filtering_type is set to contentConditionalFilter, value is set to the filtering conditions by default. NOTE: Each table has only one verification rule. Up to 500 tables can be filtered at a time. Standard SQL statements can be used to filter records. Each expression cannot contain packages, functions, variables, or constants specific to a database engine. Enter the part following WHERE in the SQL statement (excluding WHERE and semicolons), for example, sid > 3 and sname like "G %". A maximum of 512 characters are allowed. In SQL statements for setting filter criteria, keywords must be enclosed in backquotes, and the value of datatime (including date and time) must be enclosed in single quotation marks, for example, `update` > '2022-07-13 00:00:00' and age >10. Filter criteria cannot be configured for large objects, such as CLOB, BLOB, and BYTEA. Filtering rules cannot be set for objects whose database names and table names contain newline characters. You are not advised to set filter criteria for fields of approximate numeric types, such as FLOAT, DECIMAL, and DOUBLE. Do not use fields containing special characters as a filter condition. You are not advised to use non-idempotent expressions or functions as data processing conditions, such as SYSTIMESTAMP and SYSDATE, because the returned result may be different each time the function is called.
filtering_type	No	String	Filter condition type. contentConditionalFilter: simple condition-based filtering. configConditionalFilter: association table filtering. Enumerated values: contentConditionalFilter configConditionalFilter

**Table 6** Data structure description of field **add_columns**
Parameter	Mandatory	Type	Description
column_type	No	String	Column type.
column_name	No	String	Column name.
column_value	No	String	Column value.
data_type	No	String	Data type of a column.

**Table 7** Data structure description of field **db_object_column_info**
Parameter	Mandatory	Type	Description
db_name	No	String	Database name.
schema_name	No	String	Schema name.
table_name	No	String	Table name.
column_infos	No	Array of objects	Column information. For details, see Table 8.
total_count	No	Integer	Total number of database column records, which is irrelevant to pagination and is used only as a return body parameter.

**Table 8** Data structure description of field **column_infos**
Parameter	Mandatory	Type	Description
column_name	No	String	Column name.
column_type	No	String	Column type.
primary_key_or_unique_index	No	String	Primary key or unique index.
column_mapped_name	No	String	Mapped column name.
is_filtered	No	Boolean	Whether the column is filtered.
is_partition_key	No	Boolean	Whether the column is a partition column.

**Table 9** Data structure description of field **db_or_table_rename_rule**
Parameter	Mandatory	Type	Description
prefix_name	No	String	Prefix name. When type is set to prefixAndSuffix, this parameter is mandatory, and only a prefix is added to the database table name. If suffix_name is also specified, both a prefix and a suffix are added to the database table name.
suffix_name	No	String	Suffix name. When type is set to prefixAndSuffix, this parameter is mandatory, and only a suffix is added to the database table name. If prefix_name is also specified, both a prefix and a suffix are added to the database table name.
type	No	String	Database table mapping type. prefixAndSuffix: prefix, suffix, or prefix and suffix. Enumerated values: prefixAndSuffix manyToOne

**Table 10** Data structure description of field **db_object**
Parameter	Mandatory	Type	Description
object_scope	Yes	String	Migration or synchronization object scope. The value can be: all: Migrate or synchronize all objects. database: database-level migration or synchronization. table: table-level migration or synchronization. Enumerated values: all database table
target_root_db	No	Object	Destination database for database object migration or synchronization. This parameter is mandatory for database synchronization from Layer 2 to Layer 3. For details, see Table 11.
object_info	No	Map<String,DatabaseObject>	Object information for migration or synchronization. If object_scope is set to all, leave this parameter blank. If object_scope is set to database or table, this parameter is mandatory. For details, see Table 12.

**Table 11** Data structure description of field **target_root_db**
Parameter	Mandatory	Type	Description
db_name	No	String	Database name.
db_encoding	No	String	Encoding format. The default value is UTF-8.

**Table 12** Data structure description of field **object_info**
Parameter	Mandatory	Type	Description
sync_type	No	String	Type of the database in real-time synchronization. The value can be: config: This parameter is mandatory only when the database is used as an association database in advanced settings for data filtering. In this case, the database and its schemas and tables will not be synchronized to the destination database. The name and all parameters do not take effect. Enter the associated objects in schemas and tables. NOTE: To synchronize the database-level object, set sync_type to config for the lower-level object. Enumerated values: config
name	No	String	Name of the database in the destination database (database name mapping).
all	No	Boolean	Whether to migrate or synchronize the entire database. NOTE: If data filtering, column filtering, and column mapping are required for schemas, tables, and columns in the database, set this parameter to false. Otherwise, set this parameter to true. If additional columns are required for tables in the database, set this parameter to true and enter additional column information in columns of table-level objects. If the table columns in the database are used as association columns in advanced settings for data filtering, set this parameter to true, enter the association column information in columns, and enter the configuration criteria in advanced settings for data filtering in config_conditions.
schemas	No	Map<String,SchemaObject>	Schema to be migrated or synchronized. This parameter is mandatory when all is set to false. For details, see Table 13.
tables	No	Map<String,TableObject>	Table to be migrated or synchronized. This parameter is mandatory when all is set to false. For details, see Table 14.
total_table_num	No	Integer	Number of tables in the database. If the number of tables exceeds the threshold, this parameter is not displayed.
is_synchronized	No	Boolean	Whether data has been synchronized.

**Table 13** Data structure description of field **schemas**
Parameter	Mandatory	Type	Description
sync_type	No	String	Type of the schema in real-time synchronization. The value can be: config: This parameter is mandatory only when the schema is used as an association schema in advanced settings for data filtering. In this case, the schema and its tables will not be synchronized to the destination database. The name and all parameters do not take effect. Enter the associated objects in tables. NOTE: To synchronize the schema-level object, set sync_type to config for the lower-level object.
name	No	String	Name of the schema in the destination database (schema name mapping).
all	No	Boolean	Whether to migrate or synchronize the entire schema. NOTE: If data filtering, column filtering, and column mapping are required for tables and columns in the schema, set this parameter to false. Otherwise, set this parameter to true. If additional columns are required for tables in the schema, set this parameter to true and enter additional column information in columns of table-level objects. If the table columns in the schema are used as association columns in advanced settings for data filtering, set this parameter to true, enter the association column information in columns, and enter the configuration criteria in advanced settings for data filtering in config_conditions.
tables	No	Map<String,TableObject>	Table to be migrated or synchronized. This parameter is mandatory when all is set to false. For details, see Table 14.

**Table 14** Data structure description of field **tables**
Parameter	Mandatory	Type	Description
sync_type	No	String	Type of the table in real-time synchronization. The value can be: config: This parameter is mandatory only when the table is used as an association table in advanced settings for data filtering. In this case, the table and its columns will not be synchronized to the destination database. The name, all, filtered, and filter_conditions parameters do not take effect. Enter the associated objects in columns and the configuration criteria in advanced settings for data filtering in config_conditions. NOTE: To synchronize the schema-level object, set sync_type to config for the lower-level object.
type	No	String	Object type. The value can be: table: indicates a table. view: indicates a view. procedure: indicates a stored procedure. Enumerated values: table view procedure
name	No	String	Name of the table in the destination database (table name mapping).
all	No	Boolean	Whether to migrate or synchronize the entire table. NOTE: If column filtering and column mapping are not required for the table, set this parameter to true. Otherwise, set this parameter to false. If additional columns are required for the table, set this parameter to true and enter additional column information in columns. If the table columns are used as association columns in advanced settings for data filtering, set this parameter to true, enter the association column information in columns, and enter the configuration criteria in advanced settings for data filtering in config_conditions.
db_alias_name	No	String	Mapping of database names at the table level in the one-to-many scenario.
schema_alias_name	No	String	Mapping of schema names at the table level in the one-to-many scenario.
filtered	No	Boolean	Whether to filter data in the table.
filter_conditions	No	Array of strings	Filtering criteria for the table data. The processing rule value is a SQL statement. The value contains a maximum of 512 characters. Minimum length: 0 Maximum length: 512
config_conditions	No	Array of strings	Configuration criteria in advanced settings for data filtering of the table. This parameter is mandatory when the table is used as an association table for query. The processing rule value is a SQL statement. The value contains a maximum of 512 characters. Minimum length: 0 Maximum length: 512
is_synchronized	No	Boolean	Whether synchronization has been performed.
columns	No	Map<String,ColumnObject>	Columns to be synchronized, mapped, filtered, or added. This parameter is mandatory when column filtering, column mapping, and additional columns are required. This parameter takes effect only in real-time synchronization tasks. This parameter is mandatory when all is set to false. For details, see Table 15.

**Table 15** Data structure description of field **columns**
Parameter	Mandatory	Type	Description
sync_type	No	String	Type of the column in real-time synchronization. The value can be: config: This parameter is mandatory only when the column is used as an association column in advanced settings for data filtering. If the column is a primary key column or index column required for optimizing the query, primary_key_for_data_filtering or index_for_data_filtering is mandatory. NOTE: Whether to synchronize the column to the destination database depends on the filtered parameter, which is different from the database, schema, and table-level synchronization.
primary_key_for_data_filtering	No	String	Whether the column is a primary key column in advanced settings for data filtering. If the column is a primary key column, set this parameter to the column name. Otherwise, leave this parameter blank.
index_for_data_filtering	No	String	Whether the column is an index column required for optimizing the query. The index is added to the cached data. It does not affect the source table. If the column is an index column in advanced settings for data filtering, this parameter is mandatory. Otherwise, leave this parameter blank.
name	No	String	Name of the column in the destination database (column name mapping). If the column is an additional column, the value must be the same as the column name in the table-level object.
type	No	String	Data type of the column. Column filtering: Enter the data type of the source column. Additional column adding: Enter the data type of the new column. The values and constraints vary depending on the operation type. The value can be: Default: The value can be int, long, varchar(256), datetime, or timestamp. create_time: The value can be long, datetime, or timestamp. update_time: The value can be long, datetime, or timestamp. Expression: The value can be varchar (256), and the column value must be concat(__current_database, '@', __current_table). serverName@database@table: The value can be varchar(256).
primary_key_for_column_filtering	No	String	Whether the column is a primary key column in column mapping. If the column is a primary key column, set this parameter to PRI. Otherwise, leave this parameter blank.
filtered	No	Boolean	Whether the column is filtered out. This parameter cannot be used together with the additional parameter. The value can be: true: indicates that the column will be synchronized. false: indicates that the column is filtered out and will not be synchronized.
additional	No	Boolean	Whether the column is an additional column. If the column is an additional column, the value of name must be the same as the column name in the table-level object and cannot be used together with the filtered parameter.
operation_type	No	String	Operation type. The new column is filled with a specific operation type. The value can be: Default: "operation_type":"ADDITIONALCOLUMN,default_value" create_time: "operation_type":"ADDITIONALCOLUMN,create_time" update_time: "operation_type":"ADDITIONALCOLUMN,update_time" Expression: "operation_type":"ADDITIONALCOLUMN,expression" serverName@database@table: "operation_type":"ADDITIONALCOLUMN,server_database_table"
value	No	String	Value of the additional column. Constraints: This parameter is mandatory only when operation type is set to Default or serverName@database@table. When operation type is set to Expression, this parameter has a fixed value of concat(__current_database, '@', __current_table).

Response Parameters

Status code: 202

**Table 16** Response body parameters
Parameter	Type	Description
id	String	Query result ID.
job_id	String	Task ID.
status	String	Query status. Enumerated values: pending failed success

Status code: 400

**Table 17** Response body parameters
Parameter	Type	Description
error_code	String	Error code. Minimum length: 12 Maximum length: 12
error_msg	String	Error message. Minimum length: 1 Maximum length: 512

Example Request

Verifying data filtering rules

POST https://{endpoint}/v5/054ba152d480d55b2f5dc0069e7ddef0/job/c7debc9c-8e09-4a5d-8dd6-cc44f78jb20r/data-filtering/check

{
  "data_process_info" : [ {
    "filter_conditions" : [ {
      "filtering_type" : "contentConditionalFilter",
      "value" : "id>1"
    } ],
    "db_object" : {
      "object_info" : {
        "dyh4" : {
          "name" : "dyh4",
          "all" : false,
          "tables" : {
            "test1_table1" : {
              "name" : "test1_table1",
              "type" : "table",
              "all" : true
            },
            "test1_table10" : {
              "name" : "test1_table10",
              "type" : "table",
              "all" : true
            },
            "test1_table11" : {
              "name" : "test1_table11",
              "type" : "table",
              "all" : true
            }
          }
        }
      },
      "object_scope" : "table"
    }
  } ]
}

Example Response

Status code: 202

{
  "id" : "0eacda0b-9422-4535-8b62-537d1833638b",
  "job_id": "177738d1-4eee-4fcb-a4e1-09aea79jb20i",
  "status" : "pending"
}

Status code: 400

Bad Request

{
  "error_code" : "DRS.10000010",
  "error_msg" : "Job does not exist, please check job ID."
}

Status Code

Status Code	Description
202	Accept
400	Bad Request

Error Code

For details, see Error Code.

Parent topic: Data Processing

Previous topic: Querying Data Processing Rules

Next topic: Obtaining the Data Filtering Verification Result

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot