In snowflake, I am able to get the error details using TRY-CATCH method. I mocked up a data where I am trying to insert a VARCHAR data into INTEGER data type column. This exception was caught in try-catch method used in my snowflake procedure. As usual, the procedure failed and none of the records were inserted. I want to ignore this BAD data and insert rest of the records into my target table using snowflake procedure. IS THIS POSSIBLE? Also I need to INSERT that BAD data into another table. How can we achieve this? Please share you thoughts/expertise. Thanks, Joe.
2
-
You can probably use IFF to validate if the data is of the type you want as explained hereSergiu– Sergiu2022-03-02 14:16:16 +00:00Commented Mar 2, 2022 at 14:16
-
@Serigu -- thanks for the input. in my case IFF wont work.. it is something i am trying to check for a specific value. In my case, my data type itself is different. Also mine is a JSON format src value where i will have to do the validation. My Need ---> I want to route such data into a separate table and continue with the load. So that i can work on the bad data later. Can this be achieved in TRY-CATCH method in SF Procedures.Jomy– Jomy2022-03-02 15:42:36 +00:00Commented Mar 2, 2022 at 15:42
Add a comment
|
2 Answers
if you are using Snowflake procedure/ function you can write a common Error routine in captures the errors in a table and call it in your procedure. Please check this link for a common error handler which i wrote few months back.
https://github.com/hkandpal/Snowflake/blob/main/Snow_Erro_Handling.txt
3 Comments
Jomy
Thanks for the script. I went through it. Sorry, this will not serve my purpose. My need is, I wanted to log the bad data (inserting varchar data into integer column) and continue with the existing load.
Himanshu Kandpal
Hi you can use TRY_TO_NUMBER function which will insert the value NULL if the data is not integer.
Jomy
Thanks Himanshu. Yes this is an option, but i would need to inspect the data later. If we use TRY_TONUMBER, the data will be updated and loaded into table, right? But, i need to load and work on it after the load is complete.
I am trying to insert a VARCHAR data into INTEGER data type column.
I want to ignore this BAD data and insert rest of the records into my target table using snowflake procedure
If the source is VARCHAR then assumption that it contain INTEGER may be dangerous. Instead of trying to insert incorrect data and fail entire query alternative approach could validate data. TRY_CAST:
A special version of CAST , :: that is available for a subset of data type conversions. It performs the same operation (i.e. converts a value of one data type into another data type), but returns a NULL value instead of raising an error when the conversion can not be performed.
-- pass only NULLs and validated rows
INSERT INTO tab(col_int)
SELECT col_varchar
FROM source_table
WHERE TRY_CAST(col_varchar AS INT) IS NOT NULL
OR col_varchar IS NULL;
-- error table, log
INSERT INTO error_table(col_varchar)
SELECT col_varchar
FROM source_table
WHERE TRY_CAST(col_varchar AS INT) IS NULL
AND col_varchar IS NOT NULL;
1 Comment
Jomy
Thanks for the suggestion Lukasz. Using this may degrade the performance as my data is variant and I am flattening. But let me try. Do you have any info on what all options can be tried in try-catch method.