Can Snowflake DB UDF's execute SQL commands?

Question

I have a requirement in Snowflake where I must generate a bit of SQL and then execute it to create a new table.

I have successfully generated the create table statement by creating a UDF (hard-coded at the moment)

CREATE OR REPLACE FUNCTION COOL_CARGO.test()
  RETURNS STRING 
  AS 
  $$
  SELECT substr(regexp_replace(GET_DDL('TABLE', 'COOL_CARGO.DIM_BRANCH'),('DIM_BRANCH'),'COOL_CARGO.DIM_BRANCH_ERR'), 0, LENGTH(regexp_replace(GET_DDL('TABLE', 'COOL_CARGO.DIM_BRANCH'),('DIM_BRANCH'),'COOL_CARGO.DIM_BRANCH_ERR')) -2)||','||'  etl_err_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  etl_id_run int DEFAULT NULL,
  etl_err_noe int DEFAULT NULL,
  etl_err_desc varchar(512) DEFAULT NULL,
  etl_err_col varchar(256) DEFAULT NULL,
  etl_err_cod varchar(256) DEFAULT NULL'||');'
 $$
 ;

This outputs the following

create or replace TABLE COOL_CARGO.DIM_BRANCH_ERR (
    TK_BRANCH NUMBER(38,0),
    GB_BRANCH_CODE VARCHAR(256),
    GB_BRANCH_NAME VARCHAR(256),
    GB_BRANCH_CITY VARCHAR(256),
    GB_BRANCH_STATE VARCHAR(256),
    BG_BRANCH_HOME_PORT VARCHAR(256),
    BG_BRANCH_COUNTRY_CODE VARCHAR(256),
    BG_BRANCH_COUNTRY_NAME VARCHAR(256)
,  etl_err_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  etl_id_run int DEFAULT NULL,
  etl_err_noe int DEFAULT NULL,
  etl_err_desc varchar(512) DEFAULT NULL,
  etl_err_col varchar(256) DEFAULT NULL,
  etl_err_cod varchar(256) DEFAULT NULL);

I now need to create a UDF that will execute this create table statement but as it only seems to return things like strings, I cannot get it to execute by calling it from another function for example.

CREATE OR REPLACE FUNCTION COOL_CARGO.run_test()
     RETURNS string
     AS
     $$
     COOL_CARGO.test()
      $$
     ;

Then I try and run the function to create the table with

select COOL_CARGO.run_test();

I do not know if what I want can be done and I would be pretty annoyed if its not possible...

Can this be done in Snowflake DB?

Snowflake UDFs don't support calling other functions like that, nor eval-like functionality. You'd need stored procedures. They are coming! — Marcin Zukowski, Nov 03 '18 at 04:30
Thanks, I was afraid that this was the case :( I dont suppose you have any idea when stored procedures are coming? — blairmeister, Nov 05 '18 at 08:48
Snowflake community forums is the best place to ask. You might even get access to it early :) — Marcin Zukowski, Nov 06 '18 at 00:20

score 0 · Answer 1 · answered Jan 16 '20 at 05:03

You can achieve this with Snowflake's new Stored Procedures feature that launched in 2019. It allows you to build an arbitrary SQL string and perform an execution in either SQL or JavaScript.

Your existing method can easily be adapted into a stored procedure (example below is illustrative and hasn't been specifically tested):

create or replace procedure test()
  returns null
  language javascript
  as
  $$
  var orig_ddl_qry = "SELECT GET_DDL('TABLE', 'COOL_CARGO.DIM_BRANCH')";
  var get_ddl_stmt = snowflake.createStatement(
         {
         sqlText: orig_ddl_qry
         }
      );
  var get_ddl_res = get_ddl_stmt.execute();
  get_ddl_res.next();
  var orig_ddl_str = res.getColumnValue(1);
  var replaced_ddl_open = orig_ddl_str.replace("DIM_BRANCH", "COOL_CARGO.DIM_BRANCH_ERR").slice(0, -1);
  var new_ddl = replaced_ddl_open + ", etl_err_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, etl_id_run int DEFAULT NULL, etl_err_noe int DEFAULT NULL, etl_err_desc varchar(512) DEFAULT NULL, etl_err_col varchar(256) DEFAULT NULL, etl_err_cod varchar(256) DEFAULT NULL);";
  var create_stmt = snowflake.createStatement(
         {
         sqlText: new_ddl
         }
      );
  var create_ddl_res = create_stmt.execute();
  $$
  ;

CALL test();

Can Snowflake DB UDF's execute SQL commands?

1 Answers1

Linked