Query API¶

Executes a SQL query using the engine, while providing a templated or non templated query string and the custom parameters that will be translated into the query. The custom parameters provided should not have the same name as the request parameters for this endpoint (outlined below), as they are reserved in order to get accurate results for your query.

For example:

1. Providing the value to the query via the POST body:

curl -X POST \
    -H "Authorization: Bearer <PIPE:READ token>" \
    -H "Content-Type: application/json" \
    "https://api.tinybird.co/v0/sql" -d \
    '{
        "q":"SELECT * FROM <pipe> where column_name = {{String(column_name)}}",
        "column_name": "column_name_value",
    }'

2. Providing a new value to the query from the one defined within the pipe in the POST body:

curl -X POST \
    -H "Authorization: Bearer <PIPE:READ token>" \
    -H "Content-Type: application/json" \
    "https://api.tinybird.co/v0/sql" -d \
    '{
        "q":"SELECT * FROM <pipe> where column_name = {{String(column_name, "column_name_value")}}",
        "column_name": "new_column_name_value",
    }'

3. Providing a non template query in the POST body:

curl -X POST \
    -H "Authorization: Bearer <PIPE:READ token>" \
    -H "Content-Type: application/json" \
    "https://api.tinybird.co/v0/sql" -d \
    '{
        "q":"SELECT * FROM <pipe>"
    }'

4. Providing a non template query as a string in the POST body with a content type of "text/plain":

curl -X POST \
    -H "Authorization: Bearer <PIPE:READ token>" \
    -H "Content-Type: text/plain" \
    "https://api.tinybird.co/v0/sql" -d "SELECT * FROM <pipe>"

As a response, it gives you the query metadata, the resulting data and some performance statistics.

{
"meta": [
    {
        "name": "VendorID",
        "type": "Int32"
    },
    {
        "name": "tpep_pickup_datetime",
        "type": "DateTime"
    }
],
"data": [
    {
        "VendorID": 2,
        "tpep_pickup_datetime": "2001-01-05 11:45:23",
        "tpep_dropoff_datetime": "2001-01-05 11:52:05",
        "passenger_count": 5,
        "trip_distance": 1.53,
        "RatecodeID": 1,
        "store_and_fwd_flag": "N",
        "PULocationID": 71,
        "DOLocationID": 89,
        "payment_type": 2,
        "fare_amount": 7.5,
        "extra": 0.5,
        "mta_tax": 0.5,
        "tip_amount": 0,
        "tolls_amount": 0,
        "improvement_surcharge": 0.3,
        "total_amount": 8.8
    },
    {
        "VendorID": 2,
        "tpep_pickup_datetime": "2002-12-31 23:01:55",
        "tpep_dropoff_datetime": "2003-01-01 14:59:11"
    }
],
"rows": 3,
"rows_before_limit_at_least": 4,
"statistics":
    {
        "elapsed": 0.00091042,
        "rows_read": 4,
        "bytes_read": 296
    }
}

Data can be fetched in different formats. Just append FORMAT <format_name> to your SQL query:

SELECT count() from <pipe> FORMAT JSON

As you can see in the example above, timestamps do not include a time zone in their serialization. Let’s see how that relates to timestamps ingested from your original data:

• If the original timestamp had no time zone associated, you’ll read back the same date and time verbatim.

  If you ingested the timestamp 2022-11-14 11:08:46, for example, Tinybird sends "2022-11-14 11:08:46" back. This is so regardless of the time zone of the column in ClickHouse.

• If the original timestamp had a time zone associated, you’ll read back the corresponding date and time in the time zone of the destination column in ClickHouse, which is UTC by default.

  If you ingested 2022-11-14 12:08:46.574295 +0100, for instance, Tinybird sends "2022-11-14 11:08:46" back for a DateTime, and "2022-11-14 06:08:46" for a DateTime('America/New_York').