CREATE STREAM

The CREATE STREAM statement creates a (local) stream, either in the specified schema (if the qualified-stream-name includes a schema name) or in the current schema.

The name of the stream must be distinct from the name of any other stream or view in the same schema. It is good practice to include a description of the stream.

Like tables, streams have columns, and you specify the data types for these in the CREATE STREAM statement. These should map to the data source for which you are creating the stream. For column_name, any valid non-reserved SQL name is usable. Column values cannot be null.

  • Specifying OR REPLACE re-creates the stream if it already exists, enabling a definition change for an existing object, implicitly dropping it without first needing to use a DRP command. Using CREATE OR REPLACE on a stream that already has data in flight kills the stream and loses all history.
  • RENAME can be specified only if OR REPLACE has been specified.
  • For the complete list of types and values in type_specification, such as TIMESTAMP, INTEGER, or varchar(2), see the topic SQLstream Data Types in the SQLstream SQL Reference Guide.
  • For option_value, any string can be used.
  • All streams must be queried using the STREAM keyword.

Syntax

The following are basic examples of streams defined for simple data sources. Note: All streams need to be defined within a schema. See the topic SET statements in this guide for more details.

Simple Stream for Unparsed Log Data

CREATE OR REPLACE SCHEMA "FileWriterSchema"
SET SCHEMA '"FileWriterSchema"';

CREATE OR REPLACE STREAM logStream (
source VARCHAR(20),
message VARCHAR(3072))
DESCRIPTION 'Head of webwatcher stream processing';

Stream Capturing Sensor Data from Intelligent Travel System Pipeline

CREATE OR REPLACE SCHEMA "FileWriterSchema"
SET SCHEMA '"FileWriterSchema"';
CREATE OR REPLACE STREAM "LaneData" (
-- ROWTIME is time at which sensor data collected
LDS_ID INTEGER,-- loop-detector ID
LNAMEVARCHAR(12),
LNUMVARCHAR(4),
OCC SMALLINT,
VOL SMALLINT,
SPEEDDECIMAL(4,2)
) DESCRIPTION 'Conditioned LaneData for analysis queries';

Stream capturing order data from e-commerce pipeline

CREATE OR REPLACE SCHEMA "FileWriterSchema"
SET SCHEMA '"FileWriterSchema"';

CREATE OR REPLACE STREAM "OrderData" (
"key_order"BIGINT NOT NULL,
"key_user" BIGINT,
"country" SMALLINT,
"key_product" INTEGER,
"quantity" SMALLINT,
"eur" DECIMAL(19,5),
"usd" DECIMAL(19,5)
) DESCRIPTION 'conditioned order data, ready for analysis';

To SELECT from a stream, you need to use the STREAM keyword. The code below shows a SELECT from a qualified stream name (schema and stream).

SELECT STREAM * FROM "FileWriterSchema"."OrderData";