CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowpipe
Robertino Bonora
Robertino Bonora

Reputation: 108

Snowflake - Load error rows in a copy into script

I have a pipe that copies into a table with transformations, such us

COPY INTO t1 (
    col1,
    col2)
FROM (
    SELECT 
        t.$1 AS col1,
        t.$2 AS col2
    FROM '@STAGE/file1/' t
    ) file_format = (format_name = file_format_name);

Being file1 a CSV file in a S3 bucket.

The thing is that maybe a bad character could come in any of the rows of the file such us a varchar in an integer defined column.

I added the option ON_ERROR = CONTINUE in the copy into statement to continue with the loading process but i need to obtain those rows that failed to load to do something with them.

I tried to use the "SELECT * FROM TABLE(VALIDATE(t1, JOB_ID => '_last'));" after the copy was made, but doesn't work. It's showing the following error message: "Invalid argument [We couldn't find a copy for this table which occurred during this session ] for table function. Table function argument is required to be a constant."

Any thoughts on who to obtain the rows that failed?

I was thinking to run a stored procedure after this copy into finishes comparing through the primary key to obtain thous rows that wasn't loaded but i would like a better solution.

Thanks

Upvotes: 0

Views: 477

Answers (0)

Related Questions