1

we are currently running our first analytics prototype on Snowflake. The objective is to create a comprehensive analysis result table that can be used for reporting based on ~60 structured raw data tables. We created all the necessary SQL scripts using the built-in worksheet functionality. In total we wrote around 80 worksheets, each with 5-10 sql statements. As a next step we would like to automate the execution of these worksheets in a simple, sequential order. However, Tasks and Stored Procedures, both built-in solutions we looked into, fail to execute more than one SQL-statement in a single call.

Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

How are you guys handling this? Do we really have to write individual tasks/stored procedures for every single sql statement? In our case this would easily amass to more than 500 of those.

Very much interested in your input, thanks!

Stifter
  • 31
  • 1
  • 6

3 Answers3

1

It is possible to run multiple statements using tasks when code is wrapped with BEGIN ... END block:

CREATE OR REPLACE TASK test_task
WAREHOUSE = COMPUTE_WH
AS
BEGIN
  CREATE OR REPLACE TABLE test_tab(i INT);

  INSERT INTO test_tab(i) VALUES (1);

  INSERT INTO test_tab(i) SELECT i * 10 FROM test_tab;
END;

Call:

EXECUTE TASK test_task;

SELECT *
FROM TABLE(information_schema.task_history())
ORDER BY scheduled_time;


SELECT * FROM test_tab;
-- 1
-- 10
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

I think your problem is you're using the wrong tool ;) The worksheets are just not meant for batch processing, if you want to do that you should use the snowsql client:

https://docs.snowflake.com/en/user-guide/snowsql-use.html

MMV
  • 920
  • 4
  • 9
  • Thanks, will give it a try! However, the stored procedure "problem" will remain - correct? So even if I use the SnowSQL client, I still won't be able to create and call a SP with more than 1 SQL statement? – Stifter Jul 17 '20 at 11:18
  • Even the Snowflake docs have examples with multiple calls, so I'm quite sure you're doing something wrong. Tbh I'm quite sure I did personally execute multicall stored procs from webui even. – MMV Jul 19 '20 at 04:06
  • Hi, could you post the links? I was not able to find SPs with multiple SQL calls. Thank you! – Stifter Jul 20 '20 at 07:32
  • https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html has a bunch of examples and I can see some with multiple calls – MMV Jul 20 '20 at 07:52
  • I am sorry, but I don't find what I need. Some of the examples in the docs are recursive, but I am looking for an option to truly execute several SQL statement within one stored Procedure call. – Stifter Jul 21 '20 at 05:32
  • https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#protecting-privacy and scroll down to the first code example – MMV Jul 21 '20 at 07:26
  • Ok, got it now. It is still not quite what we are looking for because you need to reassign `sql_cmd` and execute it when you want to change the content of the statement. I am looking for an option to have several SQL statements concatenated and stored in a variable and then only execute it once. – Stifter Jul 21 '20 at 11:59
  • 1
    i don't think there's an option for that and i don't think there's any real gain in that either. – MMV Jul 21 '20 at 12:47
0

You cannot use worksheet to create stored procedures. You need to use Javascript API (until SQL stored procedures are made available).

https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html

user13955159
  • 1
  • 1
    Sorry if this wasn't clear from the original post. We use the Javascript API. The err message was thrown after calling the SP written in Javascript. – Stifter Jul 20 '20 at 07:33
  • @Stifter did you ever solve this? Having the exact same issue. – mikelowry Apr 20 '21 at 18:33