Managing your pipes

The Pipe API enables you to manage your pipes. With Pipes you can transform data via SQL queries, and you can also publish the results of those queries as API endpoints. Keep in mind that in order to use the Pipe API you need an Auth token with appropriate permissions.

Pipes are essentially a collection of ‘transformation’ nodes or SQL queries. From each node you can query a Datasource, the results of other nodes in the same Pipe or the results exposed in a published Pipe. Note that you cannot query unpublished nodes of a different Pipe.

How to create a Tinybird Analytics Pipe

Imagine you have an events datasource named app_events and you want to expose an API endpoint that contains the number of events per day.

First, you would create the pipe. We will call it events_per_day:

creating a pipe from a datasource
curl \\
-H "Authorization: Bearer <token> \\
-d "name=events_per_day&sql=select * from app_events" \\
https://api.tinybird.co/v0/pipes
response
{
  "id": "t_1b501ccf34764a69aaf886bac9a7a6d8",
  "name": "events_per_day",
  "published_version": "t_878a81f0e1344a2ca990c8c1aa7dd69f",
  "published_date": "2019-06-14 09:46:07.884855",
  "nodes": [
    {
      "name": "events_per_day_00",
      "sql": "select * from app_events",
      "id": "t_878a81f0e1344a2ca990c8c1aa7dd69f",
      "dependencies": [
        "app_events"
      ],
      "materialized": false,
      "created_at": "2019-06-14 09:46:07.884849"
    }
  ]
}

Now, add a new node that aggregates the first node events_per_day_00. For that let’s use “append” endpoint.

append a new node to transform the data
curl -d "select toDate(timestamp) date, count() event_count from app_events group by date" \
https://api.tinybird.co/v0/pipes/events_per_day/append
response
{
  "name": "events_per_day_01",
  "sql": "select toDate(timestamp) date, count() event_count from events_per_day_00 group by date",
  "id": "t_5164622050b244338ea2b79c19bd1e57",
  "dependencies": [
    "events_per_day_00"
  ],
  "materialized": false,
  "created_at": "2019-06-14 09:58:08.311812"
}

if we query the pipe right now it will return data from node0 because by default the first node is the published one. So let’s make the final node the good one. This is kind of what you do with git: you have a series of commits and master points the to the “published” one altough you can have many more commits.

publishing the latest version
curl -d "t_5164622050b244338ea2b79c19bd1e57" \
https://api.tinybird.co/v0/pipes/events_per_day/published
response
{
  "id": "t_1b501ccf34764a69aaf886bac9a7a6d8",
  "name": "events_per_day",
  "published_version": "t_5164622050b244338ea2b79c19bd1e57",
  "published_date": "2019-06-14 10:17:01.201962",
  "nodes": [
    {
      "name": "events_per_day_00",
      "sql": "select * from app_events",
      "id": "t_878a81f0e1344a2ca990c8c1aa7dd69f",
      "dependencies": [
        "app_events"
      ],
      "materialized": false,
      "created_at": "2019-06-14 10:17:01.201784"
    },
    {
      "name": "events_per_day_01",
      "sql": "select toDate(date) date, count() event_count from events_per_day_00 group by date",
      "id": "t_5164622050b244338ea2b79c19bd1e57",
      "dependencies": [
        "events_per_day_00"
      ],
      "materialized": false,
      "created_at": "2019-06-14 10:17:01.201943"
    }
  ]
}

Now, once is created, we need to fecth the data in order to integrate with a dashboard or any other app, there are two ways:

  1. Using Query API (see Querying your data) You can query using the pipe name as if it was a regular table in a SQL query: SELECT * FROM events_per_day where date > yesterday()

    quering a pipe using SQL
    curl -d https://api.tinybird.co/v0/sql?q=SELECT * FROM events_per_day where date > yesterday()
    
  2. Using the pipe data enpoint. It’s an alias for SELECT * FROM events_per_day

    quering a pipe using data endpoint
    curl https://api.tinybird.co/v0/pipe/events_per_day/data.json
    

Some interesting info:

  • Every single request need a valid token (see Auth Tokens management) with permission to read the pipe.

  • You can fetch data using json as we did in the example but csv and other formats can be used as well

  • As you insert the new data in app_events datasource the events_per_day is updated.

  • The pipe doesn’t need to be created step by step, you could post the whole JSON to create it, like

    creating a pipe with several nodes
     curl -X POST https://api.tinybird.co/v0/pipes -d \
     '{
         name":"pipe_name",
         "nodes": [
             {"sql": "select * from app_evetns", "name": "events_per_day_00" },
             {"sql": "select toDate(date) date, count() event_count from events_per_day_00 group by date", "name": "events_per_day_01" }
         ]
     }'
    

If you are looking to create a new datasource, take a look at the Datasources API reference.

GET /v0/pipes/?
get a list of your pipes
curl -X GET "https://api.tinybird.co/v0/pipes"

Get a list of Pipes in your account.

Pipe in the response will be the ones that are accesible using a particular token with read permissions for them.

Succesfull response
{
    "pipes": [{
        "id": "pipe_id",
        "name": "pipe_name"
    }]
}
POST /v0/pipes/?

Creates a new Pipe.

There are 3 ways to create a Pipe

  1. Providing full JSON description

with fill JSON
curl -X POST "https://api.tinybird.co/v0/pipes" -d
'{
    name":"pipe_name",
    "nodes": [
        {"sql": "select * from my_datasource limit 10", "name": "node_00", "description": "sampled data" },
        {"sql": "select count() from node_00", "name": "node_01" }
    ]
}'
  1. Setting name and sql query for the fist node

with SQL
curl -X POST "https://api.tinybird.co/v0/pipes?name=pipename&sql=select * from datasource"
  1. Creating a copy of another pipe using from argument

copying another pipe
curl -X POST "https://api.tinybird.co/v0/pipes?name=pipename&from=src_pipe
DELETE /v0/pipes/(.+)/nodes/(.+)

Drops the node in the pipeline. It does not remove related nodes so this could Leaves the pipe in an unconsistent state.

A published node can’t be removed.

removing a token
curl -X DELETE "https://api.tinybird.co/v0/pipes/:name/nodes/:node_id"
Response codes

Code

Description

204

No error, removed node

404

Pipe not found

400

The node is published. Published nodes can’t be removed

403

Forbidden. Provided token doesn’t have permissions to change the last node to the pipe, it needs ADMIN or IMPORT

PUT /v0/pipes/(.+)/nodes/(.+)

Changes the node in the pipeline

editing a pipe
curl -X PUT -d 'select * from node_0' "https://api.tinybird.co/v0/pipes/:name/nodes/:node_id?name=new_name&description=updated_explanation"
Request parameters

Key

Type

Description

name

String

new name for the node

description

String

new description for the node

token

String

Auth token. Ensure it has the PIPES:CREATE scope on it

SQL query should be sent in the body

Response codes

Code

Description

200

No error

404

Pipe not found

400

empty or wrong SQL

403

Forbidden. Provided token doesn’t have permissions to change the last node to the pipe, it needs ADMIN or PIPES:CREATE

returns a json with the node name or error

POST /v0/pipes/(.+)/nodes

Append new node to the pipeline

adding a new node
curl -d 'select * from node_0' "https://api.tinybird.co/v0/pipes/:name/nodes?name=node_name&description=explanation"
Request parameters

Key

Type

Description

name

String

The referenceable name for the node.

description

String

Use it to store a more detailed explanation of the node.

token

String

Auth token. Ensure it has the PIPES:CREATE scope on it

SQL query must be sent in the body

Response codes

Code

Description

200

No error

404

Pipe not found

400

empty or wrong SQL

403

Forbidden. Provided token doesn’t have permissions to append a node to the pipe, it needs ADMIN or PIPES:CREATE

returns a json with the node name or error

PUT /v0/pipes/(.+)/endpoint

Change the endpoint node of the Pipe.

Change the endpoint
curl -X PUT -d '2b02db0cb3991a3bf3d0bb77392d47029257e1b16f4a30d589789bc2359f435c' "https://api.tinybird.co/v0/pipes/:name/endpoint"

Node id to be published must be sent in the request body. If empty, it will unpublish the current endpoint.

Returns a json with the full pipe description

Response codes

Code

Description

200

No error

404

Pipe not found

400

empty or wrong id

403

Forbidden. Provided token doesn’t have permissions to publish a pipe, it needs ADMIN or PIPES:CREATE

GET /v0/pipes/(.+).(.+)
Get data for a pipe
curl -X GET "https://api.tinybird.co/v0/pipes/:name.format"

Returns the published node data with format.

Requests parameters

Key

Type

Description

q

String

Optional, query to execute, see API Query endpoint

The q parameter is a SQL query, please read API Query documentation. The main difference is _ shorcut can be used, so /v0/pipes/name.json?q=select * from _ is equivalent to /v0/sql?q=select * from name

formats

format

Description

csv

CSV with header

json

JSON including data, statistics and schema information

GET /v0/pipes/(.+)
Get information about a particular pipe
curl -X GET "https://api.tinybird.co/v0/pipes/:name"

Get pipe information. Provided token must have read access to the pipe

Succesfull response
{
    "id": "t_bd1c62b5e67142bd9bf9a7f113a2b6ea",
    "name": "events_pipe",
    "pipeline": {
        "nodes": [{
            "name": "events_ds_0"
            "sql": "select * from events_ds_log__raw",
            "materialized": false
        }, {
            "name": "events_ds", 
            "sql": "select * from events_ds_0 where valid = 1",
            "materialized": false
        }]
    }
}

pipe_id and pipe_name are two ways to refer to the pipe in SQL queries and API endpoints the only difference is pipe_id never change so if it’ll work even if you change the pipe_name (which is the name used to display the pipe). In general you can use pipe_id or pipe_name indistinctly:

select count(1) from events_pipe

is equivalent to

select count(1) from t_bd1c62b5e67142bd9bf9a7f113a2b6ea

t_bd1c62b5e67142bd9bf9a7f113a2b6ea is not a descriptive name so you can add a description like t_my_events_table.bd1c62b5e67142bd9bf9a7f113a2b6ea

DELETE /v0/pipes/(.+)
Drops a pipe
curl -X DELETE "https://api.tinybird.co/v0/pipes/:name"

Drops a pipe from your account. Auth token in use must have the DROP:name scope.

PUT /v0/pipes/(.+)

Changes pipe attributes

editing a token
curl -X PUT "https://api.tinybird.co/v0/pipes/:name?name=new_name"
Request parameters

Key

Type

Description

name

String

new name for the pipe

token

String

Auth token. Ensure it has the PIPES:CREATE scope on it

When there is another pipe with the same name an error is raised.