- 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¶
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; 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_FEATURE_DISABLED
STOPPED_STAGE_DROPPED
STOPPED_FILE_FORMAT_DROPPED
STOPPED_MISSING_PIPE
STOPPED_MISSING_TABLE
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 currently being processed by the pipe. If the pipe is paused, this value will decrease as any files queued before the pipe was paused are processed. When this value is
0
, either there are no files queued for this pipe or the pipe is effectively paused.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.
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} | +---------------------------------------------------+