Unlock SQL and Python interoperability for BigQuery with %%bqsql magic#

The %%bqsql Magic#

Last year, Google introduced SQL cells in Colab Enterprise notebooks. Now, with the %%bqsql cell magics in BigQuery DataFrames, this same powerful interoperability is available to all Jupyter users, whether you’re in Colab, JupyterLab, or VS Code. These magics allow you to write SQL queries that run directly on local pandas DataFrames, BigFrames DataFrames, or BigQuery tables.

Getting Started#

To get started,

1. Enable the BigQuery sandbox. Make note of your Google Cloud project ID.

2. Set up a local Python development environment (see: Setting up a Python development environment) for Google Cloud.

3. Create and activate a venv to isolate Python dependencies. On Linux or macOS, use these commands (update to your preferred Python version):

   python3.12 -m venv ~/venv
   . ~/venv/bin/activate
   

4. Install the Jupyter, bigframes, and python-calamine packages:

   pip install --upgrade jupyterlab bigframes python-calamine
   

5. Start Jupyter Lab.

   jupyter lab
   

6. Open a web browser to the URL listed in the output. It will be something like http://localhost:8888/lab?token=somesupersecretvaluehere.

7. Create a new notebook using the Jupyter Lab UI.

%pip install python-calamine pandas bigframes

Accessing the Dataset#

In this tutorial, you’ll analyze the USDA wheat data. Use the standard requests package to download the data to a temporary file, mimicking a typical local data analysis workflow.

import tempfile

import requests

url = "https://www.ers.usda.gov/media/5706/wheat-data-all-years.xlsx?v=52690"

tmp = tempfile.NamedTemporaryFile(delete=True)

with requests.get(url, stream=True) as r:
    r.raise_for_status()
    for chunk in r.iter_content(chunk_size=8192):
        tmp.write(chunk)

tmp.flush()
tmp.seek(0)

0

Use the pyarrow dtype_backend when preparing local Pandas data for SQL processing. This ensures more consistent handling of NULL values and seamless schema mapping when you hand off the data to the BigQuery SQL engine. For this example, read the ‘Table05’ sheet, which contains annual wheat supply and disappearance data:

import pandas as pd

df = pd.read_excel(
    tmp,
    sheet_name="Table05",
    dtype_backend="pyarrow",
    engine="calamine",
    header=1,  # Skip the first row.
)
tmp.close()
df

Preparing the data#

Before querying the local DataFrame with SQL, ensure that the column names are SQL-friendly. BigQuery supports flexible column names, allowing most unicode characters, but special characters like “/” and “” must be removed or replaced.

df.columns = [name.replace("/", "") for name in df.columns]
df

Filtering with Pandas#

Perform a basic filter using standard Python/Pandas syntax to remove rows with missing data. This represents the initial Python-only stage of a processing chain.

full_rows = df[~df['Beginning stocks'].isna()]
full_rows

Interoperate with SQL using the BigQuery SQL magics (%%bqsql)#

The BigQuery DataFrames library provides the %%bqsql magic, which acts as the bridge between your Python and SQL environments. It allows the BigQuery query engine to directly reference and query your local Pandas DataFrames (by implicitly uploading them as temporary tables) as well as actual BigQuery tables and external tables in GCS (Parquet, Iceberg, CSV).

To enable this integration in your notebook, load the bigframes extension. This is already completed in BigQuery Studio, Colab Enterprise, and Colab notebooks. For other environments, such as VS Code and Jupyter Lab, run the following cell:

%load_ext bigframes

To ensure the correct Google Cloud project is billed for query usage, including free tier usage, configure the project ID used by the magics. Even in the free sandbox tier, a project ID is required to allocate query resources. If you don’t set it explicitly, BigFrames will try to discover it from your environment (e.g., your Application Default Credentials).

import bigframes.pandas as bpd

PROJECT_ID = "" # @param {type:"string"}
bpd.options.bigquery.project = PROJECT_ID

%%bqsql
SELECT * FROM {full_rows}

Chaining SQL and Python: Saving SQL Results#

The true power of the %%bqsql magic lies in chaining. By providing a destination variable name as an argument to %%bqsql (e.g., %%bqsql destination_var), the query result is saved as a BigQuery DataFrame (a.k.a. BigFrames DataFrame) to that variable.

This DataFrame lives on the BigQuery engine but behaves like a Pandas DataFrame in Python. You can immediately use it in subsequent Python cells, or reference it again in another SQL cell. This allows you to build a multi-step, hybrid processing pipeline.

Filter the data to only yearly entries using SQL, and save the result into a new BigFrames DataFrame named yearly:

%%bqsql yearly
SELECT *
FROM {full_rows}
WHERE STARTS_WITH(`Time period`, 'MY')

%%bqsql timeseries
SELECT
  * EXCEPT (`Marketing year 1`),
  TIMESTAMP(CONCAT(
    REGEXP_EXTRACT(`Marketing year 1`, r'([0-9]+)\/'),
    '-01-01')) AS `year`
FROM {yearly}

Chaining Back to Python: Visualizing BigFrames Data#

Now that you’ve completed some SQL transformations, you can chain back to Python for visualization. Because BigFrames DataFrames implement the Pandas API, you can call standard visualization methods (like .plot.line()) directly on the timeseries DataFrame without downloading the full dataset first. The computations happen in BigQuery, and only the summarized chart data is sent back to the notebook.

timeseries.set_index('year').sort_index().plot.line()

Query processed 8.8 kB in a moment of slot time.

Load job c6b4d65a-4555-4efc-9f8b-7156f4c62835 is DONE. Open Job

<Axes: xlabel='year'>

pddf = timeseries.set_index('year').sort_index().to_pandas()
pddf

Conclusion: The Power of Hybrid Chaining#

By leveraging BigQuery DataFrames and the %%bqsql magic, you have built a powerful, interoperable pipeline that seamlessly transitions between SQL and Python.

This hybrid approach offers several key benefits:

• Optimal Tool Selection: Use SQL for what it does best (complex queries, window functions, regex extractions on large sets) and Python for what it does best (visualization, statistical analysis, ML, orchestrating workflow).

• Improved Readability: Instead of massive, unreadable SQL queries with dozens of CTEs, or long, complex Pandas method chains, you can split your pipeline into logical steps, alternating between SQL and Python.

• Seamless Scaling: The exact same %%bqsql code can scale from a tiny local Pandas DataFrame to billions of rows in a production BigQuery table. You only need to swap the initial local Pandas DataFrame with a BigQuery DataFrame reference.

Next Steps#

In addition to the %%bqsql cell magic, BigFrames also registers a BigQuery Accessor on standard Pandas DataFrames, allowing you to run SQL scalar functions directly on local pandas data.

For example, you can call powerful Google Cloud community UDFs from BigQuery Utils, BigFunctions, or CARTO Analytics Toolbox for BigQuery using df.bigquery.sql_scalar(...):