Snowflake存储过程使用语句

Question

我正在处理一个Snowflake存储过程，该过程将采用从一个数据库更改为另一个数据库的视图。我正在尝试

var sqlCommand = `
SELECT a.OBJECT_NAME, a.OBJECT_SCHEMA, a.OBJECT_TYPE, d.VIEW_DEFINITION
FROM VIEW_OBJECT_LIST a
INNER JOIN DB_DEV.INFORMATION_SCHEMA.VIEWS d ON a.OBJECT_NAME = d.TABLE_NAME AND a.OBJECT_SCHEMA = d.TABLE_SCHEMA
INNER JOIN DB_QA.INFORMATION_SCHEMA.VIEWS q ON a.OBJECT_NAME = q.TABLE_NAME AND a.OBJECT_SCHEMA = q.TABLE_SCHEMA AND d.VIEW_DEFINITION != q.VIEW_DEFINITION;`;

var viewList = snowflake.createStatement({ sqlText: sqlCommand}).execute();

while(viewList.next()){
var sql = viewList.VIEW_DEFINITION;
var sql = 'USE DB_QA; ' || sql;
snowflake.createStatement({ sqlText: sql }).execute();
}

但是得到错误消息

Unsupported statement type 'USE'. At Statement.execute

在Snowflake存储过程中是否有一种方法可以运行USE语句作为对Snowflakeneneneba API调用的一部分？

Answer 1

创建此存储过程时使用的执行权限是什么？如果您正在使用"；作为所有人执行"；在create过程语句中，您可能无法使用以下语句以外的语句：->

Restrictions on SQL Statements
Although caller’s rights stored procedures can execute any SQL statement that the caller has sufficient privileges to execute outside a stored procedure, owner’s rights stored procedures can call only a subset of SQL statements.
The following SQL statements can be called from inside an owner’s rights stored procedure:
SELECT.
DML.
DDL. (See above for restrictions on the ALTER USER statement.)
GRANT/REVOKE.
Variable assignment.
DESCRIBE and SHOW. (See limitations documented above.)
Other SQL statements cannot be called from inside an owner’s rights stored procedure.

请尝试使用"；作为召集人执行"；这允许雪花本机sql命令，如LIST、USE DATABASE、RM等

Answer 2

我认为唯一的问题是您没有在USE语句中定义什么是DB_QA。我假设它是数据库的名称，所以您只需要将该行修改为：

var sql = 'USE DATABASE DB_QA; ' || sql

不过，我不确定snowflake.createStatement是否允许传入多语句查询。你必须让我知道这是否有效。