我的目标：允许用户将JSON字符串传递给Snowflake存储过程。然后，我希望存储过程将字符串强制转换为变量数据类型，并将值插入到变量数据类型表中的字段中。
我尝试过的：
- 将用户的值包装在parse_json((、to_variant((和cast(val as variant(中
- 我已经使用绑定方法和构造sql语句尝试了以上所有函数使用字符串和变量的组合但没有成功
我的代码如下。如果您能为我提供任何帮助，我们将不胜感激，谢谢！
CREATE OR REPLACE PROCEDURE LOGGING_TEST_PR (ETL_NAME VARCHAR(16777216),ETL_RUN_GUID VARCHAR(16777216),TASK_NAME VARCHAR(16777216),RECORDS FLOAT8,RUN_DATA_JSON VARCHAR(16777216) )
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//USER INPUT VARIABLES
var USER_JOB_NAME = ETL_NAME.toLowerCase(),USER_JOB_RUN_GUID = ETL_RUN_GUID, USER_JOB_TASK_NAME = TASK_NAME.toLowerCase(),USER_JOB_RECORDS, USER_JOB_RUN_DATA_JSON;
//SQL_STATEMENT_COUNT VARIABLES
var SQL_STATEMENT_COUNT,SQL_COUNT,RECORD_COUNT;
//SQL_STATEMENT_INSERT VARIABLES
var SQL_INSERT_RECORD, SQL_INSERT, ROW_NUM_INSERT, RESULT_INSERT_RETVALUE;
//SQL_STATEMENT_SELECT VARIABLES
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//REVERT UNDEFNED VARIABLE VALUES BACK TO NULL
function NULL_PARAM(PARAM)
{
if (typeof PARAM === 'undefined')
{
return null;
}
else
{
return PARAM;
}
}
USER_JOB_RECORDS = NULL_PARAM(RECORDS);
USER_JOB_RUN_DATA_JSON = NULL_PARAM(RUN_DATA_JSON);
//SQL STATEMENT VARIABLES
var SQL_STATEMENT_COUNT = " SELECT COUNT(*) AS COUNT FROM EDW_DEV.LOGGING.JOB_LOG_SPROC WHERE LOWER(ETL_NAME) ='"
+ USER_JOB_NAME +"'"
+ " AND LOWER(ETL_RUN_GUID) ='" + USER_JOB_RUN_GUID +"'"
+ " AND LOWER(TASK_NAME) ='" + USER_JOB_TASK_NAME + "'";
var SQL_STATEMENT_SELECT = "SELECT LOWER(ETL_NAME) AS ETL_NAME,LOWER(ETL_RUN_GUID) AS ETL_RUN_GUID,LOWER(TASK_NAME) AS TASK_NAME,START_DTS,END_DTS FROM EDW_DEV.LOGGING.JOB_LOG_SPROC WHERE LOWER(ETL_NAME) ='"
+ USER_JOB_NAME +"'"
+ " AND LOWER(ETL_RUN_GUID) ='" + USER_JOB_RUN_GUID +"'"
+ " AND LOWER(TASK_NAME) ='" + USER_JOB_TASK_NAME + "'";
var SQL_INSERT_RECORD = "INSERT INTO EDW_DEV.LOGGING.JOB_LOG_SPROC (ETL_NAME,ETL_RUN_GUID,TASK_NAME,START_DTS,RECORDS_START,RUN_DATA_JSON)"
+ "VALUES("+ "'" + USER_JOB_NAME + "'," + "'" + USER_JOB_RUN_GUID + "'," + "'" + USER_JOB_TASK_NAME + "'," + "CONVERT_TIMEZONE('UTC', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_TZ(9)))" + ","
+ USER_JOB_RECORDS + "," + USER_JOB_RUN_DATA_JSON + ")";
try {
//CHECK IF THE RECORD FOR THE TASK_NAME EXISTS IN THE TABLE
SQL_COUNT = snowflake.createStatement({sqlText:SQL_STATEMENT_COUNT});
RESULT_COUNT = SQL_COUNT.execute(); //EXECUTE MAH SQL STATEMENT
RESULT_COUNT.next(); //GO TO FIRST ROW IN RESULT SET
RECORD_COUNT = RESULT_COUNT.getColumnValue(1);
//THE TASK_NAME IS NOT IN THE TABLE
if (RECORD_COUNT == 0)
{SQL_INSERT = snowflake.createStatement({sqlText:"INSERT INTO EDW_DEV.LOGGING.JOB_LOG_SPROC (ETL_NAME,ETL_RUN_GUID,TASK_NAME,RECORDS_START,RUN_DATA_JSON) VALUES(?,?,?,?,?);",
binds:[USER_JOB_NAME,USER_JOB_RUN_GUID,USER_JOB_TASK_NAME,USER_JOB_RECORDS,CAST(USER_JOB_RUN_DATA_JSON AS VARIANT)]});
INSERT_RESULT = SQL_INSERT.execute();
ROW_NUM_INSERT = INSERT_RESULT.next();
return RESULT_INSERT_RETVALUE = INSERT_RESULT.getColumnValue(1);
}
//THE TASK NAME IS IN THE TABLE
else if (RECORD_COUNT == 1)
{ return "UPDATE RECORD WITH END TIMESTAMP AND OPTIONAL COUNTS AND RUN DATA JSON"; }
//RUH ROOOOOOO!
else
{ return "THERE WAS AN UNFORSEEN ERROR.";}
}
catch (ERR) {
return ERR
}
$$
CALL LOGGING_TEST_PR('WAYNE','15','BAGELS',1,'{"dude":"whoa"}') }
PARSE_JSON函数对JSON非常严格。如果它在字符串中遇到任何与规范不完全匹配的内容，它将生成一个错误。您可以在https://jsonlint.com.
来自Snowflake:的销售工程师
//create the table for inserting data
create table tester (json variant);
//adding a select statement with the parse_json I am able to insert values into the table in json format
insert into tester
select parse_json(column1) as v
from values ('{ "x" : "abc", "y" : false, "z": 10} ')
as vals;
//see the result
Select * from tester;