Snowflake and Stored procedure how can we loop over rows of a file saved in an internal stage?

user_3922727 0
Viewed 3754

I have a file in a snowflake stage that I want to loop over each row, and insert column values vertically into a SAT table. I started the following JavaScript stored procedure:

CREATE PROCEDURE ADD_OBSERVATION_VALUES()
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
    
    // Get number of rows
    var num_rows_sql = "SELECT COUNT(*) FROM @ingest_stg/load/(file_format => 'csv_format', pattern => '.*[.]csv.gz') t";
    var stmt = snowflake.createStatement( {sqlText: num_rows_sql} );
    var rows_result = stmt.execute(); 
    rows_result.next();
    num_rows = rows_result.getColumnValue(1);
    
    while(i<num_rows) {
        
    }
    
$$;

In that case, how can I loop over each row of the file so I can insert column values one by one as a new row?

In other meaning, let's say I have 100 rows. I want to loop over each row, and insert its columns values into another table, but vertically.

snowflake-cloud-data-platform stored-procedures mysql javascript
1 Answers

The following code will demonstrate to read multiple columns (2) from a stage, and then insert into a table.

This is my target table:

create or replace table target_Table( 
v1 varchar, v2 varchar,
v3 varchar, v4 varchar,
v5 varchar, v6 varchar);

This is the data in the file:

select $1, $2 from @gokhan;

+-------+-------+
|  $1   |  $2   |
+-------+-------+
| zozo0 | hoho0 |
| zozo1 | hoho1 |
| zozo2 | hoho2 |
+-------+-------+

So the procedure will read these lines from the stage, and then combine all these columns as one row and insert it to the target_table:

CREATE PROCEDURE ADD_OBSERVATION_VALUES()
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
    var num_rows_sql = "SELECT $1, $2 FROM @gokhan (file_format => 'csv_format', pattern => '.*[.]csv.gz') t";
    var stmt = snowflake.createStatement( {sqlText: num_rows_sql} );
    var rows_result = stmt.execute(); 
    var value_array = [];
    
    while(rows_result.next()) {
        value_array.push( rows_result.getColumnValue(1) );
        value_array.push( rows_result.getColumnValue(2) );
    }
    
    snowflake.createStatement( { sqlText: 'INSERT INTO target_Table VALUES (?, ?, ?, ?, ?, ?)', 
    binds: value_array } ).execute();    
    
    return 'OK';
$$;

In the stored procedure, I read each line (which I assume will have 2 columns, and then push them in a JavaScript array (value_array). After reading all lines, I send these values to the INSERT statement. Of course, before sending, it's possible to do some transformation. As I know that there are 3 lines in the file, I didn't put any limit, but to avoid any errors, you may stop reading the file after reading enough data.

This is the result:

call ADD_OBSERVATION_VALUES();

select * from target_table;

+-------+-------+-------+-------+-------+-------+
|  V1   |  V2   |  V3   |  V4   |  V5   |  V6   |
+-------+-------+-------+-------+-------+-------+
| zozo0 | hoho0 | zozo1 | hoho1 | zozo2 | hoho2 |
+-------+-------+-------+-------+-------+-------+
user_125509650
Related