The droprow
filter can be used to selectively
filter out transactions based on matching column values at the ROW level.
Pre-configured filter name |
droprow
| ||
JavaScript Filter File |
tungsten-replicator/support/filters-javascript/droprow.js
| ||
Property prefix |
replicator.filter.droprow
| ||
Stage compatibility |
binlog-to-q ,
q-to-dbms
| ||
tpm Option compatibility |
--svc-extractor-filters ,
--svc-applier-filters
| ||
Data compatibility | Row Events | ||
Parameters | |||
Parameter | Type | Default | Description |
definitionsFile
| Filename |
~/droprow.json
| Location of the definitions file for row filtering. |
matchCase
| Boolean |
true
|
Controls whether text based comparisons are Case Sensitive
(true ) or not (false )
|
rule
| String |
matchany
|
Valid Values : matchany | matchall . If more than one column/value
pair are defined, this property will determine whether there must be a match for
all columns (matchall ) or only one (matchany ).
|
The filter is available by default as
droprow
, and the filter is
configured through a JSON file that defines the list of column/values
to match in a row to be dropped.
This filter has the following requirements and caveats:
The filter relies on the colnames filter being enabled.
The filter will only work on ROW events, therefore the source database needs to be running in Row-Based binary logging mode.
The filter will only compare values as part of an INSERT satement and NEW values as part of an UPDATE statement.
Rows that are affected by UPDATE and DELETE statements where the values are part of the WHERE clause will not be removed from THL.
If the filter is applied to tables with Foreign Keys, be sure to inclue all tables in the hierarchy as part of the filter to avoid referential integrity errors.
To enable the filter, for staging based deployments:
shell> tpm update --svc-extractor-filters=colnames,droprow \
--property=replicator.filter.droprow.definitionsFile=/opt/continuent/share/droprow.json
Additional parameters that override the defaults can be also supplied, for example:
shell> tpm update --svc-extractor-filters=colnames,droprow \
--property=replicator.filter.droprow.definitionsFile=/opt/continuent/share/droprow.json \
--property=replicator.filter.droprow.rule=matchall
To enable the filter, for ini based deployments:
shell>vi /etc/tungsten/tungsten.ini
[servicename
] ... svc-extractor-filters=colnames,droprow property=replicator.filter.droprow.definitionsFile=/opt/continuent/share/droprow.json ... shell>tpm update
Additional parameters that override the defaults can be also supplied, for example:
shell>vi /etc/tungsten/tungsten.ini
[servicename
] ... svc-extractor-filters=colnames,droprow property=replicator.filter.droprow.definitionsFile=/opt/continuent/share/droprow.json property=replicator.filter.droprow.rule=matchall ... shell>tpm update
A sample configuration file is provided in
tungsten-replicator/support/filters-config/droprow.json
.
The format of the file is a JSON array of schema/table/column/value
specifications. The match rule can also be supplied on a per table basis
as per the examples below. If not supplied the global default for the
filter, as described above, will be used:
[ { "schema": "vip", "table": "clients", "rule": "matchall", "columns": [ { "column": "city", "value": "London" }, { "column": "country", "value": "UK" } ] } ]
Where:
schema
specifies the name of the
schema on which to apply the filtering.
table
specifies the name of the
table on which to apply the filtering.
rule
specifies the matching rule
to apply the filtering. Setting the value in the JSON will override the global
default specified in the main configuration (See above).
matchany
(default) will remove rows if only
one condition matches. Equivalent to an OR condition.
matchall
will remove rows if only
all conditions match. Equivalent to an AND condition.
columns
is an array of column/values
to be matched.
column
is the name of the column
in the table to match.
value
is the Value of the column to
match. When Rule is matchany
this can also be supplied as
an array of Values (See second example below).
For example:
[ { "schema": "vip", "table": "customers", "rule": "matchany", "columns": [ { "column": "country", "value": [ "Australia", "UK" ] } ] } ]
The bove example filters rows from the customers table, in the vip schema where the country column is either "Australia" OR "UK"