CODEWITHSUNDEEP
snowflake-cloud-data-platform
geek94
geek94

Reputation: 473

How to read warehouse name from a different table in snowflake task creation

I want to create a snowflake task where the current active warehouse will be assigned to the task by making a query to another snowflake table. I want to do something like this:

CREATE OR REPLACE TASK task_name
warehouse = {select query for different table}
schedule = '1 minute'
As
some SQL;

Could someone help me to understand if and how it could be done.

Upvotes: 0

Views: 447

Answers (3)

Limonka
Limonka

Reputation: 846

If you want the task to choose an active warehouse for every run, I'm afraid it is not possible (would be a lovely feature though).

But if you want to use a warehouse that is active at the moment of creation, which doesn't make much sense to me, then I'd go with something like this:

show warehouses;
set wh = (select top 1 "name" from table(result_scan(last_query_id())) where "state" = 'STARTED');

CREATE TASK task_name
  warehouse = $wh
  SCHEDULE = '1 minute'
AS
    select 1;

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

building it up:

SELECT CURRENT_WAREHOUSE();

gives:

CURRENT_WAREHOUSE()
COMPUTE_WH 
BEGIN
    let task_sql text := 'SELECT 1';
    let sql text := 'CREATE OR REPLACE TASK task_name warehouse = ' || CURRENT_WAREHOUSE() || ' schedule = ''1 minute'' AS ' || task_sql;

    return sql;
END;

gives:

anonymous block
CREATE OR REPLACE TASK task_name warehouse = COMPUTE_WH schedule = '1 minute' AS SELECT 1

which if we test:

CREATE OR REPLACE TASK task_name warehouse = COMPUTE_WH schedule = '1 minute' AS SELECT 1;

works just fine.

status
Task TASK_NAME successfully created.

thus:

BEGIN
    let task_sql text := 'SELECT 1';
    let sql text := 'CREATE OR REPLACE TASK task_name warehouse = ' || CURRENT_WAREHOUSE() || ' schedule = ''1 minute'' AS ' || task_sql;

    EXECUTE IMMEDIATE sql;
    return sql;
END;

also works just fine:

describe task task_name;
created_on name id database_name schema_name owner comment warehouse schedule predecessors state definition condition allow_overlapping_execution
2022-04-04 17:06:17.858 -0700 TASK_NAME 01a36466-6310-6f1f-0000-000000000002 TEST TEST ACCOUNTADMIN COMPUTE_WH 1 minute suspended SELECT 1 false

Upvotes: 0

Pankaj
Pankaj

Reputation: 2746

If its ok to set SQL variable then one approach as below - Use/replace what-ever query in below to get warehouse name

set my_wh=(select current_warehouse());
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

select $my_wh;
+------------+
| $MY_WH     |
|------------|
| COMPUTE_WH |
+------------+

CREATE TASK t1
  warehouse = $my_wh
  SCHEDULE = '1 minute'
AS
INSERT INTO emp(empid, time_added) VALUES(100,CURRENT_TIMESTAMP);
+-------------------------------+
| status                        |
|-------------------------------|
| Task T1 successfully created. |
+------------------------

Upvotes: 2

Related Questions