BigQuery’s %%bqsql Magic Bridges SQL and Python Seamlessly

Data scientists and engineers often paddle between two worlds: SQL and Python. Each has its strengths, but switching between them in a notebook has historically been like navigating a swamp in a leaky boat—possible, but messy. Google Cloud’s new %%bqsql IPython cell magic changes that, letting you chain SQL and Python cells effortlessly.

Built on open-source tools like Jupyter, pandas, and BigFrames, this magic works with the BigQuery sandbox for free—no credit card required. Let’s dive in.

Setting Up Your Environment

To get started, enable the BigQuery sandbox and note your project ID. Then set up a local Python environment or use this Colab notebook.

1. Enable the BigQuery sandbox.
2. Install Jupyter, bigframes, and python-calamine: pip install --upgrade jupyterlab bigframes python-calamine
3. Start Jupyter Lab and create a new notebook.

Accessing and Preparing Local Data

We’ll analyze USDA wheat data. First, load it into a pandas DataFrame with pyarrow dtype backend for seamless SQL handoff.

import pandas as pd
df = pd.read_excel(url, sheet_name="Table05", dtype_backend="pyarrow", engine="calamine", header=1)

Clean column names for SQL compatibility, then filter out missing rows.

Initializing the BigQuery SQL Magic

Load the bigframes extension and set your project ID:

%load_ext bigframes
import bigframes.pandas as bpd
bpd.options.bigquery.project = "your-project-id"

Querying Local DataFrames with SQL

Now you can run SQL directly on your local DataFrame by referencing it in braces:

%%bqsql
SELECT * FROM {full_rows}

The magic uploads the DataFrame as a temporary table and runs the query on BigQuery’s engine.

Chaining SQL and Python

Save SQL results to a variable for chaining. For example, filter yearly entries:

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

Then transform further with SQL and save to timeseries:

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

Back in Python, visualize directly:

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

Why Hybrid Pipelines Matter

• Optimal tool selection: SQL for heavy lifting, Python for visualization and ML.
• Improved readability: Break complex pipelines into logical steps.
• Seamless scaling: Same code works from local DataFrames to billions of rows in BigQuery.

This magic is a game-changer for developers who want the best of both worlds without the friction. Check out the BigFrames docs for more.

Original announcement published on Google Cloud.