Library Articles
Working with Shared Query Tables
Authors: Richard Tibbetts, Eddie Galvez, Bingfang Song, Dr. John Lifter
StreamBase Systems
Date: 01-September-2007
Applicable To: StreamBase 5.0
StreamBase 5.0 introduces the concept of shared query tables. In prior releases, query tables, and the EventFlow query operators or StreamSQL statements used to manipulate a table’s content, needed to run within the same module. Introduction of shared query tables allows EventFlow query operators and StreamSQL statements contained in modules separate from the module containing the query table to manipulate the table’s content.
In developing a StreamBase application that employs shared query tables, there are three scenarios:
- The shared query table exists within the module represented by the EventFlow module reference or specified in the StreamSQL
APPLY MODULE statement. This module is referred to as the “inner” module.
- The shared query table exists within the module that contains the EventFlow module reference or StreamSQL
APPLY MODULE statement. This module is referred to as the “outer” module.
- The application is composed of multiple modules where a shared query table existing within one module in the application is accessed from another module (or modules) in the application.
When developing an EventFlow application, you specify whether a query table will be shared via an entry on the StreamBase Properties view, General tab. In a StreamSQL application, the syntax of the APPLY MODULE statement indicates whether the table is shared across modules.
When shared query tables are included in the outer module, you will create and debug the inner and outer modules independently and then complete the application by adding a reference for the inner module to the outer module. When shared query tables are included in the inner module, you must complete development of this module before you can start development of the outer module. The following sections of this document describe these procedures in greater detail.
In this scenario, both the inner and outer modules must be initially developed as complete, running applications. Then the inner module is incorporated into the outer module so that the shared query tables in the outer module are used by the query operators or StreamSQL statements in the inner module.
Developing the Outer Module
The following EventFlow application diagram presents a simple application in which a metronome operator, at 10 second intervals, initiates a read all rows query against two query tables. Since the diagram does not include any components to write data into the query tables, queries against these tables will emit a single tuple with null values in the fields derived from the tables.
To complete the application, you will create another module, which will become the inner module, to write data into these tables. Consequently, in configuring query tables QT1 and QT2, select Shared radio button in the Access Level grouping on these data constructs’ StreamBase Properties view, Table Settings tab. (Selecting the Private radio button would restrict each table’s visibility to the module containing the table; the same behavior as prior StreamBase product releases.)
Even though the inner module has not yet been developed and incorporated into the application, the outer module will run.
In the StreamSQL representation of this module, the table declarations now include the keyword OUTPUT. This indicates that the table will share its data with other modules; the table data is output from this module.
CREATE OUTPUT STREAM OS2;
CREATE OUTPUT STREAM OS3;
CREATE OUTPUT MEMORY TABLE QT1 (
sym string(4),
shares int,
price int,
PRIMARY KEY(sym) USING hash
);
CREATE OUTPUT MEMORY TABLE QT2 (
sym string(4),
shares int,
price int,
PRIMARY KEY(sym) USING hash
);
CREATE METRONOME out__Metronome1_1 (time, 10.0);
SELECT out__Metronome1_1.time AS input_time,
QT2.sym AS table_sym,
QT2.shares AS table_shares,
QT2.price AS table_price
FROM out__Metronome1_1 OUTER JOIN QT2
INTO OS2;
SELECT out__Metronome1_1.time AS input_time,
QT1.sym AS table_sym,
QT1.shares AS table_shares,
QT1.price AS table_price
FROM out__Metronome1_1 OUTER JOIN QT1
INTO OS3;
Developing the Inner Module
The following EventFlow application is a simple application that writes (using the update operation) the same data into two query tables. Each query operator is configured to pass fields from the input stream to its emitted tuple. After writing to the tables, the input data is emitted on the output stream.
The query tables QueryTable1 and QueryTable2 have the same schemas and indexing as the tables QT1 and QT2 in the outer module, since the ultimate objective is to substitute the tables from the outer module for the tables in this inner module. In configuring query tables QueryTable1 and QueryTable2, you will select the Placeholder radio button in the Access Level grouping on these data constructs’ StreamBase Properties view, Table Settings tab.
Even though this inner module has not been incorporated into the outer module, it will run.
In the StreamSQL representation of this module, the table declarations now include the keyword INPUT. This indicates that the table data will actually come from a shared query table implemented in another module; the shared table’s data is input into this module.
CREATE INPUT STREAM InputStream1 (
stock string(4),
cost int,
volume int
);
CREATE OUTPUT STREAM OS1;
CREATE INPUT MEMORY TABLE QueryTable1 (
sym string(4),
shares int,
price int,
PRIMARY KEY(sym) USING hash
);
CREATE INPUT MEMORY TABLE QueryTable2 (
sym string(4),
shares int,
price int,
PRIMARY KEY(sym) USING hash
);
CREATE STREAM out__Query1_1;
REPLACE INTO QueryTable1 (sym, shares, price)
SELECT stock AS sym, volume AS shares, cost AS price
FROM InputStream1
RETURNING InputStream1.stock AS stock,
InputStream1.cost AS cost,
InputStream1.volume AS volume
INTO out__Query1_1;
REPLACE INTO QueryTable2 (sym, shares, price)
SELECT stock AS sym, volume AS shares, cost AS price
FROM out__Query1_1
RETURNING out__Query1_1.stock AS stock,
out__Query1_1.cost AS cost,
out__Query1_1.volume AS volume
INTO OS1;
Integrating the Inner Module into the Outer Module
In StreamBase Studio, reopen the application diagram for the outer module. Drag and drop the application diagram for the inner module onto this canvas; add an input stream and output stream. Now carefully look at the module reference icon. Note the appearance of the data construct port on the bottom edge.
This port, similarly to the corresponding port on a query operator, is connected to the data construct port at the top of the query table icon. Drag an arc from the module reference to each of the shared query tables in the outer module.
The module reference will now raise typecheck errors; StreamBase is uncertain which outer module query table corresponds to each inner module query table. Select the module reference to open its StreamBase Properties view and click on the Input Tables tab. In the Current Input Table Associations listing, entries QT1 and QT2 are contained in the Application Table column. Use the dropdown list box in the Module Table column to identify the corresponding table from the inner module.
In the APPLY MODULE statement of the StreamSQL representation of the finished application, the FROM clause now includes entries that specify the query table associations. In a StreamSQL implementation of the Shared Query Tables in an Outer Module approach, this is the only syntax change that is necessary to enable the use of shared query tables. Note the order of the entries in each association: the name of a query table in the inner module is on the left and the name of the corresponding query table in the outer module is on the right. This is the same ordering paradigm as for the input stream associations; the outer module’s shared query tables are equivalent to input streams into the inner module.
In the following StreamSQL file, blue font represents statements added to the outer module file in the process of integrating the inner module, and the shared table related syntax in the APPLY MODULE statement is in red font. Note that the query table assignments are included in the FROM clause, indicating that data from the shared query tables in the outer module are input to the inner module.
CREATE INPUT STREAM InputStream2 ( stock string(4), cost int, volume int);
CREATE OUTPUT STREAM OS2;
CREATE OUTPUT STREAM OS3;
CREATE OUTPUT STREAM OS4;
CREATE OUTPUT MEMORY TABLE QT1 (
sym string(4),
shares int,
price int,
PRIMARY KEY(sym) USING hash
);
CREATE OUTPUT MEMORY TABLE QT2 (
sym string(4),
shares int,
price int,
PRIMARY KEY(sym) USING hash
);
CREATE METRONOME out__Metronome1_1 (time, 10.0);
APPLY MODULE "innerModule.ssql" –- or "innerModule.sbapp"
FROM InputStream1 = InputStream2,
QueryTable1 = QT1, QueryTable2 = QT2
INTO OS1 = OS4;
SELECT out__Metronome1_1.time AS input_time,
QT2.sym AS table_sym,
QT2.shares AS table_shares,
QT2.price AS table_price
FROM out__Metronome1_1 OUTER JOIN QT2
INTO OS2;
SELECT out__Metronome1_1.time AS input_time,
QT1.sym AS table_sym,
QT1.shares AS table_shares,
QT1.price AS table_price
FROM out__Metronome1_1 OUTER JOIN QT1
INTO OS3;
In this scenario, the inner module must be developed first. When creating the outer module, a module reference to the inner module provides direct access to the shared query table(s).
Developing the Inner Module
The following EventFlow application diagram presents a simple application in which a query operator writes (using the update operation) a query table and then emits all input and table fields to the output stream.
Since the query table is to be accessible to another module (the outer module), select Shared radio button in the Access Level grouping on this data construct’s StreamBase Properties view, Table Settings tab. (Selecting the Private radio button would restrict each table’s visibility to the module containing the table.)
In the StreamSQL representation of this module, the table declaration is the same whether the access level is shared or private; that is, there is no indication that the query table is shared or restricted to a single module.
CREATE INPUT STREAM InputStream1 (
stock string(4),
volume int,
price int
);
CREATE OUTPUT STREAM OS1;
CREATE OUTPUT MEMORY TABLE QueryTable1 (
sym string(4),
number int,
cost int,
PRIMARY KEY(sym) USING hash
);
REPLACE INTO QueryTable1 (sym, number, cost)
SELECT stock AS sym, volume AS number, price AS cost
FROM InputStream1
RETURNING InputStream1.stock AS input_stock,
InputStream1.volume AS input_volume,
InputStream1.price AS input_price,
QueryTable1.sym AS table_sym,
QueryTable1.number AS table_number,
QueryTable1.cost AS table_cost
INTO OS1;
Developing the Outer Module and Integrating the Inner Module
The following EventFlow diagram shows the completed outer module in which a metronome operator, at 10 second intervals, initiates a read all rows query against the shared query table included in the inner module. The query operator emits tuples containing all table fields to the output stream.
Note the data construct port on the top edge of the module reference icon. The data construct port of a query operator is connected to the module reference icon similarly to the data construct port on the top of a query table icon. To complete configuration of the operator Query2, access the StreamBase Properties view, Query Settings tab and in the Associated Table dropdown, select the desired shared table from the inner module against which the query operator should run. Since in this example the inner module only contains a single shared table, the single listing entry will be selected by default; if the inner module includes multiple shared tables, select the table appropriate for this query operator.
In the StreamSQL representation of this module, there are two important statements: CREATE TABLE and APPLY MODULE. Note that the query table assignment is included in the INTO clause, indicating that data from the shared query table in the inner module will be emitted into the outer module.
CREATE INPUT STREAM InputStream2 (
stock string(4),
volume int,
price int
);
CREATE OUTPUT STREAM OS2;
CREATE OUTPUT STREAM OS3;
CREATE TABLE import__InnerModule_QueryTable1;
CREATE METRONOME out__Metronome1_1 (time, 10.0);
APPLY MODULE "innerModule.ssql" –- or "innerModule.sbapp"
FROM InputStream1 = InputStream2 INTO OS1 = OS3,
QueryTable1 = import__InnerModule_QueryTable1;
SELECT import__InnerModule_QueryTable1.sym
AS table_current_sym,
import__InnerModule_QueryTable1.number
AS table_current_number,
import__InnerModule_QueryTable1.cost
AS table_current_cost
FROM out__Metronome1_1 OUTER JOIN
import__InnerModule_QueryTable1
INTO OS2;
The CREATE TABLE statement does not include a description of the table’s schema, indexing, or persistence characteristics. Since this shared table has been fully described in the inner module, its definition may not be changed from within the outer module. This form of the CREATE TABLE statement has been newly added to the StreamSQL language.
In the APPLY MODULE statement, the INTO clause now includes an entry that specifies the query table association. Note the order of the entries in the association; the name of a query table in the inner module is on the left and the name of the corresponding query table in the outer module is on the right. This is the same ordering paradigm as for the output stream associations; the inner module’s shared query table is equivalent to an output stream from the inner module.
In the situation in which the inner module contains multiple shared query tables, the StreamSQL representation would include a separate CREATE TABLE statement for each table accessed from the outer module and the INTO clause in the APPLY MODULE statement would include multiple query table assignments.
CREATE INPUT STREAM IS1 (
stock string(4),
cost int,
volume int
);
CREATE OUTPUT STREAM OS1;
CREATE OUTPUT STREAM OS2;
CREATE OUTPUT STREAM OS3;
CREATE TABLE import__SharedTables_QueryTable1;
CREATE TABLE import__SharedTables_QueryTable2;
CREATE METRONOME out__Metronome1_1 (time, 10.0);
APPLY MODULE "innerModule.ssql" –- or "innerModule.sbapp"
FROM InputStream1 = IS1
INTO OutputStream1 = OS1,
QueryTable1 = import__SharedTables_QueryTable1,
QueryTable2 = import__SharedTables_QueryTable2;
. . .
Consider the follow two EventFlow applications. In the first application, data enqueued through the input stream is stored in a shared query table. On the table’s StreamBase Properties view, Table Settings tab, select the Shared radio button in the Access Level grouping.
The StreamSQL representation of this module contains the following statements. The CREATE OUTPUT MEMORY TABLE statement identifies this as a shared query table.
CREATE INPUT STREAM InputStream1 (
stock string(4),
volume int,
price int
);
CREATE OUTPUT STREAM OS1;
CREATE OUTPUT MEMORY TABLE QueryTable1 (
sym string(4),
number int,
cost int,
PRIMARY KEY(sym) USING hash
);
REPLACE INTO QueryTable1 (sym, number, cost)
SELECT stock AS sym, volume AS number, price AS cost
FROM InputStream1
RETURNING InputStream1.stock AS input_stock,
InputStream1.volume AS input_volume,
InputStream1.price AS input_price,
QueryTable1.sym AS table_sym,
QueryTable1.number AS table_number,
QueryTable1.cost AS table_cost
INTO OS1;
In the second application, a metronome operator, at 10 second intervals, initiates a read all rows query against a query table; the query operator emits tuples containing all table fields to the output stream. On the table’s StreamBase Properties view, Table Settings tab, select the Placeholder radio button in the Access Level grouping.
The StreamSQL representation of this module contains the following statements. The CREATE INPUT MEMORY TABLE statement identifies this as a placeholder for data that will actually be stored in a shared query table.
CREATE OUTPUT STREAM OS2;
CREATE INPUT TABLE QT1 (
sym string(4),
number int,
cost int,
PRIMARY KEY(sym) USING hash
);
CREATE METRONOME out__Metronome1_1 (time, 10.0);
SELECT out__Metronome1_1.time AS input_time,
QT1.sym AS table_sym,
QT1.number AS table_number,
QT1.cost AS table_cost
FROM out__Metronome1_1 OUTER JOIN QT1
INTO OS2;
Now combine these modules into an application. Add the required input and output streams and create an arc between the data construct ports on the module references.
The StreamSQL representation contains the following statements. The blue font includes the statements that incorporate the two modules into this application. The red font identifies those statements that enable the table sharing across modules.
CREATE INPUT STREAM IS2 (
stock string(4),
volume int,
price int
);
CREATE OUTPUT STREAM OS4;
CREATE OUTPUT STREAM OS3;
CREATE TABLE sharedTable;
APPLY MODULE "FirstModule.ssql"
FROM InputStream1 = IS2
INTO OS1 = OS3, QueryTable1 = sharedTable;
APPLY MODULE "SecondModule.ssql"
FROM QT1 = sharedTable
INTO OS2 = OS4;
Note that inclusion of the table association in the INTO clause of the first APPLY MODULE statement specifies that data from the shared table is sent into the application, and the inclusion of the table association in the FROM clause of the second APPLY MODULE statement indicates that this module obtains data from the shared table.
In developing this type of application, each of the individual modules may be developed and debugged independently using either EventFlow or StreamSQL approach. After each module has been completed, they are combined into the final application.
StreamSQL APPLY statement
StreamSQL CREATE TABLE statement