Working with Shared Query Tables

The following EventFlow diagram shows a simple application. A query operator (Query1) writes (using the update operation) a query table (QueryTable1) and then emits all input and table fields to the output stream, OS1.

To make the query table accessible to other modules within the application, including the outer module:

Note that the possible settings are as follows:

The following EventFlow diagram shows the complete outer module.

The outer module holds InnerModuleRef1, which is a reference to the inner module, which is already defined and which holds the shared query table, QueryTable1. A metronome operator, at 10 second intervals, initiates a read all rows query (Query2) against QueryTable1, which is the shared query table defined in InnerModuleRef1. Query2 emits tuples containing all table fields to the output stream, OS3.

Note that the data construct port of a query operator is connected to the port of the reference to a module in the same way that a query operator connects to a query table.

To complete the configuration of the Query2 operator in the outer module:

If, in your application, the inner module holds multiple shared tables, select the table required by the query operator you are defining as the associated table.

In the following EventFlow diagram a metronome operator, at 10 second intervals, initiates a read all rows query against QT1 and QT2. Because there are no components in this module that write data into QT1 and QT2, Query5 and Query6 each emit a single tuple with null values in the fields derived from the tables.

Another module, that is, an inner module, is designed to be referenced by the outer module to complete this example application and write data into these tables. To accomplish this, specify that QT1 and QT2 in the outer module are shared by selecting Shared in the Table settings tab in the Properties view for each table.

Even though you have not yet developed the inner module, and the inner module is not yet referenced in the application, the outer module can run as a standalone application.

The inner module, which writes data to the tables in the outer module, uses placeholder tables.

The following example shows an application that writes (using the update operation) the same data into two query tables, QueryTable1 and QueryTable2. 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.

Because the goal is to substitute the tables from the outer module for the tables in this inner module, the query tables QueryTable1 and QueryTable2 have the same schema and indexing as QT1 and QT2 in the outer module.

To establish the association between the actual tables in the inner module and the tables in the outer module, select Placeholder in the Properties view Table Settings tab of QueryTable1 and QueryTable2.

Because the query operators and query tables are fully defined in the inner module, you can run this module as a standalone application, that is, you can run it without it's being referenced from the outer module. If you run the inner module as a standalone application, the placeholder tables behave as if they are private tables.

The next step is to reference the inner module in the outer module.

To complete the application:

Tables are no longer synchronized if you change a setting in an inner module using EventFlow Editor when the outer module is closed. In this example, StreamBase Studio could have automatically tracked your change to the inner module if you had not closed the outer module in Step 8.

The link shown in Step 12 corrects this kind of error by resynchronizing tables across modules.

The following StreamSQL code for the inner module creates the input and output streams, creates the shared query table, and emits to the output stream the data from both the input stream and the shared query table.

-- Inner Module Example 1

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 *
    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;

The following StreamSQL code specifies the outer module.

-- Outer Module Example 1

CREATE INPUT STREAM InputStream2 (
    stock string(4),
    volume int,
    price int
);

CREATE OUTPUT STREAM OS2;
CREATE OUTPUT STREAM OS3;

CREATE INPUT TABLE InnerModule_QueryTable1;

CREATE METRONOME Metronome1 (time, 10.0);

APPLY MODULE "innerModule.ssql"  -- or "innerModule.sbapp"
     FROM InputStream1 = InputStream2 INTO OS1 = OS3,
         QueryTable1 = InnerModule_QueryTable1;  

SELECT InnerModule_QueryTable1.sym
         AS table_current_sym,
       InnerModule_QueryTable1.number
         AS table_current_number,
       InnerModule_QueryTable1.cost
         AS table_current_cost
  FROM Metronome1 OUTER JOIN 
       InnerModule_QueryTable1
  INTO OS2;  

The following CREATE INPUT TABLE statement in the outer module creates the placeholder table:

CREATE INPUT TABLE InnerModule_QueryTable1;

The table declaration for the placeholder does not include a description of the table's schema, indexing, or persistence characteristics. Because the shared table has been fully specified in the inner module, its definition cannot be changed from within the outer module.

In the following APPLY MODULE statement, the INTO clause specifies the query table association:

APPLY MODULE "innerModule.ssql"
   FROM InputStream1 = InputStream2 INTO OS1 = OS3,
   QueryTable1 = InnerModule_QueryTable1;

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 the paradigm for the output stream associations—you can think of the shared query table defined in the inner module as equivalent to an output stream from the inner module.

If your design pattern includes an inner module that holds two or more shared query tables, you must specify both of the following in the StreamSQL for the outer module:

See the following example of the code for an outer module holding two placeholder tables, SharedTables_QueryTable1 and SharedTables_QueryTable2:

CREATE INPUT STREAM IS1 (
    stock string(4),
    cost int,
    volume int
);
CREATE OUTPUT STREAM OS1;
CREATE OUTPUT STREAM OS2;
CREATE OUTPUT STREAM OS3;

CREATE INPUT TABLE SharedTables_QueryTable1;
CREATE INPUT TABLE SharedTables_QueryTable2;

CREATE METRONOME out__Metronome1_1 (time, 10.0);

APPLY MODULE "innerModule.ssql" -- or "innerModule.sbapp"
   FROM InputStream1 = IS1
   INTO OutputStream1 = OS1,
      QueryTable1 = SharedTables_QueryTable1,
      QueryTable2 = SharedTables_QueryTable2;

The outer module specifies a metronome operator, which, at 10 second intervals, initiates a read all rows query against two shared query tables, QT1 and QT2. A query against either of the tables emits a single tuple with null values in the fields derived from the table. The module writes data into QT1 and QT2 as output streams, OS2 and OS3.

--Outer Module Example 2

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 Metronome1 (time, 10.0);

SELECT *
  FROM Metronome1 OUTER JOIN QT2
  INTO OS2;

SELECT Metronome1.time AS input_time,
       QT1.sym AS table_sym,
       QT1.shares AS table_shares,
       QT1.price AS table_price
  FROM Metronome1 OUTER JOIN QT1
  INTO OS3;

Note that the table declarations include the keyword OUTPUT, which specifies that the table being declared can share its data with other modules; that is, the table data is output from this module.

Even though the inner module has not yet been developed and referenced in the application, this outer module can run as a standalone application.

The inner module uses the update operation to write the same data into two query tables, QueryTable1 and QueryTable2, which are placeholders for the shared tables, QT1 and QT2, respectively. Each of two query operators 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 OS1.

In the StreamSQL representation of this module, the table declarations include the keyword INPUT. This indicates that the table data come from a shared query table implemented in another module. In other words, data in the shared table in the outer module is input into this inner module.

-- Inner Module Example 2

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 Query1;

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 Query1;

REPLACE INTO QueryTable2 (sym, shares, price)
  SELECT stock AS sym, volume AS shares, cost AS price
    FROM Query1
    RETURNING Query1.stock AS stock,
              Query1.cost AS cost,
              Query1.volume AS volume
    INTO OS1;

Even though this inner module has not been referenced in the outer module, it can run as a standalone application.

After developing the inner and outer modules independently, you can add to the outer module a reference to the inner module.

The following StreamSQL shows the finished application, which includes code for the outer module, the inner module, and code required to integrate the shared query table.

--Final Application Example 2

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 Metronome1 (time, 10.0);

APPLY MODULE "innerModule.ssql"  -- or "innerModule.sbapp"
FROM InputStream1 = InputStream2,
   QueryTable1 = QT1, QueryTable2 = QT2
INTO OS1 = OS4;


SELECT Metronome1.time AS input_time,
       QT2.sym AS table_sym,
       QT2.shares AS table_shares,
       QT2.price AS table_price
  FROM Metronome1 OUTER JOIN QT2
  INTO OS2;

SELECT Metronome1.time AS input_time,
       QT1.sym AS table_sym,
       QT1.shares AS table_shares,
       QT1.price AS table_price
  FROM Metronome1 OUTER JOIN QT1
  INTO OS3;

Note that, in the finished application, the APPLY MODULE statement specifies the inner module, as shown:

APPLY MODULE "innerModule.ssql"  -- or "innerModule.sbapp"

The FROM clause includes the following line, which specifies the query table associations:

QueryTable1 = QT1, QueryTable2 = QT2