Query filter - Vertica

A Query filter is invoked for requests to execute a SQL query or command.

This always includes requests of type Query, and can also include requests of type Parse for prepared statements.

Parameters

None of the parameters are required.

The parameters that can take multiple values can be separated by commas or by newlines.

Query patterns

A list of strings or regular expressions that should match the SQL request. These can be comma-separated, or separated by new lines.

Include prepared statements

If this option is selected, then requests of type Parse (for prepared statements) will be included.

Note that, unlike Query requests, Parse requests do not result in the immediate execution of the SQL: that is done only after possible Bind requests (to provide values for the prepared statement's parameters), and an Execute request.

Client IPs

A list of IP addresses (IP4 and/or IP6) and/or regular expressions for IP addresses.

Example:

12.34.56.78
1234:5678:90ab::01
regex:98\.76\..*
regex:9876:5432:.*

Users

A list of user names and/or regular expressions for user names. If specified, only queries executed by these users will cause execution of the filter.

Example: rewrite a query

A common use of this filter is to tweak one or more specific queries to affect how a Vertica client behaves, without having to change the client's code.

In this example, the client sends a query that turns out to be inefficient, so we catch the request before it gets to the server and modify the query, but only for certain users.

Query patterns: select * from acct.Orders where total > ? and status = ?

Include prepared statements: checked

Users: regex: es_.*

The code might look like:

log.info("Tweaking query");
context.packet.query = "select * from acct.Orders where total > ? and status = ? and country='ES' limit 100";