CREATE TABLE Statement

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,
    volume int,
    price int
);

CREATE OUTPUT STREAM OS1;

CREATE OUTPUT MEMORY TABLE QueryTable1 (
    sym string,
    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,
    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,
    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,
    shares int,
    price int,
    PRIMARY KEY(sym) USING hash
);

CREATE OUTPUT MEMORY TABLE QT2 (
    sym string,
    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,
    cost int,
    volume int
);
CREATE OUTPUT STREAM OS1;

CREATE INPUT MEMORY TABLE QueryTable1 (
    sym string,
    shares int,
    price int,
    PRIMARY KEY(sym) USING hash
);

CREATE INPUT MEMORY TABLE QueryTable2 (
    sym string,
    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, cost int, volume int
 );
CREATE OUTPUT STREAM OS2;
CREATE OUTPUT STREAM OS3;
CREATE OUTPUT STREAM OS4;

CREATE OUTPUT MEMORY TABLE QT1 (
    sym string,
    shares int,
    price int,
    PRIMARY KEY(sym) USING hash
);

CREATE OUTPUT MEMORY TABLE QT2 (
    sym string,
    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