COPY INTO snowflake table not loading data even with Force command

Question

I'm working on loading data from s3 bucket to snowflake table & using the copy into command.

COPY INTO DB.SCHEMA.TABLE
FROM s3://test1/test.tsv
STORAGE_INTEGRATION = "TEST_S3"
FILE_FORMAT = (type = "CSV" field_delimiter = ' ' skip_header = 0)
ON_ERROR = "CONTINUE"
;

First time it run successfully & data was loaded. when tried to re-run the copy command it failed to load data & returned - Copy executed with 0 files processed.

Then checked the documentation & understood that I should FORCE=TRUE but still gets the same message - Copy executed with 0 files processed.

COPY INTO DB.SCHEMA.TABLE
FROM s3://test1/test.tsv
STORAGE_INTEGRATION = "TEST_S3"
FILE_FORMAT = (type = "CSV" field_delimiter = ' ' skip_header = 0)
ON_ERROR = "CONTINUE"
FORCE = TRUE
;

Couldn't understand what I'm doing wrong. Does any one have any idea?

Check the view snowflake.account_usage.copy_history and see if there were any errors? — Simon D
@SimonD, yeah the copy history doesn't records it here as the re run statement isn't technically copying data. — monu goyath

monu goyath monu goyath · Accepted Answer · 2021-06-24T10:40:21

Yeah, I got it now.

Both the above codes delete the copied file in external storage & doesn't allow us to re copy with file being not present.

So if you don't wanna delete file after being copied, add the command PURGE=FALSE & re running the copy into command(with FORCE=TRUE) works & duplicates the record.

COPY INTO snowflake table not loading data even with Force command

2 Answers