Advanced templates¶
When developing multiple use cases, you might want to reuse certain parts or steps of an analysis, such as data filters or similar table operations.
Read on to learn about advanced usage of the datafile system when using the Tinybird CLI. Before reading this page, familiarize yourself with query parameters.
Templates reuse¶
You can set up templates to reuse certain parts or steps of an analysis, such as data filters or similar table operations.
To follow along, clone the ecommerce_data_project_advanced repository:
Clone demo
git clone https://github.com/tinybirdco/ecommerce_data_project_advanced.git cd ecommerce_data_project_advanced
The repository contains the following file structure:
File structure
ecommerce_data_project/ datasources/ events.datasource mv_top_per_day.datasource products.datasource fixtures/ events.csv products.csv endpoints/ sales.pipe top_products_between_dates.pipe top_products_last_week.pipe includes/ only_buy_events.incl top_products.incl pipes/ top_product_per_day.pipe
Take a look at the sales.pipe
API Endpoint and the top_product_per_day.pipe
Pipe that materializes to a mv_top_per_day
Data Source. Both use the same node, only_buy_events
, through the usage of include files:
includes/only_buy_events.incl
NODE only_buy_events SQL > SELECT toDate(timestamp) date, product, joinGet('products_join_by_id', 'color', product) as color, JSONExtractFloat(json, 'price') as price FROM events where action = 'buy'
When using include files to reuse logic in .datasource files, the extension of the file must be .datasource.incl
.
Include variables¶
You can include variables in a node template. The following example shows two API Endpoints that display the 10 top products, each filtered by different date intervals:
includes/top_products.incl
NODE endpoint DESCRIPTION > returns top 10 products for the last week SQL > select date, topKMerge(10)(top_10) as top_10 from top_product_per_day {% if '$DATE_FILTER' = 'last_week' %} where date > today() - interval 7 day {% else %} where date between {{Date(start)}} and {{Date(end)}} {% end %} group by date
In the previous examples, the DATE_FILTER
variable is sent to the top_products
include, where the variable content is retrieved using the $
prefix with the DATE_FILTER
reference.
You can also assign an array of values to an include variable. To do this, parse the variable using function templates, as explained in Template functions.
Variables and parameters¶
Parameters are variables whose value you can change through the API Endpoint request parameters. Variables only live in the template and you can set them when declaring the INCLUDE
or with the set
template syntax. For example:
Using 'set' to declare a variable
{% set my_var = 'default' %}
By default, variables are interpreted as parameters. To prevent variables or private parameters from appearing in the auto-generated API Endpoint documentation, they need to start with _
. For example:
Define private variables
% SELECT date FROM my_table WHERE a > 10 {% if defined(_private_param) %} and b = {{Int32(_private_param)}} {% end %}
You also need to use _
as a prefix when using variables in template functions. See Template functions for more information.