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.
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.
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';
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';
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";