What are all the possible ways to delete rows from a LiveView table

Issue

How do I delete rows from a Live Datamart data table?

Solution

Note: You very well might be happier reading What are the most frequently used ways to trim rows from a LiveView table first, if you haven't already. There's nothing there that isn't here, but there's more here to digest and it might be distracting if you are new to the topic.

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?

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

To accomplish this, LiveView provides a number of ways to delete rows from a data table. These various deletion methods may 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 is 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.

No real application out there is going to use all 10 ways to delete, so at the end of the survey are some design considerations to help you pick which way(s) to use for a given set of application requirements.

If you want the bottom line quickly, however, 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.

But there are plenty of alternatives to that, and they are all explained in the rest of this article.

Even though almost all of the material on this page is documented as part of the Live Datamart product documentation, these are still frequently asked questions, as the information is scattered there across several documentation pages, and it is reference rather than tutorially oriented. Further, the current TIBCO Education training course on Live Datamart (TSB501) doesn't discuss row deletion at all yet. Since row deletion is a pretty basic function to want to know about, this page collects and explains all the different ways to delete rows from Live Datamart tables. Even better, this page includes code samples not available elsewhere that will help people avoid some trial and error the first time they use the features.

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

These ways 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.

Rows May Be Deleted Using Several Interface Technologies

The means by which row deletion functionality may be invoked can also be classified by interface technology: command line, LiveView adapters for StreamBase, LiveView Client APIs, and Alert Rules.

This table summarizes the available row deletion methods.

Clearing table contents upon server restart

A Live Datamart memory table will be empty when the LiveView server process starts, so a quick and dirty way of deleting all the rows in a table is to restart the server. Similarly, a table space may unset the Restore Data on Start open so that a server restart effectively deletes all the table rows as well.

For some applications, this method may suffice, but we will not discuss this method any further on this page.

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())

Setup

The bulk of the rest of this article presents a detailed tutorial survey of all the row deletion methods available to Live View 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, go ahead and start up a Hello Live server and launch a LiveView Desktop to connect to it, so that you can see the results of the deletion methods described below.

Deleting rows using a DELETE Query (Query Path Row Deletion)

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 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'"

Live View Delete Output Adapter for StreamBase

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

The TIBCO 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 (attached below as deleteonceaday.sbapp_.txt) contains the code for this example. sample_adapter_embedded_lv-delete.zip (attached below) 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 may change the Metronome Tuple 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.

LiveView Client API Delete

The LiveView Java, .NET, and JavaScript APIs each provide two ways to delete rows from a data table:

• Using LiveViewConnection.registerQuery() and ILiveViewConnection.ExecuteQuery() methods, respectively
• Using the LiveViewConnection.deleteRows() and ILiveViewConnection.DeleteRows() methods, respectively

In general, the Query-oriented methods give more information about the DELETE query's progress, by allowing the caller to provide a listener object that has callbacks representing the start and end of the query and for any exceptions raised during the execution of the query.

By contrast, the deleteRows() methods provide no way to get such information back to the caller, but is somewhat simpler to use because there is no listener object to construct.

LiveView Java Client API Delete

The LiveView Java Client API has a DELETE query type which can perform a delete this way:

Example code snippet using the Hello LiveView sample application as a server:

QueryConfig qc 
  = new QueryConfig().setQuery("ItemsSales","Item=='Wagon'")
                     .setQueryType(LiveViewQueryType.DELETE);
common.lvServer.registerQuery(qc, new MyDeleteListener("myDelete"));

Source code for an example is attached below. To use, download deletequery.java_.txt, rename to DeleteQuery.java and copy to the java-src/com.streambase.liveview.sample.client package directory to your copy of the sample_lv-java-clientapi project, and Run As . . . Java Application against a running Hello LiveView sample server.

For convenience, a complete StreamBase Studio project containing the DeleteQuery.java application is attached below as sample_lv-java-clientapi-delete.zip. To import, use File > Import > General > Existing Projects into Workspace > Select Archive File. Once imported, select sample_lv-java-clientapi-delete/java-src/com.streambase.liveview.sample.client/DeleteQuery.java, and Run As > Java Application against an already running Hello LiveView server at lv://localhost:10080. 

LiveView .NET Client API Delete

Use the QueryConfig.QueryTypes.DELETE query type

C# Code Snippet showing how to issue a DELETE query:

using StreamBase.LiveView.API;
[ . . . ]
QueryConfig qc = new QueryConfig();
qc.Table = "ItemsSales";
qc.Predicate = "Item=='Wagon'";
qc.QueryType = QueryConfig.QueryTypes.DELETE;
ObservableQueryResult ItemsQueryResults = new ObservableQueryResult();
IQuery ItemsQuery = connection.ExecuteQuery(qc, ItemsQueryResults);

Attached below is a sample StreamBase Studio project that contains a working C# example of a LiveView DELETE Query using the LiveView .NET Client API: sample_lv-dotnet-delete

LiveView JavaScript Client API Delete

The LiveView JavaScript Client API supports deleting table rows. There is an API function deleteRows(tableName, predicate, settings).

In addition, queries against LiveView data tables from a JavaScript client support the following syntax when run from LIveView.connection.execute:

DELETE tableName 
DELETE FROM tableName [WHERE ...]

Alert Rule Delete Rows Action

A powerful and flexible way to automatically trim a Live View table is 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 action(s) fire when the query conditions are satisfied. The table against which the Delete Row action operates may be a different table than the one that the Alert Rule's condition monitors.

Note: When using a Alert Rules for deleting rows, consider safe-guarding them from firing too often, for example in LiveView Desktop the alert suppression option looks like this:

though we would recommend for delete actions suppressing subsequent execution for at least a minute if not 10 minutes or more, rather than 10 seconds, 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 will delete all ItemsSales table rows that are more than a day old whenever there are more than 20000 rows in the ItemSales table.

Example 2

As another example, suppose a LiveView 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, you should use a ?After executing actions, suppress subsequent execution for __ seconds? of some number of seconds ? 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.

Note

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.

Publish Path Row Deletion

Use a <table-delete-rule> element in the data table lvconf

Fine-grained table trimming can be configured in a data table's lvconf file with the new element <table-delete-rule>PREDICATE</table-delete-rule>.

An optional rule specifying a predicate is evaluated against this table. The predicate is used to register a query against the table where the rows added to the result set are deleted through the publish data path. For this reason, the predicate usually has a time component such as WHEN or FOR. The new element is similar to the existing row-delete-rule, but operates on the table as a whole.

For example:

 <table-delete-rule>OrdStatus=='GOOD' when ArrivalTime between epoch()and now()-seconds(3)</table-delete-rule>

Use 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, a row-delete-rule is of limited utility for table maintenance. 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

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

<row-delete-rule>myEventType=='DELETE'</row-delete-rule>

Use Control Fields to delete table rows

Live Datamart allows the creation of embedded StreamBase applications that are executed at various points along the event publication chain. Relevant to the row deletion discussion are application data sources, transform data sources, embedded publishers, join preprocessors, or preprocessor applications. (Aggregation data-sources (deprecated) and author-time aggregations handle row deletion in the aggregation target table implicitly.)

Starting with TIBCO LiveView 1.6.0, the LiveView control fields include a boolean field named CQSDelete that, when set to true, deletes any row with exactly matching primary key fields.

It is not supported to use this method by enqueuing tuples directly to the table's DataIn stream using a StreamBase Client (because bypassing the LiveView Services Layer does not allow the application to take advantage of Services Layer functionality such HA or server federation capabilities).

A transform data-source uses CQSReferred=0 to indicate a delete and does not have a CQSDelete control field.

While this is a rich topic to explore, and not yet well-documented or illustrated elsewhere, for now, we'll leave that work to another article or a future revision of this one.

lv-client publish --delete

Syntax:

 lv-client publish TableName [--delete [bufferSize [flushIntervalMS]]]

Reads CSV data from stdin and publishes to a table.

Example using Hello LiveVew server. While we do have to include every field in the table schema in the CSV representation of the tuple to be published to satisfy the lv-client implementation, only the primary key field values are used to carry out the deletion operation. 

C:\>lv-client "select max(transactionID) as maxID from ItemsSales"
 +-------+
 | maxID |
 +-------+
 |   308 |
 +-------+

Rows = 1

C:\>echo 308,0.0,,,0,0.0 | lv-client "publish ItemsSales --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 field(s) in the input tuple.

Example using Hello LiveView Sample application (pubdelete.sbapp, attached below as pubdelete.sbapp_.txt)

The pubdelete.sbapp also exists in the sample_adapter_embedded_lv-delete.zip attached below, which you may have already imported into StreamBase Studio above.

Use the LiveView Java Client API TablePublisher.publish() method with CQSDelete=true

Here is a code snippet showing the general process of how to delete a tuple using the publish path, with no exception handling included:

LiveViewConnection connection
  = LiveViewConnectionFactory.getConnection("lv://localhost:10080");
Table t = connection.getTable("ItemsSales");
// publisher name is arbitrary name and used to populate 
// PublisherID field for tracking and recoverability purposes
TablePublisher tp = t.getTablePublisher("myTablePublisher");
Tuple tuple = tp.getSchema.createTuple();
// only need to populate the primary key fields
tuple.setLong("transactionID", 87908);
// sequence number doesn't matter for non-persistent table
tp.publish(/* seq no */ 0, /* CQSDelete */ true, tuple);
tp.flush(); // blocks until publish is complete
tp.close();
connection.close();

PublishDeleteRow.java, attached below as publishdeleterow.java_.txt, is a complete Java application that illustrates how to delete a row from the Hello LiveView sample application server. This application performs a snapshot SELECT query to get a transactionID to delete, and then publishes a tuple to the table to cause the deletion of the corresponding row.

For convenience, a complete StreamBase Studio project containing the PublishDeleteRow.java application is attached below as sample_lv-java-clientapi-delete.zip. To import, use File > Import > General > Existing Projects into Workspace > Select Archive File .... Once imported, select sample_lv-java-clientapi-delete/java-src/com.streambase.liveview.sample.client/PublishDeleteRow.java, and Run As . . . Java Application against an already running Hello LiveView server at lv://localhost:10080. 

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 <row-delete-rule>. It is possible to create an Alert Rule on each node separately, and for now this is a recommended practice.

deleteonceaday.sbapp_.txt