CREATE DELTA STREAM Statement

Syntax

CREATE [  LOCAL | OUTPUT ] DELTA STREAM name
[ schema_clause ]
primary_key_clause
[ local-declare-block ]
[partition_clause ] 
as_clause
Query;

Components

name	The name of the delta stream being created.
schema_clause	Schema definition for new windows. If no schema clause is specified, it can be derived from the query.
primary_key_clause	Set primary key. See PRIMARY KEY Clause for more information.
local-declare-block	(Optional) A declaration of variables and functions that can be accessed in the query.
partition_clause	(Optional) Creates a specified number of parallel instances of the delta stream. The partition type specifies how to partition the input for the delta stream. See the PARTITION BY Clause for more information.
as_clause	Introduces query to statement.
Query	A query implemented in a statement. See Queries for more information.

Usage

A delta stream is a stateless element that can understand all opcodes. A delta stream can be used when a computation, filter, or union must be performed on the output of a window, but a state does not need be maintained.

A delta stream typically forwards the opcode it receives. However, for a filter, a delta stream modifies the opcode it receives.

An input record with an insert opcode that satisfies the filter clause has an insert opcode on the output. If it does not meet the criteria, no opcode is output.
An input record with an update opcode, where the update meets the criteria but the original record does not, outputs with an insert opcode. However, if the old record meets the criteria, it outputs with an update opcode. If the original insert meets the filter criteria but the update does not, it outputs a delete opcode.
An input record with a delete opcode outputs with a delete opcode, as long as it meets the filter criteria.

CREATE DELTA STREAM is used primarily in computations that transform through a simple projection.

See the <xref href="san1311804134032.xml">Using SPLASH in Flex Operators</xref> topic for more details.

Restrictions

A delta stream cannot use functions that cannot be repeated, such as random() or now(). When a delta stream produces a delete record, the computed column in the record gets recalculated, and as a result, will not match what was originally computed and inserted for the record. Any downstream computation using this column could lead to incorrect results. An update is internally treated as a delete followed by an insert in many contexts and hence an update would also lead to the same issue for delta streams using non-repeatable functions.
When subscribing to a delta stream, the opcodes the delta stream generates must be treated as safe opcodes. This means that any inserts/updates must be treated as upserts (insert if the record does not exist and update otherwise). Similarly, any deletes must be treated as deletes if they exist, otherwise they should be silently ignored.
There are no restrictions on the operations that a target node can perform when using a delta stream as an input.
When the delta stream is defined using a Flex operator, the SPLASH code can output only inserts or deletes. Upserts and updates are not allowed because the delta streams have no state to handle them correctly. To perform an update, issue a delete, followed by an insert.
The query of a delta stream cannot contain clauses that perform aggregation or joins.

Examples

This creates a delta stream that computes total cost:

CREATE INPUT WINDOW Trades SCHEMA  (
  TradeId   long,
   Symbol   string,
   Price    money(4),
   Shares   integer
)
PRIMARY KEY (TradeId)
;

CREATE DELTA stream TradesWithCost
PRIMARY KEY DEDUCED
AS SELECT 
		trd.TradeId, 
		trd.Symbol, 
		trd.Price, 
		trd.Shares, 
		trd.Price * trd.Shares TotalCost
FROM 
		Trades trd
;

This creates a delta stream that filters out records where total cost is less than 10,000:

CREATE DELTA stream LargeTrades
PRIMARY KEY DEDUCED
AS SELECT * FROM TradesWithCost twc WHERE twc.TotalCost >= 10000
;