Google BigQuery Magics for Jupyter and IPython

0.14.0 · active · verified Thu Apr 16

bigquery-magics provides IPython cell magics that allow users to run SQL queries directly within Jupyter and IPython notebooks against Google BigQuery. It simplifies data exploration by automatically converting query results into pandas DataFrames. The library is part of the actively maintained `google-cloud-python` monorepo and sees frequent updates, with the current stable version being 0.14.0.

Common errors

```
Project 'your-project-id' not found.
```
cause The specified Google Cloud Project ID is incorrect, does not exist, or you lack permissions to view it.

fix Verify the Project ID in the Google Cloud Console. Ensure you have the `resourcemanager.projects.get` permission and that billing is enabled for the project. Set the correct ID via `os.environ['GOOGLE_CLOUD_PROJECT'] = 'your-project-id'` or `--project` argument.
```
403 Forbidden: Request had insufficient authentication scopes.
```
cause Your authenticated user or service account lacks the necessary IAM roles or permissions to access BigQuery resources.

fix Grant the required IAM roles (e.g., `BigQuery User`, `BigQuery Data Editor`, `BigQuery Job User`) to your Google Cloud identity. If using `gcloud auth login`, ensure you've authenticated with scopes that allow BigQuery access.
```
Cannot run query: project does not have the reservation in the data region or no slots are configured.
```
cause This usually indicates an issue with BigQuery reservations, project region mismatch, or insufficient quota/billing.

fix Check if your BigQuery project has sufficient quota, or if it's assigned to a reservation with available slots. Ensure the dataset/table you're querying exists in the region where the query is being run. Verify billing is enabled.
```
400 Bad Request: Billing account not enabled for project 'your-project-id'.
```
cause The Google Cloud project associated with your queries does not have an active billing account linked.

fix Go to the Google Cloud Console, navigate to 'Billing', and ensure a valid billing account is linked and active for the specified project. BigQuery queries incur costs and require an enabled billing account.

Warnings

breaking Python 2 and older Python 3 versions (<= 3.8) are no longer supported. Ensure your environment runs Python 3.9 or newer.
Fix: Upgrade your Python environment to 3.9 or higher. The current version requires Python >= 3.10.
gotcha Authentication to Google Cloud and BigQuery is crucial. Without proper Application Default Credentials (ADC) or explicit credential setup, queries will fail with authorization errors.
Fix: Ensure you have authenticated your environment using `gcloud auth application-default login` for local development, or that your service account has the necessary IAM permissions (e.g., BigQuery User, BigQuery Data Editor) if running on Google Cloud.
gotcha Specifying the correct Google Cloud Project ID is essential. If not explicitly set via an environment variable or magic argument, the magic may fail or query against an unintended project.
Fix: Set the `GOOGLE_CLOUD_PROJECT` environment variable, or use the `--project` argument with the `%%bigquery` magic (e.g., `%%bigquery --project your-project-id`). Ensure billing is enabled for the specified project.
gotcha The `bigquery-magics` library's source code was migrated into the larger `googleapis/google-cloud-python` monorepo. While generally not a breaking change for users, be aware that issue tracking and contribution guidelines are now centralized in the monorepo.
Fix: Refer to the monorepo's documentation for the most up-to-date development and contribution information.

Install

pip install bigquery-magics Install latest stable version

Imports

bigquery_magics
```
%load_ext bigquery_magics
```
Magics are loaded using the %load_ext command, not Python's 'import' statement.

Quickstart

This quickstart demonstrates how to load the `bigquery_magics` extension, set the Google Cloud project, and execute a BigQuery SQL query directly within a Jupyter cell. The `%%bigquery` cell magic runs the query and automatically saves the results into a pandas DataFrame, specified as a line argument.

# Load the BigQuery magics extension
%load_ext bigquery_magics

# Set your Google Cloud Project ID
# Replace 'your-gcp-project-id' with your actual Project ID.
# Ensure your environment is authenticated (e.g., via gcloud auth application-default login)
import os
os.environ['GOOGLE_CLOUD_PROJECT'] = os.environ.get('GOOGLE_CLOUD_PROJECT', 'your-gcp-project-id')

# Run a BigQuery SQL query using the %%bigquery cell magic
# The results will be stored in a pandas DataFrame named 'df_names'
%%bigquery df_names
SELECT
    name,
    SUM(number) AS count
FROM
    `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY
    name
ORDER BY
    count DESC
LIMIT
    5;

# Display the DataFrame
print(df_names)

view raw JSON →