Create stage to copy data from S3 to Snowflake from within Snowflake Scripting block?

Question

I am trying to convert an existing stored procedure from javascript to snowflake scripting. I can't find any any documentation on how to create a stage so I can copy data from s3 to snowflake. Can anyone advise me, or point me to some documentation or tutorials that show how to achieve this?

This is how I did it in javascript (not the complete code - just adding for context).

$$
var create_replace_stage_cmd = `
create or replace my_stage
url = 's3://path/value1=${value1}/value1=${value2}/'
storage_integration = my_storage_integration;
`

var copy_into_table_cmd = `
copy into my_table(my_data)
from @my_stage
file_format='JSON_FILE_FORMAT'
`

    try {
        var sql_create_replace_stage_cmd = snowflake.createStatement({
            sqlText: create_replace_stage_cmd
        });
        var create_replace_stage_result = sql_create_replace_stage_cmd.execute();
        result += "\n Create stage command succeeded";
    } catch (err) {
        result += "\n Create stage command failed: " + err.code + "\n  State: " + err.state;
        result += "\n  Message: " + err.message;
        result += "\n Stack Trace:\n" + err.stackTraceTxt;
    }

    try {
        var sql_copy_into_table_cmd = snowflake.createStatement({
            sqlText: copy_into_table_cmd
        });
        var copy_into_table_result = sql_copy_into_table_cmd.execute();
        result += "\n Copy into table command succeeded";
    } catch (err) {
        result += "\n Copy into table command failed: " + err.code + "\n  State: " + err.state;
        result += "\n  Message: " + err.message;
        result += "\n Stack Trace:\n" + err.stackTraceTxt;
    }

    return result; 
$$

Snowflake scripting rewrite, so far.

CREATE OR REPLACE PROCEDURE my_stored_procedure("my_value" varchar) 
returns varchar 
language sql 
as 
$$ 
DECLARE 
current_day varchar; 
current_month varchar; 
current_year varchar; 
current_value VARCHAR; 
    
BEGIN
        
SELECT date_part(day, current_timestamp()) INTO :current_day;
SELECT date_part(month, current_timestamp()) INTO :current_month;` 
SELECT date_part(year, current_timestamp()) INTO :current_year; 
current_value:="my_value";
        
        
/* 1. Trying to create a stage which includes dynamic current_day, 
 *   current_month etc and the values passed when the stored procedure 
 *   is called, in the URL part. The stage name will be consistent but 
 *   the URL part will change depending on the date and what is passed 
 *   when the stored procedure is called.  
 *        
 * 2. Need to copy data from this stage to my snowflake database table
 */
    
RETURN current_value; 
exception   
    when statement_error then
            return object_construct('Error type', 'STATEMENT_ERROR',
                                    'SQLCODE', sqlcode,
                                    'SQLERRM', sqlerrm,
                                    'SQLSTATE', sqlstate);
END; 
$$;

This will be called on a schedule by a snowflake task.

CALL my_stored_procedure('value');

Answer 1

so first a test to "create a stage with some date prefixes" the normal way:

CREATE STAGE aws_3s_a_2022_02_19 URL = 's3://bucket_name/data/2022/02/19';

all good

so now I'll do the same via snowsql script:

execute immediate 
$$
begin
  create stage aws_3s_b_tmp URL = 's3://bucket_name/data/2022/02/19';
end;
$$
;

show stages;

created_on	name	database_name	schema_name	url
2022-02-19 00:32:46.278 -0800	AWS_3S_A_2022_02_19	TEST	PUBLIC	s3://bucket_name/data/2022/02/19
2022-02-19 00:32:41.008 -0800	AWS_3S_B_TMP	TEST	PUBLIC	s3://bucket_name/data/2022/02/19

so creating a stage in snowsql works.

fumbling some... I find that I can set a session variable and user that:

set stage_name = 's3://bucket_name/data/2022/02/19';
create stage aws_3s_c_tmp URL = $stage_name;

thus that can be done is a script:

$$
begin
  set stage_name = 's3://bucket_name/data/2022/02/19';
  create stage aws_4s_c_tmp URL = $stage_name;
end;
$$
;

created_on	name	database_name	schema_name	url
2022-02-19 00:32:46.278 -0800	AWS_3S_A_2022_02_19	TEST	PUBLIC	s3://bucket_name/data/2022/02/19
2022-02-19 00:32:41.008 -0800	AWS_3S_B_TMP	TEST	PUBLIC	s3://bucket_name/data/2022/02/19
2022-02-19 00:39:55.741 -0800	AWS_3S_C_TMP	TEST	PUBLIC	s3://bucket_name/data/2022/02/19
2022-02-19 00:41:04.685 -0800	AWS_4S_C_TMP	TEST	PUBLIC	s3://bucket_name/data/2022/02/19

BUT the session variable has to be set prior to the snowsql script, (the above only worked after creating the session variable out of scope)..

But really my original point is you don't need a stage, you can just COPY from the full bucket name/path:

thus:

execute immediate 
$$
begin
  COPY INTO test.public.the_table FROM 's3://bucket_name/data/' PATTERN = '*.json';
end;
$$
;

anyways.. I was trying to get this working... but it just doesn't play ball

execute immediate 
$$
declare
    bucket_name text;    
begin
  bucket_name := 's3://bucket_name/data/';

  COPY INTO THE_TABLES FROM :bucket_name PATTERN = '*.json';
end;
$$
;

but I would suggest if you have a Javascript store procedure working. user that. We did a very similar loading pattern (but all the SQL was written externally in javascript) to manage chunking of data from where we loaded from last. So I get what you are trying to do.

I would be more inclined to ask, why is the Javascript not working for you? as the raw SQL doesn't "seem" to want to be feed constructed input. But I am very open to the idea, I have not grok the scripting just yet.