Endpoints¶

Endpoints are a type of pipe that you can call from other applications. For example, you can ingest your data, build SQL logic inside a pipe, and then publish the result of your query as a REST API endpoint.

Create an endpoint¶

Endpoints are defined in a .pipe file using the TYPE ENDPOINT directive. See Endpoint pipes.

You can list your endpoints and their URLs and tokens using the tb endpoint command. See tb endpoint.

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 endpoint to answer different questions by passing a different value as query parameter.

Use dynamic parameters means to 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.

Call an endpoint with dynamic parameters¶

When using an 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.europe-west2.gcp.tinybird.co/v0/pipes/tr_pipe?lim=20&token=<your_token>

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 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 can 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 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 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

The addition of a LIMIT clause to a query also adds the rows_before_limit_at_least field to the response metadata. rows_before_limit_at_least is the lower bound on the number of rows returned by the query after transformations but before the limit was applied, and can be useful for response handling calculations.

Advanced templating¶

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¶

To validate if a parameter is present in the query, use the defined() function. For example:

%
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 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 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 __no_value__, 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": "__no_value__",
  "placeholder_num": 0,
  "placeholder_bool": 0,
  "placeholder_float": 0,
  "placeholder_date": "2019-01-01",
  "placeholder_ts": "2019-01-01 00:00:00",
  "placeholder_array": ["__no_value__0","__no_value__1"]
}

When using defined() functions in the WHERE clause, make sure to add 1 and use the AND operator to avoid SQL syntax errors like:

SELECT * FROM table
WHERE 1
AND attr > {{Int32(my_filter)}}