Sign InTry Free

Changefeed Log Filters

TiCDC supports filtering data by tables and events. This document introduces how to use the two types of filters.

Table filter

Table filter is a feature that allows you to keep or filter out specific databases and tables by specifying the following configurations:

[filter] # Filter rules rules = ['*.*', '!test.*']

Common filter rules:

  • rules = ['*.*']
    • Replicate all tables (not including system tables)
  • rules = ['test1.*']
    • Replicate all tables in the test1 database
  • rules = ['*.*', '!scm1.tbl2']
    • Replicate all tables except for the scm1.tbl2 table
  • rules = ['scm1.tbl2', 'scm1.tbl3']
    • Only replicate tables scm1.tbl2 and scm1.tbl3
  • rules = ['scm1.tidb_*']
    • Replicate all tables in the scm1 database whose names start with tidb_

For more information, see Table filter syntax.

Event filter rules

Starting in v6.2.0, TiCDC supports event filter. You can configure event filter rules to filter out the DML and DDL events that meet the specified conditions.

The following is an example of event filter rules:

[filter] # The event filter rules must be under the `[filter]` configuration. You can configure multiple event filters at the same time. [[filter.event-filters]] matcher = ["test.worker"] # matcher is an allow list, which means this rule only applies to the worker table in the test database. ignore-event = ["insert"] # Ignore insert events. ignore-sql = ["^drop", "add column"] # Ignore DDLs that start with "drop" or contain "add column". ignore-delete-value-expr = "name = 'john'" # Ignore delete DMLs that contain the condition "name = 'john'". ignore-insert-value-expr = "id >= 100" # Ignore insert DMLs that contain the condition "id >= 100". ignore-update-old-value-expr = "age < 18 or name = 'lili'" # Ignore update DMLs whose old value contains "age < 18" or "name = 'lili'". ignore-update-new-value-expr = "gender = 'male' and age > 18" # Ignore update DMLs whose new value contains "gender = 'male'" and "age > 18".

Description of configuration parameters:

  • matcher: the database and table that this event filter rule applies to. The syntax is the same as table filter.
  • ignore-event: the event type to be ignored. This parameter accepts an array of strings. You can configure multiple event types. Currently, the following event types are supported:
EventTypeAliasDescription
all dmlMatches all DML events
all ddlMatches all DDL events
insertDMLMatches insert DML event
updateDMLMatches update DML event
deleteDMLMatches delete DML event
create schemaDDLcreate databaseMatches create database event
drop schemaDDLdrop databaseMatches drop database event
create tableDDLMatches create table event
drop tableDDLMatches drop table event
rename tableDDLMatches rename table event
truncate tableDDLMatches truncate table event
alter tableDDLMatches alter table event, including all clauses of alter table, create index and drop index
add table partitionDDLMatches add table partition event
drop table partitionDDLMatches drop table partition event
truncate table partitionDDLMatches truncate table partition event
create viewDDLMatches create viewevent
drop viewDDLMatches drop view event
  • ignore-sql: the DDL statements to be ignored. This parameter accepts an array of strings, in which you can configure multiple regular expressions. This rule only applies to DDL events.
  • ignore-delete-value-expr: this parameter accepts a SQL expression. This rule only applies to delete DML events with the specified value.
  • ignore-insert-value-expr: this parameter accepts a SQL expression. This rule only applies to insert DML events with the specified value.
  • ignore-update-old-value-expr: this parameter accepts a SQL expression. This rule only applies to update DML events whose old value contains the specified value.
  • ignore-update-new-value-expr: this parameter accepts a SQL expression. This rule only applies to update DML events whose new value contains the specified value.
Download PDF
Playground
One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
Products
TiDB
TiDB Dedicated
TiDB Serverless
Pricing
Get Demo
Get Started
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.