Ingestion error within snowflake database

Question

So I am setting up a number of procedures that will utilize Snowflake's staging and pipeline features to pull in data from AWS S3 buckets into some tables.

I am confident I have set up the procedure correctly following their documentation. But upon testing the ingestion code I get this problem.

SAMPLE CSV DATA:

"id","uuid","companyfolder_id","name","billingAccount","createdBy","status","createdAt","destroyAt","statusChangedBy","statusChangedAt","statusChangedReason","description","tags"
"5","8c35346","0","lmyumeng","lmyumeng","[email protected]","active","1558041027","0","","0","","",""

SQL:

 USE WAREHOUSE SANTABA;

 copy into SANTABA.TESTINGEST
  from @etlStage/

ERROR:

Numeric value '"5"' is not recognized File 'snowpipetestcsv.csv', line 2, character 1 Row 1, column "TESTINGEST"["ID":1]

In this case the value 5 is correctly the ID value from the first line of the CSV. I am not sure why it would not recognize the value? The column data type here is integer.

Randy B. Randy B. · Accepted Answer · 2019-08-29T15:58:42

I did not realize that snowflake required you to define a delimiter just for quotes within a CSV within its file formatter. This was not very clear in their documentation but that is what did the trick.

 FIELD_OPTIONALLY_ENCLOSED_BY = '\042';

Ingestion error within snowflake database

2 Answers