Data Source API ¶

This endpoint supports 3 modes to enable 3 distinct operations, depending on the parameters provided:

• Create a new Data Source with a schema

• Append data to an existing Data Source

• Replace data in an existing Data Source

The mode is controlled by setting the mode parameter, for example, -d "mode=create". Each mode has different rate limits.

When importing remote files by URL, if the server hosting the remote file supports HTTP Range headers, the import process will be parallelized.

Examples

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "name=stocks" \
-d "schema=symbol String, date Date, close Float32"

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources?name=stocks" \
-F csv=@local_file.csv

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "name=stocks" \
-d url='https://.../data.csv'

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "schema=pk UInt64, insert_date Date, close Float32" \
-d "engine=ReplacingMergeTree" \
-d "engine_sorting_key=pk" \
-d "engine_ver=insert_date" \
-d "name=test123" \
-d "engine_settings=index_granularity=2048, ttl_only_drop_parts=false"

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources?name=data_source_name&mode=append" \
-F csv=@local_file.csv

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d mode='append' \
-d name='data_source_name' \
-d url='https://.../data.csv'

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources?name=data_source_name&mode=replace" \
-F csv=@local_file.csv

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d mode='replace' \
-d name='data_source_name' \
--data-urlencode "url=http://example.com/file.csv"

Modify the Data Source schema.

This endpoint supports the operation to alter the following fields of a Data Source:

The schema parameter can be used to add new columns at the end of the existing ones in a Data Source.

Be aware that currently we don’t validate if the change will affect the existing MVs (Materialized Views) attached to the Data Source to be modified, so this change may break existing MVs. For example, avoid changing a Data Source that has a MV created with something like SELECT * FROM Data Source .... If you want to have forward compatible MVs with column additions, create them especifying the columns instead of using the * operator.

Also, take in account that, for now, the only engines supporting adding new columns are those inside the MergeTree family.

To add a column to a Data Source, call this endpoint with the Data Source name and the new schema definition.

For example, having a Data Source created like this:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "name=stocks" \
-d "schema=symbol String, date Date, close Float32"

if you want to add a new column ‘concept String’, you need to call this endpoint with the new schema:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources/stocks/alter" \
-d "schema=symbol String, date Date, close Float32, concept String"

If everything went ok, you will get the operations done in the response:

{
    "operations": [
        "ADD COLUMN `concept` String"
    ]
}

You can also view the inferred operations without executing them adding dry=true in the parameters.

• To modify the description of a Data Source:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources/stocks/alter" \
-d "name=stocks" \
-d "description=My new description"

• To save in the “value” column of a Kafka Data Source the JSON as a raw string:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources/stocks/alter" \
-d "name=stocks" \
-d "kafka_store_raw_value=true"
-d "kafka_store_headers=true"

• To modify the TTL of a Data Source:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources/stocks/alter" \
-d "name=stocks" \
-d "ttl=12 hours"

• To remove the TTL of a Data Source:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "name=stocks" \
-d "ttl=false"

• To add default values to the columns of a Data Source:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "name=stocks" \
-d "schema=symbol String DEFAULT '-', date Date DEFAULT now(), close Float32 DEFAULT 1.1"

• To add default values to the columns of a NDJSON Data Source, add the default definition after the jsonpath definition:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "name=stocks" \
-d "schema=symbol String `json:$.symbol` DEFAULT '-', date Date `json:$.date` DEFAULT now(), close `json:$.close` Float32 DEFAULT 1.1"

• To make a column nullable, change the type of the column adding the Nullable type prefix to old one:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "name=stocks" \
-d "schema=symbol String `json:$.symbol, date Date `json:$.date`, close `json:$.close` Nullable(Float32)"

• To drop a column, just remove the column from the schema definition. It will not be possible removing columns that are part of the primary or partition key:

curl \
-H "Authorization: Bearer <DATASOURCES:CREATE token>" \
-X POST "https://api.tinybird.co/v0/datasources" \
-d "name=stocks" \
-d "schema=symbol String `json:$.symbol, date Date `json:$.date`"

Truncates a Data Source in your account. If the Data Source has dependent Materialized Views, those won’t be truncated in cascade. In case you want to delete data from other dependent Materialized Views, you’ll have to do a subsequent call to this method. Auth token in use must have the DATASOURCES:CREATE scope.

curl \
    -H "Authorization: Bearer <DATASOURCES:CREATE token>" \
    -X POST "https://api.tinybird.co/v0/datasources/name/truncate"

This works as well for the quarantine table of a Data Source. Remember that the quarantine table for a Data Source has the same name but with the “_quarantine” suffix.

curl \
    -H "Authorization: Bearer <DATASOURCES:DROP token>" \
    -X POST "https://api.tinybird.co/v0/datasources/:name_quarantine/truncate"

Deletes rows from a Data Source in your account given a SQL condition. Auth token in use must have the DATASOURCES:CREATE scope.

curl \
    -H "Authorization: Bearer <DATASOURCES:CREATE token>" \
    --data "delete_condition=(country='ES')" \
    "https://api.tinybird.co/v0/datasources/:name/delete"

When deleting rows from a Data Source, the response will not be the final result of the deletion but a Job. You can check the job status and progress using the Jobs API. In the response, id, job_id, and delete_id should have the same value:

{
    "id": "64e5f541-xxxx-xxxx-xxxx-00524051861b",
    "job_id": "64e5f541-xxxx-xxxx-xxxx-00524051861b",
    "job_url": "https://api.tinybird.co/v0/jobs/64e5f541-xxxx-xxxx-xxxx-00524051861b",
    "job": {
        "kind": "delete_data",
        "id": "64e5f541-xxxx-xxxx-xxxx-00524051861b",
        "job_id": "64e5f541-xxxx-xxxx-xxxx-00524051861b",
        "status": "waiting",
        "created_at": "2023-04-11 13:52:32.423207",
        "updated_at": "2023-04-11 13:52:32.423213",
        "started_at": null,
        "is_cancellable": true,
        "datasource": {
            "id": "t_c45d5ae6781b41278fcee365f5bxxxxx",
            "name": "shopping_data"
        },
        "delete_condition": "event = 'search'"
    },
    "status": "waiting",
    "delete_id": "64e5f541-xxxx-xxxx-xxxx-00524051861b"
}

To check on the progress of the delete job, use the job_id from the Delete API response to query the Jobs API.

For example, to check on the status of the above delete job:

curl \
    -H "Authorization: Bearer <TOKEN>" \
    https://api.tinybird.co/v0/jobs/64e5f541-xxxx-xxxx-xxxx-00524051861b

Would respond with:

{
    "kind": "delete_data",
    "id": "64e5f541-xxxx-xxxx-xxxx-00524051861b",
    "job_id": "64e5f541-xxxx-xxxx-xxxx-00524051861b",
    "status": "done",
    "created_at": "2023-04-11 13:52:32.423207",
    "updated_at": "2023-04-11 13:52:37.330020",
    "started_at": "2023-04-11 13:52:32.842861",
    "is_cancellable": false,
    "datasource": {
        "id": "t_c45d5ae6781b41278fcee365f5bc2d35",
        "name": "shopping_data"
    },
    "delete_condition": " event = 'search'",
    "rows_affected": 100
}

Data Source engines supported

Tinybird uses ClickHouse as the underlying storage technology. ClickHouse features different strategies to store data, these different strategies define not only where and how the data is stored but what kind of data access, queries, and availability your data has. In ClickHouse terms, a Tinybird Data Source uses a Table Engine that determines those factors.

Currently, Tinybird supports deleting data for data sources with the following Engines:

• MergeTree

• ReplacingMergeTree

• SummingMergeTree

• AggregatingMergeTree

• CollapsingMergeTree

• VersionedCollapsingMergeTree

Dependent views deletion

If the Data Source has dependent Materialized Views, those won’t be cascade deleted. In case you want to delete data from other dependent Materialized Views, you’ll have to do a subsequent call to this method for the affected view with a proper delete_condition. This applies as well to the associated quarantine Data Source.