snowflake_java_script_stored_procedure_to_unload_csv_to_s3.sql

create or replace procedure db_dev.revenue.fa_unload_sp()
  returns string not null
  language javascript
  as
  $$       
    // Get Current Date
    var date_now = new Date()
    
    // Get Year as YYYY
    var full_year = date_now.getFullYear()
    
    // Get Month and add one because month is a zero-based value in java script (where zero indicates the first month of the year).
    var full_month = date_now.getMonth() + 1
    
    // Convert number to string and pad zero if it is a single digit
    var full_month = String(full_month).padStart(2, '0')
    
    // Get date
    var full_day = date_now.getDate()
    
    // Convert number to string and pad zero if it is a single digit
    var full_day = String(full_day).padStart(2, '0')
    
    // Declare a empty variable
    var fa_data_copy = ""
    
    // Construct the copy statement
    var fa_data_copy = fa_data_copy.concat("COPY INTO @STAGE.DEV_DATA_OUTBOUND_FLEETASSIGNER/yyyy=", full_year, "/mm=", full_month, "/dd=", full_day, "/fleet_assigner", ".csv FROM (SELECT * FROM db_dev.revenue.fleet_assigner_vw) FILE_FORMAT = ( TYPE = CSV null_if=('') COMPRESSION = None) OVERWRITE = TRUE SINGLE = TRUE HEADER = FALSE;");
    
    try {
        
        // Create statement BEGIN, Begins a transaction in the current session
        var begin_statement = snowflake.createStatement({sqlText: "BEGIN"} );
        begin_statement.execute();
        
        // Create snowflake statement using above copy statement
        var copy_statement = snowflake.createStatement( {sqlText: fa_data_copy} );

        // Execute the above copy statement
        var result_set = copy_statement.execute();  
        
        // Create statement COMMIT, Commits an open transaction in the current session
        var commit_statement = snowflake.createStatement({sqlText: "COMMIT"} );
        commit_statement.execute();

        // Statement returned for info and debuging purposes
        return "Succeeded: " + fa_data_copy; 
    }
    catch (err)  {
    
        // Return error message
        return "Failed: " + err;
    }

  $$;
  
  call db_dev.revenue.fa_unload_sp();