时间戳

首页 > 解决方案 > 雪花存储过程：处理多行字符串和单引号

stored-procedures - 雪花存储过程：处理多行字符串和单引号

问题描述

我正在使用反引号在雪花存储过程中构建下面的插入查询，并且在触发 sql 时它工作正常

CREATE OR REPLACE PROCEDURE SP_TEST_CALLPROC()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$
var q = `insert into abc 
         select col1, col2, 
         to_date(col3,'YYYYMMDD') as col3 from def;`
var insrtSql = snowflake.createStatement({sqlText: q});
var result = insrtSql.execute();
result.next()
$$
;

我还想将此查询记录到日志表中，由于 TO_DATE 函数“YYYYMMDD”中的单引号导致该查询失败：

CREATE OR REPLACE PROCEDURE SP_LOAD_LOG_POC(PROCESS_ID FLOAT,
                            PROCESS_NAME STRING,
                            BATCH_ID FLOAT,
                            LOG_TXT STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS OWNER
    AS $$
 
    var query = `INSERT INTO INGESTION_LOG
                  VALUES
                  (${PROCESS_ID},'${PROCESS_NAME}',${BATCH_ID},current_timestamp(),'${LOG_TXT}');`
    var stmt = snowflake.createStatement( { sqlText: query});
    var res = stmt.execute();
 $$
;
CREATE OR REPLACE PROCEDURE SP_TEST_CALLPROC()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$
function log(process_id,process_name,batch_id,log_txt){
        snowflake.createStatement( { sqlText: `call SP_LOAD_LOG_POC(:1,:2,:3,:4)`, binds:[process_id,process_name,batch_id,log_txt] } ).execute();
        }

var q = `insert into abc 
         select col1, col2, 
         to_date(col3,'YYYYMMDD') as col3 from def;`
log(1,'test_procedure',12345,`Firing insert query : ${q}`);
var insrtSql = snowflake.createStatement({sqlText: q});
var result = insrtSql.execute();
result.next()
$$
;

CALL SP_TEST_CALLPROC()

Execution error in store procedure SP_TEST_CALLPROC: 
Execution error in store procedure SP_LOAD_LOG_POC: SQL compilation error: syntax error line 5 at position 23 unexpected 'YYYYMMDD'.
At Statement.execute, line 10 position 19 
At Statement.execute, line 4 position 169

尝试使用javascript函数replaceAll（）将单引号替换为2个单引号（'->''），但雪花存储过程中不支持此函数。任何人都可以建议如何在插入日志条目时摆脱这个错误

标签： stored-proceduressnowflake-cloud-data-platform

解决方案

如果您必须将 SQL 作为字符串传递给另一个函数、UDF 或 SP，除了单个刻度外，还需要考虑许多特殊字符。下面是我编写的几个 JavaScript 帮助函数，用于使不同的数据类型对插入安全。

由于 wrapInsertValue 函数为文本类型调用了 escapeInsertString，因此您可以只调用那个。wrapInsertValue 函数的要点是读取列的类型并相应地处理它。请注意，escapeInsertString 函数也会压缩空格。s = s.replace(/\s+/g, " ");如果您不想这样做，可以注释掉该行。

function wrapInsertValue(value, dataType){
    if (value == 'null'){
        return 'NULL';
    }
    switch (dataType){
        case "TEXT":
            return "'" + escapeInsertString(value) + "'";
        case "OBJECT":
            return "'" + escapeInsertString(value) + "'";
        case "TIMESTAMP_TZ":
            return "'" + value + "'";
        case "TIMESTAMP":
            return "'" + value + "'";
        default: return value;
    }
}

function escapeInsertString(value) {
    var s = value.replace(/\\/g, "\\\\");
    s = s.replace(/'/g, "''" );
    s = s.replace(/"/g, '\\"');
    s = s.replace(/\s+/g, " ");
//  s = s.replace(/[^\x00-\x7F]/g, "");
    return s;
}

推荐阅读