Managing your pipes

The Pipe API enables you to manage your pipes. Pipes allow to transform data. In order to use the Pipe API you must use an Auth token in order to manageme specific permissions.

Pipelines are a list of nodes with transformations. Each node has a SQL query that can use any other pipeline, datasource or a node (from the same pipeline, nodes from another pipeline can’t be used)

Example

Let’s imagine we have a datasource created an events datasource app_events and we’d like to have a new API that have the number of events per day

First, create the pipe

creating a pipe from a datasource
curl -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.