Snowflake JS存储过程问题：多库同表合并视图仅单库生效求助

开发者阿泽

2026-8-21

问题描述

我正在开发一个Snowflake JavaScript存储过程，需求如下：

遍历数组中定义的表名，在指定的所有数据库中查找这些表
对同名表通过UNION ALL合并数据，创建对应的视图
示例：若表A存在于DB1和DB2中，则创建视图合并两个库的表A数据

当前使用的存储过程代码（存在问题，仅能从单个数据库取数创建视图）：

create or replace procedure PROC_1()
  returns VARCHAR -- return final create statement
  language javascript
  as     
$$
    //given two db for testing
    var get_databases_stmt = "SELECT DATABASE_NAME FROM SNOWFLAKE.INFORMATION_SCHEMA.DATABASES WHERE DATABASE_NAME='TERRA_DB' OR DATABASE_NAME='TERRA_DB_2'"
    var get_databases_stmt = snowflake.createStatement({sqlText:get_databases_stmt });
    var databases = get_databases_stmt.execute();
    var row_count = get_databases_stmt.getRowCount();
    var rows_iterated = 0;
    //table on which view will be created       
    var results_table=['STAGE_TABLE','JS_TEST_TABLE];
    var results_db=[];
    while (databases.next())  {
        var database_name = databases.getColumnValue(1);
        //rows_iterated += 1;
        for (let j = 0; j < results_table.length; j++){
            var stmt="CREATE OR REPLACE VIEW  TERRA_DB.TERRA_SCHEMA.ALL_"+results_table[j]+" AS \n";
            stmt += "SELECT * FROM "+database_name+".TERRA_SCHEMA." + results_table[j]
            if (rows_iterated < row_count){
                stmt += " UNION ALL";
            }
            ++rows_iterated;
        }
    }
    //var sql = snowflake.createStatement({sqlText:stmt});
    //var res =sql.execute();
    return stmt;
$$;

调用语句：

call PROC_1();

问题：上述代码仅能从单个数据库取数创建视图，无法实现多库数据合并。

解决方案

原代码的核心问题是每次循环都重新赋值stmt，覆盖了之前的拼接内容，同时rows_iterated的逻辑没有针对单个表做合并判断，也没有检查表是否真实存在。以下是修正后的代码：

create or replace procedure PROC_1()
  returns VARCHAR
  language javascript
  as     
$$
    // 指定要查询的数据库
    const targetDbs = ['TERRA_DB', 'TERRA_DB_2'];
    // 要创建合并视图的表名列表（修复了原代码的引号语法错误）
    const targetTables = ['STAGE_TABLE', 'JS_TEST_TABLE'];
    // 视图的目标库和 schema
    const viewDb = 'TERRA_DB';
    const viewSchema = 'TERRA_SCHEMA';
    
    let finalResult = '';

    // 遍历每个目标表，单独处理合并逻辑
    for (const tableName of targetTables) {
        let selectClauses = [];
        
        // 遍历每个数据库，检查表是否存在并收集查询语句
        for (const dbName of targetDbs) {
            // 检查当前数据库中是否存在该表
            const checkTableStmt = snowflake.createStatement({
                sqlText: `SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
                          WHERE TABLE_CATALOG = ? AND TABLE_SCHEMA = 'TERRA_SCHEMA' AND TABLE_NAME = ?`,
                binds: [dbName, tableName]
            });
            
            try {
                const result = checkTableStmt.execute();
                if (result.next()) {
                    // 表存在，添加对应的SELECT语句
                    selectClauses.push(`SELECT * FROM "${dbName}"."TERRA_SCHEMA"."${tableName}"`);
                }
            } catch (err) {
                // 捕获可能的权限或不存在的错误，跳过当前数据库
                finalResult += `Warning: Failed to check table ${dbName}.TERRA_SCHEMA.${tableName}: ${err.message}\n`;
            }
        }
        
        // 如果有可合并的表，创建视图
        if (selectClauses.length > 0) {
            const viewName = `${viewDb}.${viewSchema}.ALL_${tableName}`;
            const createViewSql = `CREATE OR REPLACE VIEW ${viewName} AS\n${selectClauses.join('\nUNION ALL\n')}`;
            
            try {
                snowflake.createStatement({sqlText: createViewSql}).execute();
                finalResult += `Successfully created view: ${viewName}\n`;
            } catch (err) {
                finalResult += `Error creating view ${viewName}: ${err.message}\n`;
            }
        } else {
            finalResult += `No tables found for ${tableName}, skipped view creation\n`;
        }
    }
    
    return finalResult.trim();
$$;

关键修改说明

修复语法错误：修正了原代码中targetTables数组的引号未闭合问题
按表独立处理：对每个目标表单独遍历数据库，收集所有存在的表的查询语句，避免覆盖之前的拼接内容
添加表存在性检查：通过INFORMATION_SCHEMA.TABLES验证表是否存在，避免不存在的表导致SQL执行错误
正确拼接UNION ALL：使用数组的join方法自动在查询语句间添加UNION ALL，最后一个语句不会多余添加
错误处理：添加try-catch捕获检查表和创建视图时的错误，返回更清晰的执行结果
参数化查询：使用绑定变量binds避免SQL注入风险，同时提升代码安全性

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