Snowflake中JavaScript存储过程返回空JSON问题求助

问题概述 #

编写的存储过程意图在指定Schema中搜索含特定关键词的列，但执行后返回空JSON结构，且未实现关键词搜索及临时表存储功能。原代码及输出如下：

原代码 #

create or replace procedure search(schema_to_search varchar, search_term varchar)
  returns variant
  language javascript
  as
  $$
    var search_schema = schema_to_search ;
    var search_term = search_term ;
    var result_set1 = "" ;  
    var get_columns = "SELECT COLUMN2, COLUMN2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA =  '" + search_schema + "';" ;
    
    var statement1 = snowflake.createStatement({
       sqlText: get_columns
    });


    try
    {
    var result_set1 = statement1.execute();

    return result_set1;

    }
    catch(err){return "error "+err;}
    
   
    while(result_set1.next())
    {
        var db = result_set1.getColumnValue(1);
       
        
    }

    return result_set1;
  $$
  ;
 
call search('schema_name', 'a_searc_term');

原输出 #

{
  "getColumnCount": {},
  "getColumnDescription": {},
  "getColumnName": {},
  "getColumnScale": {},
  "getColumnSqlType": {},
  "getColumnType": {},
  "getColumnValBoxedType": {},
  "getColumnValue": {},
  "getColumnValueAsString": {},
  "getNumRowsAffected": {},
  "getQueryId": {},
  "getRowCount": {},
  "getSqlcode": {},
  "isColumnArray": {},
  "isColumnBinary": {},
  "isColumnBoolean": {},
  "isColumnDate": {},
  "isColumnNullable": {},
  "isColumnNumber": {},
  "isColumnObject": {},
  "isColumnText": {},
  "isColumnTime": {},
  "isColumnTimestamp": {},
  "isColumnVariant": {},
  "isDml": {},
  "next": {},
  "setCResultSet": {}
}

错误原因 #

1. 直接返回ResultSet对象：Snowflake JavaScript存储过程无法直接序列化返回ResultSet对象，你看到的JSON是ResultSet的方法列表，并非实际数据。
2. 提前终止执行流程：try块中执行execute()后立即return result_set1，导致后续的结果遍历循环完全不会执行。
3. 未实现关键词搜索逻辑：原SQL查询仅筛选Schema，未使用传入的search_term做关键词匹配，完全没达到搜索目的。
4. SQL查询无效：SELECT COLUMN2, COLUMN2是重复列，且未选择有实际意义的字段（如表名、列名、数据类型）。
5. 缺失临时表存储逻辑：原代码完全没有创建临时表、插入结果的相关代码，未实现最终目标。

修正后的代码 #

create or replace procedure search(schema_to_search varchar, search_term varchar)
  returns variant
  language javascript
  as
  $$
    var searchSchema = schema_to_search;
    var searchTerm = search_term;
    var results = [];
    
    // 创建临时表用于存储搜索结果
    const createTempTableSql = `
      CREATE OR REPLACE TEMPORARY TABLE SEARCH_RESULTS (
        TABLE_NAME VARCHAR,
        COLUMN_NAME VARCHAR,
        COLUMN_DATA_TYPE VARCHAR
      )
    `;
    snowflake.createStatement({sqlText: createTempTableSql}).execute();
    
    // 查询指定Schema中列名包含关键词的字段（模糊匹配，不区分大小写）
    const getColumnsSql = `
      SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = ? 
        AND COLUMN_NAME ILIKE ?
    `;
    
    // 使用参数绑定避免SQL注入，同时传递模糊匹配的关键词
    const statement = snowflake.createStatement({
      sqlText: getColumnsSql,
      binds: [searchSchema, `%${searchTerm}%`]
    });
    
    try {
      const resultSet = statement.execute();
      
      // 遍历结果集，组装返回数据并插入临时表
      while (resultSet.next()) {
        const tableName = resultSet.getColumnValue(1);
        const columnName = resultSet.getColumnValue(2);
        const dataType = resultSet.getColumnValue(3);
        
        const rowData = {
          table_name: tableName,
          column_name: columnName,
          data_type: dataType
        };
        results.push(rowData);
        
        // 插入临时表
        const insertSql = `
          INSERT INTO SEARCH_RESULTS (TABLE_NAME, COLUMN_NAME, COLUMN_DATA_TYPE)
          VALUES (?, ?, ?)
        `;
        snowflake.createStatement({
          sqlText: insertSql,
          binds: [tableName, columnName, dataType]
        }).execute();
      }
      
      // 返回结构化的搜索结果数组
      return results;
    } catch (err) {
      // 返回结构化错误信息便于排查
      return {
        error_message: err.message,
        sql_state: err.sqlState,
        error_code: err.code
      };
    }
  $$;
 
-- 调用存储过程
call search('schema_name', 'a_search_term');

-- 查询临时表中的结果
SELECT * FROM SEARCH_RESULTS;

关键改动说明 #

• 移除提前return：将结果返回逻辑移至遍历完成后，确保所有结果都被处理。
• 手动组装返回数据：遍历ResultSet，将每行数据组装为JSON对象存入数组，返回可序列化的结构。
• 实现关键词搜索：使用ILIKE进行不区分大小写的模糊匹配，通过参数绑定传递关键词，同时避免SQL注入风险。
• 新增临时表逻辑：创建临时表存储结果，遍历过程中插入数据，可通过SELECT语句查看最终结果。
• 优化错误处理：返回包含错误信息、SQL状态码的结构化对象，便于问题排查。

内容的提问来源于stack exchange，提问作者Antulii