Snowflake - TroubleShooting SnowPipe - Working Session

You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.
My Snowflake Udemy Course:
www.udemy.com/...
I can be reachable on [e-mail].
========================
//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 ='greaterthan_symbol' 'mydb.public.emp_data',
START_TIME ='greaterthan_symbol' DATEADD(HOUR, -10 ,CURRENT_TIMESTAMP()))
);
// Step3: Validate the pipe load
SELECT * FROM TABLE(INFORMATION_SCHEMA.VALIDATE_PIPE_LOAD
(PIPE_NAME ='greaterthan_symbol' 'mydb.pipes.employee_pipe',
START_TIME ='greaterthan_symbol' 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;

[@renukas9910]

Simply super & awesome explanation ,each and every video in the snowflake playlist .....

[@mayanknema3007]

The way you aligned your explanation with the snowflake environment is simply outstanding. Its just go wit the flow, so easy to understand & grasp. Simply superb. Keep doing the good work.

[@msenthilkumar1124]

Thank you Janardhan, For giving this wonderfull knowledge on snipipe like spoon feeding,Expecting the same in future,Godd luck.

[@yamunau.yamuna5189]

I have finded good snowflake channel really it was very helpful Thank you

[@venkataramudu5865]

Thanks for sharing good knowledge 😍sir

[@user-hk4sv1wx6z]

Hi bro,Really this video is very useful ,Thanks for that,can you make video for troubleshooting bulk loading from AWS to snowflake

[@rkprashanth]

The explanation and ppts all are very helpful, Janardhan. But I have one doubt, at 23:08 we know one file is pending to upload as you have uploaded 3rd file which has some error in file format. But in pipe_status, pendingFilecount is still showing 0 not 1. Why it is showing 0 instead of 1? Can you please let me know?

[@naveenyalamala]

Hi Sir , Even though you changed file format in step 1 , How snowpipe Successfully loaded it ? the which you loaded in s3 bucket has field delimiter is ' , ' but in file format in snowflake you changed to ' | ' then how snowpipe load it Successfully ?(lastReceivedTimeStamp and lastForwadedtimeStamp are similar based on this I thought file was loaded successful)

[@mrjana520]

Watch and listen carefully, you will understand, answer is there in that video itself

[@StockGarjanaHindi]

The third file is not loades if you see, only the rows were parsed.

[@rashuraj8892]

Sir please can u help me everything is fine bt iam getting like pipe is in cloned state, though i hv done alter pipe too

[@mrjana520]

reach me on [e-mail]

[@swarajmehta3011]

hi, is there any way to see which rows causing the error while loading the data

[@mrjana520]

yes, by using copy option VALIDATION_MODE = RETURN_ERRORS you can see the error records. Watch my Copy Options video for more details.

[@divityvali8454]

Sir can you mention topic wise lessons ,from installtion after what like that can you mention step by step

[@mrjana520]

You can follow the playlist