CREATE TABLE Statement

The following two statements are used to declare a private or shared query table.

CREATE [INPUT | OUTPUT] [MEMORY | DISK] TABLE table_identifier (
  column_identifier column_type PRIMARY KEY [USING {HASH | BTREE}]
  [, column_identifier column_type[, ...]]
);

Or

CREATE [INPUT | OUTPUT] [MEMORY | DISK] TABLE table_identifier (
  column_identifier column_type[, ...],
  PRIMARY KEY(column_identifier[, ...]) [USING {HASH | BTREE}]
);

The following statement is used in an outer StreamSQL application/module to declare a reference to a shared query table contained in an inner StreamSQL or EventFlow module. This statement is not used when an inner StreamSQL module accesses a shared query table in an outer StreamSQL or EventFlow application/module.

CREATE TABLE table_identifier;

Private or shared query tables are defined in a CREATE TABLE statement. A private table is declared without either the INPUT or OUTPUT keyword. The OUTPUT keyword is used to indicate that the table can share its content across modules. The INPUT keyword indicates that a shared query table in another module may be the target for statements run against this table.

Each query table can be declared to reside in-memory or on-disk. In the case of memory-resident tables, any data stored in the table at runtime is not saved when the StreamBase server shuts down. In the case of disk-based tables, the data stored in the tables may persist between StreamBase server sessions, provided you enabled this behavior through configuration.

Configuration file entries specify where a disk-based table is stored and how transactional semantics should be applied to the table.

Each table must have a primary index (key). Secondary indices are optional and are defined in CREATE INDEX Statement statements.

How keys are indexed for table read operations may also be specified.

There are two statements that may be used to create a private or shared query table. The first syntax is used when the table's primary key (or index) is composed of only one field. In this case, identify the primary key field as part of the field's declaration.

The second syntax may be used when the table has a composite primary key. In this case, define the primary key in a separate clause. This syntax may also be used if the primary key is based on a single table column.

Optionally, whether the table resides in memory (the default) or on disk, and whether a BTREE (the default) or HASH is used for ordering key/index values, may be specified.

Secondary indices are defined in separate CREATE INDEX statements.

A StreamSQL application/module that accesses a shared query table implemented in an inner module must include a CREATE TABLE statement that specifies a local table identifier but does not define the schema or indexing behavior for the table. The structure and indexing of the table is inferred from syntax of the APPLY Statement statement used to integrate the module into the outer StreamSQL application/module. You may not redefine the table's schema or indexing from the outer application/module.

A StreamSQL module that accesses a shared query table implemented in an outer application/module contains a CREATE TABLE statement that completely specifies the schema and indexing behavior. The outer application/module also includes an identical table description, although the table identifier may be different. The fact that the table is implemented within the outer application/module is inferred from syntax of the APPLY statement used to integrate the module into the StreamSQL application/module.

StreamSQL tables are analogous to relational database tables and applications will use stream queries to manipulate the content of these tables. Stream queries issued against a table alter the contents of the table and may return a result set of tuples that documents the changes made to the table. List, in the stream query's optional RETURNING clause, the tuple fields and table columns included in the result set.

Stream queries that do not include a RETURNING clause modify the content of StreamSQL tables but additional query operations will be needed to confirm the changes.

A SELECT statement against a table is equivalent to a table read operation. A table read operation also returns a result set of tuples but does not alter the content of the table.

The tuples returned by a table read operation or stream query against a table must be "captured" into a stream by either defining the stream query as part of a stream definition or by explicitly applying the query's result set to an existing stream.

Back to Top ^