Bridging the gap between SQL and Python with BigQuery and the %%bqsql magic

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’, <wagtail.rich_text.RichText object at 0x7fbfc3d52b80>)])]> 4. Install the Jupyter, bigframes, and python-calamine packages. code_block <ListValue: [StructValue([(‘code’, ‘pip install –upgrade jupyterlab bigframes python-calamine’), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3d521c0>)])]> 5. Start Jupyter Lab. code_block <ListValue: [StructValue([(‘code’, ‘jupyter lab’), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3d52280>)])]> 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’, <wagtail.rich_text.RichText object at 0x7fbfc3d52fd0>)])]> 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 pd\r\n\r\ndf = pd.read_excel(\r\n url,\r\n sheet_name=“Table05”,\r\n dtype_backend=“pyarrow”,\r\n engine=“calamine”,\r\n header=1, # Skip the first row.\r\n)\r\ndf’), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3077670>)])]> 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]\r\ndf’), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc30778e0>)])]> 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()]\r\nfull_rows”), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3077340>)])]> 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’, <wagtail.rich_text.RichText object at 0x7fbfc3077a30>)])]> 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 bpd\r\n\r\nbpd.options.bigquery.project = “your-project-id-here”’), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3077f10>)])]> 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’, ‘%%bqsql\r\nSELECT * FROM {full_rows}’), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3077a90>)])]> 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 yearly\r\nSELECT *\r\nFROM {full_rows}\r\nWHERE STARTS_WITH(Time period, ‘MY’)”), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3077e20>)])]> 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 timeseries\r\nSELECT\r\n * EXCEPT (Marketing year 1),\r\n TIMESTAMP(CONCAT(\r\n REGEXP_EXTRACT(Marketing year 1, r’([0-9]+)\/’),\r\n ‘-01-01’)) AS year\r\nFROM {yearly}”), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3077ee0>)])]> 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’, <wagtail.rich_text.RichText object at 0x7fbfc3077820>)])]> 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’, <wagtail.rich_text.RichText object at 0x7fbfc30779d0>)])]> 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 pd\r\nimport bigframes.pandas as bpd # Registers the accessor\r\n\r\nbpd.options.bigquery.project = “your-project-id”\r\ndf = pd.DataFrame({“x”: [1, 2, 3]})\r\npandas_s = df.bigquery.sql_scalar("bqutil.fn.cw_setbit({x}, 2)")’), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc3077fa0>)])]> 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 = (\r\n pddf\r\n .reset_index(drop=False)\r\n .bigquery.ai.forecast(\r\n data_col=“Production”,\r\n timestamp_col=“year”,\r\n horizon=10,\r\n )\r\n)\r\n\r\n# Plot the results\r\nforecasted_pandas_df_sorted = forecasted_pandas_df.sort_values(by='forecast_timestamp')\r\nplt.plot(pddf.index, pddf['Production'], label='Real Production', color='blue')\r\nplt.plot(forecasted_pandas_df_sorted['forecast_timestamp'], forecasted_pandas_df_sorted['forecast_value'], label='Forecasted Production', color='red', linestyle='–')\r\nplt.fill_between(\r\n forecasted_pandas_df_sorted['forecast_timestamp'],\r\n forecasted_pandas_df_sorted['prediction_interval_lower_bound'],\r\n forecasted_pandas_df_sorted['prediction_interval_upper_bound'],\r\n color='red',\r\n alpha=0.2,\r\n label='Confidence Interval'\r\n)\r\n# …\r\nplt.show()’), (’language’, ‘’), (‘caption’, <wagtail.rich_text.RichText object at 0x7fbfc30776a0>)])]> The BigFrames team would love to hear your feedback on the hybrid Python-SQL experience: Email: bigframes-feedback@google.com Issues: File bug reports or feature requests on the open-source BigFrames repository. Updates: To receive news and updates, subscribe to the BigFrames email list. Learn more: Read the BigFrames API reference and user guides in the documentation.