Loading JSON in Snowflake

Question

a newbie needs help with loading a JSON file into Snowflake using SnowSQL CLI. A snippet of the JSON file looks like below(it's a lot longer in fact):

        {"year":"2012","_113_cause_name":"Nephritis, nephrotic syndrome and nephrosis (N00-  N07,N17-N19,N25-N27)","cause_name":"Kidney disease","state":"Vermont","deaths":"21","aadr":"2.6"}
        ,{"year":"2017","_113_cause_name":"Nephritis, nephrotic syndrome and nephrosis (N00-N07,N17-N19,N25-N27)","cause_name":"Kidney disease","state":"Vermont","deaths":"29","aadr":"3.3"}
,{"year":"2016","_113_cause_name":"Nephritis, nephrotic syndrome and nephrosis (N00-N07,N17-N19,N25-N27)","cause_name":"Kidney disease","state":"Vermont","deaths":"30","aadr":"3.7"}
,{"year":"2013","_113_cause_name":"Nephritis, nephrotic syndrome and nephrosis (N00-N07,N17-N19,N25-N27)","cause_name":"Kidney disease","state":"Vermont","deaths":"30","aadr":"3.8"}
,{"year":"2000","_113_cause_name":"Intentional self-harm (suicide) (*U03,X60-X84,Y87.0)","cause_name":"Suicide","state":"District of Columbia","deaths":"23","aadr":"3.8"}
,{"year":"2014","_113_cause_name":"Nephritis, nephrotic syndrome and nephrosis (N00-N07,N17-N19,N25-N27)","cause_name":"Kidney disease","state":"Arizona","deaths":"325","aadr":"4.1"}
,{"year":"2009","_113_cause_name":"Intentional self-harm (suicide) (*U03,X60-X84,Y87.0)","cause_name":"Suicide","state":"District of Columbia","deaths":"29","aadr":"4.4"}
,{"year":"2015","_113_cause_name":"Nephritis, nephrotic syndrome and nephrosis (N00-N07,N17-N19,N25-N27)","cause_name":"Kidney disease","state":"Vermont","deaths":"39","aadr":"4.5"}
,{"year":"2014","_113_cause_name":"Nephritis, nephrotic syndrome and nephrosis (N00-N07,N17-N19,N25-N27)","cause_name":"Kidney disease","state":"Vermont","deaths":"37","aadr":"4.5"}
,{"year":"2011","_113_cause_name":"Nephritis, nephrotic syndrome and nephrosis (N00-N07,N17-N19,N25-N27)","cause_name":"Kidney disease","state":"South Dakota","deaths":"49","aadr":"4.5"}
,{"year":"2015","_113_cause_name":"Intentional self-harm (suicide) (*U03,X60-  X84,Y87.0)","cause_name":"Suicide","state":"District of Columbia","deaths":"34","aadr":"4.9"}````

I need to load this JSON into an internal stage and into an intermediary table with variant type column , then load it into the relational table. My SQL script works but only brings the first row of the JSON file. See my SQL below:

create or replace table CDC_SCHEMA.cdc_data(
  year string,
  _113_cause_name string,
  cause_name string,
  state string,
  deaths integer,
  aadr string
  );
        create or replace file format CDC_DB.CDC_SCHEMA.sf_tut_csv_format
  field_delimiter = none
  record_delimiter = '\\n';
        
        create or replace temporary stage CDC_DB.CDC_SCHEMA.sf_tut_stage
  file_format = CDC_DB.CDC_SCHEMA.sf_tut_csv_format; 
        
        put file:///Users/rfrenkel/Downloads/LeadingCausesofDeath_noOA.json
 @CDC_DB.CDC_SCHEMA.sf_tut_stage; 
        copy into CDC_SCHEMA.cdc_data(year,
  _113_cause_name,
  cause_name,
  state,
  deaths,
  aadr)
   from (select parse_json($1):year, parse_json($1):_113_cause_name,
                parse_json($1):cause_name, parse_json($1):state, parse_json($1):deaths,
 parse_json($1):aadr
         from @sf_tut_stage/LeadingCausesofDeath_noOA.json.gz t)
   on_error = 'continue';

/* Query the relational table                                                                                 */

        select * from cdc_data;

The last statement only returns the first line of the JSON file in the new table CDC_DATA. What am I doing wrong in parsing this JSON ? Any help is appreciated...

Dwayne Brown Dwayne Brown · Accepted Answer · 2021-01-02T02:11:38

It looks like you're using a delimited file format.

Try using the following file format and temporary stage instead:

create or replace file format CDC_DB.CDC_SCHEMA.JSON_FILE_FORMAT
TYPE = 'JSON'
COMPRESSION = 'AUTO' 
ENABLE_OCTAL = FALSE 
ALLOW_DUPLICATE = FALSE 
STRIP_OUTER_ARRAY = TRUE 
STRIP_NULL_VALUES = FALSE 
IGNORE_UTF8_ERRORS = FALSE;

create or replace temporary stage CDC_DB.CDC_SCHEMA.sf_tut_stage
  file_format = CDC_DB.CDC_SCHEMA.JSON_FILE_FORMAT;

Loading JSON in Snowflake

1 Answers