A classic DBA technique to run a large number of SQL statements is to create them using a concatenated select statement. Suppose you need to delete all tables that end with “TEST”. You can list them in Snowflake’s INFORMATION_SCHEMA using:
select “TABLE_NAME” from INFORMATION_SCHEMA.TABLES where “TABLE_NAME” ilike ‘%TEST’;
If you need to drop a handful of tables that way, a list if enough. If there are dozens or hundreds, this works better:
select 'drop table ' || "TABLE_NAME" || ';' from INFORMATION_SCHEMA.TABLES where "TABLE_NAME" ilike '%TEST';
The above example generates a drop statement for each table in a database that ends with TEST. You can use this technique to generate bulk SQL to perform a wide range of management tasks. For example you may want to change the ownership of tables in bulk like this:
SELECT 'grant ownership on table ' ||
table_name ||
' to role my_new_role copy grants;'
AS SQL_COMMAND
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE grantor = 'old_grant_role';
Executing this SQL will generate rows that look like this:
Row | SQL_COMMAND |
1 | grant ownership on table CUSTOMER to role my_new_role copy grants; |
2 | grant ownership on table LINEITEM to role my_new_role copy grants; |
3 | grant ownership on table NATION to role my_new_role copy grants; |
4 | grant ownership on table ORDERS to role my_new_role copy grants; |
You can see where this is heading. A SQL statement generates statements to run, and then someone — or preferably something by automation — runs them.
Here is that something. The Snowflake stored procedure below will:
- Accept a string parameter that is a SQL statement designed to generate rows of SQL statements to execute.
- Execute the input SQL statement to generate a list of SQL statements to run.
- Run all statements identified by the “SQL_COMMAND” column one at a time.
use database TEST;
use warehouse TEST;
create or replace procedure RunBatchSQL(sqlCommand String)
returns string
language JavaScript
as
$$
/**
* Stored procedure to execute multiple SQL statements generated from a SQL query
* Note that this procedure will always use the column named "SQL_COMMAND"
*
* @param {String} sqlCommand: The SQL query to run to generate one or more SQL commands
* @return {String}: A string containing all the SQL commands executed, each separated by a newline.
*/
cmd1_dict = {sqlText: SQLCOMMAND};
stmt = snowflake.createStatement(cmd1_dict);
rs = stmt.execute();
var s = '';
while (rs.next()) {
cmd2_dict = {sqlText: rs.getColumnValue("SQL_COMMAND")};
stmtEx = snowflake.createStatement(cmd2_dict);
stmtEx.execute();
s += rs.getColumnValue(1) + "\n";
}
return s;
$$
;
-- This is a select query that will generate a list of SQL commands to excute, in this case some grant statements.
-- This SQL will generate rows to grant select on all tables for the DBA role (change to specify another role).
select distinct ('grant select on table ' || table_schema || '.' || table_name || ' to role DBA;') AS SQL_COMMAND
from INFORMATION_SCHEMA.TABLE_PRIVILEGES
where TABLE_SCHEMA <> 'AUDIT'
order by SQL_COMMAND;
-- As a convienience, this grabs the last SQL run so that it's easier to insert into the parameter used to call the stored procedure.
set query_text = ( select QUERY_TEXT
from table(information_schema.query_history(result_limit => 2))
where SESSION_ID = Current_Session() and QUERY_TYPE = 'SELECT' order by START_TIME desc);
-- Confirm that the query_text variable has the correct SQL query to generate our SQL commands (grants in this case) to run.
select $query_text;
-- Run the stored procedure. Note that to view its output better, double click on the output to see it in multi-line format,
Call RunBatchSQL($query_text);
--Check the last several queries run to make sure it worked.
select QUERY_TEXT
from table(information_schema.query_history(result_limit => 100))
where SESSION_ID = Current_Session() order by START_TIME desc;