如何删除雪花中创建的临时表 [英] How to drop temp tables created in snowflake

问题描述

我正在通过ODI将数据加载到由c $创建的雪花临时表中，加载成功后需要将其删除，如何删除这些临时表会引起您的建议

I am loading data through ODI into snowflake temp tables created with c$ needs to be dropped after load successful,how to drop those temp tables appreciate your suggestion

推荐答案

如果您仍然需要这样做，我将编写一个存储过程，该过程将获取动态生成的SQL列表，并一次执行一行。您可以使用它运行由选择查询产生的任何生成的SQL语句列表，包括删除所有与模式（例如c $％）匹配的表。首先，这是存储过程：

If you still need this, I wrote a stored procedure that will take a list of SQL generated dynamically and execute the lines one at a time. You can use it to run any list of generated SQL statements resulting from a select query, including dropping all tables matching a pattern such as c$%. First, here's the stored procedure:

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;

$$

您可以使用此存储过程来运行任何动态生成的使用以下脚本批量处理SQL语句。运行最顶层的查询，显然可以执行以该查询测试作为参数的存储过程：

You can use this stored procedure to run any dynamically generated SQL statements in batch using the following script. Run the topmost query and it will be obvious what running the stored procedure with that query test as the parameter will do:

-- This is a select query that will generate a list of SQL commands to execute in batch. 
-- This SQL will generate rows to drop all tables starting with c$. With minor edits
-- you could limit it to a specific database or schema.
select 'drop table ' || TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || "TABLE_NAME" as SQL_COMMAND
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like 'c$%';

-- As a convenience, 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;

这篇关于如何删除雪花中创建的临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！