CREATE SCHEMA Statement

Syntax 

CREATE SCHEMA schema_identifier 
  named_schema_identifier|anonymous_schema
[EXTENDS parent_schema_name1 [, parent_schema_name2 [, ...]]]
;

Substitutable Field

schema_identifier

A unique name for the schema.

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.

parent_schema_name

A schema_identifier for a schema whose definition is incorporated into the schema being defined.

Example

The following statements create named schemas. The first example presents a schema definition and names it Schema1. The second example defines its schema by referencing the first schema by name. 

CREATE SCHEMA Schema1 (ID int, Symbol string, Price double);
CREATE SCHEMA Schema2 Schema1;

In the following sequence, we define a named schema, point, then a second schema, point3d, that extends point and adds the third dimension: 

CREATE SCHEMA point (x double, y double);
CREATE SCHEMA point3d (z double) EXTENDS point;

Discussion

Use CREATE SCHEMA to create a named schema. Unlike private (anonymous) schemas defined for a particular stream or table, a named schema is defined at the module level, and can be referenced by any component in the module that takes a schema, or in any module that imports the defining module.

For example:

CREATE SCHEMA point (x double, y double);                    [1]
CREATE SCHEMA nested (tag string, nums (a int, y double));   [2] 
CREATE INPUT STREAM in (comment string, p point);            [3]
CREATE OUTPUT STREAM out;                    
SELECT point(1,2) AS mixed FROM in INTO out;                 [4]

The following notes refer to the line numbers in brackets in the example above.

  1. This named schema contains two double fields.

  2. This named schema contains a string field (tag) and a nested tuple field (nums).

  3. This input stream includes the named schema, point, by reference. It is equivalent to:

    CREATE INPUT STREAM instr (comment string, (x double, y double) );

  4. This statement demonstrates another way to use a named schema: its identifier can be used to invoke the automatically generated named schema constructor function to create a tuple with that schema. Here, the point() function creates a tuple with the values 1 and 2. In this case, the int arguments are coerced to doubles, conforming to the named schema. See named schema constructor function for further details.

    You can also use the name of a named schema as a function with zero arguments, which creates a null tuple with the specified schema (not a tuple with all null values).

See Using Parent Schemas for more on that subject.

The following example shows how the tuple data type is used in StreamSQL to form a tuple with a tuple sub-field:

CREATE SCHEMA NamedSchema1 (myInt1 int, myDouble1 double);         1
CREATE SCHEMA NamedSchema2 (myInt2 int, myTuple1 NamedSchema1);    2

CREATE INPUT STREAM in (
  myInt3 int,
  AnonTupleField1 (myDouble2 double, myString1 string),            3
  AnonTupleField2 (myString2 string, mySubTuple NamedSchema1)      4
);

These comments refer to lines with callout numbers in the example above:

1

Create a named schema containing an int field and a double field.

2

Create another named schema, this time containing an int field and a tuple field. The tuple field's schema consists of a reference to the first named schema, and therefore, its fields are MyInt1 and myDouble1. Notice that named schemas are defined independently of any other component.

3

The input stream's schema includes an int field followed by two tuple fields with anonymous schemas.

4

The second tuple field's schema includes a string followed by a nested tuple, which references one of the named schemas.

Related Topics

Back to Top ^