Categories:

Table, View, & Sequence 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)

See also:

ALTER STREAM , DROP STREAM , SHOW STREAMS

In this Topic:

Syntax

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

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 ]
  [ ... ]

For more information about cloning, see CREATE <object> … CLONE.

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.

COMMENT = 'string_literal'

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

Default: No value

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

During this private preview, if no stream existed on the table at the point in the past specified in the AT | BEFORE clause, the new stream could return incorrect results.

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

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');