Jinja Templating with trino¶
In [1]:
Copied!
%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]:
Copied!
table_name = "tpch.tiny.orders"
table_name = "tpch.tiny.orders"
In [3]:
Copied!
%%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]:
Copied!
%%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]:
Copied!
%%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 [ ]:
Copied!
%%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 [ ]:
Copied!
%%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 [ ]:
Copied!
def get_filters():
return 1
def get_filters():
return 1
In [ ]:
Copied!
%%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]:
Copied!
%%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]:
Copied!
In [30]:
Copied!
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'))