Managing Streams

This topic describes the administrative tasks associated with managing streams.

Enabling Change Tracking on Views and Underlying Tables

In order for users to query change data on a view, change tracking must be enabled on the view and underlying tables.

Only the object owner (i.e. the role with the OWNERSHIP privilege) on a given view or underlying tables can enable change tracking.

The following options are available to enable change tracking:

  1. Create a stream on the view using the view owner role. This action enables change tracking on the view.

    If the same role also owns the underlying tables, change tracking is also enabled on the tables. Otherwise, the table owner must explicitly enable change tracking on the tables. For these steps, see Explicitly Enable Change Tracking on the Underlying Tables (in this topic).

  2. Explicitly enable change tracking on the view and tables. For instructions, see the remaining instructions in this section.

Explicitly Enable Change Tracking on Views

Set the CHANGE_TRACKING parameter when creating a view (using CREATE VIEW) or later (using ALTER VIEW).

Note that change tracking must also be explicitly enabled on the underlying tables for a view. For instructions, see Explicitly Enable Change Tracking on the Underlying Tables (in this topic).

For example, create a secure view in the current schema that selects a subset of rows from a table:

CREATE SECURE VIEW v CHANGE_TRACKING = TRUE AS SELECT col1, col2 FROM t;
Copy

For example, modify an existing view to enable change tracking:

ALTER VIEW v2 SET CHANGE_TRACKING = TRUE;
Copy

Explicitly Enable Change Tracking on the Underlying Tables

Important

When either creating or altering a view to specify CHANGE_TRACKING, the associated dependent database objects are automatically updated to enable change tracking. During the operation, the underlying resources are locked, which can cause latency for DDL/DML operations. For more information, refer to Resource locking.

If the user executing the statement has not specified a role with sufficient permissions (OWNERSHIP), the statement will fail, underlying database objects will not updated, and locks will be released.

Set the CHANGE_TRACKING parameter when creating a table (using CREATE TABLE) or later (using ALTER TABLE).

For example, to create a table in the current schema:

CREATE TABLE t (col1 STRING, col2 NUMBER) CHANGE_TRACKING = TRUE;
Copy

For example,to modify an existing table to enable change tracking:

ALTER TABLE t1 SET CHANGE_TRACKING = TRUE;
Copy

Important

When either creating or altering a TABLE to specify CHANGE_TRACKING, the table is locked for the duration of the operation which can cause latency for DML operations. For more information, refer to Resource locking.

Avoiding Stream Staleness

To prevent a stream from becoming stale, consume the stream records within a DML statement during the table’s retention period and regularly consume its change data before its STALE_AFTER timestamp (that is, within the extended data retention period for the source object).. Additionally, calling SYSTEM$STREAM_HAS_DATA on the stream prevents it from becoming stale, provided the stream is empty and the SYSTEM$STREAM_HAS_DATA function returns FALSE.

For more information on data retention periods, see Understanding & using Time Travel.

To view the data retention period for a stream, execute the DESCRIBE STREAM or SHOW STREAMS command. The STALE_AFTER column timestamp indicates when the stream is currently predicted to become stale (or when it became stale, if the timestamp is in the past). This timestamp is calculated by adding the larger of the DATA_RETENTION_TIME_IN_DAYS or MAX_DATA_EXTENSION_TIME_IN_DAYS parameter setting to the current timestamp. Note that if the timestamp is in the past, the stream might already be stale. The STALE column also indicates whether the stream is expected to be stale, though the stream might not actually be stale yet.

Consuming the change data for a stream moves the STALE_AFTER timestamp forward.

For more information, see Data Retention Period and Staleness.

View and Manage Streams in Snowsight

To view and manage a stream in Snowsight, do the following:

  1. Sign in to Snowsight.

  2. Select Data » Databases.

  3. For a specific database and schema, select Streams and select the stream you want to manage.

When viewing the stream in Snowsight, you can do the following:

  • In the Details section, review the table name to which the stream applies, the type of stream, and whether or not the stream is stale.

  • Review the SQL statement used to create the stream.

  • Manage privileges on the stream. See Manage object privileges with Snowsight.

Language: English