CODEWITHSUNDEEP
tasksnowflake-cloud-data-platformschedule
Ser
Ser

Reputation: 69

Snowflake - Schedule a Script.sql- with multiple instructions (insert, delete, create view, create table, etc)

I come from making a script.sql to fill and create tables to a database. ( 10000 lines of code)

The script is big, since I have to apply some logic to be able to fill some tables, all the instructions are like this ( BY EXEMPLE):

That is, my script is made up of many instructions, when I run it in the WorkSheet, everything works perfectly.

My question is, how can I fit all my script logic into one task? Any architecture advice before I start thinking about CRAZY options?

BY EXEMPLE

--- 1 statement CREATE OR REPLACE TABLE TEST.AFFAIRES.CLIENT .....;

--- 2 statement DROP TABLE TEST.AFF.CLIENT;

--- 3 statement CREATE OR REPLACE VIEW TEST.AFF.PROVIDER .........;

--- 100 statements etc....

Thank you for your advices

Upvotes: 1

Views: 1584

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59325

The answer from Greg is correct: A stored procedure can run all these statements in a single task.

As an alternative you could use Snowflake's stream and tasks, where you define a task with a single SQL statement, but then other tasks are automatically executed once this root task completes. This can start a chain like what you describe:

root_task -> create_task -> drop_task -> view_task

For example, 3 connected tasks:

create or replace task mytask_minute
  warehouse = test_small
  schedule = '1 MINUTE'
  when SYSTEM$STREAM_HAS_DATA('just_timestamps_stream')
as
delete from just_timestamps_stream_table;

create or replace task mytask_minute_child1
  warehouse = test_small
  after mytask_minute
as
insert into just_timestamps values(current_timestamp, 'child1');

create or replace task mytask_minute_child2
  warehouse = test_small
  after mytask_minute
as
insert into just_timestamps values(current_timestamp, 'child2');

Full example here:

https://github.com/fhoffa/snowflake_snippets/blob/main/stream_and_tasks/minimal.sql

Upvotes: 0

Greg Pavlik
Greg Pavlik

Reputation: 11086

You can write them into a stored procedure to run them all from a single task. If you don't want to write your own stored procedure, I wrote one that will read SQL statements written to a table and run them one at a time. That way all you have to do is store the statements in a table with a numbered order column to tell it the sequence. It includes simple variable substitutions.

I'm planning to move this to Snowflake Labs (it did not exist yet when I wrote this), and you can grab it here now if you're interested or just want to see how it works to try something similar:

https://github.com/GregPavlik/snowflake_script_runner

Upvotes: 3

Related Questions