Jinja Templating with trino¶

In [1]:

%load_ext jupyterlab_sql_editor.ipython_magic.trino

%config Trino.host='localhost'
%config Trino.port=8080
%config Trino.httpScheme='http'
%config Trino.auth=None
%config Trino.user='the-user'

%config Trino.cacheTTL=3600
%config Trino.outputFile="/tmp/trinodb.schema.json"

# comma seperated list of schema to cache in the schema file
%config Trino.catalogs="system,tpch"

%load_ext jupyterlab_sql_editor.ipython_magic.trino %config Trino.host='localhost' %config Trino.port=8080 %config Trino.httpScheme='http' %config Trino.auth=None %config Trino.user='the-user' %config Trino.cacheTTL=3600 %config Trino.outputFile="/tmp/trinodb.schema.json" # comma seperated list of schema to cache in the schema file %config Trino.catalogs="system,tpch"

In [2]:

table_name = "tpch.tiny.orders"

table_name = "tpch.tiny.orders"

In [3]:

%%trino --limit 3 --jinja
SELECT
    *
FROM
    {{ table_name }}

%%trino --limit 3 --jinja SELECT * FROM {{ table_name }}

Only showing top 3 row(s)

Out[3]:

orderkey	custkey	orderstatus	totalprice	orderdate	orderpriority	clerk	shippriority	comment
7492	511	O	64295.07	1996-06-26	5-LOW	Clerk#000000810	0	f the pending deposits. express, ironic deposits
7493	205	F	73649.68	1994-12-18	2-HIGH	Clerk#000000279	0	riously even instructions haggle agains
7494	1231	O	68212.31	1996-04-02	4-NOT SPECIFIED	Clerk#000000244	0	fily express packages. blithely regular requests across

In [4]:

%%trino --output sql --jinja
SELECT
    *
FROM
    {{ table_name }}

%%trino --output sql --jinja SELECT * FROM {{ table_name }}

Out[4]:

SELECT
    *
FROM
    tpch.tiny.orders

In [5]:

%%trino --limit 3 --jinja
/*
this is a comment which happens to contain a jinja template
variable {{x}}
*/

SELECT
    *
FROM
    {{ table_name }}

%%trino --limit 3 --jinja /* this is a comment which happens to contain a jinja template variable {{x}} */ SELECT * FROM {{ table_name }}

A Jinja template variable named {x} was located in your SQL statement.

However Jinja was unable to substitute it's value because the variable "x" was not found in your ipython kernel.

Option 1: If you intended to use a template variable make sure to assign a value to "x"


Option 2: If you intended to include "{{" in your statement then you'll need to escape this special Jinja variable delimiter.

To have Jinja ignore parts it would otherwise handle as variables or blocks. For example, if, with the default syntax, you want to use {{ as a raw string in a template and not start a variable, you have to use a trick.

The easiest way to output a literal variable delimiter "{{" is by using a variable expression:

{{ '{{' }}

For bigger sections, it makes sense to mark a block raw. For example, to include example Jinja syntax in a template, you can use this snippet:

%%trino --limit 3
{% raw %}
/*
This is a comment which happens to contain a jinja template
variable {{x}} that we want to keep as is.
*/
{% endraw %}

SELECT
    *
FROM
    {{ table_name }}


Raising an error to prevent statement from being executed incorrectly.

---------------------------------------------------------------------------
UndefinedError                            Traceback (most recent call last)
/tmp/ipykernel_18939/3769332149.py in <module>
----> 1 get_ipython().run_cell_magic('trino', '--limit 3 --jinja', '/*\nthis is a comment which happens to contain a jinja template\nvariable {{x}}\n*/\n\nSELECT\n    *\nFROM\n    {{ table_name }}\n')

/data/dev/jupyterlab-sql-editor/venv/lib/python3.8/site-packages/IPython/core/interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
   2417             with self.builtin_trap:
   2418                 args = (magic_arg_s, cell)
-> 2419                 result = fn(*args, **kwargs)
   2420             return result
   2421 

/data/dev/jupyterlab-sql-editor/venv/lib/python3.8/site-packages/decorator.py in fun(*args, **kw)
    230             if not kwsyntax:
    231                 args, kw = fix(args, kw, sig)
--> 232             return caller(func, *(extras + args), **kw)
    233     fun.__name__ = func.__name__
    234     fun.__doc__ = func.__doc__

/data/dev/jupyterlab-sql-editor/venv/lib/python3.8/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

/data/dev/jupyterlab-sql-editor/ipython_magic/trino/trino.py in trino(self, line, cell, local_ns)
     76                 print(f'Invalid refresh option given {args.refresh}. Valid refresh options are [all|local|none]')
     77 
---> 78         sql = self.get_sql_statement(cell, args.sql, args.jinja)
     79         if not sql:
     80             return

/data/dev/jupyterlab-sql-editor/ipython_magic/common/base.py in get_sql_statement(self, cell, sql_argument, use_jinja)
     93             print('No sql statement to execute')
     94         elif use_jinja:
---> 95             sql = self.bind_variables(sql, self.user_ns)
     96         return sql
     97 

/data/dev/jupyterlab-sql-editor/ipython_magic/common/base.py in bind_variables(query, user_ns)
     78     def bind_variables(query, user_ns):
     79         template = Template(query, undefined=ExplainUndefined)
---> 80         return template.render(user_ns)
     81 
     82     def get_catalog_array(self):

/data/dev/jupyterlab-sql-editor/venv/lib/python3.8/site-packages/jinja2/environment.py in render(self, *args, **kwargs)
   1289             return concat(self.root_render_func(ctx))  # type: ignore
   1290         except Exception:
-> 1291             self.environment.handle_exception()
   1292 
   1293     async def render_async(self, *args: t.Any, **kwargs: t.Any) -> str:

/data/dev/jupyterlab-sql-editor/venv/lib/python3.8/site-packages/jinja2/environment.py in handle_exception(self, source)
    923         from .debug import rewrite_traceback_stack
    924 
--> 925         raise rewrite_traceback_stack(source=source)
    926 
    927     def join_path(self, template: str, parent: str) -> str:

<template> in top-level template code()

/data/dev/jupyterlab-sql-editor/ipython_magic/common/base.py in __str__(self)
     58         print(HOW_TO_ESCAPE_MSG)
     59         print(RAISING_ERROR_MSG)
---> 60         return super().__str__(self)
     61 
     62 

UndefinedError: 'x' is undefined

In [ ]:

%%trino --limit 3 --jinja
{% raw %}
/*
this is a comment which happens to contain a jinja template
variable {{x}}
*/
{% endraw %}

SELECT
    *
FROM
    {{ table_name }}

%%trino --limit 3 --jinja {% raw %} /* this is a comment which happens to contain a jinja template variable {{x}} */ {% endraw %} SELECT * FROM {{ table_name }}

In [ ]:

%%trino --output sql --jinja
{% raw %}
/*
this is a comment which happens to contain a jinja template
variable {{x}}
*/
{% endraw %}

SELECT
    *
FROM
    {{ table_name }}

%%trino --output sql --jinja {% raw %} /* this is a comment which happens to contain a jinja template variable {{x}} */ {% endraw %} SELECT * FROM {{ table_name }}

In [ ]:

def get_filters():
    return 1

def get_filters(): return 1

In [ ]:

%%trino --output sql --jinja
{% raw %}
/*
this is a comment which happens to contain a jinja template
variable {{x}}
*/
{% endraw %}

{% set testing = get_filters() %}
{{testing}}
SELECT
    *
FROM
    {{ table_name }}

%%trino --output sql --jinja {% raw %} /* this is a comment which happens to contain a jinja template variable {{x}} */ {% endraw %} {% set testing = get_filters() %} {{testing}} SELECT * FROM {{ table_name }}

In [16]:

%%trino --limit 1 --output grid
SELECT
    *
FROM (
    SELECT
        ARRAY[1,2,3] as an_array,
        *
    FROM
      tpch.tiny.orders
)

%%trino --limit 1 --output grid SELECT * FROM ( SELECT ARRAY[1,2,3] as an_array, * FROM tpch.tiny.orders )

Only showing top 1 row(s)

DataGrid(auto_fit_params={'area': 'all', 'padding': 30, 'numCols': None}, corner_renderer=None, default_render…

In [29]:

In [30]:

Out[30]:

SELECT 
    *
FROM (
    SELECT
        ARRAY[1,2,3] as an_array,
        *
    FROM
      tpch.tiny.orders
)



  
WHERE
  
    1=0
    
      OR (contains(an_array, '1'))
    
      OR (contains(an_array, '2'))