Snowflake存储过程Use语句
Snowflake存储过程Use语句
提问于 2020-10-29 07:36:28
我正在使用一个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语句作为Snowflake API调用的一部分运行？
回答 2
Stack Overflow用户
发布于 2020-10-29 11:17:54
我认为唯一的问题是您还没有在您的USE语句中定义什么是DB_QA。我假设它是一个数据库的名称，所以您只需要将该行修改为：
var sql = 'USE DATABASE DB_QA; ' || sql不过，我不确定snowflake.createStatement是否允许传入多语句查询。如果有效的话，你得让我知道。
Stack Overflow用户
发布于 2020-11-08 07:27:48
创建此存储过程时使用的execute权限是什么？如果在create procedure语句中使用"EXECUTE AS OWNER“，则可能无法使用以下语句以外的语句:->
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.请尝试使用"EXECUTE AS CALLER“创建相同的过程，这允许雪花原生sql命令，如LIST，USE DATABASE，RM等。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64582859
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号