ALTER PIPE¶

Syntax¶

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

ALTER PIPE <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER PIPE <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

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

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

Where:

objectProperties ::=
  PIPE_EXECUTION_PAUSED = TRUE | FALSE

Copy

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):

ERROR_INTEGRATION = 'integration_name'

Required only when configuring Snowpipe to send error notifications to a cloud messaging service. Specifies the name of the notification integration used to communicate with the messaging service. For more information, see Snowpipe error notifications.

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

  Either of the following scenarios requires forcing a pipe to resume by calling the SYSTEM$PIPE_FORCE_RESUME function:

  • Transferring ownership of the pipe to another role. This requirement allows the new owner to evaluate the pipe status and determine how many files are waiting to be loaded by calling the SYSTEM$PIPE_STATUS function.

  • Allowing a pipe object that leverages cloud messaging to trigger data loads (i.e. where AUTO_INGEST = TRUE in the pipe definition) to become stale. A pipe is considered stale when it is paused for longer than the limited retention period for event messages received for the pipe (14 days by default).

Default: FALSE (the pipe is running by default)

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

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

• TAG tag_name [ , tag_name ... ]

• 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

• This SQL command can only load data files that were staged within the last 7 days.

• This SQL command checks the load history for both the pipe and the target table. As a result, the command queues only those files that were not loaded already using either:

  • The same pipe, provided the pipe owner did not recreate the pipe after the files were loaded.

  • A COPY INTO <table> statement.

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 and maximum allowed value is 7 days.

Usage notes¶

• Only the pipe owner (i.e. the role with the OWNERSHIP privilege on the pipe) can set or unset properties on a pipe.

  A non-owner role with the following minimum privileges can refresh a pipe (using ALTER PIPE … REFRESH …):

  Privilege	Object	Notes
  OPERATE	Pipe
  USAGE	Stage in the pipe definition	External stages only
  READ	Stage in the pipe definition	Internal stages only
  SELECT, INSERT	Table in the pipe defintion

  A non-owner role with the OPERATE privilege on the pipe can pause or resume a pipe (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = TRUE | FALSE).

  SQL operations on schema objects also require the USAGE privilege on the database and schema that contain the object.

• Currently, it is not possible to modify the following pipe properties using an ALTER PIPE statement:

  • COPY INTO <table> statement

  • AWS_SNS_TOPIC parameter

  • INTEGRATION parameter

  Instead, recreate the pipe using a CREATE OR REPLACE PIPE statement.

• Regarding metadata:

  Attention

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

Examples¶

Pause the mypipe pipe:

alter pipe mypipe SET PIPE_EXECUTION_PAUSED = true;

Copy

Add or modify the comment for pipe mypipe:

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

Copy