[go: up one dir, main page]
Scribd
Upload
52 views2 pages

Lab9 Troubleshooting Snowpipe AWS

Uploaded by

vr.sf99

This document discusses troubleshooting and managing pipes in Snowflake. It includes steps to validate if a pipe is working, check the copy history, validate a pipe load, and correct a delimiter issue. It also shows how to view pipe details, pause a pipe, modify a copy command in a pipe definition, and resume a paused pipe.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt

Lab9 Troubleshooting Snowpipe AWS

Uploaded by

vr.sf99
52 views2 pages
This document discusses troubleshooting and managing pipes in Snowflake. It includes steps to validate if a pipe is working, check the copy history, validate a pipe load, and correct a delimiter issue. It also shows how to view pipe details, pause a pipe, modify a copy command in a pipe definition, and resume a paused pipe.

Copyright

© © All Rights Reserved

Available Formats

TXT, PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

This document discusses troubleshooting and managing pipes in Snowflake. It includes steps to validate if a pipe is working, check the copy history, validate a pipe load, and correct a delimiter issue. It also shows how to view pipe details, pause a pipe, modify a copy command in a pipe definition, and resume a paused pipe.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
52 views2 pages

Lab9 Troubleshooting Snowpipe AWS

Uploaded by

vr.sf99
This document discusses troubleshooting and managing pipes in Snowflake. It includes steps to validate if a pipe is working, check the copy history, validate a pipe load, and correct a delimiter issue. It also shows how to view pipe details, pause a pipe, modify a copy command in a pipe definition, and resume a paused pipe.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
You are on page 1/ 2

========================

//Trouble shooting Pipes


========================

//Change the delimeter from ',' to '|'


CREATE OR REPLACE file format mydb.file_formats.csv_fileformat
type = csv
field_delimiter = '|'
skip_header = 1
empty_field_as_null = TRUE;

// Test the copy command


COPY INTO mydb.public.emp_data
FROM @mydb.external_stages.stage_aws_pipes
pattern = '.*employee.*';

// Check data loaded in the table or not


SELECT * FROM mydb.public.emp_data;

// Step1: Validate pipe is actually working or not


SELECT SYSTEM$PIPE_STATUS('employee_pipe');

// Step2: Check the Copy History


SELECT * FROM TABLE( INFORMATION_SCHEMA.COPY_HISTORY
(TABLE_NAME => 'mydb.public.emp_data',
START_TIME => DATEADD(HOUR, -10 ,CURRENT_TIMESTAMP()))
);

// Step3: Validate the pipe load


SELECT * FROM TABLE(INFORMATION_SCHEMA.VALIDATE_PIPE_LOAD
(PIPE_NAME => 'mydb.pipes.employee_pipe',
START_TIME => DATEADD(HOUR,-2,CURRENT_TIMESTAMP()))
);

// Correct the delimiter to ','


CREATE OR REPLACE file format mydb.file_formats.csv_fileformat
type = csv
field_delimiter = ','
skip_header = 1
empty_field_as_null = TRUE;

// Load the history files by running Copy command


COPY INTO mydb.public.emp_data
FROM @mydb.external_stages.stage_aws_pipes
FILES = ('sp_employee_3.csv');

// Validate the data in the table


SELECT * FROM mydb.public.emp_data;

===================
// Managing Pipes
===================

// How to see pipes?


DESC PIPE employee_pipe;

SHOW PIPES;
SHOW PIPES like '%employee%';
SHOW PIPES in database mydb;
SHOW PIPES in schema mydb.pipes;
SHOW PIPES like '%employee%' in Database mydb;

// How to pause a pipe


ALTER PIPE mydb.pipes.employee_pipe SET PIPE_EXECUTION_PAUSED = true;

// Want to modify the copy command


CREATE OR REPLACE pipe mydb.pipes.employee_pipe
auto_ingest = TRUE
AS
COPY INTO mydb.public.emp_data2
FROM @mydb.external_stages.stage_aws_pipes
pattern = '.*employee.*';

// How to resume the pipe


ALTER PIPE mydb.pipes.employee_pipe SET PIPE_EXECUTION_PAUSED = false;

You might also like