Table Monitors

Overview

A table monitor (SQL Stream) is an entity that can be created to watch a target table for inserts, updates, or deletes, and relay notifications for consumption by interested clients to one of the following targets:

  • a local ZMQ message queue (default)
  • an external Apache Kafka broker
  • an external webhook

A table monitor is bound to a single type of table operation to monitor, assigned when created, and cannot be changed to monitor other types afterwards. For instance, a table monitor created to watch for inserts will not monitor update or deletes or be able to be modified to watch for one of those two instead, or in addition, after creation.

Table monitors provide the following notifications, based on table activity monitored.

Table Activity Table Monitor Notification
inserts The entirety of each record inserted into the target table will be forwarded to the target.
updates The number of records updated for each update operation executed against the target table will be forwarded to the target.
deletes The number of records deleted for each delete operation executed against the target table will be forwarded to the target.

A table monitor name (if named by the user) must adhere to the standard naming criteria. Each table monitor exists within a schema and follows the standard name resolution rules for tables.

Configuration

The configuration of the table monitor processing is dependent on whether the table monitor is assigned a local ZMQ or an external consumer target.

Local ZMQ

Table monitor notifications, by default, are published to a local ZMQ, which listens for subscriber connections on the system table monitor port (default 9002), configured as set_monitor_port under Network Configuration in the gpudb.conf file.

When a table is the target of both a table monitor and a multi-head ingest, notifications are pushed from the worker nodes to the head node through the head node's table monitor proxy port (default 9003), configured as set_monitor_proxy_port under Network Configuration in the gpudb.conf file.

Notifications of inserts pulled from the queue are multi-part messages, with the first message being the topic ID and the remainder being binary-encoded Avro objects, one per record inserted. These must be decoded using the type schema returned by the table monitor creation call.

External Consumer

A table monitor can be assigned an external consumer as a target, either defined directly in its creation call (unauthenticated connections only), or via a pre-defined data sink. If defined directly, a data sink will automatically be created to support the table monitor, and return its name in the response to the creation call.

This type of table monitor will publish notifications to its target in JSON format.

Managing Table Monitors

A table monitor can be managed using the following API endpoint calls. For managing table monitors in SQL, see CREATE STREAM.

API Call Description
/create/tablemonitor Creates a table monitor, given the name of the table to monitor and any optional configuration information
/clear/tablemonitor Removes a table monitor, given a topic ID
/show/tablemonitors Outputs the properties of one or more table monitors, given a set of topic IDs; or outputs the properties of all table monitors
/grant/permission Grants the permission for a user to remove a table monitor
/has/permission Checks for the permission for a user to remove a table monitor
/revoke/permission Revokes the permission for a user to remove a table monitor

When a table monitor is created with the /create/tablemonitor endpoint, several values are returned:

  • topic ID - used to subscribe to the table monitor's notifications and to identify or remove the table monitor
  • type schema - used to decode records within insert notifications
  • info - a hierarchy of key/value pairs, which will include the randomly-generated name of the backing data sink, if one was automatically created to support the table monitor

During the table monitor's lifetime, it cannot be modified.

The existence of a table monitor on a table can be verified by calling the /show/table endpoint with the corresponding table name, and parsing the response for the list of attached table monitors found under the additional_info > table_monitor keys. Each table monitor's monitored event type (insert, update, or delete) and topic ID are available.

A table monitor will run continuously until it is manually removed.

Table monitors will be temporarily unavailable during a database restart, but will become active once the database begins servicing requests again.

Limitations & Cautions

Example

For a tutorial on using table monitors with an associated complete example, see Table Monitor Guide.