0

I am new to Snowflake and am trying to create my first task.

CREATE TASK task_update_table
WAREHOUSE = "TEST"
SCHEDULE = 'USING CRON 0 5 * * * America/Los_Angeles'
AS
INSERT INTO "TEST"."WEB"."SOME_TABLE" (ID,VALUE1,VALUE2,VALUE3)
WITH CTE AS
(SELECT 
ID
,VALUE1
,VALUE2
,VALUE3
FROM OTHER_TABLE
WHERE ID NOT IN (SELECT ID FROM "TEST"."WEB"."SOME_TABLE")
)
SELECT      
ID,VALUE1,VALUE2,VALUE3
FROM CTE

I got a message that the task was created successfully

"Task task_update_table successfully created"

I then try to run show tasks in schema SHOW TASKS IN "TEST"."WEB" and get 0 rows as a result. What am I doing wrong? why is the task not showing?

I did all of this under sysadmin and was using the same warehouse, db and schema.

jenn_maijy
  • 1

1 Answers1

0

There are some limitations around show commands that might be blocking you, particularly "SHOW commands only return objects for which the current user’s current role has been granted the necessary access privileges".

https://docs.snowflake.com/en/sql-reference/sql/show.html#general-usage-notes

I suspect the task was created by a different role (therefore owned by a different role), or perhaps it was created in different database or schema.

To find it, I'd recommend running the following using a role such as ACCOUNTADMIN.

show tasks in account;
SELECT *
FROM (
    SELECT *
    FROM   TABLE(RESULT_SCAN(LAST_QUERY_ID())))
WHERE "name" = 'TASK_UPDATE_TABLE';

While testing and learning in Snowflake, it is critical you set your session "context" correctly, using commands like this:

USE ROLE my_role_here;
USE WAREHOUSE my_warehouse_here;
USE DATABASE my_database_here;
USE SCHEMA my_schema_here;

Doing those four commands, or setting defaults for them for your user will help you tremendously when learning.

I hope this helps...Rich

Rich Murnane
  • 2,697
  • 1
  • 11
  • 23
  • Thank you so much, Rich! The strange thing is that I created a task and then ran the show command immediately after without changing the role, db or schema. I tried following your advice and still nothing, 0 rows. Could it be that tasks are not available on the standard instance level? or should I contact support? – jenn_maijy Jul 07 '20 at 20:53
  • regarding standard edition - the tasks and streams features should be available to your deployment - see https://docs.snowflake.com/en/user-guide/intro-editions.html#data-pipelines. Did you try to run the show command with a role with elevated privileges (e.g. ACCOUNTADMIN)? If you have and cannot find the task, then a support request would probably be my next recommendation. – Rich Murnane Jul 08 '20 at 11:06