Managing your pipes

The Pipes API enables you to manage your Pipes. With Pipes you can transform data via SQL queries and publish the results of those queries as API endpoints.

Pipes are essentially a collection of transformation nodes or SQL queries. From each node you can query a Data source, 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.

In order to use the Pipes API you need an Auth token with appropriate permissions.

How to create a Tinybird Analytics Pipe

Imagine you have an events Data Source named app_events and you want to expose an API endpoint containing the aggregation of events per day.

First, you would create a 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

As you can see, when creating the Pipe we have also defined the first transformation node SQL query, which in this case, is just getting all data from the Data Source. Below, you have how a successful request response looks like after creating a Pipe:

Pipe successfully created
{
  "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_0",
      "sql": "select * from app_events",
      "id": "t_878a81f0e1344a2ca990c8c1aa7dd69f",
      "dependencies": [
        "app_events"
      ],
      "materialized": false,
      "created_at": "2019-06-14 09:46:07.884849"
    }
  ]
}

Now, we are going to add a new transformation node to perform the aggregation per date using the previously created node events_per_day_0. For that let’s use “append” endpoint.

appending a new node to transform your data
curl -d "select toDate(timestamp) date, count() event_count from events_per_day_0 group by date" \
  -H "Authorization: Bearer <token>" \
  https://api.tinybird.co/v0/pipes/events_per_day/nodes

Whenever a transformation node is added to a Pipe, you will receive a response like the one below, summarizing and giving you an autogenerated name for the node, as well as some metadata such as the dependencies with other transformation nodes:

successful response
{
  "name": "events_per_day_1",
  "sql": "select toDate(timestamp) date, count() event_count from events_per_day_0 group by date",
  "id": "t_5164622050b244338ea2b79c19bd1e57",
  "dependencies": [
    "events_per_day_0"
  ],
  "materialized": false,
  "created_at": "2019-06-14 09:58:08.311812"
}

In order to make a Pipe publicly accessible through the API, you will need to enable your desired transformation node as an API endpoint. Pipes support just one enabled node so enbling one, will make previously enabled nodes to become unaccessible.

Enabling a transformation node as an API endpoint
curl \
  -X PUT \
  -H "Authorization: Bearer <token>" \
  -d t_878a81f0e1344a2ca990c8c1aa7dd69f \
  https://api.tinybird.co/v0/pipes/events_per_day/endpoint

When enabling a transformation node as an API endpoint, a JSON containing the full Pipe description is sent as the response.

Successful 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_0",
      "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_1",
      "sql": "select toDate(date) date, count() event_count from events_per_day_0 group by date",
      "id": "t_5164622050b244338ea2b79c19bd1e57",
      "dependencies": [
        "events_per_day_0"
      ],
      "materialized": false,
      "created_at": "2019-06-14 10:17:01.201943"
    }
  ]
}

Once the Pipe is created and we’ve enabled a transformation node as an Endpoint, we can easily integrate it into our 3rd party application. Using Query API (see Querying your data) you can query the Pipe using its name as if it was a regular table in a SQL query: SELECT * FROM events_per_day where date > yesterday()

querying a pipe using SQL
curl \
-H "Authorization: Bearer <token>" \
-d 'SELECT * FROM events_per_day where date > yesterday()' \
'https://api.tinybird.co/v0/sql'

If you don’t need to run any special operation against your Pipe, you can just use the Pipe data endpoint accesible at https://api.tinybird.co/v0/pipes/events_per_day.json. It’s an alias for SELECT * FROM events_per_day

Of course Pipes are updated in real-time, so as you insert the new data in app_events Data Source, every Pipe using it events_per_day is updated.

GET /v0/pipes/?

Get a list of pipes in your account.

getting a list of your pipes
curl -X GET \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    "https://api.tinybird.co/v0/pipes"

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

Successful response
{
    "pipes": [{
        "id": "pipe_id",
        "name": "pipe_name"
    }]
}
Request parameters

Key

Type

Description

dependencies

boolean

The response will include the nodes dependent data sources and pipes, default is false

attrs

String

comma separated list of the pipe attributes to return in the response. Example: attrs=name,description

node_attrs

String

comma separated list of the node attributes to return in the response. Exapmle node_attrs=id,name

Pipes id’s are immutable so you can always refer to them in your 3rd party applications to make them compatible with Pipes once they are renamed.

For lighter JSON responses consider using the attrs and node_attrs params to return exactly the attributes you need to consume.

POST /v0/pipes/?

Creates a new Pipe. There are 3 ways to create a Pipe

Creating a Pipe providing full JSON
curl -X POST \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -H "Content-Type: application/json" \
    "https://api.tinybird.co/v0/pipes" -d
    '{
        "name":"pipe_name",
        "description": "my first pipe",
        "nodes": [
            {"sql": "select * from my_datasource limit 10", "name": "node_00", "description": "sampled data" },
            {"sql": "select count() from node_00", "name": "node_01" }
        ]
    }'

If you prefer to create the minimum Pipe, and then append your transformation nodes you can set your name and first transformation node’s SQL in your POST request

Creating a pipe with a name and a SQL query
curl -X POST \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    "https://api.tinybird.co/v0/pipes?name=pipename&sql=select%20*%20from%20events"

Pipes can be also created as copies of other Pipes. Just use the from argument:

Creating a pipe from another pipe
curl -X POST \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    "https://api.tinybird.co/v0/pipes?name=pipename&from=src_pipe"
POST /v0/pipes/(.+)/nodes/(.+)/endpoint

Publishes an API endpoint

Publishing an endpoint
curl -X POST \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -d "https://api.tinybird.co/v0/pipes/:pipe/nodes/:node/endpoint"
Successful response
{
    "id": "t_60d8f84ce5d349b28160013ce99758c7",
    "name": "my_pipe",
    "description": "this is my pipe description",
    "nodes": [{
        "id": "t_bd1e095da943494d9410a812b24cea81",
        "name": "get_all",
        "sql": "SELECT * FROM my_datasource",
        "description": "This is a description for the **first** node",
        "materialized": null,
        "cluster": null,
        "dependencies": ["my_datasource"],
        "tags": {},
        "created_at": "2019-09-03 19:56:03.704840",
        "updated_at": "2019-09-04 07:05:53.191437",
        "version": 0,
        "project": null,
        "result": null,
        "ignore_sql_errors": false
    }],
    "endpoint": "t_bd1e095da943494d9410a812b24cea81",
    "created_at": "2019-09-03 19:56:03.193446",
    "updated_at": "2019-09-10 07:18:39.797083",
    "parent": null
}

The response will contain a token if there’s a unique READ token for this pipe. You could use this token to share your endpoint.

Response codes

Code

Description

200

No error

400

Wrong node id

403

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

404

Pipe not found

DELETE /v0/pipes/(.+)/nodes/(.+)/endpoint

Unpublishes an API endpoint

Unpublishing an endpoint
curl -X DELETE \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -d "https://api.tinybird.co/v0/pipes/:pipe/nodes/:node/endpoint"
Response codes

Code

Description

200

No error

400

Wrong node id

403

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

404

Pipe not found

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

Populates a Materialized View

Populating a Materialized View
curl
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -X POST "https://api.tinybird.co/v0/pipes/:pipe/nodes/:node/population" \
    -d "populate_condition=toYYYYMM(date) = 202203"

The response will not be the final result of the import but a Job. You can check the job status and progress using the Jobs API.

Request parameters

Key

Type

Description

token

String

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

populate_subset

Float

Optional. Populate with a subset percent of the data (limited to a maximum of 2M rows), this is useful to quickly test a materialized node with some data. The subset must be greater than 0 and lower than 0.1. A subset of 0.1 means a 10 percent of the data in the source Data Source will be used to populate the Materialized View. It has precedence over populate_condition

populate_condition

String

Optional. Populate with a SQL condition to be applied to the trigger Data Source of the Materialized View. For instance, populate_condition='date == toYYYYMM(now())' it’ll populate taking all the rows from the trigger Data Source which date is the current month. populate_condition is not taken into account if the populate_subset param is present. Including in the populate_condition any column present in the Data Source engine_sorting_key will make the populate job process less data.

truncate

String

Optional. Default is true. It truncates the Data Source before populating, pass false to populate over existing data, useful to populate past data while new data is being ingested.

Response codes

Code

Description

200

No error

400

Node is not materialized

403

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

404

Pipe not found, Node not found

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

Creates a Materialized View

Creating a Materialized View
curl
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -X POST "https://api.tinybird.co/v0/pipes/:pipe/nodes/:node/materialization" \
    -d "datasource=my_data_source_name"  \
    -d "populate=true"
Request parameters

Key

Type

Description

token

String

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

datasource

String

Required. Specifies the name of the destination Data Source where the Materialized View schema is defined. If the Data Source does not exist, it creates automatically with the default settings.

populate

Boolean

Optional. Default false. When true, a job is triggered to populate the destination datasource.

populate_subset

Float

Optional. Populate with a subset percent of the data (limited to a maximum of 2M rows), this is useful to quickly test a materialized node with some data. The subset must be greater than 0 and lower than 0.1. A subset of 0.1 means a 10 percent of the data in the source Data Source will be used to populate the Materialized View. Use it together with populate=true, it has precedence over populate_condition

populate_condition

String

Optional. Populate with a SQL condition to be applied to the trigger Data Source of the Materialized View. For instance, populate_condition='date == toYYYYMM(now())' it’ll populate taking all the rows from the trigger Data Source which date is the current month. Use it together with populate=true. populate_condition is not taken into account if the populate_subset param is present. Including in the populate_condition any column present in the Data Source engine_sorting_key will make the populate job process less data.

engine

String

Optional. Engine for destination Materialized View. If the Data Source already exists, the settings are not overriden.

engine_*

String

Optional. Engine parameters and options. Requires the engine parameter. If the Data Source already exists, the settings are not overriden. Check Engine Parameters and Options for more details

SQL query for the materialized node must be sent in the body encoded in utf-8

Response codes

Code

Description

200

No error

400

Node already being materialized

403

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

404

Pipe not found, Node not found

409

The Materialized View already exists

DELETE /v0/pipes/(.+)/nodes/(.+)/materialization

Removes a Materialized View

By removing a Materialized View, nor the Data Source nor the Node are deleted. The Data Source will still be present, but will stop receiving data from the Node.

Removing a Materialized View
curl
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -X DELETE "https://api.tinybird.co/v0/pipes/:pipe/nodes/:node/materialization"
Response codes

Code

Description

204

No error, Materialized View removed

403

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

404

Pipe not found, Node not found

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

Appends a new node to a Pipe.

adding a new node to a pipe
curl \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -d 'select * from node_0' "https://api.tinybird.co/v0/pipes/:name/nodes?name=node_name&description=explanation"

Appends a new node that creates a Materialized View

adding a Materialized View using a materialized node
curl \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -d 'select id, sum(amount) as amount, date from my_datasource' "https://api.tinybird.co/v0/pipes/:name/nodes?name=node_name&description=explanation&type=materialized&datasource=new_datasource&engine=AggregatingMergeTree"
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

type

String

Optional. Available options are {standard (default), materialized}. Use materialized to create a Materialized View from your new node.

datasource

String

Required with type=materialized. Specifies the name of the destination Data Source where the Materialized View schema is defined.

populate

Boolean

Optional. Default false. When true, a job is triggered to populate the destination Data Source.

populate_subset

Float

Optional. Populate with a subset percent of the data (limited to a maximum of 2M rows), this is useful to quickly test a materialized node with some data. The subset must be greater than 0 and lower than 0.1. A subset of 0.1 means a 10 percent of the data in the source Data Source will be used to populate the Materialized View. Use it together with populate=true, it has precedence over populate_condition

populate_condition

String

Optional. Populate with a SQL condition to be applied to the trigger Data Source of the Materialized View. For instance, populate_condition='date == toYYYYMM(now())' it’ll populate taking all the rows from the trigger Data Source which date is the current month. Use it together with populate=true. populate_condition is not taken into account if the populate_subset param is present. Including in the populate_condition any column present in the Data Source engine_sorting_key will make the populate job process less data.

engine

String

Optional. Engine for destination Materialized View. Requires the type parameter as materialized.

engine_*

String

Optional. Engine parameters and options. Requires the type parameter as materialized and the engine parameter. Check Engine Parameters and Options for more details

SQL query for the transformation node must be sent in the body encoded in utf-8

Response codes

Code

Description

200

No error

400

empty or wrong SQL or API param value

403

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

404

Pipe not found

409

There’s another resource with the same name, names must be unique | The Materialized View lready exists

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

Drops a particular transformation node in the Pipe. It does not remove related nodes so this could leave the Pipe in an unconsistent state. For security reasons, enabled nodes can’t be removed.

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

Code

Description

204

No error, removed node

400

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

403

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

404

Pipe not found

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

Changes a particular transformation node in the Pipe

Editing a Pipe’s transformation node
curl -X PUT \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    -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

Please, note that the desired SQL query should be sent in the body encoded in utf-8.

Response codes

Code

Description

200

No error

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

404

Pipe not found

409

There’s another resource with the same name, names must be unique

GET /v0/pipes/(.+)\.(json|csv|ndjson|parquet)

Returns the published node data in a particular format.

Getting data for a pipe
curl -X GET \
    -H "Authorization: Bearer <PIPE:READ token>" \
    "https://api.tinybird.co/v0/pipes/:name.format"
Request parameters

Key

Type

Description

q

String

Optional, query to execute, see API Query endpoint

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

The q parameter is a SQL query (see Querying your data). When using this endpoint to query your Pipes, you can use the _ shortcut, which refers to your Pipe name

Available formats

format

Description

csv

CSV with header

json

JSON including data, statistics and schema information

ndjson

One JSON object per each row

parquet

A Parquet file

GET /v0/pipes/(.+\.pipe)

Get pipe information. Provided Auth Token must have read access to the Pipe.

Getting information about a particular pipe
curl -X GET \
    -H "Authorization: Bearer <PIPE:READ token>" \
    "https://api.tinybird.co/v0/pipes/:name"

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 changes so 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:

Successful 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
        }]
    }
}

You can make your Pipe’s id more descriptive by prepending information such as t_my_events_table.bd1c62b5e67142bd9bf9a7f113a2b6ea

DELETE /v0/pipes/(.+\.pipe)

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

Dropping a pipe
curl -X DELETE \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    "https://api.tinybird.co/v0/pipes/:name"
PUT /v0/pipes/(.+\.pipe)

Changes Pipe’s metadata. When there is another Pipe with the same name an error is raised.

editing a pipe
curl -X PUT \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    "https://api.tinybird.co/v0/pipes/:name?name=new_name"
Request parameters

Key

Type

Description

name

String

new name for the pipe

description

String

new Markdown description for the pipe

token

String

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

GET /v0/pipes/(.+)

Get pipe information. Provided Auth Token must have read access to the Pipe.

Getting information about a particular pipe
curl -X GET \
    -H "Authorization: Bearer <PIPE:READ token>" \
    "https://api.tinybird.co/v0/pipes/:name"

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 changes so 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:

Successful 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
        }]
    }
}

You can make your Pipe’s id more descriptive by prepending information such as t_my_events_table.bd1c62b5e67142bd9bf9a7f113a2b6ea

DELETE /v0/pipes/(.+)

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

Dropping a pipe
curl -X DELETE \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    "https://api.tinybird.co/v0/pipes/:name"
PUT /v0/pipes/(.+)

Changes Pipe’s metadata. When there is another Pipe with the same name an error is raised.

editing a pipe
curl -X PUT \
    -H "Authorization: Bearer <PIPE:CREATE token>" \
    "https://api.tinybird.co/v0/pipes/:name?name=new_name"
Request parameters

Key

Type

Description

name

String

new name for the pipe

description

String

new Markdown description for the pipe

token

String

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

In order to be able to share this endpoint, you can create a READ token. You can add a new token for the pipe as follows:

adding a READ token to an endpoint pipe
curl -X POST "https://api.tinybird.co/v0/tokens/?name=events_per_day_token&scope=PIPES:READ:events_per_day"