时间戳

首页 > 解决方案 > 使用传递的变量插入表雪花

snowflake-cloud-data-platform - 使用传递的变量插入表雪花

问题描述

我有一个有 3 个属性的表

属性数据类型标志（0 或 1）

我的存储过程采用“Att1 Datatype1 1, Att2 Datatype2 0, Att3 Datatype3 0...) 形式的记录字符串，因此例如传递的字符串可以是“DATABASE VARCHAR 1, SCHEMA VARCHAR 1, TIMESTAMP TIMESTAMP 0”

我的代码获取表的列名并将其存储在字符串中 我的代码获取传递的字符串并将它们放入由 分隔的数组元素中，但是当我尝试插入表时出现问题我不断收到错误“不合法的识别符”

CREATE OR REPLACE PROCEDURE "ADMINDB"."TOOLKIT".ADD_ATTRIBUTES_SESSION_META ("P_ATTRIBUTE_DATATYPE_FLAG" VARCHAR(16777216))
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT='Add Attributes to the table SESSION_ATTRIBUTES_META'
EXECUTE AS CALLER
AS
$$
  var v_list = P_ATTRIBUTE_DATATYPE_FLAG;
  var arr_list = [];
  arr_list = P_ATTRIBUTE_DATATYPE_FLAG.split(',');
  var v_string;
  var arr_col_att = [];
  var v_sqlCode = `SELECT * FROM ` + "ADMINDB" + "." + "TOOLKIT" + "." + "SESSION_ATTRIBUTES_META";
  
    
  try{
    var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
    var sqlRS = sqlStmt.execute();
  }catch(err){
     errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
     errMessage += "\n  Message: " + err.message + v_sqlCode;
     errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
   }
   
  for (i = 1; i <= sqlStmt.getColumnCount(); i++) {
    arr_col_att.push(sqlStmt.getColumnName(i));
}

     arr_col_att[0] = arr_col_att[0].replace(/\s/g, ',');
    var v_col_att = arr_col_att.toString();
    v_string = arr_list[0].toString();
    v_string = v_string.replace(/\s/g, ',');
    
    v_sqlCode = `INSERT INTO ` + "ADMINDB" + "." + "TOOLKIT" + "." 
+ "SESSION_ATTRIBUTES_META" + `(` + v_col_att + `) VALUES( ` + v_string + `)`;
                
    try{
      var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
      var sqlRS = sqlStmt.execute();
    }catch(err){
      errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      errMessage += "\n  Message: " + err.message + v_sqlCode;
      errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
      throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
    }   
  return "SUCCESS!";
$$;

CALL "ADMINDB"."TOOLKIT".ADD_ATTRIBUTES_SESSION_META('DATABASE VARCHAR 1,SCHEMA VARCHAR 1,TIMESTAMP TIMESTAMP 0');

标签： snowflake-cloud-data-platform

解决方案

修改第 33 和 36 行，修复问题：

CREATE OR REPLACE PROCEDURE "ADMINDB"."TOOLKIT".ADD_ATTRIBUTES_SESSION_META ("P_ATTRIBUTE_DATATYPE_FLAG" VARCHAR(16777216))
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT='Add Attributes to the table SESSION_ATTRIBUTES_META'
EXECUTE AS CALLER
AS
$$
  var v_list = P_ATTRIBUTE_DATATYPE_FLAG;
  var arr_list = [];
  arr_list = P_ATTRIBUTE_DATATYPE_FLAG.split(',');
  var v_string;
  var arr_col_att = [];
  var v_sqlCode = `SELECT * FROM ` + "ADMINDB" + "." + "TOOLKIT" + "." + "SESSION_ATTRIBUTES_META";
  
    
  try{
    var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
    var sqlRS = sqlStmt.execute();
  }catch(err){
     errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
     errMessage += "\n  Message: " + err.message + v_sqlCode;
     errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
   }
   
  for (i = 1; i <= sqlStmt.getColumnCount(); i++) {
    arr_col_att.push(sqlStmt.getColumnName(i));
}

     arr_col_att[0] = arr_col_att[0].replace(/\s/g, ',');
    var v_col_att = arr_col_att.toString();
    v_string = arr_list[0].toString();
    v_string = v_string.replace(/\s/g, "','" );
    
    v_sqlCode = `INSERT INTO ` + "ADMINDB" + "." + "TOOLKIT" + "." 
+ "SESSION_ATTRIBUTES_META" + `(` + v_col_att + `) VALUES( '` + v_string + `' )`;
                
    try{
      var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
      var sqlRS = sqlStmt.execute();
    }catch(err){
      errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      errMessage += "\n  Message: " + err.message + v_sqlCode;
      errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
      throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
    }   
  return "SUCCESS!";
$$;

CALL "ADMINDB"."TOOLKIT".ADD_ATTRIBUTES_SESSION_META('DATABASE VARCHAR 1,SCHEMA VARCHAR 1,TIMESTAMP TIMESTAMP 0');

据我所知，您目前还没有在参数中实现多个记录的过程，但我认为您可以处理它。

推荐阅读