Snowflake使用存储过程进行错误处理 [英] Snowflake working with stored procedure on error handling

问题描述

我在Snowflake中有一个示例存储过程，它一个接一个地执行以下步骤

1. 插入具有开始详细信息的元数据
2. 获取作为参数传递的表的总数
3. 成功/失败更新元数据

此处的问题是，当数据库中不存在给定表名称时，存储过程会错误地退出，但它不使用错误详细信息更新元数据，并且元数据条目的状态显示为正在进行中，而存储过程在点#2失败。

我正在寻找一种干净的退出方式，当数据库中不存在该表时，存储过程应使用错误详细信息更新元数据并干净地退出。

有人可以看一下下面的代码并提示我这里是否遗漏了什么吗？我对Snowflake和手术程序都是新手。谢谢。

CREATE OR REPLACE PROCEDURE abc.PROC_GET_COUNT(table_name varchar)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

// SQL Queries
var get_execution_id_sql = "select t.nextval from table(getnextval(EXECUTION_SEQUENCE)) t";

var get_count_sql = `select count(*) from abc.`+ TABLE_NAME +;

var result_set1 = snowflake.createStatement({sqlText: get_execution_id_sql}).execute();

result_set1.next();

var seq_num= result_set1.getColumnValue(1);

var insert_meta_sql1= `INSERT into abc.ERROR_LOG (EXECUTION_ID, STATUS, START_TS) values ( '` +seq_num+ `', 'In_Progress', CURRENT_TIMESTAMP)`;

try {
    message = 'In insert Metadata with start details';
    snowflake.execute({sqlText: insert_meta_sql1});

    message = 'In Process of get count';
    get_count_out = snowflake.execute ({sqlText: get_count_sql});
    get_count_out.next();
    rec_count = get_count_out.getColumnValue(1);
    
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET  END_TS = current_timestamp, STATUS = 'SUCCESS', MESSAGE = '` + TABLE_NAME + ` - Total count: ` + rec_count + `'  where EXECUTION_ID = '` + seq_num + `';
    
    message = 'In update Metadata with end details';
    snowflake.execute ({sqlText: upd_meta_sql});
    
} catch (err)  {
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET  
                                END_TS = current_timestamp,
                                STATUS = 'FAILED', 
                                MESSAGE = '` + message + `. Error Details -- 
 Code: `+ err.code +`
 State: `+ err.state +`
 Message: `+ err.message +`
 Stack Trace: `+ err.stackTraceTxt +`' 
                    where EXECUTION_ID = '` + seq_num + `';
    snowflake.execute ({sqlText: upd_meta_sql});
    return "Failed: " + message + ' -- ' +err; 
    }

return 'SUCCESS';
$$;

推荐答案

我认为该问题与错误消息中返回的单引号有关。

我建议设置Message变量。当您编写较短的代码时，它更隐晦，也更难找到问题。

通过将代码分解为更简单的步骤，您会发现问题更容易解决。

CREATE OR REPLACE SCHEMA abc;

USE SCHEMA ABC;


CREATE  SEQUENCE  IF NOT EXISTS EXECUTION_SEQUENCE
   WITH 
   START  WITH   =  1
   INCREMENT  BY  =  1
   COMMENT = 'DEMO SEQUENCE ' ;



CREATE OR REPLACE TABLE abc.ERROR_LOG(EXECUTION_ID number , STATUS varchar, MESSAGE VARCHAR, START_TS timestamp_ltz , END_TS timestamp_ltz);


CREATE TABLE ABC.TEST_TABLE1(ID NUMBER, MESSAGE VARCHAR);

INSERT INTO ABC.TEST_TABLE1(ID, MESSAGE)
SELECT SEQ4() + 1 , RANDSTR(50, RANDOM()) 
FROM TABLE(GENERATOR(ROWCOUNT=>50));

SELECT * FROM ABC.TEST_TABLE1 ORDER BY 1 ASC;


CREATE OR REPLACE PROCEDURE abc.PROC_GET_COUNT(table_name varchar)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

// SQL Queries
var get_execution_id_sql = "select t.nextval from table(getnextval(EXECUTION_SEQUENCE)) t";

var get_count_sql = `select count(*) from abc.`+ TABLE_NAME +`;`

var result_set1 = snowflake.createStatement({sqlText: get_execution_id_sql}).execute();

result_set1.next();

var seq_num= result_set1.getColumnValue(1);

var insert_meta_sql1= `INSERT into abc.ERROR_LOG (EXECUTION_ID, STATUS, START_TS,MESSAGE) values ( '` +seq_num+ `', 'In_Progress', CURRENT_TIMESTAMP(), '`+TABLE_NAME+`')`;

try {
    message = 'In insert Metadata with start details';
    snowflake.execute({sqlText: insert_meta_sql1});

    message = 'In Process of get count';
    get_count_out = snowflake.execute ({sqlText: get_count_sql});
    get_count_out.next();
    rec_count = get_count_out.getColumnValue(1);
    
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET END_TS = current_timestamp(), STATUS = 'SUCCESS', MESSAGE = '` + TABLE_NAME + ` - Total count: ` + rec_count + `'  where EXECUTION_ID = ` + seq_num + `;`
    
    message = 'In update Metadata with end details';
    snowflake.execute ({sqlText: upd_meta_sql});
    
} catch (err)  {
    message = `Error Details
Code: ` + err.code + `
State:` + err.state +`
Message: ` + err.message + `
Stack Trace: ` + err.stackTraceTxt;

    // **** you need to replace the single quotes that are being returned in your error message
    message = message.replace(/'/g, `"`);
    upd_meta_sql = `UPDATE abc.ERROR_LOG 
                    SET END_TS = current_timestamp()
                    ,STATUS = 'FAILED'
                    , MESSAGE = '` + message +`' where EXECUTION_ID = ` + seq_num + `;`
    snowflake.execute ({sqlText: upd_meta_sql});
    return "Failed: " + message + ' -- ' +err; 
    }

return 'SUCCESS';
$$;



CALL abc.PROC_GET_COUNT('TEST_TABLE1');



CALL abc.PROC_GET_COUNT('TEST_TABLE_MISSING');


SELECT * FROM abc.ERROR_LOG ORDER BY 1 ASC;

解决方案为here：

这篇关于Snowflake使用存储过程进行错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！