SHOW PIPES¶

Lists the pipes for which you have access privileges. This command can be used to list the pipes for a specified database or schema (or the current database/schema for the session), or your entire account.

See also:: ALTER PIPE , CREATE PIPE , DESCRIBE PIPE , DROP PIPE

Syntax¶

SHOW PIPES [ LIKE '<pattern>' ]
           [ IN
                {
                  ACCOUNT                                         |

                  DATABASE                                        |
                  DATABASE <database_name>                        |

                  SCHEMA                                          |
                  SCHEMA <schema_name>                            |
                  <schema_name>

                  APPLICATION <application_name>                  |
                  APPLICATION PACKAGE <application_package_name>  |
                }
           ]

Copy

Parameters¶

LIKE 'pattern'

Optionally filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...

... LIKE '%TESTING%' ...

. Default: No value (no filtering is applied to the output).

[ IN ... ]

Optionally specifies the scope of the command. Specify one of the following:

ACCOUNT

Returns records for the entire account.

DATABASE, . DATABASE db_name

Returns records for the current database in use or for a specified database (db_name).

If you specify DATABASE without db_name and no database is in use, the keyword has no effect on the output.

SCHEMA, . SCHEMA schema_name, . schema_name

Returns records for the current schema in use or a specified schema (schema_name).

SCHEMA is optional if a database is in use or if you specify the fully qualified schema_name (for example, db.schema).

If no database is in use, specifying SCHEMA has no effect on the output.

APPLICATION application_name, . APPLICATION PACKAGE application_package_name: Returns records for the named Snowflake Native App or application package.

Default: Depends on whether the session currently has a database in use:

Database: DATABASE is the default (that is, the command returns the objects you have privileges to view in the database).
No database: ACCOUNT is the default (that is, the command returns the objects you have privileges to view in your account).

Usage notes¶

Returns results only for the pipe owner (i.e. the role with the OWNERSHIP privilege on the pipe), a role with the MONITOR or OPERATE privilege on the pipe, or a role with the global MONITOR EXECUTION privilege.
To determine the current status of a pipe, query the SYSTEM$PIPE_STATUS function.

Columns that start with the prefix is_ return either Y (yes) or N (no).
The command does not require a running warehouse to execute.
The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.

To view results for which more than 10K records exist, query the corresponding view (if one exists) in the Snowflake Information Schema.

To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.

Output¶

The command output provides pipe properties and metadata in the following columns:

Column	Description
`created_on`	Date and time when the pipe was created.
`name`	Name of the pipe.
`database_name`	Database in which the pipe is stored.
`schema_name`	Schema in which the pipe is stored.
`definition`	COPY statement used to load data from queued files into a Snowflake table.
`owner`	Name of the role that owns the pipe (i.e. that has the OWNERSHIP privilege on the pipe).
`notification_channel`	Amazon Resource Name of the Amazon SQS queue for the stage named in the DEFINITION column.
`comment`	Comment for this pipe.
`integration`	Name of the notification integration for pipes that rely on notification events to trigger data loads from Google Cloud Storage or Microsoft Azure cloud storage.
`pattern`	PATTERN copy option value in the COPY INTO <table> statement in the pipe definition, if the copy option was specified.
`error_integration`	Notification integration name for pipes that rely on error events in Amazon S3 cloud storage to trigger notifications.
`owner_role_type`	The type of role that owns the object, for example `ROLE`. . If a Snowflake Native App owns the object, the value is `APPLICATION`. . Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.
`invalid_reason`	Displays some detailed information for your pipes that may have issues. You can use the provided information to troubleshoot your pipes more effectively along with SYSTEM$PIPE_STATUS. If there is no issue with the pipe, the value is NULL.
`budget`	Name of the budget if the object is monitored by a budget. NULL otherwise.
`kind`	The kind of the pipe, either KAFKA or STAGE.

Examples¶

Show all the pipes that you have privileges to view in the public schema in the mydb database:

use database mydb;

show pipes;

Copy