Using Query parameters

Query parameters are great for any value of the query that you might want control dynamically from your applications, so 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.

Defining parameters in dynamic queries

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

Once you have a dynamic query, you can define parameters by using following pattern {{<data_type>(<name_of_parameter>[,<default_value>])}}

Simple select clause using dynamic parameters
%
SELECT * FROM TR LIMIT {{Int32(lim, 10)}}

The above query would return 10 results by default, or however many are specified on the lim parameter when requesting data from that endpoint.

Using Pipes API endpoints with dynamic parameters

When using an Data Pipes API endpoint which uses parameters, simply pass along the parameters. Using the example above where lim sets the amount of maximum rows you want to get, the request would look as something as:

Using a data Pipes API endpoint containning dynamic parameters
curl -d https://api.tinybird.co/v0/pipes/tr_pipe?lim=20&token=....

Note that parameters can be specified in more than one node in a Data Pipe and, when invoking the API endpoint through its URL, the parameters passed will be taken into account.

Available Data Types for dynamic parameters

Basic data types

  • Boolean. This is not actually a Clickhouse Type, but it is available for convenience and accepts passing values such as TRUE, FALSE, true or false.

  • String. For any string values.

  • DateTime and Date. Accepts values like 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 and UInt8, UInt16, UInt32, UInt64. Accepts signed or unsigned integer numbers of the specified precision.

Using column parameters

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

Using column dynamic parameters
%
SELECT * FROM TR
ORDER BY {{column(order, 'timestamp')}}
LIMIT {{Int32(lim, 10)}}

Passing Arrays

It is also possible to pass along a list of values with the Array function for parameters, like so:

Passing arrays as dynamic parameters
%
SELECT * FROM TR WHERE
access_type IN {{Array(list, 'Int32', default='101,102,110')}}

Advanced Templating using Dynamic Parameters

In order to perform more complex queries, it is possible to use flow control operators like if, else and elif in combination with the defined() function, which will allow you to check if a parameter has been received or not and act accordingly.

Those control statements need to be enclosed in curly brackets with percentages {%..%} as in the following example:

Advanced templating using dynamic parameters
%
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

What’s next

Thanks to the use of dynamic parameters in your Data Pipes nodes, you will be able to create flexible API endpoints with ease, so you don’t need to manage dozens of Data Pipes. If you want to use Tinybird.js dynamic parameters in your JavaScript applications, please take a look to our js library documentation