Filtering Data Using Query Predicates

Learn how to specify query predicates to export only the table rows that match the filter criteria.

Query Predicate

NoSQL Database Migrator provides an option to filter out data during export by specifying a query predicate. The query predicate specifies conditions that must be met for a row to be exported. The Migrator utility translates the query predicate into a SQL WHERE clause and applies it on the given table to provide a filter condition to only export the rows matching the specified condition. You can use built-in functions (modification_time(), expiration_time(), creation_time()) in the query predicate to create advanced filter options.

You can use query predicates only on Oracle NoSQL Database and Oracle NoSQL Database Cloud Service sources for all the supported sinks. For further details, see Oracle NoSQL Database and Oracle NoSQL Database Cloud Service.

For a use case demonstration, see Migrate from Oracle NoSQL Database to a JSON file.

Dump filter

The Migrator utility provides an option to echo the SQL query that is executed on the backend. This feature helps you verify the generated query and if required, refine your filter before running the migration task.

You can run the Migrator utility with the dump filter option as follows:

[~/nosqlMigrator]$./runMigrator --dump-filter|df [optional-config-file]

With the configuration file: The Migrator utility displays the supplied configuration file and the generated query as shown in the following example:

[~/nosqlMigrator]./runMigrator --dump-filter migrator-config.json

[INFO] Configuration for migration:
{
  "source" : {
    "type" : "nosqldb",
    "storeName" : "kvstore",
    "helperHosts" : ["<hostname>:5000"],
    "table" : "users",
    "queryFilter" : "$row.address.city='Houston'",
    "includeTTL" : true,
    "requestTimeoutMs" : 5000
  },
  "sink" : {
    "type" : "file",
    "format" : "json",
    "useMultiFiles" : false,
    "schemaPath" : "<complete/path/to/the/JSON/file/with/DDL/commands/for/the/schema/definition>",
    "pretty" : true,
    "dataPath" : "<complete/path/to/directory>"
  },
  "abortOnError" : true,
  "migratorVersion" : "1.8.0"
}
[INFO] Query for the migration: 'select $row, expiration_time($row) from users $row where $row.address.city='Houston''

Without the configuration file: The Migrator utility interactively collects all the inputs required to generate the configuration file including the query predicate. It then displays the generated configuration file and query.

Note:

The dump filter option only displays the configuration file and query. It does not initiate the data migration. After review, to execute the migration, run the Migrator utility with your configuration file using --c or --config option as follows:

$./runMigrator --config <complete/path/to/the/JSON/config/file>