CREATE TABLE Statement

CREATE TABLE defines a private or shared query table.

Syntax 

CREATE [INPUT | OUTPUT] [MEMORY | DISK] TABLE table_identifier (
  named_schema_identifier|anonymous_schema
  PRIMARY KEY(field_identifier[, ...]) [USING {HASH | BTREE}]
  [WITH PARAMETERS (
    parameter_name = "parameter_value" [, ...] )]
);

The older syntax, which is still supported, uses an anonymous schema:

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;

Substitutable Fields

table_identifier

A unique identifier (name) for the table.

named_schema_identifier

The identifier of a previously-defined named schema. Imports all of the named schema's fields. No parentheses are required, and no other fields are permitted. Named schemas must be defined in dependency order. If a schema is used before it is defined, an error results.

anonymous_schema

A schema definition, delimited by parentheses, in the following format:

(field_definition [, ...])
field_definition

A field definition takes the form:

field_identifier field_type

field_identifier

A unique name for a field in the schema associated with the stream. For table indexes, if the field references a named schema, the entire schema is used as the key.

field_type

One of the supported StreamBase data types.

column_identifier

A unique identifier (name) for a column in the table. Since each row in the table corresponds to a stored tuple, a column corresponds to a field_definition.

column_type

The data type contained in a specified column. Analogous to the field_type.

parameter

A parameter takes the form:

parameter_name parameter_value

parameter_name

The name of a StreamBase parameter appropriate to the statement

parameter_value

A quoted value for the parameter, which if a string must include escaped quotes

Examples

The following statements demonstrate how to use a previously defined, named schema as the table schema:

CREATE SCHEMA SymbolSchema (ID int, Symbol string, Price double);
CREATE MEMORY TABLE Symbols1 
  SymbolSchema
  PRIMARY KEY(ID);

Note that in this format, the primary key can reference an individual field in the named schema.

Here is a table with an anonymous schema. Again, we designate a specific field as the primary key:

CREATE MEMORY TABLE Symbols2 
  (ID int, Symbol string, Price double)
  PRIMARY KEY(ID) ;

The next example includes a named schema as a field within an anonymous schema:

CREATE MEMORY TABLE Symbols3 
  (ss SymbolSchema, Quantity int)
  PRIMARY KEY(ss) ;

Notice that in this example, the ss field that we specify as the primary key is a tuple field that references a named schema. This means that all of the named schema's fields are used, in sort order. We cannot designate an individual field in the named schema as the primary key, as we can with the second, non-hierarchical field (Quantity).

Discussion

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 can 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 can 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 indexes are optional and are defined in CREATE INDEX Statement statements.

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

  • Unordered, no ranges (hash): Keys are unsorted, and they are evenly distributed (hashed) across the index. A hash index is used for accessing keys based on equality.

  • Ordered, with ranges (btree): Keys are sorted. A btree index is used when output ordering and range queries are desired. Note that the sort depends on the order of the fields in the index keys.

There are two statements that can 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 can be used when the table has a composite primary key. In this case, define the primary key in a separate clause. This syntax can 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, can be specified.

Secondary indexes 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 can 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 can 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 can 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.

Related Topics

Back to Top ^