Tinybird Classic

https://www.tinybird.co/docs/classic/
Admin
Tinybird is data infrastructure for software teams that enables ingesting batch and streaming data,...
Tokens:192,752
Snippets:1,145
Trust Score:9.4
Update:1 year ago
Show doc for...
Tokens:
Raw
### MCP Server Quickstart

Source: https://www.tinybird.co/docs/classic/analytics-agents/mcp

Instructions on how to get an authentication token and the base URL for connecting your MCP client or agent framework to the Tinybird remote MCP server.

```APIDOC
## MCP Server Quickstart

Get a token and use this URL in your MCP client or agent framework:

```
https://mcp.tinybird.co?token=TINYBIRD_TOKEN
```

Replace `TINYBIRD_TOKEN` with your actual Auth Token. Use resource-scoped static tokens or JWTs for fine-grained access control.
```

--------------------------------

### Install and Verify Tinybird CLI

Source: https://www.tinybird.co/docs/classic/work-with-data/organize-your-work/continuous-integration

Installs the Tinybird CLI, optionally using a `requirements.txt` file for version pinning. It then verifies the installation by printing the CLI version.

```bash
- name: Install Tinybird CLI
   run: |
    if [ -f "requirements.txt" ]; then
      pip install -r requirements.txt
    else
      pip install tinybird-cli
    fi

- name: Tinybird version
   run: tb --version

```

--------------------------------

### Install ClickHouse Go Client

Source: https://www.tinybird.co/docs/classic/work-with-data/publish-data/guides/connect-clickhouse-go

Installs the ClickHouse Go client and initializes a Go module. This is a prerequisite for connecting to Tinybird.

```bash
go mod init your-project
go get github.com/ClickHouse/clickhouse-go/v2
```

--------------------------------

### API for Semantic Context Example

Source: https://www.tinybird.co/docs/classic/analytics-agents/best-practices

This example demonstrates creating a dedicated pipe to provide semantic context for LLMs, specifically retrieving a list of available devices.

```APIDOC
## API for Semantic Context Example

### Description
This pipe retrieves a distinct list of devices from the `analytics_events` datasource, which can be used to filter other endpoints requiring a `device` parameter.

### Method
N/A (Pipe Definition)

### Endpoint
N/A (Pipe Definition)

### Parameters
N/A

### Request Example
N/A

### Response
#### Success Response (200)
- **device** (String) - A distinct device identifier.

```pipe
DESCRIPTION >
  - Retrieves the list of available devices.
  - Use this list to filter other endpoints that require a `device` parameter.

NODE uniq_devices
SQL >
    SELECT distinct(device) as device
    FROM analytics_events

TYPE endpoint
```
```

--------------------------------

### Include File Example: top_products.incl

Source: https://www.tinybird.co/docs/classic/cli/advanced-templates

An example of an include file demonstrating variable usage for filtering product data by date intervals.

```APIDOC
## Include File Example: top_products.incl

### Description
This include file defines an endpoint template that returns the top 10 products, filtered either by the last week or a specified date range using the `DATE_FILTER` variable.

### Method
N/A (Include file definition)

### Endpoint
N/A

### Parameters
- **DATE_FILTER** (string) - Optional - Specifies the date interval ('last_week' or a custom range).
- **start** (Date) - Required if DATE_FILTER is not 'last_week' - The start date for the custom range.
- **end** (Date) - Required if DATE_FILTER is not 'last_week' - The end date for the custom range.

### Request Example
N/A

### Response
#### Success Response (200)
- **date** (Date) - The date of the aggregation.
- **top_10** (array) - An array containing the top 10 products for that date.

#### Response Example
```json
{
  "example": "{\"date\": \"2023-10-27\", \"top_10\": [ { \"product_id\": \"abc\", \"count\": 150 }, { \"product_id\": \"def\", \"count\": 120 } ] }"
}
```

```sql
NODE endpoint
DESCRIPTION >
    returns top 10 products for the last week
SQL >
    select
        date,
        topKMerge(10)(top_10) as top_10
    from top_product_per_day
    {% if '$DATE_FILTER' = 'last_week' %}
        where date > today() - interval 7 day
    {% else %}
        where date between {{Date(start)}} and {{Date(end)}}
    {% end %}
    group by date
```
```

--------------------------------

### Example Output of `tb init --git`

Source: https://www.tinybird.co/docs/classic/work-with-data/organize-your-work/working-with-version-control

Provides an example output of the `tb init --git` command, showing the initialization process, checks for existing files, diff detection, and resource pulling. It confirms the successful connection and synchronization between the local project and the Tinybird Workspace.

```bash
tb init --git

    ** - /datasources already exists, skipping
    ** - /datasources/fixtures already exists, skipping
    ** - /endpoints already exists, skipping
    ** - /pipes already exists, skipping
    ** - /tests already exists, skipping
    ** - /scripts already exists, skipping
    ** - /deploy already exists, skipping
    ** - '.tinyenv' already exists, skipping

    ** Checking diffs between remote Workspace and local. Hint: use 'tb diff' to check if your data project and Workspace synced
    Pulling datasources  [####################################]  100%
    Pulling pipes  [####################################]  100%
    Pulling tokens  [####################################]  100%
    ** No diffs detected for 'workspace'

```

--------------------------------

### Install Dependencies (npm)

Source: https://www.tinybird.co/docs/classic/get-started/use-cases/leaderboard

This command installs the necessary project dependencies for the Node.js application. It is executed from the 'app' directory of the cloned repository.

```bash
npm install

```

--------------------------------

### Configure Environment Variables (example)

Source: https://www.tinybird.co/docs/classic/get-started/use-cases/leaderboard

This example shows how to populate the '.env.local' file with essential Tinybird configuration. It includes placeholders for your Admin Token, Workspace UUID, and API host URL, which are required for the application to connect to your Tinybird workspace.

```bash
TINYBIRD_SIGNING_TOKEN="YOUR SIGNING TOKEN" # Use your Admin Token as the signing token
TINYBIRD_WORKSPACE="YOUR WORKSPACE ID" # The UUID of your Workspace
NEXT_PUBLIC_TINYBIRD_HOST="YOUR TINYBIRD API HOST e.g. https://api.tinybird.co" # Your regional API host

```

--------------------------------

### Data Project Structure Example

Source: https://www.tinybird.co/docs/classic/cli/data-projects

An example file structure for an ecommerce data project in Tinybird.

```APIDOC
## Data Project Structure Example

This is an example of a data project file structure for an ecommerce site.

### File Structure
```
ecommerce_data_project/
    datasources/
        events.datasource
        products.datasource
        fixtures/
            events.csv
            products.csv
    pipes/
        top_product_per_day.pipe

    endpoints/
        sales.pipe
        top_products.pipe

```
```

--------------------------------

### Install Faker Library (Shell)

Source: https://www.tinybird.co/docs/classic/publish-data/charts/guides/real-time-dashboard

These commands navigate to the `datagen` directory, initialize a new Node.js project using `npm init --yes`, and install the `@faker-js/faker` package. This library is essential for generating mock data.

```shell
cd datagen
npm init --yes
npm install @faker-js/faker
```

--------------------------------

### GET /v0/top_products.json with Parameters

Source: https://www.tinybird.co/docs/classic/cli/data-projects

Retrieves the top 10 products within a specified date range. This endpoint requires start and end date parameters.

```APIDOC
## GET /v0/top_products.json with Parameters

### Description
Retrieves the top 10 products for a specified date range. This endpoint accepts `start` and `end` query parameters.

### Method
GET

### Endpoint
`/v0/top_products.json`

### Query Parameters
- **token** (string) - Required - Authentication token for accessing the endpoint.
- **start** (date) - Required - The start date for filtering products (YYYY-MM-DD).
- **end** (date) - Required - The end date for filtering products (YYYY-MM-DD).

### Response
#### Success Response (200)
- **date** (string) - The date for which the top products are listed.
- **top_10** (array) - An array of the top 10 products.

#### Response Example
```json
{
  "date": "2023-10-20",
  "top_10": [
    {"product_id": "prod_xyz", "sales": 120},
    {"product_id": "prod_uvw", "sales": 110}
  ]
}
```
```

--------------------------------

### Execute Basic Queries with Agno and Tinybird MCP

Source: https://www.tinybird.co/docs/classic/analytics-agents/mcp-server-snippets

This example demonstrates basic query execution using the Agno library with Tinybird's MCP Server. It utilizes `agno`, `asyncio`, and `dotenv`. The code defines a system prompt and asynchronously executes a query against Tinybird, streaming the response.

```python
from agno.agent import Agent
from agno.models.anthropic import Claude
from agno.tools.mcp import MCPTools

import asyncio
import os

tinybird_api_key = os.getenv("TINYBIRD_TOKEN")
SYSTEM_PROMPT = "YOUR SYSTEM PROMPT"

async def main():
    async with MCPTools(
        transport="streamable-http",
        url=f"https://mcp.tinybird.co?token={tinybird_api_key}",
        timeout_seconds=120) as mcp_tools:
        agent = Agent(
            model=Claude(id="claude-4-opus-20250514"),
            tools=[mcp_tools],  # use your favorite model
            instructions=SYSTEM_PROMPT
        )
        await agent.aprint_response("top 5 pages with the most visits in the last 24 hours", stream=True)

if __name__ == "__main__":
    asyncio.run(main())

```

--------------------------------

### Display Tinybird CLI Help

Source: https://www.tinybird.co/docs/classic/cli/install

Displays the general help information for the Tinybird CLI, listing available commands and global options. This is the entry point for understanding CLI usage.

```shell
tb --help
```

--------------------------------

### Install and authenticate Tinybird CLI

Source: https://www.tinybird.co/docs/classic/publish-data/charts/guides/real-time-dashboard

This sequence of commands sets up a Python virtual environment, installs the Tinybird CLI using pip, and then authenticates the CLI with a user admin token. This enables interaction with the Tinybird Workspace from the command line.

```bash
python -m venv .venv
source .venv/bin/activate
pip install tinybird-cli
tb auth -i

```

--------------------------------

### Tinybird CLI: Formatting Example with Comments (tb fmt)

Source: https://www.tinybird.co/docs/classic/cli/command-ref

Demonstrates how comments are handled during the tb fmt command. Standard comments starting with '#' are removed, while specific comment blocks starting with '% {% comment %}' are preserved.

```sql
%
{% comment this is a comment and fmt keeps it %}

SELECT
  {% comment this is another comment and fmt keeps it %}
  count() c
FROM stock_prices_1m

```

--------------------------------

### Install Tinybird CLI with Python

Source: https://www.tinybird.co/docs/classic/get-started/use-cases/user-facing-web-analytics

Installs the Tinybird Command Line Interface using a Python virtual environment. This is the first step to interact with Tinybird from your local machine.

```shell
mkdir tinybird
cd tinybird
python -m venv .venv
source .venv/bin/activate
pip install tinybird-cli

```

--------------------------------

### Install Mockingbird CLI

Source: https://www.tinybird.co/docs/classic/get-started/use-cases/user-facing-web-analytics

Installs the Mockingbird CLI globally using npm. Mockingbird is a tool used to generate mock data streams for testing and development purposes.

```shell
npm install -g @tinybirdco/mockingbird-cli

```

--------------------------------

### Tinybird Kafka Connector Configuration Example

Source: https://www.tinybird.co/docs/classic/cli/datafiles/datasource-files

An example configuration for a Tinybird Data Source using a Kafka connector. It specifies essential connection parameters like bootstrap servers, authentication credentials, topic, and group ID, along with optional settings for offset reset and header storage.

```datasource
datasource my_kafka_datasource (
    // Kafka connection settings
    KAFKA_CONNECTION_NAME = "my_kafka_connection",
    KAFKA_BOOTSTRAP_SERVERS = "your_kafka_broker:9092",
    KAFKA_KEY = "your_kafka_key",
    KAFKA_SECRET = "your_kafka_secret",
    KAFKA_TOPIC = "your_topic",
    KAFKA_GROUP_ID = "your_group_id",

    // Optional settings
    KAFKA_AUTO_OFFSET_RESET = "earliest",
    KAFKA_STORE_HEADERS = "true",
    KAFKA_STORE_RAW_VALUE = "true",
    KAFKA_KEY_FORMAT = "json_without_schema",
    KAFKA_VALUE_FORMAT = "json_without_schema"
)
ENGINE = MergeTree
ORDER BY (id)
```

--------------------------------

### Create Tinybird Workspace using CLI

Source: https://www.tinybird.co/docs/classic/cli/quick-start

This command creates a new Tinybird Workspace with a specified name. A Workspace is a container for all your Tinybird resources.

```bash
tb workspace create customer_rewards
```

--------------------------------

### Query data using SQL in Tinybird Pipe

Source: https://www.tinybird.co/docs/classic/get-started/quick-start

This snippet demonstrates how to write a basic SQL query within a Tinybird Pipe to count records in a data source named 'orders'. Pipes are used to query and transform data in Tinybird. The result can then be run to preview.

```sql
select count() from orders
```

--------------------------------

### Create Python Virtual Environment and Activate

Source: https://www.tinybird.co/docs/classic/cli/install

Creates a new virtual environment for Python 3 and activates it. This isolates project dependencies and is a prerequisite for installing the tinybird-cli package.

```shell
python3 -m venv .venv
source .venv/bin/activate
```

--------------------------------

### Tinybird Query API - topPages Example

Source: https://www.tinybird.co/docs/classic/publish-data/endpoints

Example GraphQL query to fetch top pages data from Tinybird.

```APIDOC
## GraphQL Query for Tinybird

### Description
An example GraphQL query to fetch `topPages` data from the Tinybird API.

### Method
POST

### Endpoint
/graphql (or your specific GraphQL endpoint)

### Parameters
#### Request Body
- **query** (string) - Required - The GraphQL query string.

### Request Example
```graphql
query Tinybird {
    tinybird {
        topPages {
            data {
                action
                payload
            }
            rows
        }
    }
}
```

### Response
#### Success Response (200)
- **data** (object) - Contains the result of the GraphQL query.
  - **tinybird** (object) - The response from the tinybird schema.
    - **topPages** (object) - The result of the topPages query.
      - **data** (array) - An array of page data objects.
        - **action** (string) - The action associated with the page.
        - **payload** (string) - The payload of the action.
      - **rows** (integer) - The total number of rows returned.
```

--------------------------------

### Tinybird Datafile Basic Syntax Example

Source: https://www.tinybird.co/docs/classic/cli/datafiles

Demonstrates the basic command-value structure used in Tinybird datafiles. Instructions are typically at the beginning of a line in upper case.

```plaintext
COMMAND value
ANOTHER_INSTR "Value with multiple words"
```

--------------------------------

### Display Tinybird Datasource Command Help

Source: https://www.tinybird.co/docs/classic/cli/install

Displays the help information specific to the `datasource` command within the Tinybird CLI. This provides details on its subcommands, options, and usage.

```shell
tb datasource --help
```

--------------------------------

### Kafka Connector Configuration Example

Source: https://www.tinybird.co/docs/classic/dev-reference/datafiles/datasource-files

Provides an example of how to configure a Kafka, Confluent, or RedPanda connector within a Tinybird .datasource file. This includes essential connection parameters and options for data handling and authentication.

```datasource
# Example .datasource file for Kafka connection

FROM kafka
(
    KAFKA_CONNECTION_NAME='my_kafka_connection',
    KAFKA_BOOTSTRAP_SERVERS='host1:9092,host2:9092',
    KAFKA_KEY='my_key',
    KAFKA_SECRET='my_secret',
    KAFKA_TOPIC='my_topic',
    KAFKA_GROUP_ID='my_group_id',
    KAFKA_AUTO_OFFSET_RESET='earliest',
    KAFKA_STORE_HEADERS='True',
    KAFKA_STORE_BINARY_HEADERS='True',
    KAFKA_STORE_RAW_VALUE='True',
    KAFKA_SCHEMA_REGISTRY_URL='http://schema-registry:8081',
    KAFKA_KEY_FORMAT='avro',
    KAFKA_VALUE_FORMAT='json_with_schema'
)
```

--------------------------------

### Include File Example: only_buy_events.incl

Source: https://www.tinybird.co/docs/classic/cli/advanced-templates

An example of an include file used to define a node for filtering 'buy' events and extracting relevant data.

```APIDOC
## Include File Example: only_buy_events.incl

### Description
This include file defines a node named `only_buy_events` that selects buy events from the `events` table, joins product details, and extracts price information.

### Method
N/A (Include file definition)

### Endpoint
N/A

### Parameters
N/A

### Request Example
N/A

### Response
N/A

```sql
NODE only_buy_events
SQL >
    SELECT
        toDate(timestamp) date,
        product,
        joinGet('products_join_by_id', 'color', product) as color,
        JSONExtractFloat(json, 'price') as price
    FROM events
    where action = 'buy'
```
```

--------------------------------

### Define Data Source

Source: https://www.tinybird.co/docs/classic/cli/data-projects

Defines how data is ingested and stored. Shows an example of an `event.datasource` file and how to push it.

```APIDOC
## Define Data Sources

Data Sources define data ingestion and storage. You can add data using the Data Sources API.

### Example `event.datasource` file
```
DESCRIPTION >
    # Events from users
    This contains all the events produced by Kafka, there are 4 fixed columns.
    plus a `json` column which contains the rest of the data for that event.
    See [documentation](url_for_docs) for the different events.

SCHEMA >
    timestamp DateTime,
    product String,
    user_id String,
    action String
    json String

ENGINE MergeTree
ENGINE_SORTING_KEY timestamp

```

### Push the Events Data Source
```bash
tb push datasources/events.datasource
```

**Note:** Data Sources cannot be overridden. To modify, remove the existing one or upload with a new name.
```

--------------------------------

### Install Tinybird CLI and Check Authentication in GitHub Actions

Source: https://www.tinybird.co/docs/classic/work-with-data/organize-your-work/continuous-integration

These GitHub Actions steps install the Tinybird CLI, either from a requirements file or directly, and then verify the authentication by checking the CLI version and the authentication details against the provided host and token secrets. This ensures the workflow can interact with the Tinybird workspace.

```bash
- name: Install Tinybird CLI
   run: |
    if [ -f "requirements.txt" ]; then
      pip install -r requirements.txt
    else
      pip install tinybird-cli
    fi

- name: Tinybird version
  run: tb --version

- name: Check auth
  run: tb --host ${{ secrets.tb_host }} --token ${{ secrets.tb_admin_token }} auth info

```

--------------------------------

### Run Development Server (npm)

Source: https://www.tinybird.co/docs/classic/get-started/use-cases/leaderboard

This command starts the development server for the Node.js application. It allows you to run the app locally and access it via 'http://localhost:3000'.

```bash
npm run dev

```

--------------------------------

### Example Tinybird Copy Pipe SQL Query and Configuration (CLI)

Source: https://www.tinybird.co/docs/classic/work-with-data/process-and-copy/copy-pipes

An example of a Tinybird Copy Pipe configured via the CLI. It includes a SQL query to select and aggregate daily sales data, specifying the target data source and a cron schedule for hourly execution. Dynamic parameters like {{DateTime(job_timestamp)}} and {{String(country, 'US')}} are used.

```sql
NODE daily_sales
SQL >
    %
    SELECT toStartOfDay(starting_date) day, country, sum(sales) as total_sales
    FROM teams
    WHERE
    day BETWEEN toStartOfDay({{DateTime(job_timestamp)}}) - interval 1 day AND toStartOfDay({{DateTime(job_timestamp)}})
    and country = {{ String(country, 'US')}}
    GROUP BY day, country

TYPE COPY
TARGET_DATASOURCE sales_hour_copy
COPY_SCHEDULE 0 * * * *
```

--------------------------------

### Clone Tinybird Demo Project (Shell)

Source: https://www.tinybird.co/docs/classic/cli/advanced-templates

Clones the ecommerce_data_project_advanced repository from GitHub and navigates into the directory. This is a prerequisite for following the advanced template examples.

```shell
git clone https://github.com/tinybirdco/ecommerce_data_project_advanced.git
cd ecommerce_data_project_advanced
```

--------------------------------

### Clone Demo Repository

Source: https://www.tinybird.co/docs/classic/cli/advanced-templates

Clone the ecommerce_data_project_advanced repository to follow along with the advanced template examples.

```APIDOC
## Clone Demo Repository

### Description
Clone the `ecommerce_data_project_advanced` repository to access example files for advanced template usage.

### Method
```bash
git clone https://github.com/tinybirdco/ecommerce_data_project_advanced.git
cd ecommerce_data_project_advanced
```

### Endpoint
N/A

### Parameters
N/A

### Request Example
N/A

### Response
N/A
```

--------------------------------

### Call Tinybird API endpoint using curl

Source: https://www.tinybird.co/docs/classic/get-started/quick-start

This example shows how to invoke a published Tinybird API endpoint using a curl command in a terminal. This is used to test the API endpoint created from a Tinybird Pipe and retrieve the data.

```bash
curl "https://api.tinybird.co/v0/pipes/YOUR_PIPE_NAME/YOUR_NODE_NAME?token=YOUR_TOKEN"

```

--------------------------------

### Tinybird Datafile Multiline Syntax Example

Source: https://www.tinybird.co/docs/classic/cli/datafiles

Illustrates how instructions in Tinybird datafiles can span multiple lines, commonly used for defining schemas.

```plaintext
SCHEMA >
    `d` DateTime,
    `total` Int32,
    `from_novoa` Int16
```

--------------------------------

### NGINX Configuration Example

Source: https://www.tinybird.co/docs/classic/publish-data/endpoints

Example NGINX configuration to proxy GET requests to a Tinybird API Endpoint.

```APIDOC
## GET /top-10-products

### Description
Proxies a GET request to the Tinybird API Endpoint for top 10 products.

### Method
GET

### Endpoint
/top-10-products

### Parameters
#### Query Parameters
- **token** (string) - Required - The Tinybird API token with READ access.

### Request Example
(No request body for GET)

### Response
#### Success Response (200)
- **data** (array) - Contains an array of product data.
  - **action** (string) - The action associated with the product.
  - **payload** (string) - The payload of the action.
- **rows** (integer) - The number of rows returned.

#### Response Example
```json
{
  "data": [
    {
      "action": "view",
      "payload": "product-123"
    },
    {
      "action": "purchase",
      "payload": "product-456"
    }
  ],
  "rows": 10
}
```
```

--------------------------------

### Typical Tinybird Project File Structure

Source: https://www.tinybird.co/docs/classic/cli/datafiles

An example of a standard directory layout for a Tinybird project, showing the organization of different datafile types like datasources, endpoints, includes, and pipes.

```treeview
tinybird
├── datasources/
│   └── connections/
│       └── my_connector_name.incl
│   └── my_datasource.datasource
├── endpoints/
├── includes/
├── pipes/
```

--------------------------------

### Curl Example for Tinybird Local Connection

Source: https://www.tinybird.co/docs/classic/work-with-data/publish-data/clickhouse-interface

Demonstrates how to test the ClickHouse interface connection to a local Tinybird development environment using curl.

```APIDOC
## Test Connection with curl

```bash
curl -H "X-ClickHouse-Key: your_token_here" \
     "http://localhost:7182/?query=SELECT%20*%20FROM%20system.tables"
```
```

--------------------------------

### Define Data Pipes

Source: https://www.tinybird.co/docs/classic/cli/data-projects

Explains how to create data pipes using nodes and SQL to transform data as it's inserted. Includes an example pipe and how to push it.

```APIDOC
## Define Data Pipes

Data Pipes transform data during insertion. They can consist of multiple nodes.

### Example `top_product_per_day.pipe` file
```
NODE only_buy_events
DESCRIPTION >
    filters all the buy events

SQL >
    SELECT
        toDate(timestamp) date,
        product,
        JSONExtractFloat(json, 'price') AS price
    FROM events
    WHERE action = 'buy'


NODE top_per_day
SQL >
   SELECT date,
          topKState(10)(product) top_10,
          sumState(price) total_sales
    FROM only_buy_events
    GROUP BY date

TYPE materialized
DATASOURCE top_per_day_mv
ENGINE AggregatingMergeTree
ENGINE_SORTING_KEY date

```

### Push and Populate Pipe
```bash
tb push pipes/top_product_per_day.pipe --populate
```

The `--populate` flag processes existing data. A job URL will be provided to monitor the process.
```

--------------------------------

### Create and Push Tinybird Pipe using CLI

Source: https://www.tinybird.co/docs/classic/cli/quick-start

This sequence of commands generates a Tinybird Pipe with a SQL query and then pushes it to the Tinybird service. Pushing a Pipe automatically publishes it as a REST API Endpoint.

```bash
tb pipe generate rewards 'select count() from orders'
tb push rewards.pipe
```

--------------------------------

### NGINX Configuration for Tinybird API Proxy

Source: https://www.tinybird.co/docs/classic/publish-data/endpoints

An NGINX configuration example that sets up a server to proxy `GET` requests to a Tinybird API endpoint. This configuration ensures the API token remains server-side and is not exposed to clients.

```nginx
worker_processes  1;

events {
    worker_connections  1024;
}

http {
    server {
        listen       8080;
        server_name  localhost;
        location /top-10-products {
                proxy_pass https://api.tinybird.co/v0/pipes/top-10-products.json?token=p.eyJ1Ijog...;
        }
    }
}
```

--------------------------------

### Initialize Tinybird Project and Authenticate

Source: https://www.tinybird.co/docs/classic/get-data-in/migrate/migrate-from-postgres

Sets the Tinybird administrator token and host environment variables, then authenticates the Tinybird CLI and initializes a new project. This prepares the local environment for interacting with Tinybird.

```bash
export TB_ADMIN_TOKEN=<YOUR_WORKSPACE_ADMIN_TOKEN>
export TB_HOST=https://api.us-east.aws.tinybird.co #replace with your host

tb auth --host $TB_HOST --token $TB_ADMIN_TOKEN
tb init
```

--------------------------------

### Example GraphQL Query for Tinybird API

Source: https://www.tinybird.co/docs/classic/publish-data/endpoints

A sample GraphQL query to fetch data from Tinybird, specifically the 'topPages' endpoint. This demonstrates how to structure requests to the API.

```graphql
query Tinybird {
    tinybird {
        topPages {
            data {
                action
                payload
            }
            rows
        }
    }
}
```

--------------------------------

### Get Minimum Timestamp from Postgres Table

Source: https://www.tinybird.co/docs/classic/get-data-in/migrate/migrate-from-postgres

This SQL query is executed against a PostgreSQL database to retrieve the minimum value from the 'timestamp' column in the 'events' table. This is typically used to determine the starting point for a data backfill.

```sql
SELECT min(timestamp) FROM events;
```

--------------------------------

### Initialize Tinybird Project with Git CLI

Source: https://www.tinybird.co/docs/classic/work-with-data/organize-your-work/working-with-version-control

Initializes a new Tinybird project and connects it to a Git repository using the Tinybird CLI. This command sets up the project structure and links it to the specified Git repository, preparing it for version control and deployment workflows.

```bash
tb init --git
```

--------------------------------

### Run Tinybird Classic Local Container

Source: https://www.tinybird.co/docs/classic/cli/local-container

This command starts the Tinybird Classic local container. It exposes port 7181 and sets the compatibility mode for Classic features. Ensure you have a container runtime like Docker installed.

```bash
docker run --platform linux/amd64 -p 7181:7181 --name tinybird-classic-local -e COMPATIBILITY_MODE=1 -d tinybirdco/tinybird-local:latest

```

--------------------------------

### Authenticate Tinybird CLI (Interactive)

Source: https://www.tinybird.co/docs/classic/cli/install

Initiates an interactive authentication process for the Tinybird CLI. It prompts the user to select a region and provide an admin token.

```shell
tb auth -i
```

--------------------------------

### List Tinybird Tokens via CLI

Source: https://www.tinybird.co/docs/classic/administration/auth-tokens

This command lists all available tokens within your Tinybird workspace using the command-line interface. Ensure the Tinybird CLI is installed and authenticated.

```bash
tb token ls
```

--------------------------------

### Create Static Token CLI Example

Source: https://www.tinybird.co/docs/classic/administration/auth-tokens

This command-line interface (CLI) command is used to create a static token in Tinybird. Use `--help` for detailed options and configurations.

```bash
tb token create static --help
```

--------------------------------

### Generate Time Series (PostgreSQL's generate_series)

Source: https://www.tinybird.co/docs/classic/work-with-data/query/guides/adapt-postgres-queries

This PostgreSQL example shows how to generate a time series of datetime values using the generate_series function. It defines a start timestamp, an end timestamp, and an interval to produce rows representing each hour within the specified period.

```sql
WITH daterange AS (
    SELECT *
    FROM generate_series(
        '2021-01-01 UTC'::timestamptz, -- start
        '2021-01-02 UTC'::timestamptz, -- stop
        interval '1 hour'   -- step
    ) AS t(hh)
)
SELECT * FROM daterange;

```

--------------------------------

### Install tinybird-cli using pip

Source: https://www.tinybird.co/docs/classic/cli/install

Installs the tinybird-cli package using pip within an activated virtual environment. This command fetches and installs the latest stable version of the CLI.

```shell
pip install tinybird-cli
```

--------------------------------

### Install ClickHouse Python Client

Source: https://www.tinybird.co/docs/classic/work-with-data/publish-data/guides/connect-clickhouse-python

Installs the official ClickHouse Python client using pip. Ensure you have Python 3.8 or later installed.

```bash
pip install clickhouse-connect

```

--------------------------------

### Define a Parameterized API Endpoint - Tinybird Pipe

Source: https://www.tinybird.co/docs/classic/cli/data-projects

This snippet shows how to define a Tinybird API endpoint that accepts 'start' and 'end' date parameters. The `%` character at the beginning of the SQL definition enables parameter templating. The endpoint can then be queried with specific date ranges, for example: `{% user("apiHost") %}/v0/top_products.json?start=2018-09-07&end=2018-09-17&token=TOKEN`.

```tinybird-pipe
NODE endpoint
DESCRIPTION >
    returns top 10 products for the last week
SQL >
    %
    SELECT
        date,
        topKMerge(10)(top_10) AS top_10
    FROM top_per_day
    WHERE date between {{Date(start)}} AND {{Date(end)}}
    GRUP BY date


```

--------------------------------

### SQL Example for Hourly Data Snapshot

Source: https://www.tinybird.co/docs/classic/work-with-data/process-and-copy/copy-pipes

A standard SQL query to select data from a datasource within the last hour, before applying the `job_timestamp` rounding optimization for Copy Pipes.

```sql
SELECT *
FROM datasource
WHERE datetime >= now() - interval 1 hour
    AND datetime < now()

```

--------------------------------

### Tinybird Project Structure Example

Source: https://www.tinybird.co/docs/classic/work-with-data/organize-your-work/working-with-version-control

Illustrates the default directory structure created by the `tb init` command for a Tinybird project. This structure organizes data sources, endpoints, pipes, tests, scripts, and deployment configurations for effective project management.

```bash
- /datasources
- /datasources/fixtures
- /endpoints
- /pipes
- /tests
- /scripts
- /scripts/exec_test.sh
- /scripts/append_fixtures.sh
- /deploy

```

--------------------------------

### Install ClickHouse JS Client with npm

Source: https://www.tinybird.co/docs/classic/work-with-data/publish-data/guides/connect-clickhouse-js

Installs the official ClickHouse JavaScript client using npm. Ensure Node.js 16.0 or later is installed.

```bash
npm install @clickhouse/client

```

--------------------------------

### Example .datasource File Configuration

Source: https://www.tinybird.co/docs/classic/cli/datafiles/datasource-files

This snippet shows a complete .datasource file configuration, including comments, token access, data source description, tags, schema definition, engine settings, indexes, and workspace sharing. It demonstrates how to define a data source for analytics events.

```datasource
# A comment
TOKEN tracker APPEND

DESCRIPTION >
    Analytics events **landing data source**

TAGS stock, recommendations

SCHEMA >
    `timestamp` DateTime `json:$.timestamp`,
    `session_id` String `json:$.session_id`,
    `action` LowCardinality(String) `json:$.action`,
    `version` LowCardinality(String) `json:$.version`,
    `payload` String `json:$.payload`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp"
ENGINE_TTL "timestamp + toIntervalDay(60)"
ENGINE_SETTINGS "index_granularity=8192"

INDEXES >
    INDEX idx1 action TYPE bloom_filter GRANULARITY 3

SHARED_WITH >
    analytics_production
    analytics_staging

```

--------------------------------

### Pipe Documentation and Parameter Best Practices

Source: https://www.tinybird.co/docs/classic/analytics-agents/best-practices

This section explains how to document Tinybird pipes with LLM-friendly descriptions and utilize metadata fields like `description`, `required`, and `example` for pipe parameters.

```APIDOC
## Pipe Documentation Example

### Description
This example shows how to document a pipe that retrieves the most visited pages for a given period, including optional date filters and pagination parameters.

### Method
N/A (Pipe Definition)

### Endpoint
N/A (Pipe Definition)

### Parameters
#### Query Parameters
- **date_from** (Date) - Optional - Starting day for filtering a date range. Defaults to the last 7 days.
- **date_to** (Date) - Optional - Finishing day for filtering a date range. Defaults to today.
- **skip** (Int32) - Optional - Pagination parameter for skipping records. Defaults to 0.
- **limit** (Int32) - Optional - Pagination parameter for limiting records. Defaults to 50.

### Request Example
N/A

### Response
#### Success Response (200)
- **pathname** (String) - The URL path of the page.
- **visits** (Int64) - The unique number of visits to the page.
- **hits** (Int64) - The total number of hits to the page.

```pipe
DESCRIPTION >
    - Use this tool when you need to get most visited pages for a given period.
    - Parameters:
        - `date_from` and `date_to`: Optional date filters, defaulting to the last 7 days.
        - `skip` and `limit`: Pagination parameters.
    - Response: `pathname`, unique `visits`, and total `hits` for the given period.

TOKEN "dashboard" READ

NODE endpoint
SQL >
    %
    select pathname, uniqMerge(visits) as visits, countMerge(hits) as hits
    from analytics_pages_mv
    where
        {% if defined(date_from) %}
            date
            >=
            {{ Date(date_from, description="Starting day for filtering a date range", required=False, example="2025-05-01") }}
        {% else %} date >= timestampAdd(today(), interval -7 day)
        {% end %}
        {% if defined(date_to) %}
            and date
            <=
            {{ Date(date_to, description="Finishing day for filtering a date range", required=False, example="2025-05-01") }}
        {% else %} and date <= today()
        {% end %}
    group by pathname
    order by visits desc
    limit {{ Int32(skip, 0) }},{{ Int32(limit, 50) }}

TYPE endpoint
```
```

--------------------------------

### Install Tinybird Charts Library

Source: https://www.tinybird.co/docs/classic/publish-data/charts/guides/add-charts-to-nextjs

Installs the Tinybird Charts npm package. This library provides React components for displaying Tinybird data visualizations.

```bash
npm install @tinybirdco/charts
```

--------------------------------

### Generate Datasource Schema and Ingest Data with Tinybird CLI

Source: https://www.tinybird.co/docs/classic/cli/quick-start

These commands are used to process local data files. 'generate' infers the schema from a data file, 'push' uploads the generated datasource file to Tinybird, and 'append' ingests the data from the file into the datasource.

```bash
tb datasource generate orders.ndjson        # Infer the schema
tb push orders.datasource                   # Upload the datasource file
tb datasource append orders orders.ndjson   # Ingest the data
```

--------------------------------

### MCP Monitoring Example

Source: https://www.tinybird.co/docs/classic/analytics-agents/mcp

An example SQL query to monitor SQL queries executed by AI agents through the MCP server by checking the `tinybird.pipe_stats_rt` data source.

```APIDOC
### MCP Monitoring

Monitor SQL queries executed by AI agents for unexpected patterns, using Tinybird service data sources.

```sql
SELECT *
FROM tinybird.pipe_stats_rt
WHERE url LIKE '%from=mcp%'
AND start_datetime > now() - INTERVAL 1 HOUR
```
```

--------------------------------

### Set Environment Variables for Tinybird

Source: https://www.tinybird.co/docs/classic/get-started/use-cases/vector-search-recommendation

Sets the Tinybird host and token with DATASOURCES:WRITE scope for data ingestion.

```shell
export TB_HOST=your_tinybird_host
export TB_TOKEN=your_tinybird_token
```

--------------------------------

### Create folder structure with mkdir

Source: https://www.tinybird.co/docs/classic/publish-data/charts/guides/real-time-dashboard

This command creates the necessary directory structure for the project. It initializes the main project folder and subfolders for data generation, application code, and Tinybird configurations.

```bash
mkdir tinybird-signatures-dashboard && cd tinybird-signatures-dashboard
mkdir datagen datagen/utils app tinybird

```

--------------------------------

### Install Tinybird AI Python SDK

Source: https://www.tinybird.co/docs/classic/get-data-in/guides/ingest-litellm

Instructions for installing the Tinybird Python SDK with AI support using pip. This package is required to send LiteLLM events to Tinybird.

```shell
pip install tinybird-python-sdk[ai]
```

--------------------------------

### Verify Data in Tinybird Data Source

Source: https://www.tinybird.co/docs/classic/get-started/use-cases/vector-search-recommendation

Command to query and verify data within the 'posts' Data Source using the Tinybird CLI.

```shell
tb sql 'SELECT * FROM posts'
```

--------------------------------

### JSONPath Examples

Source: https://www.tinybird.co/docs/classic/cli/datafiles/datasource-files

Examples demonstrating Tinybird's JSONPath syntax for extracting data from JSON, including handling nested fields and arrays, and a workaround for complex nested arrays.

```APIDOC
## JSONPath Syntax and Limitations

### Description
Tinybird supports JSONPath for extracting data. However, it has limitations with nested arrays, supporting them only at the first level. For complex nested structures, it's recommended to store the entire JSON as a String and parse it using JSONExtract functions.

### Example 1: Basic JSONPath

```json
SCHEMA >
    field String `json:$.field`,
    nested_nested_field String `json:$.nested.nested_field`,
    an_array Array(Int16) `json:$.an_array[:]`,
    a_nested_array_nested_array Array(Int16) `json:$.a_nested_array.nested_array[:]`,
    whole_message String `json:$`
```

### Example 2: Handling Attributes with Dots

Use brackets `['attribute.name']` for attributes with dots that are not actual nested attributes.

```json
{
    "attributes": {
        "otel.attributes": {
            "cli_command": "datasource ls"
        }
    }
}
```

### Example 3: Extracting Nested Attributes

```json
SCHEMA >
    cli_command String `json:$.attributes.['otel.attributes'].cli_command`
```

### Limitation
Tinybird's JSONPath syntax supports nested objects at multiple levels, but **supports nested arrays only at the first level**.

### Workaround for Complex Nested Arrays

To ingest and transform more complex JSON objects, store the whole JSON as a String (`<column_name> String `json:$``) and use JSONExtract functions to parse at query time or in materializations.
```

--------------------------------

### Datasource File Instructions

Source: https://www.tinybird.co/docs/classic/dev-reference/datafiles/datasource-files

Overview of instructions available for .datasource files to define Data Sources.

```APIDOC
## Datasource Files (.datasource)

Datasource files describe your Data Sources. You can use `.datasource` files to define the schema, engine, and other settings of your Data Sources.

### Available Instructions

The following instructions are available for `.datasource` files:

| Declaration             | Required | Description                                                                                                     |
| ---------------------- | -------- | --------------------------------------------------------------------------------------------------------------- |
| `SCHEMA <indented_schema_definition>` | Yes      | Defines a block for a Data Source schema. The block must be indented.                                             |
| `DESCRIPTION <markdown_string>`     | No       | Description of the Data Source.                                                                                 |
| `TOKEN <token_name> APPEND`       | No       | Grants append access to a Data Source to the token named `<token_name>`.                                        |
| `TAGS <tag_names>`                | No       | Comma-separated list of tags. Tags are used to organize your data project.                                      |
| `ENGINE <engine_type>`            | No       | Sets the engine for Data Source. Default value is `MergeTree`.                                                  |
| `ENGINE_SORTING_KEY <sql>`        | No       | Sets the `ORDER BY` expression for the Data Source.                                                             |
| `ENGINE_PARTITION_KEY <sql>`      | No       | Sets the `PARTITION` expression for the Data Source.                                                            |
| `ENGINE_TTL <sql>`                | No       | Sets the `TTL` expression for the Data Source.                                                                  |
| `ENGINE_VER <column_name>`        | No       | Column with the version of the object state. Required when using `ENGINE ReplacingMergeTree`.                   |
| `ENGINE_SIGN <column_name>`       | No       | Column to compute the state. Required when using `ENGINE CollapsingMergeTree` or `ENGINE VersionedCollapsingMergeTree`. |
| `ENGINE_VERSION <column_name>`    | No       | Column with the version of the object state. Required when `ENGINE VersionedCollapsingMergeTree`.               |
| `ENGINE_SETTINGS <settings>`      | No       | Comma-separated list of key-value pairs that describe engine settings for the Data Source.                      |
| `INDEXES <index definitions>`     | No       | Defines one or more indexes for the Data Source.                                                                |
| `SHARED_WITH <workspace_name>`  | No       | Shares the Data Source with one or more Workspaces.                                                             |

### Example Datasource File

##### tinybird/datasources/example.datasource
```
# A comment
TOKEN tracker APPEND

DESCRIPTION >
    Analytics events **landing data source**

TAGS stock, recommendations

SCHEMA >
    `timestamp` DateTime `json:$.timestamp`,
    `session_id` String `json:$.session_id`,
    `action` LowCardinality(String) `json:$.action`,
    `version` LowCardinality(String) `json:$.version`,
    `payload` String `json:$.payload`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp"
ENGINE_TTL "timestamp + toIntervalDay(60)"
ENGINE_SETTINGS "index_granularity=8192"

INDEXES >
    INDEX idx1 action TYPE bloom_filter GRANULARITY 3

SHARED_WITH >
    analytics_production
    analytics_staging
```
```

--------------------------------

### Datasource Documentation Best Practices

Source: https://www.tinybird.co/docs/classic/analytics-agents/best-practices

This section details how to add LLM-friendly descriptions to your Tinybird datasources using the `DESCRIPTION` field for better context understanding by language models.

```APIDOC
## Datasource Documentation Example

### Description
This example demonstrates how to document a datasource with LLM-friendly descriptions, including its purpose, schema, and engine details.

### Method
N/A (Configuration Example)

### Endpoint
N/A (Datasource Definition)

### Parameters
N/A

### Request Example
N/A

### Response
N/A

```datasource
DESCRIPTION >
    - `analytics_events` contains web analytics events, such as `page_hit` actions or custom events.
    - The `action` column specifies the event type for each `session_id` and `timestamp`.
    - The `payload` is a JSON string with metadata about the action, such as the `user_agent`.

TOKEN "tracker" APPEND

SCHEMA >
    `timestamp` DateTime `json:$.timestamp`,
    `session_id` Nullable(String) `json:$.session_id`,
    `action` LowCardinality(String) `json:$.action`,
    `version` LowCardinality(String) `json:$.version`,
    `payload` String `json:$.payload`

ENGINE MergeTree
ENGINE_PARTITION_KEY toYYYYMM(timestamp)
ENGINE_SORTING_KEY timestamp
ENGINE_TTL timestamp + toIntervalDay(60)
```
```

--------------------------------

### Complete JWT Payload Example

Source: https://www.tinybird.co/docs/classic/administration/auth-tokens

An example of a comprehensive JWT payload including all required and optional fields such as workspace_id, name, exp, scopes, and limits. The workspace admin token is used as the signing key.

```json
{
    "workspace_id": "workspaces_id",
    "name": "frontend_jwt",
    "exp": 123123123123,
    "scopes": [
        {
            "type": "PIPES:READ",
            "resource": "requests_per_day",
            "fixed_params": {
                "org_id": "testing"
            }
        }
    ],
    "limits": {
      "rps": 10
    }
}

```

--------------------------------

### Clone Tinybird E-commerce Demo Project

Source: https://www.tinybird.co/docs/classic/cli/data-projects

Clones the Tinybird e-commerce demo project from GitHub to a local directory. This is the first step to follow the tutorial and work with the provided example files.

```bash
git clone https://github.com/tinybirdco/ecommerce_data_project.git
cd ecommerce_data_project
```

--------------------------------

### Tinybird API Endpoint Example with JWT Fixed Parameters

Source: https://www.tinybird.co/docs/classic/administration/auth-tokens

An example of a Tinybird pipe that defines an API endpoint. This endpoint uses a JWT fixed parameter 'org' to filter data by the 'org_id' column.

```sql
SELECT fieldA, fieldB FROM my_ds WHERE org_id = '{{ String(org) }}'
TYPE ENDPOINT

```

--------------------------------

### Push Tinybird SQL Pipe

Source: https://www.tinybird.co/docs/classic/get-started/use-cases/vector-search-recommendation

Command to push the 'similar_posts.pipe' file to Tinybird, which automatically publishes it as a REST API endpoint.

```shell
cd tinybird
tb push pipes/similar_posts.pipe
```

--------------------------------

### Install SWR for Data Fetching

Source: https://www.tinybird.co/docs/classic/publish-data/charts/guides/real-time-dashboard

Installs the SWR (Stale-While-Revalidate) React library using npm. SWR is used for efficient data fetching, caching, and revalidation in React applications, simplifying data management.

```bash
npm i swr
```

--------------------------------

### Run Tinybird CLI using Docker

Source: https://www.tinybird.co/docs/classic/cli/install

Runs the Tinybird CLI within a Docker container, mounting a local directory for data persistence. This allows usage without local installation and sets the current directory within the container.

```shell
# Assuming a projects/data path
docker run -v ~/projects/data:/mnt/data -it tinybirdco/tinybird-cli-docker
cd mnt/data
```
Tokens:
Raw