Monitor Snowpipe and tasks running in Snowflake

To Nha Notes | May 8, 2023, 12:17 p.m.

Pipes

• List pipes

 SHOW PIPES;

• Check pipes status

SELECT SYSTEM$PIPE_STATUS('<PIPE_NAME>');

• Check pipes progress

SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(TABLE_NAME => '<RAW_TABLE_NAME>', START_TIME => DATEADD(HOURS, -3, CURRENT_TIMESTAMP()))) ORDER BY LAST_LOAD_TIME DESC;

• Resume pipes if its execution state is PAUSED

SELECT SYSTEM$PIPE_FORCE_RESUME('<PIPE_NAME>');

• Resume pipes if its execution state is STALLED_COMPILATION_ERROR

SELECT SYSTEM$PIPE_FORCE_RESUME('<PIPE_NAME>');

• Refresh pipes to re-ingestion staged files ingested

ALTER PIPE FAQ_TRACKING.PUBLIC.EVENT_TRACK_PIPE REFRESH;

Tasks

• List tasks

SHOW TASKS;

• Start task

ALTER TASK <TASK_NAME> RESUME;

• Suspend task

ALTER TASK <TASK_NAME> SUSPEND;

• Run task

EXECUTE TASK <TASK_NAME>

• Abort the run of the specified task

SELECT SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS( '<TASK_NAME>');

• Check task execution history

SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(scheduled_time_range_start => DATEADD('hour',-3, CURRENT_TIMESTAMP()), result_limit => 10, task_name=> '<TASK_NAME>')) ORDER BY SCHEDULED_TIME DESC;