scheduled-taskssnowflake-cloud-data-platformcreate-view

Making a view of all tasks - unexpected 'SHOW'

I would like to create a view that shows a full list of active and inactive tasks. Is there a way to do that?

Context: I'm making some automatic reporting that alerts us on failed tasks or inactive tasks.

CREATE OR REPLACE VIEW "DB"."PUBLIC"."SNOWFLAKE_TASK_LIST" COPY GRANTS AS
SHOW tasks IN "DEV"."PUBLIC";

Gives me the error: unexpected 'SHOW'

Solution

  • I have no tasks, but give I suspect this a SHOW thing verse a task thing, I will substitute USERS:

    use role accountadmin;
show users;

    CREATE OR REPLACE VIEW "DB"."PUBLIC"."SNOWFLAKE_USERS_LIST" AS
SHOW users;

    it would seem that SHOWs are not SELECTs. which makes sense, when given they are different, they give results that appear like table base results, but a number of setup functions are not true tables in the sense they are in Postgres.

    This can further be seen in the fact

    select * from (
    show users
);

    gives the error

    SQL compilation error: Object 'SHOW' does not exist or not authorized.

    enter image description here

    and yet

    SHOW users;

select * from table(result_scan(-1));

    works.

    But maybe the TASK_HISTORY table info table has your needed details, OR maybe the show command does work inside a stored procedure, thus you can asses the result_scan(-1) to capture the details.