Snowflake Stored Procedure with JavaScript

I wrote my first Snowflake stored procedure using the public CitiBikes data from Citigroup Inc. We need to use JavaScript to make a stored procedure in Snowflake, see the docs here.

use role sysadmin;
use warehouse compute_wh;
use database citibikes;
use schema public;


CREATE or replace PROCEDURE proc_lw()
  RETURNS VARCHAR
  LANGUAGE javascript
  EXECUTE AS OWNER 
  AS
  $$
  var status = 'Failure'
  var rs = snowflake.execute( { sqlText: 
      `select *
from table(information_schema.copy_history(table_name=>'trips',start_time=> dateadd(hours, -1, current_timestamp())));`
       } );
       
  if (typeof(rs) != 'undefined' && rs != null)
  {
    status = 'Success'
  } 
  return status;
  $$;
  
CALL proc_lw()

Note the back-ticks before and after your input SQL to the snowflake.execute() function. Not my favorite syntax but... It worked well!

For more information:

Laurent Weichberger

ompoint (at) gmail (dot) com