Categories:

System Functions (System Information)

SYSTEM$PIPE_STATUS

Retrieves a JSON representation of the current status of a pipe.

For more information, see Loading Continuously Using Snowpipe.

Syntax

SYSTEM$PIPE_STATUS( '<pipe_name>' )

Arguments

pipe_name

Pipe for which you want to retrieve the current status.

Usage Notes

  • Returns results only for the pipe owner (i.e. the role with the OWNERSHIP privilege on the pipe) or a role with the MONITOR privilege on the pipe.

  • pipe_name is a string so it must be enclosed in single quotes:

    • Note that the entire name must be enclosed in single quotes, including the database and schema (if the name is fully-qualified), i.e. '<db>.<schema>.<pipe_name>'.

    • If the pipe name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/characters. The double quotes must be enclosed within the single quotes, i.e. '"<pipe_name>"'.

Output

The function returns a JSON object containing the following name/value pairs (if applicable to the current pipe status):

{“executionState”:”<value>”,”oldestFileTimestamp”:<value>,”pendingFileCount”:<value>,”notificationChannelName”:”<value>”,”numOutstandingMessagesOnChannel”:<value>,”lastReceivedMessageTimestamp”:”<value>”,”lastForwardedMessageTimestamp”:”<value>”,”error”:<value>,”fault”:<value>}

Where:

executionState

Current execution state of the pipe. The value could be any one of the following:

  • RUNNING (i.e. everything is normal; Snowflake may or may not be actively processing files for this pipe)

  • STOPPED_CLONED (i.e. the pipe is contained by a database or schema clone)

  • STOPPED_FEATURE_DISABLED

  • STOPPED_STAGE_DROPPED

  • STOPPED_FILE_FORMAT_DROPPED

  • STOPPED_NOTIFICATION_INTEGRATION_DROPPED

  • STOPPED_MISSING_PIPE

  • STOPPED_MISSING_TABLE (the target table defined in the pipe definition was dropped)

  • STALLED_COMPILATION_ERROR

  • STALLED_INITIALIZATION_ERROR

  • STALLED_EXECUTION_ERROR

  • STALLED_INTERNAL_ERROR

  • PAUSED

  • PAUSED_BY_SNOWFLAKE_ADMIN

  • PAUSED_BY_ACCOUNT_ADMIN

oldestFileTimestamp

Earliest timestamp among data files currently queued (if applicable), where the timestamp is set when the file is added to the queue.

pendingFileCount

Number of files queued for loading by the pipe.

This count can increase even if a pipe is paused. Depending on the AUTO_INGEST setting for the pipe, the number of queued files can increase as follows:

AUTO_INGEST = TRUE

Files added to the cloud storage bucket or container trigger new file event notifications for the pipe.

Note that if a paused pipe becomes stale, the pendingFileCount count ignores any event notifications older than the limited retention period.

AUTO_INGEST = FALSE

Calls to the insertFiles REST endpoint trigger files to be queued for loading by the pipe.

notificationChannelName

Amazon SQS queue or Microsoft Azure storage queue associated with the pipe.

numOutstandingMessagesOnChannel

Number of messages in the queue that have been queued but not received yet.

lastReceivedMessageTimestamp

Timestamp of the last message received from the queue. Note that this message might not apply to the specific pipe, e.g., if the path/prefix associated with the message does not match the path/prefix in the pipe definition. In addition, only messages triggered by created data objects are consumed by auto-ingest pipes.

lastForwardedMessageTimestamp

Timestamp of the last “create object” event message with a matching path/prefix that was forwarded to the pipe.

channelErrorMessage

Error message produced when attempting to read messages from the associated Google Cloud Pub/Sub queue or Microsoft Azure Event Grid storage queue.

lastErrorRecordTimestamp

Timestamp of last channel error message (i.e. error message reported in the channelErrorMessage value).

error

Error message produced when the pipe was last compiled for execution (if applicable); often caused by problems accessing the necessary objects (i.e. table, stage, file format) due to permission problems or dropped objects.

fault

Most recent internal Snowflake process error (if applicable). Used primarily by Snowflake for debugging purposes.

Examples

Retrieve the status for a pipe with a case-insensitive name:

SELECT SYSTEM$PIPE_STATUS('mydb.myschema.mypipe');

+---------------------------------------------------+
| SYSTEM$PIPE_STATUS('MYDB.MYSCHEMA.MYPIPE')        |
|---------------------------------------------------|
| {"executionState":"RUNNING","pendingFileCount":0} |
+---------------------------------------------------+

Retrieve the status for a pipe with a case-sensitive name:

SELECT SYSTEM$PIPE_STATUS('mydb.myschema."myPipe"');

+---------------------------------------------------+
| SYSTEM$PIPE_STATUS('MYDB.MYSCHEMA."MYPIPE"')      |
|---------------------------------------------------|
| {"executionState":"RUNNING","pendingFileCount":0} |
+---------------------------------------------------+