процесс или функция, которая будет выполнять цикл по каждой базе данных в snowflake и перечислять таблицы с пустыми строками

ProgramBox

процесс или функция, которая будет выполнять цикл по каждой базе данных в snowflake и перечислять таблицы с пустыми строками

Post author:admin
Запись опубликована:10 января, 2022
Post category:Вопросы по программированию

#snowflake-cloud-data-platform

Вопрос:

Может ли кто-нибудь помочь найти эти таблицы с нулевыми строками?

 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;

    }
    
    $;

1. Это что, вопрос?

2. В названии был вопрос «может ли кто-нибудь помочь», так что да. Моя очистка удалила это. Я изменю тело, чтобы отразить вопрос об операции.

3. Помимо опечатки ROWS_COUNT против ROW_COUNT и не перебирал набор результатов, что не так с приведенным выше кодом? Я немного сбит с толку этим запросом.

4. загрузка автоматизирована, поэтому я ищу процедуру/функцию, которая может перебирать базы данных и возвращать пустые строки, чтобы я мог создавать задачу и планировать выполнение задачи для вызова процесса каждый час

5. Он создает proc, но когда я вызываю proc, он возвращает одну строку в тестовой базе данных, в которой я создал таблицы без строки, я думаю, что в ней отсутствуют некоторые аргументы вверху в фигурных скобках рядом с именем proc, и я использовал разные аргументы, тогда proc не сработал

Ответ №1:

Если у вас есть вариант использования, в котором можно получить количество строк на несколько минут старше последнего изменения (обычно 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.

1. Что не так с INFORMATION_SCHEMA? Почему вы предложили ACCOUNT_USAGE, в то время как INFORMATION_SCHEMA не имеет задержек?

2. INFORMATION_SCHEMA охватывает только одну базу данных, и, по-видимому, поэтому операция искала хранимую процедуру для сбора данных в разных базах данных. Если задержка неприемлема, то лучше всего использовать генератор SP или SQL. Сначала я хочу понять вариант использования.

3. загрузка автоматизирована, поэтому я ищу процедуру/функцию, которая может перебирать базы данных и возвращать пустые строки, чтобы я мог создавать задачу и планировать выполнение задачи для вызова процесса каждый час

4. Итак, поскольку вам нужно проверить это с помощью автоматизации, вот SP, который вернет массив вариантов с базой данных, схемой и таблицей каждой таблицы с нулевой строкой.

Ответ №2:

Грязный и быстрый вариант, добавляющий Грегу отличный ответ … очевидно, что если вам нравятся результаты, добавьте » создать или заменить таблицы 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