pipes

Creates, updates, deletes, gets or lists a pipes resource.

Overview

Name	pipes
Type	Resource
Id	snowflake.pipelines.pipes

Fields

The following fields are returned by SELECT queries:

get

successful

Name	Datatype	Description
name	string	Name of the pipe
database_name	string	Database in which the pipe is stored
schema_name	string	Schema in which the pipe is stored
auto_ingest	boolean	TRUE if all files from stage need to be auto-ingested
aws_sns_topic	string	Optional, if provided, auto_ingest pipe will only receive messages from this SNS topic.
budget	string	Name of the budget if the pipe is monitored by a budget
comment	string	user comment associated to an object in the dictionary
copy_statement	string	COPY INTO <table> statement used to load data from queued files into a Snowflake table. This statement serves as the text/definition for the pipe and is displayed in the SHOW PIPES output (pattern: (?i)^COPY INTO .*)
created_on	string (date-time)	Date and time when the pipe was created.
error_integration	string	Link to integration object that point to a user provided Azure storage queue / SQS. When present, errors (e.g. ingest failure for Snowpipe or a user task failure or replication failure) will be sent to this queue to notify customers
integration	string	Link to integration object that ties a user provided storage queue to an auto_ingest enabled pipe. Required for auto_ingest to work on azure.
invalid_reason	string	Displays some detailed information for your pipes that may have issues
owner	string	Role that owns the pipe
owner_role_type	string	The type of role that owns the pipe
pattern	string	PATTERN copy option value in the COPY INTO <table> statement in the pipe definition, if the copy option was specified.

list

A Snowflake pipe

Name	Datatype	Description
name	string	Name of the pipe
database_name	string	Database in which the pipe is stored
schema_name	string	Schema in which the pipe is stored
auto_ingest	boolean	TRUE if all files from stage need to be auto-ingested
aws_sns_topic	string	Optional, if provided, auto_ingest pipe will only receive messages from this SNS topic.
budget	string	Name of the budget if the pipe is monitored by a budget
comment	string	user comment associated to an object in the dictionary
copy_statement	string	COPY INTO <table> statement used to load data from queued files into a Snowflake table. This statement serves as the text/definition for the pipe and is displayed in the SHOW PIPES output (pattern: (?i)^COPY INTO .*)
created_on	string (date-time)	Date and time when the pipe was created.
error_integration	string	Link to integration object that point to a user provided Azure storage queue / SQS. When present, errors (e.g. ingest failure for Snowpipe or a user task failure or replication failure) will be sent to this queue to notify customers
integration	string	Link to integration object that ties a user provided storage queue to an auto_ingest enabled pipe. Required for auto_ingest to work on azure.
invalid_reason	string	Displays some detailed information for your pipes that may have issues
owner	string	Role that owns the pipe
owner_role_type	string	The type of role that owns the pipe
pattern	string	PATTERN copy option value in the COPY INTO <table> statement in the pipe definition, if the copy option was specified.

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
get	select	database_name, schema_name, name, endpoint		Fetch a pipe
list	select	database_name, schema_name, endpoint	like	List pipes
create	insert	database_name, schema_name, endpoint, name, copy_statement	createMode	Create a pipe
delete	delete	database_name, schema_name, name, endpoint	ifExists	Delete a pipe
refresh	exec	database_name, schema_name, name, endpoint	ifExists, prefix, modified_after	Refresh the pipe

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
database_name	string	Identifier (i.e. name) for the database to which the resource belongs. You can use the /api/v2/databases GET request to get a list of available databases.
endpoint	string	Organization and account identifier (orgname-accountname) (default: orgname-accountname)
name	string	Identifier (i.e. name) for the resource.
schema_name	string	Identifier (i.e. name) for the schema to which the resource belongs. You can use the /api/v2/databases/{database}/schemas GET request to get a list of available schemas for the specified database.
createMode	string	Parameter allowing support for different modes of resource creation. Possible values include: - errorIfExists: Throws an error if you try to create a resource that already exists. - orReplace: Automatically replaces the existing resource with the current one. - ifNotExists: Creates a new resource when an alter is requested for a non-existent resource.
ifExists	boolean	Parameter that specifies how to handle the request for a resource that does not exist: - true: The endpoint does not throw an error if the resource does not exist. It returns a 200 success response, but does not take any action on the resource. - false: The endpoint throws an error if the resource doesn't exist.
like	string	Parameter to filter the command output by resource name. Uses case-insensitive pattern matching, with support for SQL wildcard characters.
modified_after	string (date-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)
prefix	string	Path (or prefix) appended to the stage reference in the pipe definition. The path limits the set of files to load.

SELECT examples

get

Fetch a pipe

SELECT
name,
database_name,
schema_name,
auto_ingest,
aws_sns_topic,
budget,
comment,
copy_statement,
created_on,
error_integration,
integration,
invalid_reason,
owner,
owner_role_type,
pattern
FROM snowflake.pipelines.pipes
WHERE database_name = '{{ database_name }}' -- required
AND schema_name = '{{ schema_name }}' -- required
AND name = '{{ name }}' -- required
AND endpoint = '{{ endpoint }}' -- required
;

list

List pipes

SELECT
name,
database_name,
schema_name,
auto_ingest,
aws_sns_topic,
budget,
comment,
copy_statement,
created_on,
error_integration,
integration,
invalid_reason,
owner,
owner_role_type,
pattern
FROM snowflake.pipelines.pipes
WHERE database_name = '{{ database_name }}' -- required
AND schema_name = '{{ schema_name }}' -- required
AND endpoint = '{{ endpoint }}' -- required
AND "like" = '{{ like }}'
;

INSERT examples

create

Create a pipe

INSERT INTO snowflake.pipelines.pipes (
name,
comment,
auto_ingest,
error_integration,
aws_sns_topic,
integration,
copy_statement,
database_name,
schema_name,
endpoint,
createMode
)
SELECT 
'{{ name }}' /* required */,
'{{ comment }}',
{{ auto_ingest }},
'{{ error_integration }}',
'{{ aws_sns_topic }}',
'{{ integration }}',
'{{ copy_statement }}' /* required */,
'{{ database_name }}',
'{{ schema_name }}',
'{{ endpoint }}',
'{{ createMode }}'
RETURNING
status
;

Manifest

# Description fields are for documentation purposes
- name: pipes
props:
  - name: database_name
    value: "{{ database_name }}"
    description: Required parameter for the pipes resource.
  - name: schema_name
    value: "{{ schema_name }}"
    description: Required parameter for the pipes resource.
  - name: endpoint
    value: "{{ endpoint }}"
    description: Required parameter for the pipes resource.
  - name: name
    value: "{{ name }}"
    description: |
      Name of the pipe
  - name: comment
    value: "{{ comment }}"
    description: |
      user comment associated to an object in the dictionary
  - name: auto_ingest
    value: {{ auto_ingest }}
    description: |
      TRUE if all files from stage need to be auto-ingested
  - name: error_integration
    value: "{{ error_integration }}"
    description: |
      Link to integration object that point to a user provided Azure storage queue / SQS. When present,  errors (e.g. ingest failure for Snowpipe or a user task failure or replication failure) will be sent to this queue to notify customers
  - name: aws_sns_topic
    value: "{{ aws_sns_topic }}"
    description: |
      Optional, if provided, auto_ingest pipe will only receive messages from this SNS topic.
  - name: integration
    value: "{{ integration }}"
    description: |
      Link to integration object that ties a user provided storage queue to an auto_ingest enabled pipe. Required for auto_ingest to work on azure.
  - name: copy_statement
    value: "{{ copy_statement }}"
    description: |
      COPY INTO <table> statement used to load data from queued files into a Snowflake table. This statement serves as the text/definition for the pipe and is displayed in the SHOW PIPES output
  - name: createMode
    value: "{{ createMode }}"
    description: Parameter allowing support for different modes of resource creation. Possible values include: - `errorIfExists`: Throws an error if you try to create a resource that already exists. - `orReplace`: Automatically replaces the existing resource with the current one. - `ifNotExists`: Creates a new resource when an alter is requested for a non-existent resource.
    description: Parameter allowing support for different modes of resource creation. Possible values include: - `errorIfExists`: Throws an error if you try to create a resource that already exists. - `orReplace`: Automatically replaces the existing resource with the current one. - `ifNotExists`: Creates a new resource when an alter is requested for a non-existent resource.

DELETE examples

delete

Delete a pipe

DELETE FROM snowflake.pipelines.pipes
WHERE database_name = '{{ database_name }}' --required
AND schema_name = '{{ schema_name }}' --required
AND name = '{{ name }}' --required
AND endpoint = '{{ endpoint }}' --required
AND ifExists = '{{ ifExists }}'
;

Lifecycle Methods

refresh

Refresh the pipe

EXEC snowflake.pipelines.pipes.refresh 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}, 
@prefix='{{ prefix }}', 
@modified_after='{{ modified_after }}'
;