Snowflake DDL not allowing to load data into table

Question

Not able to insert data into Snowflake table with Specific data types

This may be very silly question but i just started trying out Snowflake and stuck at first step itself . I need to look up XML element from the DETAILS column

select testId from app_event  table ;

This is how i am trying to create a table in Snowflake

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
);

This is my Sample data

ID  VERSION ACT_TYPE    EVE_TYPE    CLI_ID  DETAILS OBJ_TYPE    DATE_TIME   AAPP_EVENT_TO_UTC_DT    GRO_ID  OBJECT_NAME OBJ_ID  USER_NAME   USER_ID EVENT_ID    FINDINGS    SUMMARY

003cce70-ffff-43bc-8905-5e1d64475aa1    0   SCREENED_CASE   WORLDCHECK  0   <?xml version="1.0" encoding="UTF-8" standalone="yes"?><testPayload><testId>565656-21cf-4c7e-8071-574a1ef78981</testId><testCode>COMPLETED</testCode><testState>TEST</testState><testResults>1</testResults><testRequiredResults>0</testRequiredResults><testExcludedResults>0</testExcludedResults><testAutoResolvedResults>1</testAutoResolvedResults><testproviderTypes>WATCHLIST</testproviderTypes></testPayload>  CASE    9/16/2020 9:45  9/16/2020 9:45  erutrt7-d726-4672-8599-83d21927bec5 0   5786765dfgdfgdfg    System User USER_SYSTEM 0   0   <?xml version="1.0" encoding="UTF-8" standalone="yes"?><testCaseEventSummary><testTypes>WATCHLIST</testTypes><testResults>1</testResults></testCaseEventSummary>

I am to create table with above DDL but when i insert it throws error But when i declare all data type as varchar i am able to insert rows into this table

Can you please suggest what i am missing .

This is how i am trying to load data into table

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

9/16/2020 9:45 - I guess it i unknown timestamp literal and implicit conversion fails try: '2020-09-16 09:45:00'::timestamp instead — Lukasz Szozda
It looks like version is being set to the letter 'o' and I assume it should be zero — NickW
"help me with XML element query " - this sounds like a new question. If it is then please raise a new question, provide some sample data, the required result you are trying to achieve and show what you have already tried — NickW
What has that csv file got to do with what you are doing? Are there parts of the process you are following that you haven't explained in your question? — NickW
Does your file have a header row in it? If so then your file format in your copy command doesn't allow for that — NickW

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-04-24T15:02:50

I am to create table with above DDL but when i insert it throws error But when i declare all data type as varchar i am able to insert rows into this table

This kind of error message indicates that there is implicit conversion that fails.

Most likely for column AAPP_EVENT_TO_UTC_DT timestamp the value 9/16/2020 9:45 is not recoginized.

For providing date/timestamps: '2020-09-16 09:45:00'::timestamp could be used or TRY_TO_TIMESTAMP(string_expr, format) function providing specific format.

Snowflake DDL not allowing to load data into table

1 Answers