using session variables while deploying stored procedures-snowflake

Question

I have a situation where I am pushing my stored procedure DDL into git repository. These codes have some environment specific codes, like I have dev,qa and prd, while I need to replace every time while deploying.

What I am looking for is to be able to use variables, that could be replaced while deploying. Something like this

var env= 'dev';

create or replace procedure test
returns varchar
language javascript
as
$$
insert into raw_$env.employee values(1);
$$;

When I run this code I want to have the code deployed like this.

create or replace procedure test
returns varchar
language javascript
as
$$
insert into raw_raw.employee values(1);
$$;

waldente waldente · Accepted Answer · 2020-03-27T03:55:24

To specify the dynamic schema name, you can use IDENTIFIER(). Then use the javascript stored procedure API to execute.

create or replace procedure test()
returns text
language javascript
execute as caller
as
$$
  snowflake.createStatement({ sqlText: `set t = 'raw_' || $env || '.employee'` }).execute()
  snowflake.createStatement({ sqlText: `insert into identifier($t) values(1)` }).execute()
  return 'SUCCESS'
$$;

set env= 'dev';

call test();

You may want to specify the database as well.

UPDATE -- Static solution, hard coding the 'env' parameter

RUN IN SNOWSQL

!define env=dev;

create or replace procedure test()
returns text
language javascript
execute as caller
as
$$
  snowflake.createStatement({ sqlText: `insert into raw_&env.employee values(1)` }).execute()  return 'SUCCESS'
$$;

To verify that the substition is static:

select get_ddl('procedure','test()')

The env parameter can also be defined in a config file or on the SNOWSQL command line.

using session variables while deploying stored procedures-snowflake

1 Answers