Using query parameters¶

Query parameters are great for any value of the query that you might want control dynamically from your applications. For example, you can get your API Endpoint to answer different questions by passing a different value as query parameter.

Using dynamic parameters means you can do things like:

• Filtering as part of a WHERE clause.
• Changing the number of results as part of a LIMIT clause.
• Sorting order as part of an ORDER BY clause.
• Selecting specific columns for ORDER BY or GROUP BY clauses.

Define dynamic parameters¶

To make a query dynamic, start the query with a % character. That signals the engine that it needs to parse potential parameters.

After you have created a dynamic query, you can define parameters by using the following pattern {{<data_type>(<name_of_parameter>[,<default_value>, description=<"This is a description">, required=<True|False>])}}. For example:

%
SELECT * FROM TR LIMIT {{Int32(lim, 10, description="Limit the number of rows in the response", required=False)}}

The previous query returns 10 results by default, or however many are specified on the lim parameter when requesting data from that API Endpoint.

Use Pipes API Endpoints with dynamic parameters¶

When using a Data Pipes API Endpoint that uses parameters, pass in the desired parameters.

Using the previous example where lim sets the amount of maximum rows you want to get, the request would look like this:

curl -d https://api.tinybird.co/v0/pipes/tr_pipe?lim=20&token=....

You can specify parameters in more than one Node in a Data Pipe. When invoking the API Endpoint through its URL, the passed parameters are included in the request.

Leverage dynamic parameters¶

As well as using dynamic parameters in your API Endpoints, you can then leverage them further downstream for monitoring purposes.

When you pass a parameter to your queries, you can build Pipes to reference the parameters and query the Service Data Sources with them, even if you don't use them in the API Endpoints themselves.

Review the Service Data Sources docs to use the available options. For example, using the user_agent column on pipe_stats_rt shows which user agent made the request. Pass any additional things you need as a parameter to improve visibility and avoid, or get insights into, incidents and Workspace performance. This process helps you forward things like user agent or others from any app requests all the way through to Tinybird, and track if the request was done in the app and details like which device was used.

SELECT
  toStartOfMinute(start_datetime) as date,
  count(),
  parameters['referrer']
FROM tinybird.pipes_stats_rt
WHERE 
  (
    pipe_id = '<pipe_id_here>' and status_code != 429)
    or
    pipe_name = '<pipe_name_here>' and status_code != 429)
  )
  and
  start_datetime > now() - interval - 1 hour
GROUP BY date, parameters['referrer']
ORDER BY count() DESC, date DESC

Available data types for dynamic parameters¶

You can use the following data types for dynamic parameters:

• Boolean: Accepts True and False as values, as well as strings like 'TRUE', 'FALSE', 'true', 'false', '1', or '0', or the integers 1 and 0.
• String: For any string values.
• DateTime64, DateTime and Date: Accepts values like YYYY-MM-DD HH:MM:SS.MMM, YYYY-MM-DD HH:MM:SS and YYYYMMDD respectively.
• Float32 and Float64: Accepts floating point numbers of either 32 or 64 bit precision.
• Int or Integer: Accepts integer numbers of any precision.
• Int8, Int16, Int32, Int64, Int128, Int256 and UInt8, UInt16, UInt32, UInt64, UInt128, UInt256: Accepts signed or unsigned integer numbers of the specified precision.

Use column parameters¶

You can use column to pass along column names of a defined type as parameters, like:

%
SELECT * FROM TR 
ORDER BY {{column(order_by, 'timestamp')}}
LIMIT {{Int32(lim, 10)}}

Always define the column function's second argument, the one for the default value. The alternative for not defining the argument is to validate that the first argument is defined, but this only has an effect on the execution of the API Endpoint. A placeholder is used in the development of the Pipes.

%
SELECT * FROM TR
{% if defined(order_by) %}
ORDER BY {{column(order_by)}}
{% end %}

Pass arrays¶

You can pass along a list of values with the Array function for parameters, like so:

%
SELECT * FROM TR WHERE 
access_type IN {{Array(access_numbers, 'Int32', default='101,102,110')}}

Send stringified JSON as parameter¶

Consider the following stringified JSON:

"filters": [
    {
        "operand": "date",
        "operator": "equals",
        "value": "2018-01-02"
    },
    {
        "operand": "high",
        "operator": "greater_than",
        "value": "100"
    },
    {
        "operand": "symbol",
        "operator": "in_list",
        "value": "AAPL,AMZN"
    }
]

You can use the JSON() function to use filters as a query parameter. The following example shows to use the filters field from the JSON snippet with the stock_prices_1m sample dataset.

%
SELECT symbol, date, high
FROM stock_prices_1m
WHERE
    1
    {% if defined(filters) %}
        {% for item in JSON(filters, '[]') %}
            {% if item.get('operator', '') == 'equals' %}
                AND {{ column(item.get('operand', '')) }} == {{ item.get('value', '') }}
            {% elif item.get('operator') == 'greater_than' %}
                AND {{ column(item.get('operand', '')) }} > {{ item.get('value', '') }}
            {% elif item.get('operator') == 'in_list' %}
                AND {{ column(item.get('operand', '')) }} IN splitByChar(',',{{ item.get('value', '') }})
            {% end %}
        {% end %}
    {% end %}

When accessing the fields in a JSON object, use the following syntax:

item.get('Field', 'Default value to avoid SQL errors').

Pagination¶

You paginate results by adding LIMIT and OFFSET clauses to your query. You can parameterize the values of these clauses, allowing you to pass pagination values as query parameters to your API Endpoint.

Use the LIMIT clause to select only the first n rows of a query result. Use the OFFSET clause to skip n rows from the beginning of a query result. Together, you can dynamically chunk the results of a query up into pages.

For example, the following query introduces two dynamic parameters page_size and page which lets you control the pagination of a query result using query parameters on the URL of an API Endpoint.

%
SELECT * FROM TR
LIMIT {{Int32(page_size, 100)}}
OFFSET {{Int32(page, 0) * Int32(page_size, 100)}}

You can also use pages to perform calculations such as count(). The following example counts the total number of pages:

%
SELECT count() as total_rows, ceil(total_rows/{{Int32(page_size, 100)}}) pages FROM endpoint_to_paginate

Advanced templating using dynamic parameters¶

To build more complex queries, use flow control operators like if, else and elif in combination with the defined() function, which helps you to check if a parameter whether a parameter has been received and act accordingly.

Tinybird's templating system is based on the Tornado Python framework, and uses Python syntax. You must enclose control statements in curly brackets with percentages {%..%} as in the following example:

%
SELECT
  toDate(start_datetime) as day,
  countIf(status_code < 400) requests,
  countIf(status_code >= 400) errors,
  avg(duration) avg_duration
FROM
  log_events
WHERE
  endsWith(user_email, {{String(email, 'gmail.com')}}) AND 
  start_datetime >= {{DateTime(start_date, '2019-09-20 00:00:00')}} AND
  start_datetime <= {{DateTime(end_date, '2019-10-10 00:00:00')}}
  {% if method != 'All' %} AND method = {{String(method,'POST')}} {% end %}
GROUP BY
  day
ORDER BY
  day DESC

Validate presence of a parameter¶

%
select * from table
{% if defined(my_filter) %}
where attr > {{Int32(my_filter)}}
{% end %}

When you call the API Endpoint with /v0/pipes/:PIPE.json?my_filter=20 it applies the filter.

Default parameter values and placeholders¶

Following best practices, you should set default parameter values as follows:

%
SELECT * FROM table
WHERE attr > {{Int32(my_filter, 10)}}

When you call the API Endpoint with /v0/pipes/:PIPE.json without setting any value to my_filter, it automatically applies the default value of 10.

If you don't set a default value for a parameter, you should validate that the parameter is defined before using it in the query as explained previously.

If you don't validate the parameter and it's not defined, the query might fail. Tinybird populates the parameter with a placeholder value based on the data type. For instance, numerical data types are populated with 0, strings with __placeholder__, and date and timestamps with 2019-01-01 and 2019-01-01 00:00:00 respectively. You could try yourself with a query like this:

%
  SELECT 
      {{String(param)}} as placeholder_string,
      {{Int32(param)}} as placeholder_num,
      {{Boolean(param)}} as placeholder_bool,
      {{Float32(param)}} as placeholder_float,
      {{Date(param)}} as placeholder_date,
      {{DateTime(param)}} as placeholder_ts,
      {{Array(param)}} as placeholder_array

This returns the following values:

{
  "placeholder_string": "__placeholder__",
  "placeholder_num": 0,
  "placeholder_bool": 0,
  "placeholder_float": 0,
  "placeholder_date": "2019-01-01",
  "placeholder_ts": "2019-01-01 00:00:00",
  "placeholder_array": ["__placeholder__0","__placeholder__1"]
}

Test dynamic parameters¶

Any dynamic parameters you create appears in the UI along with a "Test new values" button. Select this button to open a test dialog populated with the default value of your parameters.

The test dialog is a convenient way to quickly test different Pipe values than the default ones without impacting production. Use the View API page to see API Endpoint metrics resulting from that specific combination of parameters. Close the dialog to bring the Pipe back to its default production state.

Cascade parameters¶

Parameters with the same name in different Pipes are cascaded down the dependency chain.

For example, if you publish Pipe A with the parameter foo, and then Pipe B which uses Pipe A as a Data Source also with the parameter foo, then when you call the API Endpoint of Pipe B with foo=bar, the value of foo will be bar in both Pipes.

Throw errors¶

The following example stops the API Endpoint processing and returns a 400 error:

%
{% if not defined(my_filter) %}
{{ error('my_filter (int32) query param is required') }}
{% end %}
select * from table
where attr > {{Int32(my_filter)}}

The custom_error function is an advanced version of error where you can customize the response and other aspects. The function gets an object as the first argument, which is sent as JSON, and the status_code as a second argument, which defaults to 400.

%
{% if not defined(my_filter) %}
{{ custom_error({'error_id': 10001, 'error': 'my_filter (int32) query param is required'}) }}
{% end %}
select * from table
where attr > {{Int32(my_filter)}}

Limits¶

You can't use query parameters in Nodes that are published as Materialized Views, only as API Endpoints or in on-demand Copies or Sinks.

You can use query parameters in scheduled Sinks and Copies, but must have a default. That default is used in the scheduled execution. The preview step fails if the default doesn't exist.

Next steps¶

Thanks to the magic of dynamic parameters, you can create flexible API Endpoints with ease, so you don't need to manage or test dozens of Pipes. Be sure you're familiar with the 5 rules for faster SQL queries.