Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 37 ยท Pipelines and Integrations

The Python Client Library

Use google-cloud-bigquery from Python: authenticate, run parameterized queries, do dry runs, load DataFrames and stream rows, with a runnable stand-in client.

SQL is the language of BigQuery, but real pipelines wrap it in code: a Python service that loads a file, runs a query with parameters, and posts the result somewhere. Google's official package, google-cloud-bigquery, is the standard way to do that. It exposes a Client object whose methods start jobs, wait for them and return rows.

Python on this site cannot authenticate to Google Cloud or reach BigQuery, so the real-client code is shown read-only. To let you run and practice the same patterns, the examples use a tiny stand-in client, a hand-written class over SQLite with the same method names (query, result, dry_run). It is labelled in each example; it is not the real library.

Install and authenticate

Terminal
pip install google-cloud-bigquery pandas db-dtypes pyarrow
gcloud auth application-default login

Application Default Credentials let the library find your identity locally. On Google Cloud (Cloud Run, Compute Engine, Cloud Functions) it uses the attached service account automatically, so no key files are needed. Avoid downloading JSON key files; they are long-lived secrets that leak easily.

Python
from google.cloud import bigquery

client = bigquery.Client(project="my-project")

Run a query

client.query(sql) starts a job and returns immediately with a QueryJob. job.result() waits and returns an iterator of rows:

Python
sql = """
SELECT country, COUNT(*) AS orders
FROM `my-project.shop.orders`
WHERE order_date >= '2026-03-01'
GROUP BY country
ORDER BY orders DESC
"""
job = client.query(sql)
for row in job.result():
    print(row["country"], row.orders)   # access by name or attribute

Rows behave like both tuples and dictionaries. The job object also reports job.total_bytes_processed, job.total_bytes_billed, job.cache_hit and job.slot_millis after it finishes.

Always use query parameters

Never build SQL with f-strings and user input; that invites SQL injection and defeats the cache. Use query parameters, written @name in the SQL:

Python
job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("start", "DATE", "2026-03-01"),
        bigquery.ScalarQueryParameter("min_total", "NUMERIC", "20"),
        bigquery.ArrayQueryParameter("countries", "STRING", ["DE", "FR"]),
    ],
    maximum_bytes_billed=10 * 1024**3,    # fail if it would bill more than 10 GiB
    labels={"team": "analytics", "job": "daily_report"},
)
sql = """
SELECT order_id, total
FROM `my-project.shop.orders`
WHERE order_date >= @start AND total >= @min_total AND country IN UNNEST(@countries)
"""
rows = client.query(sql, job_config=job_config).result()

Parameters can only stand in for values, not table or column names. If you must choose a table dynamically, validate the name against an allow-list first.

Dry runs and cost guards

Python
config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
job = client.query(sql, job_config=config)
print(f"This query will process {job.total_bytes_processed / 1024**3:.2f} GiB")

A dry run returns immediately and charges nothing. Combine it with a threshold check in CI or before an expensive batch, as the next example does with the stand-in.

A stand-in client you can run

This class mimics the shape of the real library over an in-memory SQLite database, including ScalarQueryParameter-style named parameters, dry_run and maximum_bytes_billed. It is not BigQuery: it estimates bytes from a fixed number per row and uses SQLite SQL.

Python Stand-in for client.query (SQLite, not BigQuery)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The pattern, not the engine, is the lesson: estimate first, pass values as parameters, cap the bytes, then iterate over results.

DataFrames and loading

With pandas and db-dtypes installed, results convert directly:

Python
df = client.query(sql, job_config=job_config).to_dataframe()

For big results use to_arrow() or the BigQuery Storage Read API (used automatically when google-cloud-bigquery-storage is installed), which streams data far faster than the REST API. To upload a DataFrame:

Python
load_config = bigquery.LoadJobConfig(
    write_disposition="WRITE_TRUNCATE",
    schema=[bigquery.SchemaField("order_id", "INT64"),
            bigquery.SchemaField("total", "NUMERIC")],
)
client.load_table_from_dataframe(df, "my-project.shop.orders_clean",
                                 job_config=load_config).result()

Loading a file from Cloud Storage uses client.load_table_from_uri("gs://bucket/file.parquet", table_id, job_config=...). For a few rows in near real time, client.insert_rows_json(table_id, rows) uses the legacy streaming API; for volume, prefer the Storage Write API through the google-cloud-bigquery-storage package.

Practicing the shape of results

Real result() rows support column access by name. A common chore is turning them into a list of dictionaries for JSON. The stand-in rows below are plain dictionaries, so the same code works on both:

Python From rows to a JSON report

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With the real library, replace rows with [dict(r) for r in job.result()].

Good habits

  • Create one Client and reuse it.
  • Always parameterize, and set maximum_bytes_billed and labels.
  • Check job.errors and let exceptions propagate; do not swallow google.api_core.exceptions.GoogleAPICallError.
  • Use a service account with only the roles the job needs (IAM roles).
  • Make jobs idempotent, as in scheduled queries.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does client.query(sql) return?

    query starts an asynchronous job; result() blocks until it finishes and iterates the rows.
  2. 2. Why use query parameters instead of f-strings?

    Values are passed separately from the SQL text, which is safer and cache-friendly.
  3. 3. How do you estimate bytes without running the query?

    A dry run returns the estimate immediately and charges nothing.
  4. 4. How should code running on Cloud Run authenticate?

    On Google Cloud the library picks up the runtime service account automatically; avoid long-lived key files.

Summary

  • bigquery.Client().query(sql) starts a job; .result() waits and yields rows.
  • Use QueryJobConfig for parameters, dry_run, maximum_bytes_billed and labels.
  • Convert with to_dataframe() or to_arrow(); load with load_table_from_dataframe or load_table_from_uri.
  • Authenticate with Application Default Credentials and least-privilege service accounts.
  • The stand-in here only mirrors the shape of the API; it is not BigQuery.

Next: Streaming, Dataform and Connected Tools