Thursday, February 03, 2022

How To: Pattern for Pipeline Stored Procedure Created in Snowflake

I created a typical pattern for my stored procedures within the pipeline part of the system.   Here is my "GoTo" pattern to start a stored procedure in Snowflake.  This is a JScript structured stored procedure of course and uses the cloning technique for isolating processes from access interruption for down stream users and systems.  Other types of stored procedures may need more control flow logic in JSCRIPT.    

CREATE OR REPLACE PROCEDURE {Schema}.{Pipeline Process Name}()

  RETURNS VARCHAR
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
     sqlStatements = 
    `
----------------------------------------------- 
-- Process: {Place Process Name Here}   
-- Typical Run: {X} minutes on {Y} warehouse
-- Typical Output: {Z} Records
----------------------------------------------- 
CREATE OR REPLACE TABLE {Staging Schema}.{Table Name} 
AS
SELECT * FROM {Staging Schema}.{Process Name};
GRANT OWNERSHIP ON {Staging Schema}.{Table Name} TO ROLE {Security Role};
CREATE OR REPLACE TABLE {Publishing Schema}.{Table Name} CLONE {Staging Schema}.{Table Name};
GRANT OWNERSHIP ON VIEW {Publishing Schema}.{Table Name} TO ROLE {Security Role};

----------------------------------------------- 
-- Process: {Place Process Name Here}   
-- 
Typical Run: {X} minutes on {Y} warehouse
-- Typical Output: {Z} Records
----------------------------------------------- 
CREATE OR REPLACE TABLE {Staging Schema}.{Table Name} 
AS
SELECT * FROM {Staging Schema}.{Process Name};
GRANT OWNERSHIP ON {Staging Schema}.{Table Name} TO ROLE {Security Role};
CREATE OR REPLACE TABLE {Publishing Schema}.{Table Name} CLONE {Staging Schema}.{Table Name};
GRANT OWNERSHIP ON VIEW {Publishing Schema}.{Table Name} TO ROLE {Security Role};
`;
    
        statementList = sqlStatements.split(";") ;
        index = 0;
        
      try {
        for (index in statementList) {
            // don't execute empty strings.
            if (statementList[index].trim()) {
                rs = snowflake.execute({sqlText: statementList[index]});
                }
            }          
        return "Succeeded: " + statementList.length + " Statements Executed.";   // Return a success/error indicator.
        }
    catch (err)  {
        throw "Failed: " + err + ".  SQL Executed(" + statementList[index] +  ")";   // Return a success/error indicator.
        }
  $$;
GRANT OWNERSHIP ON PROCEDURE {Staging Schema}.{Pipeline Process Name}() TO ROLE {Security Role}; 


  /*

  CALL {Staging Schema}.{Pipeline Process Name}();

  */



No comments: