Title: Snowflake COPY INTO from JSON - ON_ERROR = CONTINUE - Weird Issue
Tags: sql;snowflake-cloud-data-platform
Question: I am trying to load JSON file from Staging area (S3) into Stage table using COPY INTO command.
Table:
create or replace TABLE stage_tableA  (
RAW_JSON VARIANT NOT NULL
Copy Command:
copy into stage_tableA from @stgS3/filename_45.gz  file_format = (format_name = 'file_json')
Got the below error when executing the above (sample provided)
SQL Error [100069] [22P02]: Error parsing JSON: document is too large, max size 16777216 bytes  If you would like to continue loading
when an error is encountered, use other values such as 'SKIP_FILE' or
'CONTINUE' for the ON_ERROR option. For more information on loading
options, please run 'info loading_data' in a SQL client.
When I had put "ON_ERROR=CONTINUE" , records got partially loaded, i.e  until the record with more than max size. But no records after the Error record was loaded.
Was "ON_ERROR=CONTINUE" supposed to skip only the record that has max size and load records before and after it ?


Here is another answer: Try setting the option strip_outer_array = true for file format and attempt the loading again.
The considerations for loading large size semi-structured data are documented in the below article:
https://docs.snowflake.com/en/user-guide/semistructured-considerations.html

Here is another answer: I partially agree with Chris. The ON_ERROR=CONTINUE option only helps if the there are in fact more than 1 JSON objects in the file. If it's 1 massive object then you would simply not get an error or the record loaded when using ON_ERROR=CONTINUE.
If you know your JSON payload is smaller than 16mb then definitely try the strip_outer_array = true. Also, if your JSON has a lot of nulls ("NULL") as values use the STRIP_NULL_VALUES = TRUE as this will slim your payload as well. Hope that helps.

Here is another answer: Yes, the ON_ERROR=CONTINUE skips the offending line and continues to load the rest of the file.
To help us provide more insight, can you answer the following:
How many records are in your file?
How many got loaded?
At what line was the error first encountered?
You can find this information using the COPY_HISTORY() table function
Comment for this answer: 1. 32361 records
2. 5351 got loaded
3. 5352 is error line
Comment for this answer: Yes, lines after 5352 don't have the max size. I could load the entire file after removing the line 5352 and file got loaded successfully. And yes I understand that record size should be reduced, but it is coming from the source which we don't have control.
Comment for this answer: I see an indirect reference for ON_ERROR=CONTINUE here .
https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html#optional-parameters
Goto -> VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS
Then Check the notes for "RETURN_ALL_ERRORS"
Returns all errors across all files specified in the COPY statement, including files with errors that were partially loaded during an earlier load because the ON_ERROR copy option was set to CONTINUE during the load.
Looks like our understanding is incorrect.
Comment for this answer: Can you confirm that any of the lines after 5,321 do not have the same size limit being exceeded?   You should attempt to reduce your document size before staging your file, 16MB seems large for a json document.
Comment for this answer: I am not sure why the lines after 5352 wouldn't be loading.  This is going to be a support ticket you'll need to log with Snowflake.  Your understanding of the ON_ERROR parameter was correct.  Sorry I could not be more help

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:01/14/20', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><snowflake-cloud-data-platform>', 'Title': 'Snowflake COPY INTO from JSON - ON_ERROR = CONTINUE - Weird Issue', 'ViewCount': 3291, 'id': 59742740, 'user_AboutMe': None, 'user_AccountId': 17529564.0, 'user_DisplayName': 'kars1', 'user_Reputation': 21.0, 'user_Views': 0.0, 'user_id': 12713886.0}}