Filters

By default, all data available on the specified source is replicated to MemSQL. A filter file can be used to replicate only a specified subset of source data.

Use --filter <filter_file> to specify the desired filter file. If this parameter is not specified, all source data that the user can access at the source is replicated to the destination.

Example:

./bin/replicate snapshot conf/conn/oracle.yaml conf/conn/memsql.yaml --filter source_filter.yaml

The filter file contains a list of allow rules where, depending on the database type, each rule is used for filtering a single catalog or schema. When used, only the listed catalogs/schemas are replicated.

Typically, for databases that support both catalog and schema, both must be specified for each schema. If the catalog is already specified in the connection configuration file, then only the schema must be specified in the filter file.

Each rule specifies which object types to replicate. Currently, only TABLE objects can be replicated, while VIEW is currently not supported.

Each rule can either allow or block certain tables. Allowed tables must be specified as a map, whereas blocked tables must be specified as a list. If no tables have been explicitly allowed or blocked as a part of a specific rule, all tables in the corresponding catalog/schema are implicitly allowed.

Use the unlisted key to define behavior for tables that are neither explicitly allowed nor blocked. It can have two values: BLOCK or ALLOW. By default, this value is set to BLOCK, and can be changed to ALLOW to allow blacklist filtering.

For any allowed table, it’s possible to either allow or block specific columns. However, it’s not possible to simultaneously allow some columns while blocking others as it’s unclear how to handle columns that were neither explicitly allowed nor blocked.

File Format

# List of rules for each catalog/schema
allow:
# catalog or schema keys can be omitted if not supported by the database
- catalog: <catalog_name>
  schema: <schema_name>
  # Object types that are eligible for replication
  types: [TABLE]
  # Tables that are eligible for replication (map)
  allow:
    <table_name>:
      # Columns that are eligible for replication
      allow: [<column_name>, <column_name>, <column_name>]
    <table_name>:
      # Columns that are NOT be eligible for replication
      block: [<column_name>, <column_name>]
  # Tables that are NOT be eligible for replication (list)
  block: [<table_name>, <table_name>]
  # Rule for all tables not explicitly allowed or blocked
  unlisted: BLOCK
# Filtering rule for another schema
- catalog: <catalog_name>
  schema: <schema_name>
  types: [TABLE]
  allow:
    <table_name>:
      allow: [<column_name>, <column_name>, <column_name>]
    <table_name>:
      block: [<column_name>, <column_name>]
  block: [<table_name>, <table_name>]
  unlisted: BLOCK

Examples

General

allow:
- catalog: "io"
  schema: "test-org"
  types: [TABLE]
  allow:
    "users":
      allow: ["first_name", "last_name"]
    "roles":
      block: ["admin"]
  block: ["offices"]
  unlisted: ALLOW

In the above example:

  • Only the io.test-org schema is considered and only TABLE objects are replicated.
  • From the users table, only the first_name and last_name columns are replicated. Concurrently, all columns from the roles table are replicated, with the exception of the admin column.
  • The offices table is explicitly blocked and is excluded by the filter. Since unlisted is set to ALLOW, all tables that are neither explicitly allowed nor blocked are replicated.

Oracle

allow:
- schema: "test-org"
  types: [TABLE]
  allow:
	"users":
  	  allow: ["first_name", "last_name"]
	"roles":
  	  block: ["admin"]
  block: ["offices"]
  unlisted: ALLOW

Note: For Oracle, the schema key is used, while the catalog key is omitted.

MemSQL

allow:
- catalog: "test-org"
  types: [TABLE]
  allow:
	"users":
  	  allow: ["first_name", "last_name"]
	"roles":
  	  block: ["admin"]
  block: ["offices"]
  unlisted: ALLOW

Note: For MemSQL, the catalog key is used, while the schema is omitted

Was this article useful?