Querying your data

The Query API allows you to query your Pipes inside Tinybird Analytics as if you were running SQL statements against a regular database.

Using the Query API
 curl \
     -H "Authorization: Bearer <PIPE:READ token>" \
     "https://api.tinybird.co/v0/sql?q=SELECT * FROM <pipe> FORMAT JSON"
 # or
 curl \
     -H "Authorization: Bearer <PIPE:READ token>" \
     "https://api.tinybird.co/v0/pipes/<pipe>.json?q=select count() from _"

Every resource in Tinybird Analytics is secured so in order to use the Query API you must provide an Auth token with Pipe read permissions.

GET /v0/sql

Executes a SQL query using the engine.

Running sql queries against your data
curl "https://api.tinybird.co/v0/sql?q=SELECT * FROM <pipe>"

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

Successful response
{
"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 many different formats. Just append FORMAT <format_name> to your SQL query:

Requesting different formats with SQL
SELECT count() from <pipe> FORMAT JSON
Request parameters

Key

Type

Description

q

String

The SQL query

pipeline

String

(Optional) The name of the pipe. It allows to writing a query like ‘SELECT * FROM _’ where ‘_’ is a placeholder for the ‘pipeline’ parameter

output_format_json_quote_64bit_integers

int

(Optional) Controls quoting of 64-bit or bigger integers (like UInt64 or Int128) when they are output in a JSON format. Such integers are enclosed in quotes by default. This behavior is compatible with most JavaScript implementations. Possible values: 0 — Integers are output without quotes. 1 — Integers are enclosed in quotes. Default value is 0

Available formats

format

Description

CSV

CSV without header

CSVWithNames

CSV with header

JSON

JSON including data, statistics and schema information

TSV

TSV without header

TSVWithNames

TSV with header

PrettyCompact

Formatted table

JSONEachRow

Newline-delimited JSON values (NDJSON)

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').