Google Cloud BigQuery

Warnings

• breaking google-cloud-bigquery-storage and pyarrow are now required dependencies in v3.x (previously optional). Installing v3 without them causes ImportError on to_dataframe().
  Fix: pip install 'google-cloud-bigquery[pandas]' — installs all required deps including db-dtypes.
• breaking to_dataframe() dtype mappings changed in v3. INT64 → Int64 (nullable), BOOLEAN → boolean (nullable), DATE → dbdate. Code checking dtype == 'int64' or 'bool' will fail silently or raise.
  Fix: Use pandas nullable dtypes in comparisons: df['col'].dtype == 'Int64' not 'int64'. Or use pd.api.types.is_integer_dtype().
• gotcha client.query() returns a QueryJob — NOT results. It starts the job asynchronously. Must call .result() to block and wait for completion before iterating rows.
  Fix: rows = client.query(sql).result() — the .result() call is mandatory.
• gotcha Authentication uses Application Default Credentials (ADC) — no API key. Locally: run 'gcloud auth application-default login'. In production: use service account key or Workload Identity. Missing credentials raises DefaultCredentialsError.
  Fix: Local: gcloud auth application-default login. Production: set GOOGLE_APPLICATION_CREDENTIALS=/path/to/key.json or use Workload Identity.
• gotcha BigQuery table references use backtick syntax in SQL: `project.dataset.table`. Using regular quotes raises BadRequest syntax error.
  Fix: SELECT * FROM `myproject.mydataset.mytable` — backticks required for fully qualified table names.
• gotcha StandardSqlDataType and related types moved from google.cloud.bigquery_v2 to google.cloud.bigquery in v3. Old imports raise ImportError.
  Fix: from google.cloud.bigquery import StandardSqlDataType — not from google.cloud.bigquery_v2
• gotcha Python 3.7 and 3.8 support dropped in v3.x. New major version in Q4 2024 dropped these Python versions.
  Fix: Use Python 3.9+

Install

• pip install google-cloud-bigquery Python (core)
• pip install 'google-cloud-bigquery[pandas]' Python (with pandas/to_dataframe support)
• pip install 'google-cloud-bigquery[pandas,pyarrow]' Python (with pandas + pyarrow — recommended)

Imports

• Client (basic query)

  from google.cloud import bigquery
  
  # ADC auth — uses GOOGLE_APPLICATION_CREDENTIALS env var
  # or gcloud CLI credentials locally
  client = bigquery.Client(project='my-project')
  
  # query() returns a QueryJob — NOT results
  query = """
      SELECT name, COUNT(*) as count
      FROM `bigquery-public-data.usa_names.usa_1910_2013`
      WHERE state = 'TX'
      GROUP BY name
      ORDER BY count DESC
      LIMIT 10
  """
  query_job = client.query(query)  # starts job
  rows = query_job.result()       # BLOCKS until complete
  
  for row in rows:
      print(row.name, row.count)

  client.query() returns a QueryJob object — not results. Must call .result() to block and get rows. Iterating over QueryJob directly may work but is not guaranteed to be complete.
• to_dataframe

  from google.cloud import bigquery
  
  client = bigquery.Client(project='my-project')
  query = 'SELECT id, name, created_at FROM `myproject.mydataset.mytable` LIMIT 100'
  
  # to_dataframe() requires pandas + pyarrow + db-dtypes
  df = client.query(query).result().to_dataframe()
  print(df.dtypes)
  # id: Int64 (nullable) — not int64
  # name: object
  # created_at: dbdate

  v3 changed to_dataframe() dtypes. INT64 now maps to pandas Int64 (nullable), BOOLEAN to boolean (nullable), DATE to dbdate. Code checking dtype == 'int64' or 'bool' will fail.

Quickstart

BigQuery Python client — query and to_dataframe with ADC auth.

# pip install 'google-cloud-bigquery[pandas]'
# Set up ADC: gcloud auth application-default login
# or set GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json

from google.cloud import bigquery

client = bigquery.Client(project='my-gcp-project')

# Query public dataset
query = """
    SELECT
        name,
        SUM(number) as total
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE state = 'CA'
    GROUP BY name
    ORDER BY total DESC
    LIMIT 5
"""

# Run query and wait for results
query_job = client.query(query)
rows = query_job.result()  # blocks until done

for row in rows:
    print(f'{row.name}: {row.total}')

# As DataFrame
df = rows.to_dataframe()
print(df.head())