Categories:

Data Pipeline DDL

CREATE STREAM¶

Creates a new stream in the current/specified schema or replaces an existing stream. A stream records data manipulation language (DML) changes made to a table, including information about inserts, updates, and deletes. The table for which changes are recorded is called the source table.

In addition, this command supports the following variant:

• CREATE STREAM … CLONE (creates a clone of an existing stream)

In this Topic:

Syntax¶

-- Table stream
CREATE [ OR REPLACE ] STREAM [IF NOT EXISTS]
<name>
[ COPY GRANTS ]
ON TABLE <table_name>
[ { AT | BEFORE } { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ]
[ APPEND_ONLY = TRUE | FALSE ]
[ COMMENT = '<string_literal>' ]


Variant Syntax¶

CREATE STREAM … CLONE

Creates a new stream with the same definition as the source stream. The clone inherits the current offset (i.e. the current transactional version of the table) from the source stream.

CREATE [ OR REPLACE ] STREAM <name> CLONE <source_stream>
[ COPY GRANTS ]
[ ... ]


Required Parameters¶

name

String that specifies the identifier (i.e. name) for the stream; must be unique for the schema in which the stream is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements.

table_name

String that specifies the identifier (i.e. name) for the table whose changes are tracked by the stream (i.e. the source table).

Access control

To query a stream, a role must have the SELECT privilege on the underlying table.

Optional Parameters¶

COPY GRANTS

Specifies to retain the access permissions from the original stream when a new stream is created using any of the following CREATE STREAM variants:

• CREATE OR REPLACE STREAM

• CREATE STREAM … CLONE

The parameter copies all permissions, except OWNERSHIP, from the existing stream to the new stream. By default, the role that executes the CREATE STREAM command owns the new stream.

Note

• If the CREATE STREAM statement references more than one stream (e.g. create or replace stream t1 clone t2;), the COPY GRANTS clause gives precedence to the stream being replaced.

• The SHOW GRANTS output for the replacement stream lists the grantee for the copied privileges as the role that executed the CREATE STREAM statement, with the current timestamp when the statement was executed.

• The operation to copy grants occurs atomically in the CREATE STREAM command (i.e. within the same transaction).

Note

This parameter is not supported currently.

 AT | BEFORE TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id>

Creates a stream on a table at a specific time/point in the past (using Time Travel). The AT | BEFORE clause determines the point in the past from which historical data is requested for the table:

• The AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with a timestamp equal to the specified parameter.

• The BEFORE keyword specifies that the request refers to a point immediately preceding the specified parameter.

Note

Currently, a stream must be created on a table before change tracking information is recorded for the table. If no stream existed on the table at the point in the past specified in the AT | BEFORE clause, the CREATE STREAM statement fails. No stream can be created at a time in the past before change tracking was recorded.

APPEND_ONLY = TRUE | FALSE

Specifies whether this is an append-only stream. Append-only streams track row inserts only. Update and delete operations (including table truncates) are not recorded. For example, if 10 rows are inserted into a table and then 5 of those rows are deleted before the offset for an append-only stream is advanced, the stream records 10 rows.

This type of stream improves query performance over standard streams and is very useful for extract, load, transform (ELT) and similar scenarios that depend exclusively on row inserts.

A standard stream joins the deleted and inserted rows in the change set to determine which rows were deleted and which were updated. An append-only stream returns the appended rows only and therefore can be much more performant than a standard stream. For example, the source table can be truncated immediately after the rows in an append-only stream are consumed, and the record deletions do not contribute to the overhead the next time the stream is queried or consumed.

Default

FALSE

COMMENT = 'string_literal'

String (literal) that specifies a comment for the table.

Default: No value

Output¶

The output for a stream includes the same columns as the source table along with the following additional columns:

• METADATA$ACTION: Specifies the action (INSERT or DELETE). • METADATA$ISUPDATE: Specifies whether the action recorded (INSERT or DELETE) is part of an UPDATE applied to the rows in the source table.

Note that streams record the differences between two offsets. If a row is added and then updated in the current offset, the delta change is a new row. The METADATA$ISUPDATE row records a FALSE value. • METADATA$ROW_ID: Specifies the unique and immutable ID for the row, which can be used to track changes to specific rows over time.

Usage Notes¶

• Creating a stream requires a role that has been explicitly granted the following privileges, along with the USAGE privileges on the database and schema:

• Schema: CREATE STREAM

• Source table: SELECT

• A stream can be queried multiple times to update multiple objects in the same transaction and it will return the same data.

• The stream position (i.e. offset) is advanced when the stream is used in a DML statement. The position is updated at the end of the transaction to the beginning timestamp of the transaction. The stream describes change records starting from the current position of the stream and ending at the current transactional timestamp.

To ensure multiple statements access the same change records in the stream, surround them with an explicit transaction statement (BEGIN .. COMMIT). An explicit transaction locks the stream, so that DML updates to the source table are not reported to the stream until the transaction is committed.

• Streams have no Fail-safe period or Time Travel retention period. The metadata in these objects cannot be recovered if a stream is dropped.

• When the first stream for a table is created, a pair of hidden columns are added to the table and begin storing change tracking metadata. The columns consume a small amount of storage.

Examples¶

Creating a Table Stream¶

Create a stream on the mytable table:

CREATE STREAM mystream ON TABLE mytable;


Using Time Travel with the Source Table¶

Create a stream on the mytable table as it existed before the date and time in the specified timestamp:

CREATE STREAM mystream ON TABLE mytable BEFORE (TIMESTAMP => TO_TIMESTAMP(40*365*86400));


Create a stream on the mytable table as it existed exactly at the date and time of the specified timestamp:

CREATE STREAM mystream ON TABLE mytable AT (TIMESTAMP => TO_TIMESTAMP_TZ('02/02/2019 01:02:03', 'mm/dd/yyyy hh24:mi:ss'));


Create a stream on the mytable table as it existed 5 minutes ago:

CREATE STREAM mystream ON TABLE mytable AT(OFFSET => -60*5);


Create a stream on the mytable table including transactions up to, but not including any changes made by the specified transaction:

CREATE STREAM mystream ON TABLE mytable BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');