How can I load large JSON files from Snowflake Stage?

Question

My company are in the process of migrating from a local DB to a Data Warehouse as the load is too much for SQL Server at the moment. We've looked at what cloud solutions we might be able to use and decided on Snowflake. We need to process quite heavy compressed JSONs of up to 300 MB per file at times. I've read through the documentation, created the stage, file format and table as (json_data variant). I've loaded my first JSON file using SnowSQL CLI and that worked too. The test file is 3.7 kb. When trying to copy into mytable I got this error

Error parsing JSON: document is too large, max size 16777216 bytes

How can I avoid this error without having to split the files before being uploaded in the Stage? The data is being sent by an app so every hour we'd have to load this data in when going live.

This error means that your copy into is likely trying to load the entire document into a single record. Could you provide a sample of the first few lines of a JSON file, as well as the COPY INTO statement you are executing. You might need to add some parameters to help Snowflake parse the file correctly. — Mike Walton

Soni Soni · Accepted Answer · 2020-07-30T03:47:33

I would assume that error "Error parsing JSON: document is too large, max size 16777216 bytes" is caused because snowflake thinks your file has a single json record which practically does not look correct.

In JSON file format, use STRIP_OUTER_ELEMENT = TRUE .. this will turn your single JSON document array into individual JSON records.

Kindly try this and see if this works for you.

Update:

I have tested this and it works without any issues with way bigger single file than max size 16777216.

Steps

--On Worksheet

create or replace stage testdb.testschema.stage_java;

--On SnowSQL

put file://C:/Users/sonij/OneDrive/Desktop/bigjson.json @STAGE_JAVA AUTO_COMPRESS=false SOURCE_COMPRESSION=NONE OVERWRITE=TRUE;

--On Worksheet

create or replace file format bigjson_ff
    type = json
        COMPRESSION = none
        STRIP_OUTER_ARRAY = true
        ALLOW_DUPLICATE = TRUE;

select
    parse_json($1)
from @stage_java/bigjson.json (file_format => 'bigjson_ff');

How can I load large JSON files from Snowflake Stage?

3 Answers