1

Snowkflake documentation for COPY INTO command states (for COPY options)

ON_ERROR = CONTINUE | SKIP_FILE | SKIP_FILE_num | SKIP_FILE_num% | ABORT_STATEMENT

Continue loading the file. The COPY statement returns an error message for a maximum of one error encountered per data file. Note that the difference between the ROWS_PARSED and ROWS_LOADED column values represents the number of rows that include detected errors. However, each of these rows could include multiple errors. To view all errors in the data files, use the VALIDATION_MODE parameter or query the VALIDATE function.

But for me, it just doesn't seem to obey, as I see the default value i.e SKIP_FILE is getting applied as files are getting skipped on any error in the file.

create or replace file format jsonThing type = 'json' DATE_FORMAT='yyyy-mm-dd' 
   TIMESTAMP_FORMAT='YYYY-MM-DD"T"HH24:MI:SSZ' TRIM_SPACE=TRUE NULL_IF=('\\N', 'NULL',''); 

create or replace stage snowflake_json_stage
   storage_integration = snowflake_json_storage_integration
   url = 'azure://snowflakejson.blob.core.windows.net/cdrs'
   file_format = jsonThing
   COPY_OPTIONS = (ON_ERROR=CONTINUE PURGE=TRUE MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE)
   COMMENT='The snowflake json stage';

CREATE or REPLACE PIPE SNOWFLAKE_JSON_PIPE
   AUTO_INGEST = TRUE
   integration = snowflake_json_notification_integration
   as
   COPY INTO purge.public.cdrs
   from @SNOWFLAKE_JSON_STAGE 
   ON_ERROR=CONTINUE 
   MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE;

Do ON_ERROR=CONTINUE options work with PIPE?

NOTE: The file is an NDJSON file.

Noobie
  • 461
  • 1
  • 12
  • 34
  • Where are you seeing `SKIP_FILE` being applied? Is it possible that the whole file is bad and you are just seeing the first issue? – Mike Walton Sep 30 '20 at 14:43
  • No, I cross-check after I remove the bad JSON and reupload the file to Azure the alone JSON (valid) get inserted. Also does not work if change the order of BAD and Valid json in the file. – Noobie Sep 30 '20 at 14:45
  • ON_ERROR=CONTINUE will ignore bad file and attempt to load remaining files, At end it will display the status of each file, Are you referring same here? – Sriga Sep 30 '20 at 16:33
  • @Sriga What I meant was if a file is a NDJSON file. I'm expecting the PIPE to work and insert all the GOOD/VALID JSON and not stop or abort the loading of that file if it encounters a BAD/INVALID JSON. Much like how this guy has explained it https://stackoverflow.com/questions/60156843/how-to-parse-json-efficiently-in-snowpipe-with-on-error-continue?rq=1 just that it does not work for me. Else to the CONITINUE Options does not make sense based on what you are referring since it essentially is `SKIP_FILE` then? – Noobie Sep 30 '20 at 19:00
  • @sriga `CONTINUE` will continue to load records from the file. `SKIP_FILE` will skip the file and move to the next one. – Mike Walton Oct 01 '20 at 00:14
  • Your questions is about whether ON_ERROR works with PIPEs, but I can't see in your question if you tested this with a straight COPY command or not. Can you try it and let us know the result? – Hotchips Oct 01 '20 at 03:26
  • @Hotchips does that matter because if it does doc should be more clear that `ON_ERROR=CONTINUE` is not supported for pipe – Noobie Oct 01 '20 at 03:32
  • @Hotchips also check without PIPE does not work. – Noobie Oct 01 '20 at 06:44
  • Thanks @Noobie. I suspect this is in the interaction of MATCH_BY_COLUMN_NAME and ON_ERROR - I think your best option is to contact Snowflake Support to see what they can tell you about it. – Hotchips Oct 01 '20 at 10:26

0 Answers0