Is there any way to do error handling in snowflake?

user_13071574 0
Viewed 11284

Am currently loading data from one snowflake table to another table in snowflake, also doing some datatype conversions while doing the data loads

But when there is any error, my load is getting failed.I need to capture the error rows in a table and continue my load though any errors occur.

I have tried that using stored procedure as below but only able to capture error information:- Please let me know if there is any way to achieve this in snowflake.

CREATE OR REPLACE PROCEDURE LOAD_TABLE_A() 
RETURNS varchar 
NOT NULL 
LANGUAGE javascript 
AS 
$$
var result;
var sql_command = "insert into TABLE A"
 sql_command += " select"
 sql_command += " migration_status,to_date(status_date,'ddmmyyyy') as status_date,"
 sql_command += " to_time(status_time,'HH24MISS') as status_time,unique_unit_of_migration_number,reason,"
 sql_command += " to_timestamp_ntz(current_timestamp) as insert_date_time"
 sql_command += " from TABLE B"
 sql_command += " where insert_date_time>(select max(insert_date_time) from TABLE A);"
try {
    snowflake.execute({ sqlText: sql_command});
    result = "Succeeded";
} 
catch (err) {
    result = "Failed";
    snowflake.execute({
      sqlText: `insert into mcs_error_log VALUES (?,?,?,?)`
      ,binds: [err.code, err.state, err.message, err.stackTraceTxt]
      });
}
return result;
$$;
snowflake-cloud-data-platform stored-procedures
4 Answers

I worked through an example how to send good rows from one table to another while sending bad ones to a separate table. It should be on the Snowflake blog shortly. The key is using multi-table inserts like so:

-- Create a staging table with all columns defined as strings.
-- This will hold all raw values from the load filess.
create or replace table SALES_RAW
(                                       -- Actual Data Type
  SALE_TIMESTAMP            string,     -- timestamp
  ITEM_SKU                  string,     -- int
  PRICE                     string,     -- number(10,2)
  IS_TAXABLE                string,     -- boolean
  COMMENTS                  string      -- string
);

-- Create the production table with actual data types.
create or replace table SALES_STAGE
(
  SALE_TIMESTAMP            timestamp,
  ITEM_SKU                  int,
  PRICE                     number(10,2),
  IS_TAXABLE                boolean,
  COMMENTS                  string
);

-- Simulate adding some rows from a load file. Two rows are good.
-- Four rows generate errors when converting to the data types.
insert into SALES_RAW 
    (SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, COMMENTS) 
    values
    ('2020-03-17 18:21:34', '23289', '3.42',   'TRUE',  'Good row.'),
    ('2020-17-03 18:21:56', '91832', '1.41',   'FALSE', 'Bad row: SALE_TIMESTAMP has the month and day transposed.'),
    ('2020-03-17 18:22:03', '7O242', '2.99',   'T',     'Bad row: ITEM_SKU has a capital "O" instead of a zero.'),
    ('2020-03-17 18:22:10', '53921', '$6.25',  'F',     'Bad row: PRICE should not have a dollar sign.'),
    ('2020-03-17 18:22:17', '90210', '2.49',   'Foo',   'Bad row: IS_TAXABLE cannot be converted to true or false'),
    ('2020-03-17 18:22:24', '80386', '1.89',   '1',     'Good row.');

-- Make sure the rows inserted okay.
select * from SALES_RAW;

-- Create a table to hold the bad rows.
create or replace table SALES_BAD_ROWS like SALES_RAW;

-- Insert good rows into SALES_STAGE and
-- bad rows into SALES_BAD_ROWS
insert first
  when  SALE_TIMESTAMP_X is null and SALE_TIMESTAMP is not null or
        ITEM_SKU_X       is null and SALE_TIMESTAMP is not null or
        PRICE_X          is null and PRICE          is not null or
        IS_TAXABLE_X     is null and IS_TAXABLE     is not null
  then
        into SALES_BAD_ROWS
            (SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, COMMENTS)
        values
            (SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, COMMENTS)  
  else
        into SALES_STAGE 
            (SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, COMMENTS) 
         values
            (SALE_TIMESTAMP_X, ITEM_SKU_X, PRICE_X, IS_TAXABLE_X, COMMENTS)
select  try_to_timestamp (SALE_TIMESTAMP)   as SALE_TIMESTAMP_X,
        try_to_number    (ITEM_SKU, 10, 0)  as ITEM_SKU_X,
        try_to_number    (PRICE, 10, 2)     as PRICE_X,
        try_to_boolean   (IS_TAXABLE)       as IS_TAXABLE_X,
                                               COMMENTS, 
                                               SALE_TIMESTAMP,
                                               ITEM_SKU,
                                               PRICE,
                                               IS_TAXABLE
from    SALES_RAW;

-- Examine the two good rows
select * from SALES_STAGE;

-- Examine the four bad rows
select * from SALES_BAD_ROWS;
user_127563810
user_61732590
user_42192020

I think a nice solution is to wrap your SQL calls with a helper method.

For example lets say instead of doing snowflake.execute({}) ...

You use something like:

EXEC(select * from table1 where x > ?,[param1]);

Inside the EXEC method you can have a try catch and you can easily add things like a continue handler, or exit_handler were you can put logic to log your errors on a table.

I have assembled a repo with a tools and some snippets. Maybe take a look at: https://github.com/orellabac/SnowJS-Helpers

user_5809370
Related