Using Jinja & Parameters

Julian Schulte -

To make your work with SQL much more efficient, we have integrated Jinja templating syntax into all Notebooks. Jinja enables us to deliver you some of our most powerful features, including

  • Interactive Parameter Blocks
  • Modular Querying through Query Referencing
  • Function-like Macro Definition
  • Additional Logic like Loops and Conditions for SQL

and much more.
Below you'll find an overview over all of them including guides on how to use them.

Parameter Blocks

One of the simplest use cases of Jinja are parameter blocks. To create a parameter block, either click on the + button and select Parameter or type the shortcut ??? +space. Once created, you are presented with an input field for your new parameter and one for its name.

Using Parameters in Queries

To use a parameter in a query, simply insert it's name at the wanted position in your query and enclose it with two curly braces on each side.

{{ parameters belong between two pairs of curly braces }}

To preview your jinja-parsed query, click on the {{-button on the left of the SQL-block.

Changing the Parameter Type

By default, a new parameter is of type String. To change it, click on the Settings-icon at the right of the block and select one of the available options.

Hiding Queries

Although this is technically not a feature of the Parameter Block we figured that this would be a good place to point you towards the hide/show function of the query blocks again (read about it here if you haven't yet). Together with the parameter block, this can make up for a very clean look for your notebook.

SQL Block Referencing

Another great use case of Jinja is the ability to reference SQL-blocks via their name in other SQL-blocks. This allows you to create reusable snippets that you can query again and again and again ... and consequently to avoid code repetition.

To reference a block of SQL in your code, enclose it's name in the already known double curly braces like this : {{ referenced_block_name }}.

Macros

Macros are another way to make your code modular and reusable. They are comparable to functions in regular programming and can therefore be used to put often used idioms into reusable functions.

Creating a Macro

Macros are defined by an opening and a closing macro/endmacro tag. Both of these tags are enclosed by an inner pair of %-signs and a pair of curly braces like this: {% macro %}.

In the opening tag you can give your macro a name and define what parameters it takes in
( preview() and table_name in the example below).

In between the tags you can now put your actual code. In the above example case, a call of the macro preview will execute a SELECT * statement for the table that was given to the macro via the parameter table_name and LIMIT the results to 10 rows.

Loops and Conditions

Another main advantage of combining SQL with Jinja is the ability to combine SQL code with conditions and loops like for- and while-loops. This can make your queries more dynamic and save you a lot of repetitions, making your notebook much more readable.

If/Else - Conditional Statements

To create an if-clause, an opening {% if %} and a closing {% endif %} tag are needed. In the opening tag, you define your condition, and follow it up with the code that should be executed when the condition is met.

In the example above, the if-condition is met if the boolean parameter discounted is equal to 1 which resembles True . If this is the case, only store orders which have a discount (i.e. ones who have a discount unlike 0) are being queried for.

If it is not the case, the else clause will be triggered. This means that only non-discounted products will be fetched from the database.

If you want to add even more complexity, you can also add {% elif %} clauses into your statement, which give you more conditions to ask for before falling back to the default {% else %}-clause.

For-Loops

To create a for-loop, similar to your macros and if-statements, opening  {% for %} and closing {% endfor %} tags are needed.

In the following statement, a for loop is used to create 3 joined SELECT Statements in only 1/3 of the lines that would normally be needed to do it.
Once again we can view the jinja-parsed query via the {{-button on the left of the query-block.

Jinja Syntax

To learn more about jinja2 syntax, we recommend this documentation