Categories:
Data Loading / Unloading DDL

ALTER PIPE

Modifies the properties for an existing pipe object. Also supports the following operations:

  • Pausing the pipe.
  • Refreshing a pipe; i.e., copying the specified staged data files to the Snowpipe ingest queue for loading into the target table.
  • Adding/overwriting/removing a comment for a pipe.
See also:
CREATE PIPE , DESCRIBE PIPE , DROP PIPE , SHOW PIPES

Syntax

ALTER PIPE [ IF EXISTS ] <name> SET { [ objProperties ]
                                      [ COMMENT = '<string_literal>' ] }

ALTER PIPE [ IF EXISTS ] <name> UNSET { <property_name> | COMMENT } [ , ... ]

ALTER PIPE [ IF EXISTS ] <name> REFRESH { [ PREFIX = '<path>' ] [ MODIFIED_AFTER = <start_time> ] }

Where:

objProperties ::=
  PIPE_EXECUTION_PAUSED = TRUE | FALSE

Parameters

name
Specifies the identifier for the pipe to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.
SET ...

Specifies one (or more) properties to set for the pipe (separated by blank spaces, commas, or new lines):

PIPE_EXECUTION_PAUSED = TRUE | FALSE

Specifies whether to pause a running pipe, typically in preparation for transferring ownership of the pipe:

  • TRUE pauses the pipe. The executionState reported by SYSTEM$PIPE_STATUS is PAUSED. Note that the pipe owner can continue to submit files to a paused pipe; however, they won’t be processed until the pipe is resumed.

  • FALSE resumes the pipe. The executionState reported by SYSTEM$PIPE_STATUS is RUNNING.

    Note

    If ownership of the pipe is transferred to another role after the pipe was paused, it is necessary to force the pipe to resume using SYSTEM$PIPE_FORCE_RESUME. This allows the new owner to evaluate the pipe status and determine how many files are waiting to be loaded using SYSTEM$PIPE_STATUS.

Default: FALSE (the pipe is running by default)

COMMENT = 'string'
Adds a comment or overwrites an existing comment for the pipe.
UNSET ...

Specifies one (or more) properties to unset for the pipe, which resets them to the defaults:

  • PIPE_EXECUTION_PAUSED
  • COMMENT

You can reset multiple properties with a single ALTER statement; however, each property must be separated by a comma. When resetting a property, specify only the name; specifying a value for the property will return an error.

REFRESH

Copies a set of staged data files to the Snowpipe ingest queue for loading into the target table. This clause accepts an optional path and can further filter the list of files to load based on a specified start time.

Note

An ALTER PIPE .. REFRESH statement can only load data files that were staged within the last 7 days.

Important

The REFRESH functionality is intended for short term use to resolve specific issues when Snowpipe fails to load a subset of files and is not intended for regular use.

PREFIX = 'path'

Path (or prefix) appended to the stage reference in the pipe definition. The path limits the set of files to load. Only files that start with the specified path are included in the data load.

For example, suppose the pipe definition references @mystage/path1/. If the path value is d1/, the ALTER PIPE statement limits loads to files in the @mystage stage with the /path1/d1/ path. See the examples for more information.

Note that the path must be enclosed in single quotes.

MODIFIED_AFTER = 'start_time'

Timestamp (in ISO-8601 format) of the oldest data files to copy into the Snowpipe ingest queue based on the LAST_MODIFIED date (i.e. date when a file was staged).

The default value is 7 days.

Examples

Pause the mypipe pipe:

alter pipe mypipe SET PIPE_EXECUTION_PAUSED = true;

Add or modify the comment for pipe mypipe:

alter pipe mypipe SET COMMENT = "Pipe for North American sales data";

Refreshing a Pipe

Set up for examples:

CREATE PIPE mypipe AS COPY INTO mytable FROM @mystage/path1/;

Load data files from the @mystage/path1/ stage and path into the mytable table, as defined in the mypipe pipe definition:

ALTER PIPE mypipe REFRESH;

Same as the previous example, but append d1 to the path to further limit the list of files to load. In the current example, the statement loads files from the @mystage/path1/d1/ stage and path:

ALTER PIPE mypipe REFRESH PREFIX='d1/';

Same as the previous example, but only load files staged after a specified timestamp:

ALTER PIPE mypipe REFRESH PREFIX='d1/' MODIFIED_AFTER='2018-07-30T13:56:46-07:00';