Extract data from Apache Pinot into a CSV format and load it into BigQuery

Move real-time analytics data out of Apache Pinot and into BigQuery for long-term warehousing and SQL analysis. This blueprint queries a Pinot table over JDBC, serializes the result set to CSV, and loads it into a typed BigQuery table, solving the common problem of bridging a low-latency OLAP store with a cloud data warehouse without writing glue scripts or managing intermediate infrastructure.

How it works

1. query_pinot (io.kestra.plugin.jdbc.pinot.Query) runs a SQL query against a Pinot broker over the jdbc:pinot driver, casting the location_st_point bytes column to a string so it survives serialization, and emits the rows as an Ion-formatted internal storage file.
2. write_to_csv (io.kestra.plugin.serdes.csv.IonToCsv) converts that Ion file into a CSV file, consuming the upstream output via {{ outputs.query_pinot.uri }}.
3. load_bigquery (io.kestra.plugin.gcp.bigquery.Load) loads the CSV into my_project.my_dataset.my_table, applying an explicit field schema (FLOAT64, STRING) and csvOptions (comma delimiter, skipLeadingRows: 1) so the destination table is created with correct types at runtime.

What you get

• A repeatable Pinot to BigQuery extract and load with no custom code.
• Type-safe ingestion: the BigQuery schema is declared inline, so the table is created or validated on every run.
• Clean handling of binary Pinot columns by casting them to STRING during extraction.
• File-based handoff between tasks using Kestra internal storage, keeping memory usage flat regardless of row count.

Who it's for

• Data engineers consolidating Pinot real-time metrics into a warehouse.
• Analytics teams that need Pinot data available alongside other BigQuery datasets.
• Platform teams replacing ad hoc export scripts with a governed pipeline.

Why orchestrate this with Kestra

Pinot is built for fast queries, not for scheduling cross-system data movement, and it has no native way to trigger, retry, or track a load into BigQuery. Kestra adds event triggers and schedules, automatic retries on transient broker or load failures, full execution lineage across the query, serialize, and load steps, and a single declarative YAML definition you can version control. That fills the gap Pinot's own engine cannot: reliable, observable, end-to-end orchestration.

Prerequisites

• A reachable Apache Pinot broker (update the jdbc:pinot:// URL).
• A Google Cloud project with BigQuery enabled and a dataset created.

Secrets

• GCP_SERVICE_ACCOUNT_JSON: the GCP service account JSON key used by the BigQuery Load task, referenced as {{ secret('GCP_SERVICE_ACCOUNT_JSON') }}.

Quick start

1. Spin up Pinot locally if you need sample data:

   docker pull apachepinot/pinot:latest
   docker run -p 9000:9000 apachepinot/pinot:latest QuickStart -type batch
   

   QuickStart mode loads example datasets, including the starbucksStores table used here.
2. Add the GCP_SERVICE_ACCOUNT_JSON secret to your Kestra instance.
3. Update projectId, destinationTable, and the Pinot url to match your environment.
4. Run the flow and verify the rows land in your BigQuery table.

How to extend

• Swap the sql query and the BigQuery schema to move any Pinot table.
• Add a schedule or event trigger to run the load on a cadence or in response to upstream events.
• Replace IonToCsv with another serdes format, or add downstream bigquery.Query tasks to transform after load.
• Parameterize the project, dataset, and table with inputs for reuse across environments.

Links

• Google Cloud (GCP) plugin docs
• BigQuery orchestration with Kestra