CREATE SUNDECK ROUTINE

Creates a Routine. A routine is a collection of hooks. Hooks can be fired before a query is submitted to Snowflake (Pre-hooks) or after the query runs in Snowflake (Post-hooks). A routine contains zero or more configuration fields and zero or more child hooks, which are executed in the order that they are declared. For more information on hooks, please refer to the hooks overview.

Routines are user-defined, and can be used directly within a flow, or as part of another routine. When a routine is used in another routine or flow it should be referenced by its fully qualified identifier (i.e. ROUTINE.MY_ROUTINE).

See also:: SHOW SUNDECK ROUTINES, DESCRIBE SUNDECK ROUTINE, DROP SUNDECK ROUTINE,

Syntax

CREATE [OR REPLACE] SUNDECK ROUTINE [IF NOT EXISTS] <routine-identifier>
    [COMMENT <comment>] 
    [COLLECT INPUTS <input-schema>]
    [WITH PRE HOOKS
        <hook-definition> [ <hook-definition>* ]]
    [WITH POST HOOKS
        <hook-definition> [ <hook-definition>* ]]
;

CREATE [OR REPLACE] SUNDECK ROUTINE [IF NOT EXISTS] <new-routine-identifier> 
    CLONE <previous-routine-identifier>;

Where:

<hook-definition> ::=
    HOOK <hook-instance-identifier>
    { IF <condition-expression> THEN | ALWAYS }
    <hook-instance-type-id> <hook-configuration>
    [ ENABLED = { TRUE | FALSE } ]

Parameters

<routine-identifier>: Specifies the identifier for the routine to create. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive (e.g. “My Object”).

All routines will be created in the ROUTINE namespace, which prevents identifier collisions with existing Sundeck-provided hooks. When creating a routine, it is optional to prepend the routine identifier with the ROUTINE. namespace designation; however when referring to this routine later (within another routine or flow), the ROUTINE namespace must be provided as part of the identifier (ie ROUTINE.MY_ROUTINE).

A routine cannot refer to itself directly or indirectly. For example, if routine A refers to routine B, routine B cannot refer to routine A. Such recursive references will result in an error during routine creation.

<comment>

An optional comment for the routine.

<input-schema>

Input schema is a JSON Schema provided as a snowflake string literal. The schema describes the input that the routine will receive. The schema is used to validate the input before the routine is used in another routine or flow. The schema is a json object with the following properties:

properties - An object that describes the properties of the input. Each property is an object with the following properties:
- type - The type of the property.
- description - A description of the property.
- default - A default value for the property.
- title - A title for the property. This is used to show the property in the UI.
- other type-specific keywords
required - An array of required properties.
type - A constant of "object".

<hook-definition>

A hook instance definition (see hook parameters below). A routine definition can contain multiple child hooks.

<new-routine-identifier>

The identifier for the new routine to clone. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive (e.g. “My Object”).

<previous-routine-identifier>

The identifier of the routine to clone. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive (e.g. “My Object”).

Hook Parameters

<hook-instance-identifier>: The identifier you want to give to the child hook instance. Should describe the purpose of this hook for later reference. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive (e.g. “My Object”).
<condition-expression>: The condition expression is a boolean statement that includes one or more conditions along with parentheses, AND, OR, NOT, etc. Review the conditions reference for a complete overview.
<hook-instance-type-id>: The identifier of the hook type to use. This can be a built-in system hook type or a routine type. Routines should be referenced by their fully qualified identifier (i.e. ROUTINE.MY_FIRST_ROUTINE). Currently available built-in system Pre-hooks and built-in system Post-hooks
<hook-configuration>: A Snowflake object literal that describes the configuration for the provided hook.
ENABLED = { TRUE | FALSE }: Specifies whether hook will be active or not. If omitted, hook is activated.

Usage Notes

Hook identifiers follow Snowflake Identifier rules.
Snowflake variant literal is similar but not the same as JSON. Snowflake variant literals are expressed using brackets and single quotes. Additionally, string literals can be expressed using either single quote literals ' or $$ literals.

Examples

CREATE SUNDECK ROUTINE IF NOT EXISTS "Route to private warehouse if query contains 'hello'"
    COLLECT INPUTS ''
    WITH PRE HOOKS
    HOOK "Check For Things"
      IF SQL_CONTAINS('hello') 
      THEN ROUTE {'toWarehouse':'PRIVATE'}
;

CREATE OR REPLACE SUNDECK ROUTINE "Copy of My First Routine"
    CLONE "My First Routine";

CREATE SUNDECK ROUTINE "Warehouse planning routine"
    COLLECT INPUTS $$
    {
      "properties": {
        "LARGE_WH": {
            "default": "BIG_WH",
            "description": "The warehouse name.",
            "type": "string"
        }
      },
      "required": [
        "LARGE_WH"
      ],
      "type": "object"
    }
    $$
    WITH PRE HOOKS
    HOOK "private warehouse for join queries"
      IF SQL_CONTAINS('join') 
      THEN ROUTE {'toWarehouse':'PRIVATE'}
    WITH POST HOOKS
    HOOK "Shrink Warehouse"
      IF WAREHOUSE = {{LARGE_WH}}
      THEN SQL {
      'sqlText': 'BEGIN
                    CALL my_co.is_after_business_hours() INTO :is_open;
                    IF (is_open) THEN
                      ALTER WAREHOUSE my_wh SET warehouse_size=SMALL;
                    END IF;
                  END'
        }
;

CREATE SUNDECK ROUTINE "Route triple join queries"
    COLLECT INPUTS $$
{
  "properties": {
    "TO_WH": {
      "default": "MY_BIG_WH",
      "description": "The warehouse to route triple join queries.",
      "type": "string"
    }
  },
  "required": [
    "TO_WH"
  ],
  "type": "object"
}
$$
    WITH PRE HOOKS
    HOOK "Check For Things"
      IF sql_matches('SELECT\s+.*?\s+FROM\s+(\w+)\s*(,|\s+JOIN\s+)(\w+)\s*(,|\s+JOIN\s+)(\w+)', 'i')
      THEN ROUTE {'toWarehouse':'{{TO_WH}}'}
;

CREATE SUNDECK ROUTINE "Route to small warehouse unless query seems large" 
COLLECT INPUTS $$
{
  "properties": {
    "SMALL_WH": {
      "default": "MY_SMALL_WH",
      "description": "The warehouse to route most queries.",
      "type": "string"
    },
    "BIG_WH": {
      "default": "MY_BIG_WH",
      "description": "The warehouse to route create table statements to.",
      "type": "string"
    }
  },
  "required": [
    "SMALL_WH",
    "BIG_WH"
  ],
  "type": "object"
}
$$
WITH PRE HOOKS
  HOOK "Route all queries from large warehouse to small"
  IF WAREHOUSE={{BIG_WH}}
  THEN ROUTE {'toWarehouse': '{{SMALL_WH}}'}

  HOOK "reroute create table as to large database"
  IF WAREHOUSE={{SMALL_WH}} AND (SQL_MATCHES('create table .*', 'i') OR SQL_MATCHES('create transient table .*', 'i'))
  THEN ROUTE {'toWarehouse': '{{BIG_WH}}'}

  HOOK "reroute specific tables to large database"
  IF WAREHOUSE={{SMALL_WH}} AND (table contains snowflake_sample_data.tpch_sf100.lineitem or table contains snowflake_sample_data.tpch_sf1000.lineitem)
  THEN ROUTE {'toWarehouse': '{{BIG_WH}}'}

  HOOK "reroute query with 3 or more joins to large database"
  IF WAREHOUSE={{SMALL_WH}}
  THEN ROUTINE."Route triple join queries" {'TO_WH': '{{BIG_WH}}'}
;

CREATE OR REPLACE SUNDECK ROUTINE capture_elapsed_time
WITH PRE HOOKS
    HOOK TRACK_ELAPSED_TIME
    ALWAYS
    SQL {
        'sqlText': 'insert into my_db.public.elapsed_time(query_text, avg_time)
                        select query_text, avg(total_elapsed_time)
                            from snowflake.account_usage.query_history
                            where query_text = \'{{ \'{{query_text}}\' }}\'
                            group by query_text',
        'errorBehavior' : 'BYPASS'
    }
;

CREATE OR REPLACE SUNDECK ROUTINE capture_insert_queries
WITH POST HOOKS
    HOOK TRACK_INSERT
    IF sql_matches('INSERT .*', 'i')
    THEN SQL {
        'sqlText': 'INSERT INTO my_db.public.inserts 
            VALUES(current_timestamp, \'INSERT\', \'{{ \'{{query_id}}\' }}\')',
        'errorBehavior' : 'BYPASS'
    }
;