GCS Sink¶

Set up a GCS Sink to export data from Tinybird to any GCS bucket in CSV, NDJSON, or Parquet format. Offload data on a batch-based schedule using Tinybird's fully managed Connector.

Setting up the GCS Sink requires:

1. Configuring a Service Account with these permissions in GCP.
2. Creating a connection file in Tinybird.
3. Creating a Sink pipe that uses this connection

Environment considerations¶

Before setting up the GCS Sink, understand how it works in different environments.

Cloud environment¶

In the Tinybird Cloud environment, Tinybird uses the Service Account credentials you provide to write to your GCS bucket.

Local environment¶

When using the GCS Sink in the Tinybird Local environment, which runs in a container, you need to pass your local GCP credentials to the container. These credentials must have the permissions described in the GCS permissions section, including access to GCS operations like storage.objects.create, storage.objects.get, etc.

Set up the sink¶

tb connection create gcs

TYPE gcs
GCS_SERVICE_ACCOUNT_CREDENTIALS_JSON {{ tb_secret("GCS_KEY") }}

NODE node_0

SQL >
    SELECT *
    FROM events
    WHERE status = 'processed'

TYPE sink
EXPORT_CONNECTION_NAME "gcs_sample"
EXPORT_BUCKET_URI "gs://tinybird-sinks"
EXPORT_FILE_TEMPLATE "daily_prices" # Supports partitioning
EXPORT_SCHEDULE "*/5 * * * *"
EXPORT_FORMAT "csv" # Optional
EXPORT_COMPRESSION "gz" # Optional
EXPORT_STRATEGY "create_new" # Optional

tb --cloud deploy --check

tb --cloud deploy

Connection file settings¶

The GCS connector uses the following settings in .connection files:

*Either GCS_SERVICE_ACCOUNT_CREDENTIALS_JSON (for Service Account authentication) or both GCS_ACCESS_ID and GCS_SECRET (for HMAC authentication) are required.

Pipe file settings¶

The GCS Sink pipe uses the following settings in .pipe files:

Supported regions¶

The Tinybird GCS Sink feature only supports exporting data to the following GCP regions:

• us-*
• eu-*
• us-central-*
• us-east-*
• us-west-*
• europe-west-*
• northamerica-northeast-*

Scheduling considerations¶

The schedule applied to a Sink pipe doesn't guarantee that the underlying job executes immediately at the configured time. The job is placed into a job queue when the configured time elapses. It is possible that, if the queue is busy, the job could be delayed and executed after the scheduled time.

To reduce the chances of a busy queue affecting your Sink pipe execution schedule, distribute the jobs over a wider period of time rather than grouping them close together.

Write strategies¶

The EXPORT_STRATEGY parameter determines how Tinybird handles existing files in your GCS bucket:

• create_new (default): Creates new files without overwriting existing ones. If a file with the same name already exists, Tinybird appends a suffix to make the filename unique.
• replace: Overwrites existing files with the same name. Use this when you want to replace previous exports entirely.

Query parameters¶

You can add query parameters to your Sink pipes, the same way you do in API Endpoints or Copy pipes.

• For on-demand executions, you can set parameters when you trigger the Sink pipe to whatever values you wish.
• For scheduled executions, the Sink pipe uses the default parameter values.

Execute the Sink pipe¶

On-demand execution¶

You can trigger your Sink pipe manually using:

tb sink run <pipe_name>

Scheduled execution¶

If you configured a schedule with EXPORT_SCHEDULE, the Sink pipe runs automatically according to the cron expression.

Once the Sink pipe is triggered, it creates a standard Tinybird job that can be followed via the v0/jobs API or using tb job ls --kind=sink.

File template¶

Partition the result into different files to organize your data and get smaller files. The file template defines the partitioning based on the values of columns in the result set.

Partition by column¶

Add a template variable like {COLUMN_NAME} to the filename. For instance, consider the following query schema and result for an export:

With the given file template invoice_summary_{customer_id}.csv you'd get 3 files:

invoice_summary_ACME.csv

invoice_summary_OSCORP.csv

invoice_summary_GLOBEX.csv

Values format¶

To help partition in a sensible way, you can add a format string to the column name using the following placeholders:

For instance, for a result like this:

All 7 events have different times in the column timestamp. Using a file template like invoices_{timestamp} would create 7 different files.

If you were interested in writing one file per hour, you could use a file template like invoices_{timestamp, '%Y%m%d-%H'}. You'd then get only two files for that dataset:

invoices_20230707-09.csv

invoices_20230706-23.csv

By number of files¶

You also have the option to write the result into X files. Instead of using a column name, use an integer between brackets.

Example: invoice_summary.{8}.csv

This is convenient to reduce the file size of the result, especially when the files are meant to be consumed by other services where uploading big files is discouraged.

The results are written in random order. This means that the final result rows would be written in X files, but you can't count the specific order of the result.

There are a maximum of 16 files.

Combining different partitions¶

It's possible to add more than one partitioning parameter in the file template. This is useful, for instance, when you do a daily dump of data, but want to export one file per hour.

Setting the file template as invoices/dt={timestamp, '%Y-%m-%d'}/H{timestamp, '%H}.csv would create the following file structure in different days and executions:

Invoices
├── dt=2023-07-07
│   └── H23.csv
│   └── H22.csv
│   └── H21.csv
│   └── ...
├── dt=2023-07-06
│   └── H23.csv
│   └── H22.csv

You can also mix column names and number of files. For instance, setting the file template as invoices/{customer_id}/dump_{4}.csv would create the following file structure in different days and executions:

Invoices
├── ACME
│   └── dump_0.csv
│   └── dump_1.csv
│   └── dump_2.csv
│   └── dump_3.csv
├── OSCORP
│   └── dump_0.csv
│   └── dump_1.csv
│   └── dump_2.csv
│   └── dump_3.csv

Supported file types¶

The GCS Sink supports exporting data in the following file formats:

You can optionally configure the export format using the EXPORT_FORMAT parameter (defaults to CSV) and compression using the EXPORT_COMPRESSION parameter in your Sink pipe configuration.

GCS permissions¶

The GCS Connector requires a Service Account with specific permissions to access objects in your GCS bucket. Following the principle of least privilege, the minimum required roles are:

• Storage Object Creator, roles/storage.objectCreator - Allows users to create objects
• Storage Object Viewer (roles/storage.objectViewer) - Grants access to view objects and their metadata, and list objects in a bucket
• Storage Bucket Viewer (roles/storage.legacyBucketReader) - Allows users to list objects in a bucket and view bucket metadata

Create a Service Account in GCP:

1. In the GCP console, create or use an existing Service Account.
2. Assign the following roles to the service account for the specific bucket or project:
   • roles/storage.legacyBucketReader
   • roles/storage.objectCreator
   • roles/storage.objectViewer
3. Generate a JSON key file and download it.
4. Store the key as a Tinybird secret.

Observability¶

Sink pipes operations are logged in the tinybird.jobs_log Service Data Source. You can filter by job_type = 'sink' to see only Sink pipe executions.

For more detailed Sink-specific information, you can also use tinybird.sinks_ops_log.

Data Transfer incurred by Sink pipes is tracked in tinybird.data_transfer Service Data Source.

Limits & quotas¶

Billing¶

Tinybird bills Sink pipes based on Data Transfer. When a Sink pipe executes, it uses your plan's included compute resources to run the query, then writes the result to a bucket as Data Transfer. If Tinybird compresses the resulting files, it accounts for the compressed size.

Data transfer¶

Data Transfer depends on your environment. There are two scenarios:

• The destination bucket is in the same cloud provider and region as your Tinybird Workspace: $0.01 / GB
• The destination bucket is in a different cloud provider or region as your Tinybird Workspace: $0.10 / GB

Next steps¶

• Get familiar with the Service Data Source and see what's going on in your account
• Deep dive on Tinybird's pipes concept