This post was originally published on this site

Data scientists and data engineers often find themselves caught between two worlds: SQL and Python. Some find SQL more intuitive, especially when combined with a powerful engine like BigQuery to process data at scale. Others find it easier to work in Python with its rich ecosystem of libraries and runtimes. Historically, using these languages together in one notebook required moving data from SQL results to in-memory and writing from Python memory to temporary tables for SQL to access.

To solve this friction, the Google Cloud team introduced SQL cells in Colab Enterprise. Now, we are expanding that seamless experience to the broader open-source ecosystem. With the %%bqsql IPython cell magic, you can now effortlessly chain data processing workloads across SQL and Python code cells.

Thanks to open-source packages like Jupyter, pandas, BigFrames, and the BigQuery sandbox, you can follow all steps in this guide for free* and without a credit card.

*See the BigQuery sandbox documentation for limitations.

Setting up your environment

To get started,

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

2. Set up a local Python development environment, or alternatively, open this notebook in Colab, which has a Python environment already installed. 

To set up a local python environment, see the steps on Google Cloud Documentation. Continue with the following steps, if you choose to set up a local python environment, else jump to the next section. 

3. Activate the venv you created in the previous step to isolate Python dependencies.

On Linux or macOS, use these commands (update to your preferred Python version):

code_block
<ListValue: [StructValue([('code', '. ./env/bin/activate'), ('language', ''), ('caption', )])]>

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

code_block
<ListValue: [StructValue([('code', 'pip install –upgrade jupyterlab bigframes python-calamine'), ('language', ''), ('caption', )])]>

5. Start Jupyter Lab.

code_block
<ListValue: [StructValue([('code', 'jupyter lab'), ('language', ''), ('caption', )])]>

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 (File > New > Notebook). Alternatively, download the notebook associated with this tutorial from the BigQuery DataFrames GitHub repository and open it.

Accessing and preparing local data

In this tutorial, you’ll analyze the USDA wheat data. Pandas will download the data, mimicking a typical local data analysis workflow.

code_block
<ListValue: [StructValue([('code', 'url = "https://www.ers.usda.gov/media/5706/wheat-data-all-years.xlsx?v=52690"'), ('language', ''), ('caption', )])]>

Next, read the data into a local pandas DataFrame. 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:

code_block
<ListValue: [StructValue([('code', 'import pandas as pdrnrndf = pd.read_excel(rn url,rn sheet_name="Table05",rn dtype_backend="pyarrow",rn engine="calamine",rn header=1, # Skip the first row.rn)rndf'), ('language', ''), ('caption', )])]>

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.

code_block
<ListValue: [StructValue([('code', 'df.columns = [name.replace("/", "") for name in df.columns]rndf'), ('language', ''), ('caption', )])]>

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.

code_block
<ListValue: [StructValue([('code', "full_rows = df[~df['Beginning stocks'].isna()]rnfull_rows"), ('language', ''), ('caption', )])]>

Initializing the BigQuery SQL magic

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.

code_block
<ListValue: [StructValue([('code', '%load_ext bigframes'), ('language', ''), ('caption', )])]>

Note: The extension is pre-loaded in BigQuery Studio and Colab environments.

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).

code_block
<ListValue: [StructValue([('code', 'import bigframes.pandas as bpdrnrnbpd.options.bigquery.project = "your-project-id-here"'), ('language', ''), ('caption', )])]>

Querying local pandas DataFrames with SQL

With the project configured, you can now run SQL queries directly against your local pandas DataFrame (full_rows) as if it were a table in BigQuery. Simply reference the variable name inside braces {full_rows} in your SQL query. You may be prompted for an authorization code, which you’ll obtain by following the link provided as part of the same message.

code_block
<ListValue: [StructValue([('code', '%%bqsqlrnSELECT * FROM {full_rows}'), ('language', ''), ('caption', )])]>

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 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:

code_block
<ListValue: [StructValue([('code', "%%bqsql yearlyrnSELECT *rnFROM {full_rows}rnWHERE STARTS_WITH(`Time period`, 'MY')"), ('language', ''), ('caption', )])]>

Now, you can chain another SQL operation. Reference the yearly BigFrames DataFrame that you just created, extract the year using SQL regular expressions, cast it to a timestamp, and save the results into a new BigFrames DataFrame named timeseries.

code_block
<ListValue: [StructValue([('code', "%%bqsql timeseriesrnSELECTrn * EXCEPT (`Marketing year 1`),rn TIMESTAMP(CONCAT(rn REGEXP_EXTRACT(`Marketing year 1`, r'([0-9]+)\/'),rn '-01-01')) AS `year`rnFROM {yearly}"), ('language', ''), ('caption', )])]>

Notice how you are building a chain from Python to SQL and back again.

Returning to Python for visualization

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.

code_block
<ListValue: [StructValue([('code', "timeseries.set_index('year').sort_index().plot.line()"), ('language', ''), ('caption', )])]>

Alternatively, download the time series as a pandas DataFrame to use with your visualization library of choice.

code_block
<ListValue: [StructValue([('code', "pddf = timeseries.set_index('year').sort_index().to_pandas()"), ('language', ''), ('caption', )])]>

Why a hybrid pipeline matters

By pairing BigQuery DataFrames with  %%bqsql magics, you have built a powerful, interoperable pipeline that seamlessly transitions between SQL and Python.

  • local pandas df and full_rows DataFrames

  • to SQL filter

  • to BigFrames yearly DataFrame

  • to SQL transform

  • to BigFrames timeseries DataFrame

  • to Python data visualization

  • to local pandas DataFrame.

This architecture offers key advantages:

  • Optimal tool selection: Use SQL for what it does best (heavy aggregations, window functions, and complex joins) and Python for what it does best (visualization, statistical modeling, and ML orchestration).

  • Improved code readability: Instead of writing massive SQL queries with dozens of common table expressions (CTEs), or doing complex aggregations using pandas APIs which are often convoluted compared to SQL, 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 and scaling up

Check out the other notebooks in the BigFrames API reference site. 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(...):

code_block
<ListValue: [StructValue([('code', 'import pandas as pdrnimport bigframes.pandas as bpd # Registers the accessorrnrnbpd.options.bigquery.project = "your-project-id"rndf = pd.DataFrame({"x": [1, 2, 3]})rnpandas_s = df.bigquery.sql_scalar("`bqutil`.fn.cw_setbit({x}, 2)")'), ('language', ''), ('caption', )])]>

While the BigQuery sandbox offers a powerful environment to test these hybrid Python-SQL workflows for free, some advanced features like BigQuery Machine Learning (BQML) are restricted. By connecting a billing account to your Google Cloud project, you can unlock advanced capabilities such as the bigframes.bigquery.ai.forecast function to predict time-series data using Google’s state-of-the-art foundational models directly from your SQL/Python chain.

code_block
<ListValue: [StructValue([('code', 'forecasted_pandas_df = (rn pddfrn .reset_index(drop=False)rn .bigquery.ai.forecast(rn data_col="Production",rn timestamp_col="year",rn horizon=10,rn )rn)rnrn# Plot the resultsrnforecasted_pandas_df_sorted = forecasted_pandas_df.sort_values(by='forecast_timestamp')rnplt.plot(pddf.index, pddf['Production'], label='Real Production', color='blue')rnplt.plot(forecasted_pandas_df_sorted['forecast_timestamp'], forecasted_pandas_df_sorted['forecast_value'], label='Forecasted Production', color='red', linestyle='–')rnplt.fill_between(rn forecasted_pandas_df_sorted['forecast_timestamp'],rn forecasted_pandas_df_sorted['prediction_interval_lower_bound'],rn forecasted_pandas_df_sorted['prediction_interval_upper_bound'],rn color='red',rn alpha=0.2,rn label='Confidence Interval'rn)rn# …rnplt.show()'), ('language', ''), ('caption', )])]>

image1

The BigFrames team would love to hear your feedback on the hybrid Python-SQL experience: