What are the most frequently used ways to trim rows from a LiveView table

Issue

What are the most frequently used ways to trim rows from a Live Datamart table?

Solution

This issue is often asked about in other ways, for example:

• How do I maintain the contents of a Live Datamart table?
• Does Live Datamart support TTL?
• How can I trim a Live Datamart table?
• Can I set a finite window size for a LiveView table?

Note: This article is a condensation of the article What Are All Possible Ways to Delete Rows from a LiveView table? which you might also find interesting, but definitely read this one first.

All of these questions lead eventually to the same result: if you want a row in a Live Datamart table to no longer be in that table, the row must be deleted from the table somehow.

There are actually a lot of ways to delete rows from LiveView tables. Only a few of them are frequently used to maintain tables, though, and those few are discussed briefly here.

To accomplish this, Live Datamart provides a number of ways to delete rows from a data table. These various deletion methods can be used in a number of different application and administration contexts. Which deletion methods you choose to use as an application designer depends on a number of application- and context-specific considerations. However, you are most likely going to have to choose, as the default behavior for a Live Datamart table, to insert a new row in the table for each unique value of the table's primary key.  If that primary key is, for example, a unique ID that keeps incrementing, then the table can grow to be very large and exhaust available resources. So some kind of explicit table size maintenance is almost always required for every Live Datamart application.

Frequently, as of Live Datamart 2.0.3 (the current release as of this writing), if you want a table to automatically trim itself, you are probably going to end up using an Alert Rule with a Delete Rows action or to schedule a periodic DELETE query to run using some scheduling mechanism that's not formally part of the Live Datamart product feature set itself, such as cron or a StreamBase application.

[Editor's Note: Live Datamart 2.1.3 now has a built-in cron mechanism that you can use to issue periodic DELETE queries.]

Rows Can Be Deleted Using Either the Query Path or the Publish Path

Row deletion methods in Live Datamart break down into two categories: via the publish path, or via the query path.

By publish path, we mean deleting based on sending a tuple to the table's DataIn stream that causes a single row deletion in line with insert and update publications to the same table. The affected table row on the publish path is always identified by primary key.

By query path, we mean issuing a DELETE query against the table that causes one or more rows to be deleted, potentially asynchronously to publish path actions. The affected rows are specified by evaluating the WHERE clause of the query -- an arbitrary predicate. For categorization purposes, the Alert Rule Delete Rows action uses the query path mechanism.

Setup

The bulk of the rest of this article presents quick tutorial survey of the most frequently used row deletion methods available to LiveView applications.

The examples and samples all refer to the Hello LiveView application sample that ships as part of the Live Datamart product, and assume that the Hello LiveView application running in order to operate.

In order to follow along, start up a Hello LiveView server and launch the LiveView Desktop client to connect to it, so that you can see the results of the deletion methods described below.

Query Path Row Deletion

Alert Rule Delete Rows Action

A powerful and flexible way to automatically trim a LiveView table is to define an Alert Rule that has a Delete Rows action. This is a preferred way of continuously controlling table size based on flexible criteria.

An Alert Rule establishes a continuous query against a table that is evaluated every time an event is published to the table. The Alert Rule's actions fire when the query conditions are satisfied. The table against which the Delete Row action operates may be a different table than the table that the Alert Rule's condition monitors.

Note: When using an Alert Rule action for deleting rows, consider safe-guarding it from firing too often. For example, in LiveView Desktop, the alert suppression option looks like this:

We recommend for delete actions to suppress subsequent execution for at least a minute if not 10 minutes or more, rather than the 10 seconds shown in the above example, since scanning a very large table could take a while and trimming the table doesn't typically have to occur all that often.

Example 1

You can set an alert rule on the LiveViewStatistics table with a condition predicate such as:

 (TableName == 'ItemSales') && RecordCount > 20000

You can configure a Delete Rows action for this rule for table ItemSales with a query predicate:

 (transactionTime < now()-hours(24))

This alert rule deletes all ItemsSales table rows that are more than a day old whenever there are more than 20000 rows in the ItemSales table.

Example 2

Suppose a Live Datamart table has a timestamp column that gets something like now() published to it regularly. For the sample_helloliveview application, that would be the ItemsSales table:

 when transactionTime between today() and today()+hours(1)

So the delete would occur the first time transactionTime was published with a time between midnight and 1 AM. You also need to set the "suppress subsequent execution for" to something like 4000 to ensure the delete doesn't happen more than once in that hour window.

Example 3

Another common Delete Rows action pattern is something like when transactionTime between now()-hours(2) and now() and then the delete predicate is something like transactionTime < now()+hours(1). Every two hours this will delete all rows with a transactionTime older than 1 hour. Again, use an "After executing actions, suppress subsequent execution for __ seconds" of some number of seconds, such as 30 or 60 seconds. This is to ensure the delete starts and completes before more rows hit the time window and issue more deletes.

Example 4

The Hello LiveView sample application contains a Delete Rows Alert Rule called Trim ItemsSales Table that deletes all ItemsSales table rows that are more than 4 minutes old, every 5 minutes. The run period is slightly longer than the trim period to give the delete rows action time to run on a very large table before the next alert would fire.

While most alert actions are enabled by default, prior to Live Datamart 1.6.0, you do need to enable the delete action by setting: 

 liveview.alert.action.delete.enabled=true

in your LiveView project's sbd.sbconf.

The delete alert action is enabled by default as of Live Datamart 1.6.0.

Alert Rule Management Interfaces

There are several interfaces for creating and managing Alert Rules. While the details of these interfaces are beyond the scope of this document, briefly they are:

• lv-client addalertrule
• LiveView Web UI Alert Manager
• LiveView Desktop Manage Alerts . . .
• LiveView Java Client API (LiveViewConnection.getAlertManager())
• LiveView JavaScript Client API (LiveView.Connection.addAlertRule())

Deleting rows using a DELETE Query

LiveQL supports a DELETE query type that deletes all the rows in the specified table that matches the query's WHERE predicate. The DELETE query type is SNAPSHOT only; there are no CONTINUOUS variants of the DELETE query type.

A LiveView client can issue a DELETE query using the command line, the LiveView Delete Adapter, and the LiveView Java and .NET Client APIs.

lv-client delete

For ad-hoc manual deletes, you can use the lv-client delete command, as documented in the LiveView Help under LiveView Admin Guide > StreamBase LiveView Command Reference > lv-client

The general syntax is:

 lv-client -u sb://lvserver:port "delete from tablename where predicate"

(The "from" is optional as of Live DataMart 2.0.3 and is not accepted in prior releases.)

For example, to delete a row from the Hello LiveView sample that ships with LiveView, you would use:

 lv-client -u sb://localhost:10000 "delete from ItemSales where Item='Wagon'"

LiveView Delete Output Adapter for StreamBase

The Spotfire LiveView Delete adapter allows a StreamBase application to delete one or more rows in a LiveView table based on a specified predicate.

The Spotfire LiveView Query, Delete, Publish, and Ready Adapters sample (sample_adapter_embedded_lv-sbd) contains the file lv2sbd.sbapp has a small sample of using the LiveView Delete adapter.

The LiveView Output Adapters for StreamBase all use the LiveView Client API for Java to perform their functions. This means they can be run in a StreamBase process that is separate from the StreamBase instance that is inside the LiveView Server process, if desired.

Example: Using the LiveView Delete Adapter to trim the ItemsSales table once a day at midnight:

This application uses a Metronome operator that outputs a tuple once a second. The Filter will let one of those tuples through a day, at midnight local time. The Map sets which table to delete rows from, as well as the delete query predicate, which will delete all rows whose transactionTime is more than one day old.

DeleteOnceADay.sbapp contains the code for this example. The sample_adapter_embedded_lv-delete.zip file attached to this page contains a StreamBase Studio project that has this application in it, as well, and may be imported into StreamBase Studio using File > Import > General > Existing Projects into Workspace > Archive File. You can change the MetronomeTuple output interval and the Filter predicate to cause the delete to run more often just to watch it work without having to wait up half the night.

Publish Path Row Deletion

To be very clear: publish path row deletes are PK-based deletes.  These have a place in some situations; however, for common table-size and time-based table maintenance activities, the query path option is required. Given the desire to delete a window of time, you must pose a query as you do not have the PKs of all rows that fall within the time window.  One could view the options (query path vs. publish path) as table maintenance vs. record maintenance, if you will.

Use a row-delete-rule row-delete-rule element in the data table lvconf

A row-delete-rule is an optional rule specifying a predicate that is evaluated against the data in any incoming insert or update. If the incoming data satisfies the predicate and a row with a matching primary key exists, then no update occurs and the row is deleted. If the incoming data satisfies the predicate and no row with a matching primary key exists, then no insert occurs. If the incoming data does not satisfy the predicate, then an insert or update occurs as normal.  Since a row-delete-rule's predicate can only refer to values from the incoming event tuple's field value, it may be helpful to think of it more as an input pre-processing validation, or as a way to do deletions based on "commands" transmitted within the input events itself.

Examples

row-delete-rule row-delete-rule transactionTime < now()-days(1)

row-delete-rule row-delete-rule myEventType=='DELETE'

Use a LiveView Publish Output Adapter

The LiveView Publish Output Adapter may be used to delete rows from a data table. The Delete Field Name adapter property is an optional input boolean field that if true will delete the row corresponding to the value of the primary key fields in the input tuple.

Example using Hello LiveView Sample application (pubdelete.sbapp)

The pubdelete.sbapp also exists in the sample_adapter_embedded_lv-delete.zip file attached to this page that you may have already imported into StreamBase Studio above.

Design Notes

• Since the DELETE query is a snapshot-oriented function, a delete query must be invoked periodically in order to be used as a means for continuous table maintenance. Periodic invocation can be arranged in various ways, for example, a LiveView Query Adapter could be driven by Metronome operator, or an lv-client delete could be invoked from a cron job. Creating these invocation schemes are a matter left to the LiveView application designer. For a more built-in style of continuous table maintenance using deletes, consider using an Alert Rule with a Delete Rows action or a row-delete-rule.

• Alert rule-based deletes are created from a LiveView client application. There are at least two options for deploying Alert Rules. The first is to run a script or a client application in the deployment environment to create the Alert Rules after the LiveVew application itself has been deployed and the server has been started in the deployment environment. The second method involves creating the Alert Rules in the development environment and packaging the contents on the system data store with the application itself, and deploying this package. This will effectively pre-create the rules in the server. The precise mechanisms of these deployment styles are beyond the scope of this article.

• As a a matter of LiveView application design practice, consider anything, including deletes, that happens on the query path to be asynchronous to anything that happens on the publish path. For consistency, it might be better for many applications to only do deletions on the publish path. However, this choice loses the flexibility of doing predicate-based deletions.

• A Delete that occurs via the publish path affects only one row, and must specify a primary key value. To delete more than one row in a single action or to delete based on an arbitrary predicate, you must use the query path.

• When using publish path deletion using the LiveView Adapters for StreamBase, it will be helpful to enable the Share LiveView Connection property on each adapter instance so the LiveView Server receives a consistent presentation of the data. This comment applies analogously to any Client API-oriented use of the publish path.

• High Availability considerations. As of Live Datamart 2.0.3, the only row deletion method that will work seamlessly across a multi-node HA configuration is the lvconf . It is possible to create an Alert Rule on each node separately, and for now this is a recommended practice.

sample_adapter_embedded_lv-delete.zip