Snowflake - How can I query the stream's metadata and save to table

Question

I have various streams but some of the streams are becoming stale. To avoid them becoming stale, I want to put some process in place which can read the 'show stream' property 'stale after', if it is only 1 day left, run a process to refresh the stream.

Answer 1

To achieve your goal you have to capture the output of SHOW STREAMS https://docs.snowflake.com/en/sql-reference/sql/show-streams.html. You can start building a stored procedure that runs it and returns its output as a resultset using TABLE(RESULT_SCAN(LAST_QUERY_ID())) similar to the following one, which could be enriched by a parameter for the time window you want to check (your "1 day left) and a subsequent CREATE OR REPLACE STREAM.

Please note this is not a full solution to your problem but only half of it as it does not include the action required to re-create the stale streams.

CREATE OR REPLACE PROCEDURE sp_show_stream_stale() 
RETURNS VARIANT NOT NULL 
LANGUAGE Javascript  
EXECUTE AS Caller 
AS 
$$ 
var sql_command0 = snowflake.createStatement({ sqlText:`show streams in database`});

var sql_command1 = snowflake.createStatement({ sqlText:`SELECT "created_on"
                                                            , "name"
                                                            , "database_name"
                                                            , "schema_name"
                                                            , "owner"
                                                            , "comment"
                                                            , "table_name"
                                                            , "type"
                                                            , "stale"
                                                            , "mode"
                                                            , "stale_after" 
                                                        FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))`});

try { 
    sql_command0.execute();
    var db = sql_command1.execute(); 
    var json_rows = {}; 
    var array_of_rows = []; 
    var COLUMNS = ["created_on","name","database_name","schema_name","owner", "comment", "table_name", "type", "stale", "mode", "stale_after"]; 
    var row_num = 1;
    while (db.next()) {  
        json_rows = {}; 
        for (var col_num = 0; col_num < COLUMNS.length; col_num = col_num + 1) { 
            var col_name = COLUMNS[col_num]; 
            json_rows[col_name] = db.getColumnValue(col_num + 1);
        } 
        array_of_rows.push(json_rows); 
        ++row_num; 
    }
    return array_of_rows; 
}
catch (err) { 
    return "Failed: " + err;
}
$$;

As the resultset is a single JSON, you can run the stored procedure and soon after the following SELECT statement to get the resultset in tabular format.

  CALL sp_show_stream_stale();

  SELECT value:created_on::datetime as "created_on",
    value:name::string as "name",
    value:database_name::string  as "database_name",
    value:schema_name::string as "schema_name",
    value:owner::string as "owner",
    value:comment::string as "comment",
    value:table_name::string as "table_name",
    value:type::string as "type",
    value:stale::string as "stale",
    value:mode::string as "mode",
    value:stale_after::datetime as "stale_after"
  FROM (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())))
      , LATERAL FLATTEN(Input => sp_show_stream_stale)    
  WHERE DATEDIFF(Day, current_timestamp, value:stale_after::datetime) <= 1 ;