Run Multiple Snowflake UDF's Given Parameters in an Array

user_17054583 0
Viewed 93

Basically, what I really want to do is

for i in arr:
 foo(i)

where foo is a snowflake script that looks up records matching i and merges them into a different table.

Is there anyway to do this?

snowflake-cloud-data-platform
2 Answers

SQL Scripting is currently not supported yet, but simple Stored Procedure with something like below should do the trick:

create or replace table table1 (a int);
create or replace table table2 (a int);
insert into table1 values (1), (2), (3), (4), (5);
select * from table1;
select * from table2; -- empty at this stage
 
CREATE OR REPLACE PROCEDURE my_proc(INPUT VARCHAR) 
RETURNS BOOLEAN 
LANGUAGE JAVASCRIPT 
AS 
$$
    function my_insert(i) {
        var stmt = snowflake.createStatement( {
            sqlText: "INSERT INTO table2 SELECT * FROM table1 WHERE a = ?",
            binds: [i]
        } ); 
        stmt.execute(); 
    }
    
    arr = INPUT.split(",");
    for (var i=0; i<arr.length; i++) {
      my_insert(arr[i])
    }
    
    return true;
$$ ; 


call my_proc('1,2,5');
select * from table2; -- should have values 1, 2 and 5
user_98793790

For a purely Snowflake solution then you can only do this within a stored procedure.

Obviously, any coding language (that can connect to Snowflake) can do this.

user_136583990
Related