Managing Data Tables Through Size


This article describes the various ways to manage LiveView tables by deleting rows from a data table.

To accomplish this, Live Datamart provides a number of ways to delete rows. 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.

If you want a table to automatically trim itself, methods include using an Alert Rule with a Delete Rows action or scheduling a periodic DELETE query to run using some scheduling mechanism such as LiveView's built-in cron mechanism, which you can use to issue periodic DELETE queries.

Using the Query Path or the Publish Path to Delete Rows

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

By publish path refers to 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 refers to 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.

By comparison, 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.

The remaining sections below present a 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 and assume that the Hello LiveView application running in order to operate.

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.

  1. Start StreamBase Studio in the SB Authoring perspective.

  2. Load the Hello LiveView sample.

    1. Select FileLoad StreamBase Sample from Studio's top-level menu.

    2. In the search field, enter Hello to narrow the list of samples.

    3. Select the Hello LiveView sample from the TIBCO Live Datamart list.

    4. Click OK.

    The Hello LiveView sample loads into Studio with the project name sample_lv-helloliveview.

  3. In the Package Explorer view, select the name of the project, right-click, and from the context menu, select Run AsLiveView Project. The Console view shows several messages as the LiveView Server compiles the project and starts. Wait until the console message All tables have been loaded. LiveView is ready to accept client connections before proceeding to the next step.

  4. Launch LiveView Desktop to connect to view the running sample.

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.

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:

TIBCO recommends for delete actions to suppress subsequent execution for at least a minute if not 10 minutes or more, rather than the five 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 Hello LiveView sample, that would be the ItemsSales table:

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

The delete would occur the first time transactionTime was published with a time between midnight and 1 AM. You must also set the “suppress subsequent execution for” to something like 4000 to ensure the delete does not occur more than once in that hour window.

Example 3

Another common Delete Rows action pattern can resemble when transactionTime between now()-hours(2) and now() where the delete predicate resembles transactionTime < now()+hours(1). Every two hours, this will delete all rows with a transactionTime older than one hour. Again, use an “After executing actions, suppress subsequent execution for __ seconds” of some number of seconds, such as 30 or 60 seconds. This ensures 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 four minutes old, every five 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.

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

  • lv-client addalertrule

  • LiveView Web Monitor Interface

  • 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 command

For ad-hoc manual deletes, you can use the lv-client delete command. See lv-client(1) for more information. The general syntax is:

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

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

lv-client -u sb://localhost:10000 "delete from ItemSales where Item='Wagon'"
LiveView Delete Output Adapter for StreamBase

The TIBCO LiveView Delete Output adapter allows a StreamBase application to delete one or more rows in a LiveView table based on a specified predicate. See LiveView Delete Output Adapter for more information.

The TIBCO LiveView Query, Delete, Publish, and Ready Adapters sample

This sample contains the file lv2sbd.sbapp, which contains a small sample of using the LiveView Delete adapter. See the sample's README file for more information.

Publish Path Row Deletion

To be very clear: publish path row deletes are primary key-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 primary keys of all rows that fall within the time window. One could view the options (query path versus publish path) as table maintenance versus record maintenance, if you will.

Using a 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.

<!-- delete rows with incoming events more than a day old -->
<row-delete-rule>transactionTime < now()-days(1)</row-delete-rule>

Using 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.