在计划任务中调用时，雪花工作程序未成功执行 [英] Snowflake a working procedure is not being successfully executed when calling it within a scheduled task

问题描述

我在雪花上有一个完全运行的过程，调用它时完全没有错误:

I am having a fully running procedure on snowflake with no errors at all when calling it:

call ADD_MONTHLY_OBSERVATION_VALUES('@test_azure_blob_stg/Monthly_Report.csv', 'GENERIC_CSV_FORMAT');

我想将此命令包装到一个任务中，并将其安排到特定时间，如下所示:

I wanted to wrap this command into a task and schedule it to a specific time like so:

CREATE OR REPLACE TASK ADD_MONTHLY_OBSERVATION_VALUES_TASK
    WAREHOUSE = 'DEV_WH'
    TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
    //SCHEDULE = 'USING CRON 0 6-7 * * SUN,MON,TUE,WED,THU Asia/Dacca'
    //Schedule for each minute
    SCHEDULE = 'USING CRON * * * * * UTC'
AS
    call ADD_MONTHLY_OBSERVATION_VALUES('@test_azure_blob_stg/Monthly_Report.csv', 'GENERIC_CSV_FORMAT');

然后我恢复了工作:

ALTER TASK ADD_MONTHLY_OBSERVATION_VALUES_TASK RESUME;

当我查看任务历史时:

状态:成功

ERROR_CODE:NULL

ERROR_CODE: NULL

ERROR_MESSAGE: NULL

ERROR_MESSAGE: NULL

QUERY_START_TIME:2021-01-27 16:00:06.198 -0800

QUERY_START_TIME: 2021-01-27 16:00:06.198 -0800

COMPLETED_TIME: 2021-01-27 16:00:24.902 -0800

COMPLETED_TIME: 2021-01-27 16:00:24.902 -0800

返回值:空

实际上，当所有内容都已成功添加/更新后，该过程将返回一个字符串DONE.

Actually the procedure will return a string DONE when everything have been successfully added/updated.

运行时:

show tasks;

过程的状态是started.

为什么在使用任务调用时过程没有被执行?

Why the procedure is not being executed when called using a task?

有新数据从 Azure 上传到暂存文件，因此该过程应检测新提交并启动插入过程.

There was new data uploaded to the staged file from Azure, so the procedure should detect the new submissions and start the insert process.

推荐答案

如果在任务之外运行该过程，则必须使用任务所有者角色来确保正确测试.如果您的过程在具有任务所有者权限的任务之外工作，则它也应该在任务内工作.

If you run the procedure outside the task, you have to use the task owner role to ensure correct testing. If your procedure works outside the task with task owner privileges, it should also work within the task.

所以...我认为您的访问/权限管理存在问题.任务执行的 SQL 语句只能对角色具有所需权限的 Snowflake 对象进行操作.-->您必须向您的任务所有者角色授予对过程中对象的更多权限.

So... I assume there is a problem with your access/rights management. SQL statements executed by the task can only operate on Snowflake objects on which the role has the required privileges. --> You have to grant more privileges on the objects within your procedure to your task owner role.

在此处查看更多信息:https://docs.snowflake.com/en/sql-reference/sql/create-task.html

这篇关于在计划任务中调用时，雪花工作程序未成功执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！