Jinja

Why Jinja is awesome

Jinja is a powerful templating language, and it's fully supported in hyperquery. It allows you to use simple bits of code to programmatically modify your SQL.

For example, if you've ever wished you could apply a function to a bunch of columns at once, Jinja lets you do that easily with a for loop. Take the following query, for example, which takes a table of row-level page visits and aggregates it into sums of page visits per page:

select
  sum(IF(dim_page = '/safety', 1, 0)) AS safety_visits,
  sum(IF(dim_page = '/financials', 1, 0)) AS financials_visits,
  sum(IF(dim_page = '/setup', 1, 0)) AS setup_visits,
  sum(IF(dim_page = '/privacy', 1, 0)) AS privacy_visits,
  sum(IF(dim_page = '/data', 1, 0)) AS data_visits,
  sum(IF(dim_page = '/onboarding', 1, 0)) AS onboarding_visits,
from fct_page_visits

There's clearly a lot of duplicated code, and it can be cumbersome to write/update this work manually (imagine if there were even more!).

Jinja, however, allows you to do this very, very simply:

select
  {% for page in ['safety', 'financials', 'setup', 'privacy', 'data', 'onboarding'] %}
  sum(IF(dim_page = '/{{ page }}', 1, 0)) as {{ page }}_visits,
  {% endfor %}
from fct_page_visits

which renders (roughly) as the query above.

That said, if you're worried about learning a whole new language just for us, don't worry - Jinja2 is an open source library, you can easily install it on your own device and render templates for a simpler writing experience if you ever move off of hyperquery. And as it's already commonly used in the SQL world (as in tools like dbt), in our opinion, it's a worthwhile investment to take your SQL-writing skills to the next level.

Why we built this in hyperquery

Jinja really shines in hyperquery when you combine it with our app-building features. Take, for example, the query above. Jinja enables programmatic templating, but the result is still quite static - if I wanted to change the pages I was looking at, for example, I'd have to manually update the list.

Instead, in hyperquery, I can create a multiple-select dropdown component, reference it within the Jinja for loop, then dynamically have my query adjust according to whatever selections I make. See below: