Template Engine (Experimental)

The TEMPLATE_ENGINE Pre-hook enables pre-processing of Django-like template markup embedded within the query text of incoming queries. This allows SQL developers to submit SQL statements which are modified dynamically at runtime into their final form, before being submitted to Snowflake. Templating opens up a wide range of potential use cases for SQL optimization.

A simple example of a templated SQL statement that can be submitted through Sundeck:

{% with tables = "table1 table2 table3"|split:" " %}
{% for table in tables %}
SELECT * FROM {{ table }}
{% if not forloop.Last %}
UNION ALL
{% endif %}
{% endfor %}
{% endwith %}

With the TEMPLATE_ENGINE pre-hook enabled, the statement above is expanded to this query, before being forwarded to Snowflake for execution:

SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3

This hook is experimental and may change in future releases. Currently it uses the Pongo2 template engine. This is a derivative of the Django templating engine. It is recommended to review the Pongo2 documentation for more information on how to use the template engine.

We have also added the following custom filters:

replace: Replace a string with another string (eg {{ "hello world"|replace:"world|universe" }} returns “hello universe”)
regex_search: Search for a regular expression in a string (eg {{ "hello world"|regex_search:"(world)" }} returns [“world”])
extract: Extract an element from an array (eg {{ ["a", "b", "c"]|extract:1 }} returns “b”)

Configure Template Engine

TEMPLATE_ENGINE {
  'alternativeBrackets'  : boolean,
}

Arguments

alternativeBrackets: Allows the hook to optionally automatically translate {|{ foo }|} => {{ foo }} and {|% foo %|} => {% foo %}

This is useful if you are already using a template engine that uses the same bracket notation.

Examples

CREATE OR REPLACE SUNDECK FLOW "template flow"
  PRE HOOK template_hook
  ALWAYS 
  TEMPLATE_ENGINE {'alternativeBrackets': FALSE}
  ENABLED = TRUE;