How to configure a LiveView join preprocessor

Issue

How to configure a LiveView join preprocessor

Solution

It's sometimes said that Live Datamart can't do table joins, and that is true if you are looking at things only from the perspective of issuing a LiveQL query from some client to the Live Datamart server. Think of this as wish for a join-on-query feature we don't have and probably never will, perhaps because arbitrary live streaming joins from many clients at once is, well, really computationally expensive.

However, Live Datamart does have a feature called a join preprocessor. What the join preprocessor gives us is join-on-publish, not join-on-query.

This feature is nice for populating a denormalized your incoming event schemas to accommodate the single table LiveQL query model.

From a standard SQL perspective, the join preprocessor feature is a little odd. You are not joining data from two tables and viewing the results as a new result set schema. You are actually writing the results of the join INTO some specified columns of one of the tables participating in the join activity itself, which is called the target table. And since you can't create new columns in Live Datamart tables on the fly, the columns we're joining into have to already exist in the target table -- any value initially published into a target column gets overwritten by the join preprocessor.

The Live Datamart preprocessor documentation has a partial example of a half-active join preprocessor in it.

Attached is a runnable version of this sample: Download LiveViewJoin.zip from resources at bottom of the page .

Import the attached project into StreamBase Studio (File > Import... > Existing Projects into Workspace > Archive File > ....LiveViewJoin.zip

Right click on the LiveViewJoin project name in the Package Explorer view and select Run As > StreamBase LiveView Project

After seeing a line like

2015-05-14 16:30:48.988 INFO  { Server Started }  -
	*** All tables have been loaded. LiveView is ready to accept client connections. ***

in the SB Test/Debug perspective's Console view, then

Right click on the LiveViewJoin project name in the Package Explorer view and select StreamBase > Open StreamBase Command Prompt Here (might be Shell rather than prompt depending on your platform OS)

Start up LiveView Desktop and Open Query on both JoinTargetTable and JoinForeignTable

From the command or shell prompt, run the following:

lv-client
connect;
publish JoinForeignTable;
TIBX,TIBCO Software
IBM,International Business Machines
 
publish JoinTargetTable
1,B,IBM,,123,1000,300.0
2,S,IBM,,345,500,234.00
 
select * from JoinTargetTable;
+---------+------+--------+---------------------------------+---------+----------+-------+
| OrderID | Side | Symbol | CompanyName                     | Account | Quantity | Price |
+---------+------+--------+---------------------------------+---------+----------+-------+
| 1       | B    | IBM    | International Business Machines | 123     |   1000.0 | 300.0 |
| 2       | S    | IBM    | International Business Machines | 345     |    500.0 | 234.0 |
+---------+------+--------+---------------------------------+---------+----------+-------+

LiveView Desktop will show:

The Live Datamart documentation notes that this half-active join configuration is analogous to the SQL

SELECT JoinForeignTable.CompanyName As CompanyName, JoinTargetTable.* As *
FROM JoinForeignTable, JoinTargetTable
WHERE JoinForeignTable.Symbol=JoinTargetTable.Symbol

UPDATE JoinTargetTable t
SET CompanyName =
 (SELECT JoinForeignTable.CompanyName As t.CompanyName,
    FROM JoinForeignTable WHERE JoinForeignTable.Symbol=t.Symbol
)
WHERE JoinForeignTable.Symbol=t.Symbol

liveviewjoin_0.zip