Snowflake Stored Procedure - how do I retrieve the number of rows created by a CTAS Statement

Question

In a snowflake stored procedure I am executing CTAS statements and want to retrieve the number of rows in the resultant object. We don't have access to QUERY_HISTORY (we get an error), and RESULT_SCAN(LAST_QUERY_ID()) doesn't help either (it gives us back the Table xyz Created result, but does not have meta-data i.e. number of rows created).

I can do it with a Select Count(*) in a separate query, but that seems to be a hack since the Row Count is right there in the History.

    CREATE OR REPLACE PROCEDURE EDW_ADMIN.DAG_TEST()
    RETURNS VARCHAR(512)
    LANGUAGE JAVASCRIPT
    AS 
    $$
    {
    let strCTAS = "";
    let rsCTAS;
    let rsRowsAffected;
    let rowsAffected = 0;   

    strCTAS = "CREATE OR REPLACE TABLE EDW_ADMIN.DEMO_PROC_TEMP AS SELECT * FROM RAW_BIR.H_RPTUNIT;";
    rsCTAS = snowflake.execute( {sqlText: strCTAS} );

    // This works in a Query Worksheet in the browser, but gives me the following error when called from a procedure
    //      "[Stored procedure execution error: Requested information on the current user is not accessible in stored procedure.]"
    rsRowsAffected = snowflake.execute( {sqlText: "SELECT ROWS_PRODUCED FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT=>100)) WHERE QUERY_ID = LAST_QUERY_ID();"} );
    rsRowsAffected.next();
    rowsAffected = rsRowsAffected.getColumnValue(1); 


    // This works, but you are doing execute i/o which is really un-necessary
    // rsRowsAffected = snowflake.execute( {sqlText: "SELECT COUNT(*) FROM EDW_ADMIN.DEMO_PROC_TEMP;"} );
    // rsRowsAffected.next();
    // rowsAffected = rsRowsAffected.getColumnValue(1); 

    // This does NOT work, RESULT_SCAN has no metadata associated with it, this returns "Table DEMO_PROC_TEMP successfully created."
    // rsRowsAffected = snowflake.execute ( {sqlText: "SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));" } );
    // rsRowsAffected.next();
    // rowsAffected = rsRowsAffected.getColumnValue(1); 

    return rowsAffected;
}
$$
;

CALL EDW_ADMIN.DAG_TEST();

DROP EDW_ADMIN.DEMO_PROC_TEMP;
DROP PROCEDURE EDW_ADMIN.DAG_TEST();

waldente waldente · Accepted Answer · 2019-12-16T17:36:50

Try adding execute as caller to the stored procedure declaration. For example:

create or replace procedure p() 
returns text
language javascript
execute as caller
as
  $$
  const stmt1 = snowflake.createStatement( { sqlText: "create or replace table t as select $1 x from values (1),(2),(3)" } )
  const rs1 = stmt1.execute()

  const stmt2 = snowflake.createStatement( { sqlText: "SELECT ROWS_PRODUCED FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT=>100)) WHERE QUERY_ID = LAST_QUERY_ID()" } )
  const rs2 = stmt2.execute()
  rs2.next()
  const rowsAffected = rs2.getColumnValue(1)
  return rowsAffected
  $$
;


call p();

returns 3

Snowflake Stored Procedure - how do I retrieve the number of rows created by a CTAS Statement

1 Answers