将雪花般循环遍历每个数据库并列出具有空行的表的过程或函数 [英] proc or function that will loop over each database in snowflake and list tables with empty rows

问题描述

有人能帮忙查找这些零行表吗？

CREATE OR REPLACE PROCEDURE checkrows()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
AS
$
function ExecuteNonQuery(querystring) {

 var out = '';

 cmd1 = {sqlText: select * from information_schema.tables where rows_count = 0;};

 stmt = snowflake.createStatement(cmd1);

 var rs;

 try{

     rs = stmt.execute();

     rs.next();

     out = "SUCCESS: " + rs.getColumnValue(0);

      }

      catch(err) {

      throw "ERROR: " + err.message.replace(/
/g, " ");

      }enter code here

     return out;

    }
    
    $$;

推荐答案

如果您有一个用例，可以获取比上次更改早几分钟的行数(通常为15-90分钟，但最多3小时)，则只需运行以下命令：

select * from "SNOWFLAKE"."ACCOUNT_USAGE"."TABLES"
where TABLE_TYPE = 'BASE TABLE' and DELETED is null and ROW_COUNT = 0;

编辑：由于此操作需要自动化，此SP将返回一个变量数组，其中的对象包含零行的所有表的数据库、架构和表名称。

create or replace procedure FIND_EMPTY_TABLES(DATABASE_PATTERN string) -- Use .* for all databases in account. It will skip SNOWFLAKE and SNOWFLAKE_SAMPLE_DATA
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 
        from ${account.databases[i].name}.INFORMATION_SCHEMA.TABLES
        where TABLE_TYPE = 'BASE TABLE' and ROW_COUNT = 0`)));
}

return out;

//------

function getQuery(sql){
    cmd1 = {sqlText: sql};
    var query = new Query(snowflake.createStatement(cmd1));
    query.resultSet = query.statement.execute();
    return query;
}

function executeSingleValueQuery(columnName, queryString) {
    cmd = {sqlText: queryString};
    stmt = snowflake.createStatement(cmd);
    var rs;
    rs = stmt.execute();
    rs.next();
    return rs.getColumnValue(columnName);
}

function getDatabasesInAccount(databasePattern){
    const SYSTEM_DB_NAMES = ["SNOWFLAKE", "SNOWFLAKE_SAMPLE_DATA"];
    var db = executeSingleValueQuery("name", "show databases");
    var i = 0;
    var dbRS = getResultSet(`select DATABASE_NAME from "${db}".INFORMATION_SCHEMA.DATABASES where rlike (DATABASE_NAME, '${databasePattern}');`);
    var databases = [];
    var db;
    while (dbRS.next()){
        db = new Database(dbRS.getColumnValue("DATABASE_NAME"));
        if (!SYSTEM_DB_NAMES.includes(db)) {
            databases.push(db);
        }
    }
    return new Account(databases);
}

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

function rsToJSON(query) {
    var i;
    var row = {};
    var table = [];
    while (query.resultSet.next()) {
        for(col = 1; col <= query.statement.getColumnCount(); col++) {
            row[query.statement.getColumnName(col)] = query.resultSet.getColumnValue(col);
        }
        table.push(row);
    }
    return table;
}

$$;


call FIND_EMPTY_TABLES('.*');  -- .* is the RegExp pattern that tell it to check all databases except built-in ones.

这篇关于将雪花般循环遍历每个数据库并列出具有空行的表的过程或函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！