11.4.15. droprow.js Filter

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"