过程或函数，它将遍历snowflake中的每个数据库并列出具有空行的表

Question

有人可以帮助找到这些零行表吗?

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(/n/g, " ");
}enter code here
return out;
}

$$;

Answer 1

如果您有一个用例，可以获得比上次更改早几分钟的行计数(通常为15-90分钟，但最多3小时)，您可以简单地运行以下命令:

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

编辑:因为这需要自动化，这个SP将返回一个变量数组，其中包含所有零行表的db、schema和表名。

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.

Answer 2

肮脏和快速的选项添加到gregs优秀的答案…显然，如果你喜欢的结果添加'创建或替换zero_row_count_base_tables作为…"…设置为每周或每天重新创建视图，然后在需要时运行视图。

SELECT
'  SELECT  TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME  from UTIL_DB.INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE'  AND ROW_COUNT > 0 '
UNION 
SELECT  concat( 'UNION SELECT  TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME  
FROM ', DATABASES.DATABASE_NAME,'.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'  AND ROW_COUNT > 0   ')
FROM  UTIL_DB.INFORMATION_SCHEMA.DATABASES