0

I am using Copy command to load data from CSV file to a table using internal Stage.

After loading data I am using below code to get number of rows loaded and failed.

Select * from table(Result_Scan('Copy_Query_ID'))

I am also using below query to get actual failed records:

select * from table(validate("Table_Name",job_id=>'Copy_Query_ID'))

it worked fine few times. But I noticed today that first query shows as below:

Rows_Parsed      Rows_Loaded     Error_Seen
10000            9600            400

So I was expecting 400 rows in second query result but instead I see 10400 records: All rows once and additional 400 records for some other errors. If all rows are error rows then why are they loaded? Can i not use this queries for this purpose?

Note- In my file I have 6 fields but I am using only 4 of them in Copy and rest two fields I am getting using SYSdate(), may be this is the reason for mismatch?

Copy into table(col1,col2,col3,col4,col5,col6) from  ( select $1,$2,$3,$4, sysdate(),'10/20/2020' from %@table)

so I am guessing validate is not looking at my new values for field 5,6 instead it is taking these values from file?

Simon D
  • 5,730
  • 2
  • 17
  • 31
PythonDeveloper
  • 289
  • 1
  • 4
  • 24
  • This is hard to diagnose on StackOverflow because it likely needs Snowflake to look at the backend. I recommend opening a support ticket with your query_id's for these statements. – Mike Walton Oct 25 '20 at 00:46
  • **validate** returns errors for the last executed **COPY** command. When you execute `select * from table(validate(t1, job_id => '_last'));` there is a column that shows the error **CODE**. What is the error code and is it always same code or different ones? – Sergiu Oct 28 '20 at 08:27
  • Error Code is "time format not correct" ( 400 records are inserted with actual error code, and 10000 records are inserted with time format not correct error message). In Actual table col5 is at number 3 in this example, so I think when I run validate it validates against the file ( not against actual Copy which was run), and it tries to match $3 into col5 as part of validate , this might be the reason it gives all rows as error records. But If that's the case how can i Get error records if I use customized Copy commands? – PythonDeveloper Nov 11 '20 at 16:17

0 Answers0