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.jsonAdditional 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=matchallTo 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"