用 %%bqsql 串起 BigQuery 与 Python：在一个 Notebook 里完成混合数据流水线

数据分析经常在两种语言之间来回切换：SQL 适合连接、聚合、窗口计算和大规模扫描，Python 则拥有成熟的可视化、统计建模与机器学习生态。过去，把两者放进同一个 Notebook，往往意味着手动下载查询结果、上传临时表，并维护两套中间数据。BigQuery DataFrames 提供的 %%bqsql IPython cell magic，把这些交接动作收进了 Notebook 工作流。

%%bqsql 实际解决了什么

加载 BigFrames 扩展后，SQL 单元格可以通过 {变量名} 引用 Python 环境中的 pandas DataFrame。BigFrames 会把本地数据隐式上传为临时表，再交给 BigQuery 执行查询。

反方向也成立：给 %%bqsql 指定一个目标变量名，查询结果就会成为 BigQuery DataFrame。它提供接近 pandas 的操作方式，但数据和主要计算仍留在 BigQuery 引擎中。

一条典型链路因此可以写成：

本地 pandas DataFrame
  -> SQL 清洗与筛选
  -> BigQuery DataFrame
  -> SQL 聚合或时间字段转换
  -> Python 绘图、建模或编排
  -> 必要时再转成本地 pandas DataFrame

这里的关键不是“用 SQL 替代 Python”或反过来，而是让每一步使用更合适的工具。复杂连接和窗口函数通常用 SQL 更直接；绘图、统计检验和模型调用则更适合留在 Python。

搭建可运行的 Notebook 环境

本地实践需要一个 Google Cloud 项目 ID。BigQuery sandbox 可以用于试验，并且不要求信用卡，但仍有配额和功能限制；查询资源也必须归属到一个项目。

可以这样创建隔离环境。下面假设机器已经安装 Python 3：

python3 -m venv env
. ./env/bin/activate
python -m pip install --upgrade pip
pip install --upgrade jupyterlab bigframes python-calamine
jupyter lab

在 Jupyter Lab 中新建 Notebook，然后加载扩展并配置项目。把 your-project-id 改成自己的项目 ID：

%load_ext bigframes

import bigframes.pandas as bpd

bpd.options.bigquery.project = "your-project-id"

在本地环境中，首次访问 BigQuery 时可能出现认证提示。应按照 Notebook 给出的链接完成授权。若未显式设置项目，BigFrames 会尝试从 Application Default Credentials 等环境信息中发现项目，但生产 Notebook 最好明确配置，避免查询记到错误项目。

从 pandas 进入 SQL，再回到 Python

下面是一段可以直接放进 Notebook 改造的完整流程。示例读取 USDA 小麦年度数据；外部文件地址可能变化，因此在长期项目中应把输入文件固定到受控对象存储，并记录版本。

先由 pandas 下载和整理 Excel 数据：

import pandas as pd

url = "https://www.ers.usda.gov/media/5706/wheat-data-all-years.xlsx?v=52690"

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

# 清理不便直接用于 SQL 标识符的字符。
df.columns = [name.replace("/", "") for name in df.columns]
full_rows = df[~df["Beginning stocks"].isna()]

print(full_rows.head())

dtype_backend="pyarrow" 有助于保持 NULL 语义和 BigQuery 类型映射的一致性。列名也值得提前规范：虽然 BigQuery 支持灵活列名，但斜杠等特殊字符会增加引用和跨工具处理的复杂度。

接下来新增一个 SQL 单元格，直接引用本地变量 full_rows：

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

yearly 不是普通的内存 pandas DataFrame，而是查询结果对应的 BigQuery DataFrame。继续添加一个 SQL 单元格，把年份提取为时间戳：

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

然后回到 Python 单元格绘图：

chart = (
    timeseries
    .set_index("year")
    .sort_index()
    .plot.line(y="Production", title="Annual wheat production")
)

BigFrames 的 pandas 风格 API 会尽量把计算下推到 BigQuery。绘图时只需把生成图表所需的数据带回 Notebook，而不必在一开始就下载完整数据集。

如果后续使用的 Python 库只接受原生 pandas 对象，可以在数据已经筛选或聚合后显式物化：

local_timeseries = (
    timeseries
    .set_index("year")
    .sort_index()
    .to_pandas()
)

print(type(local_timeseries))
print(local_timeseries.head())

to_pandas() 是一道重要边界：它会把数据下载到 Notebook 内存。对于大表，应先检查行数、列数和预计结果规模，并尽量在 BigQuery 侧完成过滤与聚合。

让混合流水线保持可控

这种模式能减少胶水代码，但不会自动消除成本、类型和生命周期问题。落地时应重点检查以下事项：

• 明确执行位置：普通 pandas 操作发生在本地；BigQuery DataFrame 的可下推操作主要发生在 BigQuery。调用 to_pandas() 后，内存和网络成本会转移到 Notebook。
• 控制扫描量：生产查询应选择必要列、尽早过滤，并利用分区和聚簇。Notebook 写得短，不代表查询扫描的数据少。
• 规范类型与列名：输入 pandas 数据建议使用 PyArrow dtype，并在进入 SQL 前处理特殊列名、时区、NULL 和混合类型。
• 管理临时数据：本地 DataFrame 被 SQL 引用时需要隐式上传。不要把密钥、个人数据或受监管字段随意传入临时分析环境。
• 固定依赖版本：团队共享 Notebook 时，应维护 requirements.txt 或锁文件，避免 BigFrames、pandas 与 Jupyter 升级后出现行为差异。
• 区分试验与生产：sandbox 适合学习和原型验证，但部分高级能力受到限制。启用计费或使用 BigQuery ML 等功能前，应检查区域、权限、配额和费用预算。

%%bqsql 最适合处理那些本来就需要 SQL 和 Python 协作的任务。小型纯内存数据没有必要为了形式上传到 BigQuery；而对大规模连接、聚合和时间序列处理，它能让 Notebook 保持分步可读，同时把重计算留在数据仓库中。采用时可以从一条现有分析链路开始，只替换最昂贵或最难读的步骤，并把每次跨越本地内存与 BigQuery 的边界写清楚。