Managing Snowpipe

This topic describes the administrative tasks associated with managing Snowpipe.

In this Topic:

Pausing and Resuming Pipes

The PIPE_EXECUTION_PAUSED parameter enables pausing or resuming a pipe, typically in preparation for transferring ownership of the pipe. This parameter is supported at the following levels:

  • Account
  • Schema
  • Pipe

At the pipe level, the object owner (or a parent role in a role hierarchy) can set the parameter to pause or resume an individual pipe.

An account administrator (user with the ACCOUNTADMIN role) can set this parameter at the account level to pause or resume all pipes in the account. Likewise, a user with the MODIFY privilege on the schema can pause or resume pipes at the schema level. Note that this larger domain control only affects pipes for which the parameter was not already set at a lower level; e.g., by the owner at the object level.

If ownership of the pipe is transferred to another role after the pipe was paused, it is necessary to force the pipe to resume using SYSTEM$PIPE_FORCE_RESUME. This allows the new owner to evaluate the pipe status and determine how many files are waiting to be loaded using SYSTEM$PIPE_STATUS.

The following example illustrates pausing and resuming pipes at different domain levels:

-- Set the active role for the session to the MYPIPE pipe owner
use role customerole1;

-- Pause the running pipe
alter pipe mpeters_db.public.mypipe set PIPE_EXECUTION_PAUSED=true;

-- Verify the pipe status
select system$pipe_status('mpeters_db.public.mypipe');

+--------------------------------------------------+
| SYSTEM$PIPE_STATUS('MPETERS_DB.PUBLIC.MYPIPE')   |
|--------------------------------------------------|
| {"executionState":"PAUSED","pendingFileCount":0} |
+--------------------------------------------------+

-- Set the active role for the session to a role that has the MODIFY privilege on the schema containing the pipe

use role sysadmin;

-- Pause all running pipes contained in the schema
alter schema PUBLIC set PIPE_EXECUTION_PAUSED=true;

/* The statement pauses all pipes for which the PIPE_EXECUTION_PAUSED parameter was not already explicitly set */

-- Resume running all running pipes contained in the schema
alter schema PUBLIC set PIPE_EXECUTION_PAUSED=false;

-- Verify the pipe status
select system$pipe_status('mpeters_db.public.mypipe');

-- The MYPIPE pipe is still paused
+--------------------------------------------------+
| SYSTEM$PIPE_STATUS('MPETERS_DB.PUBLIC.MYPIPE')   |
|--------------------------------------------------|
| {"executionState":"PAUSED","pendingFileCount":0} |
+--------------------------------------------------+

-- Set the active role for the session to the MYPIPE pipe owner
use role customerole1;

 -- Resume running the paused pipe
alter pipe mpeters_db.public.mypipe set PIPE_EXECUTION_PAUSED=false;

-- Verify the pipe status
select system$pipe_status('mpeters_db.public.mypipe');

-- The MYPIPE pipe is now running
+---------------------------------------------------+
| SYSTEM$PIPE_STATUS('MPETERS_DB.PUBLIC.MYPIPE')    |
|---------------------------------------------------|
| {"executionState":"RUNNING","pendingFileCount":0} |
+---------------------------------------------------+

Modifying the COPY Statement in a Pipe Definition

Complete the following steps to modify the COPY statement in a pipe definition; for example, when columns are added to the target table.

To execute the commands in this section, the current role for the user must have the OWNERSHIP privilege on the pipe.

  1. Pause the pipe by executing ALTER PIPE … SET PIPE_EXECUTION_PAUSED=true.
  2. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is PAUSED and the pending file count is 0.
  3. Recreate the pipe to change the COPY statement in the definition. Choose either of the following options:
  4. Query the SYSTEM$PIPE_STATUS function again and verify that the pipe execution state is RUNNING.

Note

The file loading metadata is associated with the pipe object rather than the table. Recreating the pipe removes the history of files loaded. Ensure that files already loaded by Snowpipe are not accidentally resubmitted to the pipe and loaded into the target table again. To view the query history for a table, query the COPY_HISTORY function.