Google Cloud Blog
Follow
Bridging the gap between SQL and Python with BigQuery and the %%bqsql magic
Data scientists and engineers often struggle with integrating SQL and Python in their workflows. Historically, this involved cumbersome data transfers between the two environments. Google Cloud has addressed this by introducing SQL cells in Colab Enterprise and now expanding this to the open-source ecosystem with the %%bqsql IPython cell magic. This allows seamless chaining of SQL and Python code in a single notebook. The process can be followed for free using open-source packages like Jupyter, pandas, and BigFrames, along with the BigQuery sandbox. Setup involves enabling the BigQuery sandbox and configuring a local Python environment or using Colab. Local data, like USDA wheat data, can be loaded into pandas DataFrames and prepared for SQL processing. The %%bqsql magic enables BigQuery to directly query local pandas DataFrames, treating them as temporary tables. The true power lies in chaining operations; SQL query results can be saved as BigFrames DataFrames for subsequent Python use or further SQL transformations. This hybrid pipeline allows optimal tool selection, separating heavy data processing in SQL from visualization and modeling in Python. Code readability improves by breaking down complex queries into logical steps, and the solution seamlessly scales from local data to massive BigQuery tables. Beyond %%bqsql, BigFrames offers other integration features, and connecting a billing account unlocks advanced capabilities like BQML. Feedback on this hybrid experience can be shared with the BigFrames team via email or by filing issues on their repository.