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 the following pattern {{<data_type>(<name_of_parameter>[,<default_value>])}}
%
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 a 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:
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. AcceptsTrueandFalseas values, as well as strings like'TRUE','FALSE','true','false','1', or'0', or the integers1and0. ClickHouse does not have a boolean type, this function is provided just for convenience and the generated SQL is1or0accordingly.String. For any string values.DateTime64,DateTimeandDate. Accepts values likeYYYY-MM-DD HH:MM:SS.MMM,YYYY-MM-DD HH:MM:SSandYYYYMMDDrespectively.Float32andFloat64. Accepts floating point numbers of either 32 or 64 bit precision.IntorInteger. Accepts integer numbers of any precision.Int8,Int16,Int32,Int64,Int128,Int256andUInt8,UInt16,UInt32,UInt64,UInt128,UInt256. 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:
%
SELECT * FROM TR
ORDER BY {{column(order_by, 'timestamp')}}
LIMIT {{Int32(lim, 10)}}
Although the column function’s second argument, the one for the default value, is optional, it’s highly recommended to always define it. The alternative for not defining it is to validate the first argument is defined.
%
SELECT * FROM TR
{% if defined(order_by) %}
ORDER BY {{column(order_by)}}
{% end %}
Passing Arrays¶
It is also possible to pass along a list of values with the Array function for parameters, like so:
%
SELECT * FROM TR WHERE
access_type IN {{Array(list, 'Int32', default='101,102,110')}}
Pagination¶
It is possible to 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.
The LIMIT clause allows you to select only the first n rows of a query result. The OFFSET clause allows you 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)}}
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:
%
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 param¶
%
select * from table
{% if defined(my_filter) %}
where attr > {{Int32(my_filter)}}
{% end %}
When you call the endpoint with /v0/pipes/:PIPE.json?my_filter=20 it will apply the filter
Throw errors¶
%
{% if not defined(my_filter) %}
{{ error('my_filter (int32) query param is required') }}
{% end %}
select * from table
where attr > {{Int32(my_filter)}}
It will stop the endpoint processing and will return a 400 error.
custom_error function is an advanced version of error where you can customize the response, for example. It gets an object as the first argument which will be sent as JSON and the status_code as a second argument (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)}}
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.
