stored-procedures - 有没有办法在雪花中进行错误处理?
问题描述
我目前正在将数据从一个雪花表加载到雪花中的另一张表,还在进行数据加载时进行一些数据类型转换
但是当出现任何错误时,我的加载就会失败。我需要捕获表中的错误行并继续我的加载,尽管发生任何错误。
我尝试过使用如下存储过程,但只能捕获错误信息:-如果有任何方法可以在雪花中实现这一点,请告诉我。
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;
$$;
解决方案
我完成了一个示例,如何将好的行从一个表发送到另一个表,同时将坏行发送到另一个表。它应该很快就会出现在雪花博客上。关键是使用多表插入,如下所示:
-- 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;
推荐阅读
- ios - 在较新的 iOS 版本上调试 React Native White Screen of Death
- excel - 将 xlPrimary 和 xlSecondary 轴设置为百分比不起作用
- flutter - 如何为互联网连接事件注册应用程序侦听器?
- scala - 计数后 Spark 作业快速运行
- c# - 如何在 Azure 的一个事务中执行多个 SQL 文件?
- html - 有没有办法在闪亮中包含图标 css 代码
- oracle - oracle 程序中的错误绑定变量
- database - 如何在 NEO4J 中的连接记录中添加关系
- android - 如何检查房间数据库中的数据是否加密?
- php - Swift 邮件发出收据错误:没有有效的收件人