Snowflake 'get' statement within stored procedure not working

Question

Problem statement: snowflake 'get' statement does not work inside a snowflake stored procedure.

CREATE OR REPLACE PROCEDURE SP_UNLOAD_DYNAMIC("prefix" varchar)
    RETURNS varchar(1000)
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
    var result  = "";

    try {
            
        var sql00   = "select concat('" + prefix + "', to_char(ts,'yyyymmddhh24miss'),'.txt') from ts";
        var stmt00  = snowflake.execute({ sqlText:sql00 });
        stmt00.next();
        var rs = stmt00.getColumnValue(1);
        
        var sql01   = "copy into @my_unload_stage/unload/" + rs + " from tmp_dedupe header=false single=true max_file_size=4900000000 overwrite=true;"
        var stmt01  = snowflake.execute({ sqlText:sql01 });
        stmt01.next();
           
        var sql02   = 'get @my_unload_stage file://D:\\project_x\\OutputFiles_STG\\';        
        var stmt02  =  snowflake.execute({ sqlText:sql02 });
        stmt02.next();
        
        result = rs;
        
    }
     
    catch (err)  {
        result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
        result += "\n  Message: " + err.message;
        result += "\nStack Trace:\n" + err.stackTraceTxt; 
    }
     
    return result;
    $$;

Everything seems works until stmt02. The get statement works in snowsql, but does not work in the stored procedure.

Thoughts, and thank you in advance.

-ibby

What's the error message you are getting? Does the file get populated or not i.e. is it the get statement that is failing or the "stmt02.next();" that is failing? — NickW
its the 'get' statement. when i checked the snowflake query logs, it shows processed. green check. — jmobi2121

Mike Walton Mike Walton · Accepted Answer · 2021-05-30T05:22:35

I don't think you can execute a GET statement from within a stored procedure. You'd be asking Snowflake to reach out to a client and move a file to that client. How would a Snowflake VWH know where the client is to do that?

In order to do what you're doing, I believe you'd need to create an external application that leverages one of the Snowflake connectors, like Python, JDBC, ODBC, etc. that support GET and then maybe expose that in a Lambda or Azure function. Then, you could call that API via a Snowflake External Function from within your Snowflake Stored Procedure.

https://docs.snowflake.com/en/sql-reference/external-functions.html

Snowflake 'get' statement within stored procedure not working

1 Answers