5
0
In continuation of previous post, we will be discussing the Case 3, where the File has an outer array structure but the property STRIP_OUTER_ARRAY =TRUE has been removed from the FILE FORMAT.
Case 3 : Consider the same file i.e. Book.json where OUTERARRAY was there and this time we have changed the file format ,i.e. remove the STRIP_OUTER_ARRAY = TRUE from the format in below way:
create or replace file format demo_db.public.json_format1
type = 'json'
[
{
"timestamp": 1565001986254,
"book": [
{
"symbol": "XBTUSD",
"id": 8798792450,
"side": "Sell",
"size": 21580,
"price": 12075.5
},
{
"symbol": "XBTUSD",
"id": 8798792500,
"side": "Sell",
"size": 827344,
"price": 12075
},
{
"symbol": "XBTUSD",
"id": 8798792550,
"side": "Sell",
"size": 1000228,
"price": 12074.5
},
{
"symbol": "XBTUSD",
"id": 8798792600,
"side": "Sell",
"size": 36873,
"price": 12074
},
{
"symbol": "XBTUSD",
"id": 8798895100,
"side": "Buy",
"size": 33760,
"price": 11049
},
{
"symbol": "XBTUSD",
"id": 8798895200,
"side": "Buy",
"size": 3069,
"price": 11048
},
{
"symbol": "XBTUSD",
"id": 8798895250,
"side": "Buy",
"size": 2000,
"price": 11047.5
},
{
"symbol": "XBTUSD",
"id": 8798895300,
"side": "Buy",
"size": 64454,
"price": 11047
},
{
"symbol": "XBTUSD",
"id": 8798895350,
"side": "Buy",
"size": 18854,
"price": 11046.5
},
{
"symbol": "XBTUSD",
"id": 8798895400,
"side": "Buy",
"size": 689,
"price": 11046
},
{
"symbol": "XBTUSD",
"id": 8798895450,
"side": "Buy",
"size": 6550,
"price": 11045.5
},
{
"symbol": "XBTUSD",
"id": 8798895500,
"side": "Buy",
"size": 20650,
"price": 11045
},
{
"symbol": "XBTUSD",
"id": 8798895550,
"side": "Buy",
"size": 17118,
"price": 11044.5
}
]
},
{
"timestamp": 1565001986275,
"book": [
{
"symbol": "XBTUSD",
"id": 8798792450,
"side": "Sell",
"size": 21580,
"price": 12075.5
},
{
"symbol": "XBTUSD",
"id": 8798792500,
"side": "Sell",
"size": 827344,
"price": 12075
},
{
"symbol": "XBTUSD",
"id": 8798792550,
"side": "Sell",
"size": 1000228,
"price": 12074.5
},
{
"symbol": "XBTUSD",
"id": 8798842150,
"side": "Buy",
"size": 736050,
"price": 11578.5
},
{
"symbol": "XBTUSD",
"id": 8798842200,
"side": "Buy",
"size": 58449,
"price": 11578
},
{
"symbol": "XBTUSD",
"id": 8798842250,
"side": "Buy",
"size": 2254,
"price": 11577.5
},
{
"symbol": "XBTUSD",
"id": 8798842300,
"side": "Buy",
"size": 55252,
"price": 11577
},
{
"symbol": "XBTUSD",
"id": 8798842350,
"side": "Buy",
"size": 2564,
"price": 11576.5
},
{
"symbol": "XBTUSD",
"id": 8798842400,
"side": "Buy",
"size": 172251,
"price": 11576
}
]
}
]create or replace stage demo_db.public.ext_json_stage
URL = ‘s3://testsnowflakepro/snowflake/json/json1/’
STORAGE_INTEGRATION = S3_INT file_format = demo_db.public.json_format;
## Create table
create or replace table client2 (json_data variant not null);
## Copy data into table
copy into client2
from @ext_json_stage_1/Book.json
Note: Because of the elimination of STRIP_OUTER_ARRAY = TRUE all the records got loaded into the single row.
Case 4 : Recompose the JSON after reading line by line with in the below way:
CREATE OR REPLACE TABLE client3 (json_data variant);
COPY INTO client3 FROM (SELECT $1 book FROM @ext_json_stage/Book.json);
As we can see two rows are loaded into the table.
Case 5 : Error parsing JSON: document too large : Consider the huge file say 500 MB for this use case, However , While trying to load the data into the table we are getting below error: For instance ….
Error parsing JSON: document too large, max size 16777216 bytes File ‘snowflake/json/json1/citylots.json’, line 55848, character 157 Row 0 starts at line 1, column $1. If you would like to continue loading when an error encounter. Use other values such as ‘SKIP_FILE’ or ‘CONTINUE’ for the ON_ERROR option.
Solution : Need to split the JSON file in chunks , but ensure ,the integrity of JSON should not be compromised as it is not like TEXT or line based files, and the output should not be invalid JSON. Splitting the JSON into Chunks is not part of this post and will discuss entire process in separate thread.
