Use BigQuery and Python script running in Docker to analyze Wikipedia

Combine Google BigQuery, CSV serialization, and Python Pandas to extract and analyze the top 10 most viewed Wikipedia pages of the current day. This blueprint queries the public bigquery-public-data.wikipedia.pageviews_2024 dataset, converts the result set into CSV, then runs a containerized Pandas script to compute the maximum view count and publish it as a tracked Kestra output. Use it as a starting point for content analytics, trending topic detection, public dataset exploration, or any pipeline that mixes SQL warehouses with Python data science tooling.

How it works

1. io.kestra.plugin.gcp.bigquery.Query runs a parameterized SQL query against the public Wikipedia pageviews table, filtered to today's English-language pages, ordered by hour and views, limited to the top 10 rows. fetchType: STORE writes the result to Kestra internal storage as an Ion file.
2. io.kestra.plugin.serdes.csv.IonToCsv converts the Ion result into a portable CSV file consumable by any downstream tool.
3. io.kestra.plugin.scripts.python.Script runs inside a Docker task runner (io.kestra.plugin.scripts.runner.docker.Docker) with pandas and kestra installed. It reads the CSV via inputFiles, loads it into a DataFrame, and emits the peak view count using Kestra.outputs({'views': int(views)}).

What you get

• Daily, repeatable extract of the top 10 Wikipedia pages from BigQuery public data
• CSV artifact stored in Kestra internal storage for reuse by other flows
• Tracked numeric output (views) you can graph over time on the Outputs tab
• BigQuery execution metrics on the Metrics tab: total.bytes.billed, total.partitions.processed, rows processed, query duration

Who it's for

• Data engineers building content analytics or trending topic pipelines
• Analytics engineers prototyping BigQuery to Python workflows
• Platform teams demonstrating Kestra's BigQuery and Python script integration
• Anyone learning how to chain SQL warehouses with Pandas in a single declarative flow

Why orchestrate this with Kestra

BigQuery's scheduled queries can run SQL on a cron, but they cannot hand results off to a Python container, capture custom application metrics, or branch on values computed in code. Kestra closes that gap: event or schedule triggers fire the query, retries handle transient API errors, the Ion to CSV step gives you a stable artifact, and the Docker-backed Python task adds full data science tooling. Every run produces lineage, logs, metrics, and outputs you can chart, alert on, or feed into downstream flows, all from declarative YAML.

Prerequisites

• A Google Cloud project with BigQuery API enabled and access to BigQuery public datasets
• A GCP service account JSON key with roles/bigquery.jobUser and roles/bigquery.dataViewer
• Docker available to the Kestra worker for the Python task runner

Secrets

• GCP_SERVICE_ACCOUNT_JSON: full JSON content of the GCP service account key used by the BigQuery query task

Quick start

1. Add the GCP_SERVICE_ACCOUNT_JSON secret to your Kestra instance.
2. Replace projectId: test-project with your real GCP project ID.
3. Save the flow under your namespace and run it once from the UI.
4. Open the Execution page and inspect the Outputs, Metrics, and Logs tabs.
5. Add a Schedule trigger (for example 0 9 * * *) to capture the top pages every morning.

How to extend

• Swap the SQL to target a different public dataset (GitHub Archive, Stack Overflow, Google Trends).
• Replace IonToCsv with IonToJson or IonToParquet for downstream tools that prefer JSON or columnar formats.
• Push the CSV to Google Cloud Storage with io.kestra.plugin.gcp.gcs.Upload for long-term archival.
• Extend the Pandas script to produce a chart with matplotlib and expose it via outputFiles.
• Send the daily peak views to Slack with io.kestra.plugin.notifications.slack.SlackIncomingWebhook for a morning digest.

Links

• plugin-gcp documentation
• BigQuery orchestration with Kestra