Snowflake遍历数据库查空表存储过程重复返回默认表问题求助

原有代码核心错误 #

• 系统库过滤逻辑失效：将Database实例对象和字符串类型的系统库名直接比对，永远匹配失败，导致SNOWFLAKE等系统库也被遍历
• 数据库列表查询逻辑错误：通过executeSingleValueQuery("name", "show databases")只能拿到第一个数据库名，后续基于该数据库查询全局DATABASES视图逻辑冗余且容易出错
• 结果转换函数逻辑错误：遍历结果集时未每次重置row对象，导致上一行数据残留，出现重复返回
• 语法错误：language javascript后多了多余的反引号，会导致存储过程创建失败

修正后完整代码 #

create or replace procedure FIND_EMPTY_TABLES(DATABASE_PATTERN string) 
returns variant
language javascript
execute as owner
as
$$
class Account {constructor(databases){this.databases = databases;}}
class Database {constructor(name) {this.name = name;}}
class Query{constructor(statement){this.statement = statement;}}

var account = getDatabasesInAccount(DATABASE_PATTERN);
var out = [];

for (var i = 0; i < account.databases.length; i++) {
    out = out.concat(rsToJSON(getQuery(
       `select TABLE_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_OWNER, ROW_COUNT 
        from ${account.databases[i].name}.INFORMATION_SCHEMA.TABLES
        where TABLE_TYPE = 'BASE TABLE' and ROW_COUNT = 0`)));
}

return out;

// 工具函数定义
function getQuery(sql){
    const cmd1 = {sqlText: sql};
    var query = new Query(snowflake.createStatement(cmd1));
    query.resultSet = query.statement.execute();
    return query;
}

function getResultSet(sql){
    const cmd  = {sqlText: sql};
    const stmt = snowflake.createStatement(cmd);
    return stmt.execute();
}

function getDatabasesInAccount(databasePattern){
    const SYSTEM_DB_NAMES = ["SNOWFLAKE", "SNOWFLAKE_SAMPLE_DATA"];
    // 直接查询全局 INFORMATION_SCHEMA.DATABASES 视图获取符合规则的数据库
    const dbRS = getResultSet(`select DATABASE_NAME from INFORMATION_SCHEMA.DATABASES where rlike(DATABASE_NAME, '${databasePattern}', 'i')`);
    const databases = [];
    while (dbRS.next()){
        const dbName = dbRS.getColumnValue("DATABASE_NAME");
        // 过滤系统库：直接比对字符串
        if (!SYSTEM_DB_NAMES.includes(dbName.toUpperCase())) {
            databases.push(new Database(dbName));
        }
    }
    return new Account(databases);
}
  
function rsToJSON(query) {
    const table = [];
    while (query.resultSet.next()) {
        // 每次循环重置row对象，避免数据残留
        const row = {};
        for(col = 1; col <= query.statement.getColumnCount(); col++) {
            row[query.statement.getColumnName(col)] = query.resultSet.getColumnValue(col);
        }
        table.push(row);
    }
    return table;
}
$$;

调用示例 #

传入正则表达式匹配目标数据库名，例如匹配所有名称带DEV的数据库：

CALL FIND_EMPTY_TABLES('.*DEV.*');

返回结果为JSON数组，每个元素对应一张空表的元数据信息。

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