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