sql - 在 Snowflake 中加载 JSON

Question

新手在使用 SnowSQL CLI 将 JSON 文件加载到 Snowflake 中需要帮助。 JSON 文件的片段如下所示(实际上要长很多):

        {"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"}````

我需要将此 JSON 加载到内部阶段和具有变体类型列的中间表中，然后将其加载到关系表中。我的 SQL 脚本有效，但只带来 JSON 文件的第一行。请参阅下面的 SQL:

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;

最后一条语句只返回新表 CDC_DATA 中 JSON 文件的第一行。我在解析这个 JSON 时做错了什么？感谢任何帮助...

Answer 1

看起来您使用的是带分隔符的文件格式。

尝试使用以下文件格式和临时阶段:

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;