pipes

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

Overview

Name	pipes
Type	Resource
Id	snowflake.pipe.pipes

Fields

Name	Datatype	Description
name	string	Name of the pipe
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 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
created_on	string	Date and time when the pipe was created.
database_name	string	Database in which the pipe is stored
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 statement in the pipe definition, if the copy option was specified.
schema_name	string	Schema in which the pipe is stored

Methods

Name	Accessible by	Required Params	Optional Params	Description
fetch_pipe	SELECT	database_name, name, schema_name, endpoint	-	Fetch a pipe
list_pipes	SELECT	database_name, schema_name, endpoint	like	List pipes
create_pipe	INSERT	database_name, schema_name, data__copy_statement, data__name, endpoint	createMode	Create a pipe
delete_pipe	DELETE	database_name, name, schema_name, endpoint	ifExists	Delete a pipe
refresh_pipe	EXEC	database_name, name, schema_name, endpoint	ifExists, prefix, modified_after	Refresh the pipe

Optional Parameter Details

Name	Description	Type	Default
createMode	Query 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.	string	errorIfExists
ifExists	Query 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.	boolean	false
like	Query parameter to filter the command output by resource name. Uses case-insensitive pattern matching, with support for SQL wildcard characters.	string	-
modified_after	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)	string	-
prefix	Path (or prefix) appended to the stage reference in the pipe definition. The path limits the set of files to load.	string	-

SELECT examples

List pipes

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

INSERT example

Use the following StackQL query and manifest file to create a new pipes resource.

Required Properties

/*+ create */
INSERT INTO snowflake.pipe.pipes (
data__name,
data__comment,
data__auto_ingest,
data__error_integration,
data__aws_sns_topic,
data__integration,
data__copy_statement,
database_name,
schema_name,
endpoint
)
SELECT 
'{{ name }}',
'{{ comment }}',
'{{ auto_ingest }}',
'{{ error_integration }}',
'{{ aws_sns_topic }}',
'{{ integration }}',
'{{ copy_statement }}',
'{{ database_name }}',
'{{ schema_name }}',
'{{ endpoint }}'
;

All Properties

/*+ create */
INSERT INTO snowflake.pipe.pipes (
data__name,
data__copy_statement,
database_name,
schema_name,
endpoint
)
SELECT 
'{{ name }}',
'{{ copy_statement }}',
'{{ database_name }}',
'{{ schema_name }}',
'{{ endpoint }}'
;

Manifest

- name: pipes
  props:
    - name: database_name
      value: string
    - name: schema_name
      value: string
    - name: data__copy_statement
      value: string
    - name: data__name
      value: string
    - name: endpoint
      value: string
    - name: name
      value: string
    - name: comment
      value: string
    - name: auto_ingest
      value: boolean
    - name: error_integration
      value: string
    - name: aws_sns_topic
      value: string
    - name: integration
      value: string
    - name: copy_statement
      value: string

DELETE example

Deletes the specified pipes resource.

/*+ delete */
DELETE FROM snowflake.pipe.pipes
WHERE database_name = '{{ database_name }}'
AND name = '{{ name }}'
AND schema_name = '{{ schema_name }}'
AND endpoint = '{{ endpoint }}';