Jinja2 templating

Supported connections: BigQuery, Postgres, Presto, Redshift, Snowflake
All metrics calculations and executed SQL queries will be passed through the Jinja2 engine, so any basic Jinja2 templating, as you might expect, is supported. If you're not familiar with Jinja2, a basic example is shown below.
1
{% set table = census.public_data %}
2
select count(*) from {{ table }}
Copied!
Reusable templates
On top of this, we provide support for reusable templates, which should be saved in the ~/.whale/templates folder  and named after the name of the warehouse connection that you would like to use this template for. Connection names can be found by running wh connections, in the name field of each yaml block. 
1
.whale
2
└── templates
3
    └── warehouse-connection-name.sql
Copied!
For example, consider the following BigQuery connection setup:
1
---
2
name: bq-1
3
metadata_source: Bigquery
4
key_path: ~
5
project_credentials: ~
6
project_id: my-bigquery-project
Copied!
The name of the connection here is bq-1, so you'll need to create a file as follows:
1
.whale
2
└── templates
3
    └── bq-1.sql
Copied!
And the template within will automatically be pre-pended to queries against this connection.
Template example
The following snippet enables the value {{ last_day }} to be used to performantly get data from the latest partition in BigQuery.
1
{% set last_day = "_PARTITIONDATE = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)" %}
Copied!
The following query, then, could be run by whale:
1
select count(*) from table.schema where {{ last_day }}
Copied!

Additional features - Previous

Query automation and alerting

Next - Customization

Custom UI/UX

Last modified 10mo ago

Copy link

Contents

Reusable templates

Template example