CODEWITHSUNDEEP
snowflake-cloud-data-platform
danD
danD

Reputation: 736

snowflake need to throw error when bulk copy same bucket

Is there a way to force an error when we insert the file from same bucket in same table..

Usually when we copy into a table and if the file is already copied then no data is loaded. and if we need to load it again we use the command.

force=true

However our need is to throw the error when the staging area is not updated. And since the staging area is not under our control (its belong to other team) we cannot do any change there.

Upvotes: 0

Views: 237

Answers (2)

PIG
PIG

Reputation: 602

you can use copy_history table and create one stored procedure use throw to raise error like below. Where is val is the filename you want to copy , if it already loaded it will throw error.

create or replace procedure ifcopied(val varchar)
                                   returns varchar
                                   language javascript
                                   execute as caller
                                   as
                                   $$
                                   var sql_command = `select count(status) from table(information_schema.copy_history(table_name=>'T1', start_time=> dateadd(hours, -1, current_timestamp()))) 
                                                        where file_name ='` + VAL + "'";
                                   var stmt = snowflake.createStatement({sqlText:sql_command});                       
                                   var res = stmt.execute();
                                   res.next();
                                   row_status = res.getColumnValue(1);
                                   if (row_status == 1) {
                                   throw "File Already Loaded"
                                   } else {
                                   var sql_copy = `copy into T1 from @%T1/` + VAL ;
                                   var stmt_copy = snowflake.createStatement({sqlText:sql_copy}).execute();
                                   }
                                   $$
                                   ;
call ifcopied('test5.gz');

Upvotes: 1

Suzy Lockwood
Suzy Lockwood

Reputation: 1180

The definition of FORCE = TRUE from the official Snowflake docs:

Boolean that specifies to load all files, regardless of whether they’ve been loaded previously and have not changed since they were loaded. Note that this option reloads files, potentially duplicating data in a table.

If you have a requirement to use this option but ask if you can throw an error if the file is not updated, then no, the very definition of this option is to load the files again even if they have not changed.

I guess the question remains why you would use this option to load the data again when it hasn't changed (if that is what you are trying to avoid). Perhaps I am misunderstanding what you mean by "staging area is not updated".

Upvotes: 0

Related Questions