sql - how to loop through date string parameter in javascript stored procedure snowflake

Question

I am new to javascript and snowflake uses js like syntax in stored procedures.

I am trying to create dynamic SQL so that it loops between start_date to end_date to copy into from different s3 folders based on dates

CREATE PROCEDURE load_dynamic_s3path_to_table(begin_date strings, end_date strings)
AS
$$

    
    var stmt = snowflake.createStatement(
        {sqlText: "copy into table from s3://test/2020-01-01/"}
        );
    var rs = stmt.execute();

$$;

Based on the date parameters in the stored procedure, for example begin_date 2020-01-01, end_date 2020-01-03.

I want to execute the copy into commands 3 times;

copy into table from s3://test/2020-01-01/
copy into table from s3://test/2020-01-02/
copy into table from s3://test/2020-01-03/

I imagine the pseudo code to be something like this:

CREATE PROCEDURE load_dynamic_s3path_to_table(begin_date strings, end_date strings)
AS
$$
    var dates = begin_date
    while date(dates)>=date(begin_date) and date(dates)<date(end_date)
        var stmt = snowflake.createStatement(
            {sqlText: "copy into table from s3://test/" + dates}
            );
        var rs = stmt.execute();
        dates+=1

$$;

Could anyone help turn the pseudo code into the right js syntax and executable in snowflake?

Thank you!

question from:https://stackoverflow.com/questions/65881058/how-to-loop-through-date-string-parameter-in-javascript-stored-procedure-snowfla

Answer 1

Managing dates in JavaScript is not trivial, but this stored procedure does what you want:

CREATE OR REPLACE PROCEDURE dates("begin_date" string, "end_date" string)
RETURNS string
LANGUAGE javascript
AS
$$
    function printed_date(d) {
        return d.toISOString().split("T")[0]
    }

    function execute_query(d) {
        var stmt = snowflake.createStatement({
            sqlText: "select '" + d + "' x"
            // "copy into table from s3://test/" + d
        });
        var rs = stmt.execute();
        rs.next();
        return rs.getColumnValue(1);
    }

    var running_date = new Date(begin_date)
    var last_day = new Date(end_date)
    var total_days = (last_day - running_date) / (1000 * 60 * 60 * 24);
    cs = []
    for(var iter=0; iter<=total_days; iter++ ) {
        cs.push(execute_query(printed_date(running_date)));
        running_date.setDate(running_date.getDate() + 1)
   }
   return cs
$$;

call dates('2020-10-01', '2020-10-04');
-- 2020-10-01,2020-10-02,2020-10-03,2020-10-04