Using query parameters

Query parameters allow you to control query values dynamically from your application. This makes it really quick and easy to get your endpoint answering different questions, just by passing in a different value as the query parameter.

Using dynmic 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

In order to make a query dynamic, 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>])}}. Example:

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.

Use Pipes API endpoints with dynamic parameters

When using a Data Pipes API endpoint that uses parameters, pass in the desired parameters. Using the example above where lim sets the amount of maximum rows you want to get, the request would look like this:

Using a data Pipes API endpoint containing dynamic parameters
curl -d

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 passed parameters are included in the request.

Available data types for dynamic parameters

Basic data types

  • 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. ClickHouse does not have a boolean type, this function is provided just for convenience and the generated SQL is 1 or 0 accordingly.
  • 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:

Using column dynamic parameters
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 that the first argument is defined.

Validate the column parameter when not defining a default value
{% if defined(order_by) %}
ORDER BY {{column(order_by)}}
{% end %}

Pass 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
access_type IN {{Array(access_numbers, 'Int32', default='101,102,110')}}


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.

Paging results using dynamic parameters
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:

Advanced templating using dynamic parameters
  toDate(start_datetime) as day,
  countIf(status_code < 400) requests,
  countIf(status_code >= 400) errors,
  avg(duration) avg_duration
  endsWith(user_email, {{String(email, '')}}) 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 %}
  day DESC

Validate presence of a parameter

Validate if a param is in the query
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

Test dynamic parameters

Any dynamic parameters you create are displayed in the UI above the Pipe, along with a "Test new values" button. Selecting this button opens a Test popup, populated with the default value of your parameters.

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

Throw errors

Validate if a param is defined and throw an error if it's not defined
{% 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)

Validate if a param is defined and throw an error if it's not defined
{% 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)}}

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.