Advanced template functions for dynamic API Endpoint¶

The Template functions section of the Advanced templates docs explains functions that help you create more advanced dynamic templates. On this page, you'll learn about how these templates can be used to create dynamic API Endpoint with Tinybird.

Prerequisites¶

Before continuing, make sure you're familiar with template functions and query parameters. You may also find the blog post "Creating an API with dynamic parameters on stock market data" insightful.

The data¶

This guide uses the ecommerce events data enriched with products. The data looks like this:

SELECT *, price, city, day FROM events_mat
ANY LEFT JOIN products_join_sku ON product_id = sku

Tips and tricks¶

When the complexity of Pipes and API Endpoints grows, developing them and knowing what's going-on to debug problems can become challenging. Here are some tricks that we use when using our product for ourselves and for our clients that we think would be useful for you as well:

WHERE 1=1¶

When you filter by different criteria, given by dynamic parameters that can be omitted, you'll need a WHERE clause. But if none of the parameters are present, you'll need to add a WHERE statement with a dummy condition (like 1=1) that's always true, and then add the other filter statements dynamically if the parameters are defined, like we do in the defined example of this guide.

set¶

The set function present in the previous snippet lets you set the value of a parameter in a Node, so that you can check the output of a query depending on the value of the parameters it takes. Otherwise, you'd have to to publish an API Endpoint and make requests to it with different parameters. Using set, you don't have to exit the Tinybird UI while creating an API Endpoint and the whole process is faster, without needing to go back and forth between your browser or IDE and Postman (or whatever you use to make requests).

Another example of its usage:

%
{% set select_cols = 'date,user_id,event,city' %}
SELECT
  {{columns(select_cols)}}
FROM events_mat

You can use more than one set statement. Just put each one on a separate line at the beginning of a Node.

set is also a way to set defaults for parameters. If you used set statements to test your API Endpoint while developing, remember to remove them before publishing your code, because if not, the set will override any incoming param.

The default argument¶

Another way to set default values for parameters is using the default argument that most Tinybird template functions accept. The previous code could be rewritten as follows:

%
SELECT
  {{columns(select_cols, 'date,user_id,event,city')}}
FROM events_mat

Keep in mind that defining the same parameter in more than one place in your code in different ways can lead to inconsistent behavior. Here's a solution to avoid that:

Using WITH statements to avoid duplicating code¶

If you are going to use the same dynamic parameters more than once in a Node of a Pipe, it's good to define them in one place only to avoid duplicating code. It also makes it clearer knowing which parameters are going to appear in the Node. This can be done with one or more statements at the beginning of a Node, using the WITHclause.

The WITH clause in ClickHouse supports CTEs. They're preprocessed before executing the query, and they can only return one row (this is different to other databases such as Postgres). This is better seen with a live example:

%
{% set terms='orchid' %}
WITH {{split_to_array(terms, '1,2,3')}} AS needles
SELECT
  *, 
  joinGet(products_join_sku, 'color', product_id) color,
  joinGet(products_join_sku, 'title', product_id) title
FROM events
WHERE 
  multiMatchAny(lower(color), needles)
  OR multiMatchAny(lower(title), needles)

Documenting your API Endpoints¶

Tinybird creates auto-generated documentation for all your published API Endpoints, taking the information from the dynamic parameters found in the Pipe. It's best practice to set default values and descriptions for every parameter in one place (also because some functions don't accept a description, for example). We normally do that in the final Node, with WITH statements at the beginning. See how we'd do it in the last section of this guide.

Hidden parameters¶

If you use some functions like enumerate_with_last in the example below, you'll end up with some variables (called x, last in that code snippet) that Tinybird will interpret as if they were parameters that you can set, and they will appear in the auto-generated documentation page. To avoid that, add a leading underscore to their name, renaming x to _x and last to _last.

Debugging any query¶

We have an experimental feature that lets you see how the actual SQL code that will be run on ClickHouse for any published API Endpoint looks, interpolating the query string parameters that you pass in the request URL. If you have a complex query and you'd like to know what is the SQL that will be run, let us know and we'll give you access to this feature to debug a query.

Now let's explore some of the Tinybird advanced template functions, what they allow you to do, and some tricks that will improve your experience creating dynamic API Endpoints on Tinybird.

Advanced functions¶

Most of these functions also appear in the Advanced templates section of our docs. Here we'll provide practical examples of their usage so that it's easier for you to understand how to use them.

defined¶

The defined function lets you check if a query string parameter exists in the request URL or not.

Imagine you want to filter events with a price within a minimum or a maximum price, set by two dynamic parameters that could be omitted. A way to define the API Endpoint would be like this:

%
{% set min_price=20 %}
{% set max_price=50 %}

SELECT *, price
FROM events_mat
WHERE 1 = 1
{% if defined(min_price) %}
  AND price >= {{Float32(min_price)}}
{% end %}
{% if defined(max_price) %}
  AND price <= {{Float32(max_price)}}
{% end %}

To see the effect of having a parameter not defined, use set to set its value to None like this:

%
{% set min_price=None %}
{% set max_price=None %}

SELECT *, price
FROM events_mat
WHERE 1 = 1
{% if defined(min_price) %}
  AND price >= {{Float32(min_price)}}
{% end %}
{% if defined(max_price) %}
  AND price <= {{Float32(max_price)}}
{% end %}

You could also provide some smart defaults to avoid needing to use the defined function at all:

%
SELECT *, price
FROM events_mat_cols
WHERE price >= {{Float32(min_price, 0)}}
  AND price <= {{Float32(max_price, 999999999)}}

Array(variable_name, 'type', [default])¶

Transforms a comma-separated list of values into a Tuple. You can provide a default value for it or not:

%
SELECT 
    {{Array(code, 'UInt32', default='13412,1234123,4123')}} AS codes_1,
    {{Array(code, 'UInt32', '13412,1234123,4123')}} AS codes_2,
    {{Array(code, 'UInt32')}} AS codes_3

To filter events whose type belongs to the ones provided in a dynamic parameter, separated by commas, you'd define the API Endpoint like this:

%
SELECT * 
FROM events
WHERE event IN {{Array(event_types, 'String', default='buy,view')}}

And then the URL of the API Endpoint would be something like https://api.tinybird.co/v0/pipes/your_pipe_name.json?event_types=buy,view

sql_and¶

sql_and lets you create a filter with AND operators and several expressions dynamically, taking into account if the dynamic parameters in a template it are present in the request URL.

It's not possible to use ClickHouse functions inside the {{ }} brackets in templates. sql_and can only be used with the{column_name}__{operand} syntax. This function does the same as what you saw in the previous query: filtering a column by the values that are present in a tuple generated by Array(...) if operand is in, are greater than (with the gt operand), or less than (with the lt operand). Let's see an example to make it clearer:

%
SELECT
  *, 
  joinGet(products_join_sku, 'section_id', product_id) section_id
FROM events
WHERE {{sql_and(event__in=Array(event_types, 'String', default='buy,view'),
                section_id__in=Array(sections, 'Int16', default='1,2'))}}

You don't have to provide default values. If you set the defined argument of Array to False, when that parameter is not provided, no SQL expression will be generated. You can see this in the next code snippet:

%
SELECT
  *, 
  joinGet(products_join_sku, 'section_id', product_id) section_id
FROM events
WHERE {{sql_and(event__in=Array(event_types, 'String', default='buy,view'),
                section_id__in=Array(sections, 'Int16', defined=False))}}

split_to_array(name, [default])¶

This works similarly to Array, but it returns an Array of Strings (instead of a tuple). You'll have to cast the result to the type you want after. As you can see here too, they behave in a similar way:

%
SELECT
    {{Array(code, 'UInt32', default='1,2,3')}},
    {{split_to_array(code, '1,2,3')}},
    arrayMap(x->toInt32(x), {{split_to_array(code, '1,2,3')}}),
    1 in {{Array(code, 'UInt32', default='1,2,3')}},
    '1' in {{split_to_array(code, '1,2,3')}}

One thing that you'll want to keep in mind is that you can't pass non-constant values (arrays, for example) to operations that require them. For example, this would fail:

%
SELECT
    1 IN arrayMap(x->toInt32(x), {{split_to_array(code, '1,2,3')}})

If you find an error like this, you should use a Tuple instead (remember that {{Array(...)}} returns a tuple). This will work:

%
SELECT
    1 IN {{Array(code, 'Int32', default='1,2,3')}}

split_to_array is often used with enumerate_with_last.

column and columns¶

They let you select one or several columns from a Data Source or Pipe, given their name. You can also provide a default value.

%
SELECT {{columns(cols, 'date,user_id,event')}}
FROM events

%
SELECT date, {{column(user, 'user_id')}}
FROM events

enumerate_with_last¶

As the docs say, it creates an iterable array, returning a Boolean value that allows checking if the current element is the last element in the array. Its most common usage is to select several columns, or compute some function over them. We can see an example of columns and enumerate_with_last here:

%
SELECT 
    {% if defined(group_by) %}
        {{columns(group_by)}},
    {% end %}
    sum(price) AS revenue,
    {% for last, x in enumerate_with_last(split_to_array(count_unique_vals_columns, 'section_id,city')) %}
        uniq({{symbol(x)}}) as {{symbol(x)}}
        {% if not last %},{% end %}
    {% end %}
  FROM events_enriched
{% if defined(group_by) %}
    GROUP BY 
         {{columns(group_by)}}

    ORDER BY 
        {{columns(group_by)}}
{% end %}

If you use the defined function around a parameter it doesn't make sense to give it a default value because if it's not provided, that line will never be run.

error and custom_error¶

They let you return customized error responses. With error you can customize the error message:

%
{% if not defined(event_types) %}
  {{error('You need to provide a value for event_types')}}
{% end %}
SELECT
  *, 
  joinGet(products_join_sku, 'section_id', product_id) section_id
FROM events
WHERE event IN {{Array(event_types, 'String')}}

{"error": "You need to provide a value for event_types"}

And with custom_error you can also customize the response code:

%
{% if not defined(event_types) %}
  {{custom_error({'error': 'You need to provide a value for event_types', 'code': 400})}}
{% end %}
SELECT
  *, 
  joinGet(products_join_sku, 'section_id', product_id) section_id
FROM events
WHERE event IN {{Array(event_types, 'String')}}

{"error": "You need to provide a value for event_types", "code": 400}

Note: error and custom_error have to be placed at the start of a Node or they won't work. The order should be:

1. set lines, to give some parameter a default value (optional)
2. Parameter validation functions: error and custom_error definitions
3. The SQL query itself

Putting it all together¶

We've created a Pipe where we use most of these advanced techniques to filter ecommerce events. You can see its live documentation page here and play with it on Swagger here.

This is its code:

NODE events_enriched
SQL >

    SELECT 
        *, 
        price, 
        city, 
        day
    FROM events_mat_cols 
    ANY LEFT JOIN products_join_sku ON product_id = sku



NODE filter_by_price
SQL >

    %
        SELECT * FROM events_enriched
        WHERE 1 = 1
        {% if defined(min_price) %}
          AND price >= {{Float32(min_price)}}
        {% end %}
        {% if defined(max_price) %}
          AND price <= {{Float32(max_price)}}
        {% end %}



NODE filter_by_event_type_and_section_id
SQL >

    %
    SELECT 
          *
        FROM filter_by_price
        {% if defined(event_types) or defined(section_ids) %} ... 
            WHERE {{sql_and(event__in=Array(event_types, 'String', defined=False, enum=['remove_item_from_cart','view','search','buy','add_item_to_cart']),
                            section_id__in=Array(section_ids, 'Int32', defined=False))}}
        {% end %}



NODE filter_by_title_or_color
SQL >

    %
    SELECT *
    FROM filter_by_event_type_and_section_id
    {% if defined(search_terms) %}
    WHERE 
      multiMatchAny(lower(color), {{split_to_array(search_terms)}})
      OR multiMatchAny(lower(title), {{split_to_array(search_terms)}})
    {% end %}



NODE group_by_or_not
SQL >

    %
        SELECT 
            {% if defined(group_by) %}
              {{columns(group_by)}},
              sum(price) AS revenue,
              {% for _last, _x in enumerate_with_last(split_to_array(count_unique_vals_columns)) %}
                  uniq({{symbol(_x)}}) as {{symbol(_x)}}
                  {% if not _last %},{% end %}
              {% end %}
            {% else %}
              *
            {% end %}
        FROM filter_by_title_or_color
        {% if defined(group_by) %}
            GROUP BY {{columns(group_by)}}
            ORDER BY {{columns(group_by)}}
        {% end %}



NODE pagination
SQL >

    %
    WITH 
      {{Array(group_by, 
      'String', 
      '',
               description='Comma-separated name of columns. If defined, group by and order the results by these columns. The sum of revenue will be returned')}},
      {{Array(count_unique_vals_columns, 'String', '',
               description='Comma-separated name of columns. If both group_by and count_unique_vals_columns are defined, the number of unique values in the columns given in count_unique_vals_columns will be returned as well')}},
       {{Array(search_terms, 'String', '',
              description='Comma-separated list of search terms present in the color or title of products')}},
       {{Array(event_types, 'String', '',
               description="Comma-separated list of event name types", enum=['remove_item_from_cart','view','search','buy','add_item_to_cart'])}},
       {{Array(section_ids, 'String', '',
               description="Comma-separated list of section IDs. The minimum value for an ID is 0 and the max is 50.")}}
    SELECT * FROM group_by_or_not
    LIMIT {{Int32(page_size, 100)}}
    OFFSET {{Int32(page, 0) * Int32(page_size, 100)}}

To replicate it in your account, copy the previous code to a new file called advanced_dynamic_endpoints.pipe locally and run `tb push pipes/advanced_dynamic_endpoints.pipe` with our CLI to push it to your Tinybird account.