Managing Streams

This topic describes the administrative tasks associated with managing streams.

In this Topic:

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;

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

ALTER VIEW v2 SET CHANGE_TRACKING = TRUE;

Explicitly Enable Change Tracking on the Underlying Tables

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

For example, create a table in the current schema:

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

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

ALTER TABLE t1 SET CHANGE_TRACKING = TRUE;

Avoiding Stream Staleness

To avoid having a stream become stale, we strongly recommend that you regularly consume its change data before its STALE_AFTER timestamp (that is, within the extended data retention period for the source object).

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 may already be already stale. The STALE column also indicates whether the stream is expected to be stale, though the stream may 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.

Back to top