CODEWITHSUNDEEP
datatablessnowflake-cloud-data-platform
DataYes
DataYes

Reputation: 25

How do I load <file name>.csv.gz from snowflake stage into a snowflake table?

I have successfully loaded 1000 files into a Snowflake stage=MT_STAGE. Every file has exact same schema. Every file has exact same naming convention (filename).csv.gz Every file is about 50 megs (+/- a couple megs). Every file has between 115k-120k records. Every file has 184 columns. I have created a Snowflake table=MT_TABLE. I keep on getting errors trying to do a "COPY INTO" to move files from stage into a single table. I've tried countless variations of the command, with & without different options. I've spent 3 days reading documentation and trying to watch videos. I have failed. Can anyone help?

copy into MT_TABLE from @MT_STAGE;

Copy executed with 0 files processed

copy into MT_TABLE from @MT_STAGE (type=csv field_delimiter=”,” skip_header=1);

Syntax error: unexpected '('. (line 1)

copy into MT_TABLE from @MT_STAGE type=csv field_delimiter=”,” skip_header=1;

Syntax error: unexpected '”,'. (line 1)

Upvotes: 0

Views: 2299

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

So as per Mike's statement if there are comma's in your data

col_a col_b col c
no comma one, comma two,, commas 
col_a, col_b, col_b
no comma, one, comma, two,, commas

how can anything tell which is the correct way to know what is in what

col_a col_b col c
no comma one, comma two,, commas
no comma, one , comma two,, commas
no comma one, comma, two , commas
no comma, one , comma, two , commas
no comma one, comma, two, commas
no comma, one , comma, two, commas

which is the correct line.

So you ether change the field delimeter from , to pipe | or you quote the data

no comma| one, comma| two,, commas

double quotes

"no comma","one, comma"," two,, commas"

single quotes

'no comma','one, comma',' two,, commas'

The cool thing is, if you change your column delimiter it has to not be in the in the data OR the data has to be quoted.

And if you change to quoting it has to not be in the filed OR it has to be escaped.

OR you can encode as some safe data type like base64 and it takes more space, but now it's transportation transport safe:

bm8gY29tbWE,IG9uZSwgY29tbWE,IHR3bywsIGNvbW1hcw

Upvotes: 1

Related Questions