Using Delta Streams for Query Tables

Introduction

StreamBase supports table delta streams, which are special-purpose input streams that track changes to Query Tables. If you create a table delta stream for a Query Table, then every insert, update, or delete operation on the data in the table causes the delta stream to emit tuples that represent the changes. Table delta streams support both on-disk and in-memory Query Tables.

You can create table delta streams for any Query Table. A table delta stream is named with _SB_delta_ prefixed to the name of the table that the delta stream is tracking.

Schemas for the Delta Stream

A table delta stream has four fields representing the type of operation performed, a count of modifications, and two tuple fields, old and new to contain a row of data before and after a change event.

When you create a delta stream for a table, the schema for the delta stream's old and new fields is automatically made the same as the schema of the table itself. If you later change the schema of the table, you must explicitly update the fields in the table delta stream to match.

You can instead use a named schema to define both the Query Table's schema and the schema of the old and new fields. In this case, when you modify the named schema, the table and the table delta stream that use it are both updated automatically.

Use Cases

Query Table delta streams are useful in any design pattern that requires change notification of Query Table change events.

Change Notification Design Patterns

You can use delta streams in a trading application where there is a requirement to monitor changes to the consolidated order books.

Replication Design Patterns

Another design pattern is to use one delta stream to track changes and handle updates, while a second delta stream for the same table is accessible for queries. In this pattern, queries can be processed against the data in the second stream without locking the data in the other stream.

In a trading application, for example, a delta stream could track an order book, handling updates and maintaining the state of the book, while a second delta stream for the same table is accessible to trading algorithms that require access to the data.

Using Table Delta Streams in EventFlow Applications

To add a delta stream to a Query Table in an EventFlow application:

  1. Right-click the Query Table component to open the context menu.

  2. Click Add Delta Stream for table "table_name"

    For example, see the following EventFlow fragment that shows adding a delta stream to Bids_and_Asks:

  3. To ensure that the stream is always available for enqueuing regardless of module depth, click the General tab in the Properties view, and select Always expose Stream for Enqueue, as shown:

    The delta stream, _SB_delta_Bids_and_Asks is added near the Bids_and_Asks table, as shown:

Explicit Updates

If you change the schema of the associated Query Table, you can manually update the delta stream's schema to match:

  1. Right-click the Query Table's icon (not the delta stream's icon).

  2. Click Update Delta Stream "table_name" Schema.

Avoiding Runtime Errors

You cannot access a Query Table downstream from that same Query Table's delta stream; if your application attempts to do so, StreamBase generates a runtime error.

This error situation is shown in the following fragment, where the Trades table has a delta stream _SB_delta_Trades, but the ReadQuery operator attempts to read from the Trades table after the delta stream:

In this case, the error displayed in the Console is as follows:

Runtime exception: Accessing table default.Trades downstream of table delta stream is 
forbidden 

Schema for a Table Delta Stream

The schema for a delta stream is shown in the following table:

Field Type Values
operation string One of insert, update, or delete.
modifyCount long An incrementing number, which keeps track of modifications to a table. Each modification attempt causes the number to increment.

A failed modification (such as an update that results in no row changes) still increments the modification count. Thus, the modifyCount number always increases, but is not guaranteed to be in numeric order from tuple to tuple on the delta stream.

old tuple A tuple with the same schema as the table being tracked, containing the data for a table row at the beginning of a change event. That is, for a delete event, it contains the row before deletion; for an update, it contains the row before the update.

In the case of table truncation, the value of this tuple field is NULL. One and only one delta tuple is produced for a truncation event.

new tuple A tuple with the same schema as the table being tracked, containing the data for a table row at the end of a change event. That is, for an update, it contains the row after the update. For an insert, it contains the inserted row.

Tip

Use a named schema to specify the schema for the Query Table, and the delta stream's old and new fields.

Operations

The following table lists the kinds of changes tracked by a table delta stream and shows how StreamBase handles them as events in the schema for the delta stream.

Change in the query table Change in the table delta stream
Adding a row that is not involved with any existing rows

An insert event. Inserts a row with the new contents.

Change to an existing row

An update event. The old field shows the row before the update and the new field, after.

Update to a row that does not exist An insert event.
An update that results in no change to the table. No delta stream event is emitted (but the modifyCount field is incremented).
Single and multi-row deletes.

A delete event for each deleted row. A delete operation for a row generates a delta stream tuple that has delete in the operation field, and the deleted row contents in its old field.

Truncations (a delete operation with All Rows selected) A single delete event. A truncation of the entire table generates a delta stream tuple that has delete in the operation field, and null in both old and new fields.

The following table summarizes the values seen in the old and new tuple fields:

Operation old tuple field new tuple field
Insert null inserted row
Update row before the update row after the update
Delete deleted row null
Delete All Rows (truncate) null null