CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Sudarshan kumar
Sudarshan kumar

Reputation: 1585

Error parsing JSON exception for xml filed in copy command Snowflake

Hi I have declared a table like this

create or replace table app_event (
  ID varchar(36)  not null primary key, 
  VERSION number, 
  ACT_TYPE varchar(255),
  EVE_TYPE varchar(255),
  CLI_ID varchar(36),
  DETAILS variant,
  OBJ_TYPE varchar(255),
  DATE_TIME timestamp,
  AAPP_EVENT_TO_UTC_DT timestamp,
  GRO_ID varchar(36),
  OBJECT_NAME varchar(255),
  OBJ_ID varchar(255),
  USER_NAME varchar(255),
  USER_ID varchar(255),
  EVENT_ID varchar(255),
  FINDINGS varchar(255),
  SUMMARY variant
);

DETAILS column will contain xml file so that i can run xml function and get element of that xml file .

My sample rows looks like this

dfjkghdfkjghdf8gd7f7997,0,TEST_CASE,CHECK,74356476476DFD,<?xml version="1.0" encoding="UTF-8"?><testPayload><testId>3495864795uiyiu</testId><testCode>COMPLETED</testCode><testState>ONGOING</testState><noOfNewTest>1</noOfNewTest><noOfReviewRequiredTest>0</noOfReviewRequiredTest><noOfExcludedTest>0</noOfExcludedTest><noOfAutoResolvedTest>1</noOfAutoResolvedTest><testerTypes>WATCHLIST</testerTypes></testPayload>,CASE,41:31.3,NULL,948794853948dgjd,(null),dfjkghdfkjghdf8gd7f7997,test user,dfjkghdfkjghdf8gd7f7997,NULL,(null),(null)

When i declare DETAILS as varchar i am able to load file but when i declare this as variant i get below error for that column only

Error parsing JSON: dfjkghdfkjghdf8gd7f7997COMPLETED</status File 'SNOWFLAKE/Sudarshan.csv', line 1, character 89 Row 1, column "AUDIT_EVENT"["DETAILS":6]

Can you please help on this ? I can not use varchar as i need to query element of xml also in my query .

This is how i load into table and i use default CSV format ,file is available in S3 .

COPY INTO demo_db.public.app_event 
FROM @my_s3_stage/
FILES = ('app_Even.csv')
file_format=(type='CSV');

Based on Answer this is how i am loading

copy into demo_db.public.app_event from (
    select
        $1,$2,$3,$4,$5,
        parse_xml($6),$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,parse_xml($17)
    from @~/Audit_Even.csv d
)
file_format = (
    type = CSV
)

But when i execute it says zero row processed and no mentioned stage here

Upvotes: 1

Views: 442

Answers (1)

Simon D
Simon D

Reputation: 6269

If you are using a COPY INTO statement then you need to put in a subquery to convert the data before loading it into the table. Use the parse_xml within your copy statement's subquery, something like this:

copy into app_event from (
    select
        $1,
        parse_xml($2)  -- <---- "$2" is the column number in the CSV that contains the xml
    from @~/test.csv.gz d -- <---- This is my own internal user stage. You'll need to change this to your external stage or whatever
)
file_format = (
    type = CSV
)

It is hard to provide you with a good SQL statement without a full example of your existing code (your copy / insert statement). In my example above, I'm copying a file in my own user stage (@~/test.csv.gz) with the default CSV file format options. You are likely using an external stage but it should be easy to adapt this to your own example.

Upvotes: 1

Related Questions