雪花动态SQL：相当于DBMS_SQL/SP_Executesql

问题描述

我正在搜索允许执行动态SQL(理想情况下使用绑定参数)的sp_executesql/DBMS_SQL等效项。

基本原理：生成要在SQL中运行并从SQL执行的代码

我知道Snowflake没有包括控制流语法(While/If-Then/Try-Catch)的过程性SQL组件，并且可以通过存储过程中的JavaScript代码减轻此类构造的影响。

示例场景：

生成任意SQL：此处生成表格

SELECT create_table_sql FROM ( 
 SELECT LISTAGG(REPLACE(CHAR(13) || ',a<index> INT DEFAULT UNIFORM(1, 10000, RANDOM())', '<index>', seq8()+1),'')
       WITHIN GROUP(ORDER BY seq8()) AS column_list
   
   ,REPLACE(REPLACE(
          'CREATE OR REPLACE TABLE <table_name>(id INT  <column_list>);'
          ,'<table_name>', 'wide5')
          ,'<column_list>', column_list) AS create_table_sql
 FROM TABLE(GENERATOR(rowcount => 5))
);

输出：

CREATE OR REPLACE TABLE wide5(id INT  
,a1 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a2 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a3 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a4 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a5 INT DEFAULT UNIFORM(1, 10000, RANDOM()));

现在的目标是从WebUI执行它。我的第一个想法是将它赋给变量。由于大小限制(小问题)而失败：

SET sql_text = (SELECT create_table_sql FROM ...);

此处应该类似于EXECUTE IMMEDIATE/EXEC或其他RDBMS中已知的参数化对等项。

....(generated_code)

我创建了自己的简化版本：

CREATE OR REPLACE PROCEDURE execute_immediate(sql_param STRING)
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
 var rs = snowflake.execute( { sqlText: SQL_PARAM});   
 return 'Done.';
$$;

并将子查询直接作为参数传递(此处Snowflake大放异彩)：

CALL execute_immediate(subquery);

示例：

CALL execute_immediate(
 SELECT create_table_sql 
 FROM ( 
  SELECT LISTAGG(REPLACE(CHAR(13) || ',a<index> INT DEFAULT UNIFORM(1, 10000, RANDOM())', '<index>', seq8()+1),'') 
       WITHIN GROUP(ORDER BY seq8()) AS column_list
   
   ,REPLACE(REPLACE(
          'CREATE OR REPLACE TABLE <table_name>(id INT  <column_list>);'
          ,'<table_name>', 'wide5')
          ,'<column_list>', column_list) AS create_table_sql
 FROM TABLE(GENERATOR(rowcount => 5)))
);

创建表时正在检查SELECT查询：SELECT * FROM wide5。

它正在起作用，但我相信它可以做得更好。

我尝试过但发现不完全令人满意的其他考虑因素和替代方案：

动态SQL可能很难编写且容易出错，但使用参数绑定/引用标识符和限制用户输入仍然是安全的。

推荐答案

可以使用Snowflake Scripting：

DROP TABLE IF EXISTS wide5;

-- to be run with Snowsight
DECLARE
    SQL STRING;
BEGIN
    SELECT create_table_sql 
    INTO :SQL 
    FROM ( 
     SELECT LISTAGG(REPLACE(CHAR(13) || ',a<index> INT DEFAULT UNIFORM(1, 10000, RANDOM())'
           , '<index>', seq8()+1),'')
           WITHIN GROUP(ORDER BY seq8()) AS column_list

       ,REPLACE(REPLACE(
              'CREATE OR REPLACE TABLE <table_name>(id INT  <column_list>);'
              ,'<table_name>', 'wide5')
              ,'<column_list>', column_list) AS create_table_sql
     FROM TABLE(GENERATOR(rowcount => 5))
    );

    EXECUTE IMMEDIATE :SQL;
END;


SELECT * FROM wide5;
-- ID   A1  A2  A3  A4  A5