BigQuery ML：大规模数据建模

可视化演示

对比传统ML管道与BigQuery ML的SQL原生建模流程，展示数据仓库内原地训练如何消除数据搬运开销，实现PB级数据高效建模。

初识 BigQuery ML：数据仓库里的智能厨房

小林第一次打开 BigQuery ML 的文档时，眼前一亮：原来不必等数据导出、不用开 Python 环境，直接在公司的数据仓库里就能训练模型。如果数据仓库是一座“中央厨房”，那么 BigQuery ML 就是把“锅碗瓢盆、菜谱和厨师”全部请进了厨房里——机器学习不再是数据科学家的专属小灶，而是所有会用 SQL 的人都能点的一道“标准化菜品”。

从技术角度看，BigQuery ML 是 Google Cloud 提供的一项全托管机器学习服务。它深度集成在 BigQuery 数据仓库中，让你可以用标准 SQL 语句完成模型创建、训练、评估和预测。所谓“全托管”，指的是底层的分布式集群、资源调度、模型生命周期这些复杂事情都由云平台接管，你只需要关心业务问题。这就像去餐厅吃饭，你不需要关心后厨有多少灶台、厨师几点下班，只要点菜就好。

更关键的是，“数据不搬家”的原则。传统机器学习必须把数据从仓库导出到训练环境，而 BigQuery ML 直接在存储数据的地方建模，数据在查询引擎内部流转，既省去了传输时间，又避免了数据在导出过程中泄露的风险。用一句话概括：训练的模型，是从数据里“长”出来的。整个学习过程可以用一个很抽象的公式描述：

$$\displaystyle M^* = \arg\min_{M} \mathcal{L}(M; D)$$

1. 最优模型 $M^*$：模型就是“菜谱”，里面有各种调味料的用量参数。$M^*$ 表示“最优的那本菜谱”。

$$M^*$$

2. 搜索过程 $\arg\min_{M}$：意思是从所有候选菜谱中，挑出“做出来的菜最难吃的那道误差最小”的那一本。

$$\arg\min_{M}$$

3. 损失函数 $\mathcal{L}$：它量化“菜谱做出来的菜”和“顾客实际想要的菜”之间的差距，差距越小越好。

$$\mathcal{L}(M; D)$$

4. 训练数据 $D$：数据 $D$ 就是仓库里积累的历史订单——顾客过去的购买记录。

$$D$$

小林这下明白了：BigQuery ML 帮他把“在数据中找到最优菜谱”这件难事，包装成了一个简单的 SQL 操作。他不再需要自己搭训练服务器，只要会用 SQL，就能让这座中央厨房自动完成整套学习流程。从那以后，他再也不必深更半夜对着 CSV 文件发愁了。

模型类型全家福：不只是招牌菜

一家合格的餐厅，不可能只有一道菜。小林发现，BigQuery ML 的“菜单”也很丰富：回归问题有线性回归，分类问题有逻辑回归和决策树，时间序列预测有 ARIMA_PLUS，甚至还有矩阵分解和深度神经网络（DNN）。这和传统机器学习库能提供的模型几乎不相上下，但区别在于：这里每个模型都可以用「在 SQL 里指定一个参数」的方式端上桌。

其中最基本的“招牌菜”是线性回归。它的思想非常直白：假设我们要预测牛奶销量，那么销量可以由几个因素线性组合出来，比如当天的促销力度、历史销量、气温等。每个因素乘上一个权重，再加上一个基础值，就得到预测结果：

$$\displaystyle \hat{y} = w^{T}x + b$$

1. 预测值 $\hat{y}$：带小帽子的 $y$，表示“模型估计出来的销量”，而不是真实销量。

$$\hat{y}$$

2. 特征向量 $x$：把所有影响因素排成一列，比如 $x_1$ 是促销力度、$x_2$ 是气温。它就是菜谱里的“食材清单”。

$$x = (x_1, x_2, \dots, x_m)$$

3. 权重向量 $w^{T}$：每个食材放多少克，由权重决定。$w^{T}$ 表示把权重向量转置后与特征做内积，也就是“加权求和”。

$$w^{T}x = w_1 x_1 + w_2 x_2 + \dots + w_m x_m$$

4. 偏置 $b$：即使所有食材用量都是零，烤箱也要有基础温度。$b$ 就是那个“基础温度”。

$$b$$

在 BigQuery ML 里，想换“菜系”只需在 OPTIONS(model_type='...') 里改一个值：LINEAR_REG 是线性回归，LOGISTIC_REG 是逻辑回归，ARIMA_PLUS 是时间序列。小林笑着想：这就像在大众点评上点菜，你选“川菜”还是“粤菜”，只是勾选不同标签，上菜流程完全一样。

SQL 建模流程：从下单到上菜

小林第一次用 BigQuery ML 训练模型时，被它的简洁震惊到了。设想你走进一家“智能厨房”，点菜的方式不是吆喝，而是递上一张写着流程的卡片：先告诉厨房“我要学做预测销量这道菜”（CREATE MODEL），然后厨房会端出几盘样品让你试吃（ML.EVALUATE），最后，以后每周你都能直接“打包带走”预测结果（ML.PREDICT）。

整个建模流程，就是三条 SQL 语句：

其中最重要的 “下单” 语句，本质上是让 BigQuery ML 在后台完成一次优化：通过不断调整模型参数，让预测值与真实值的差距尽可能小。衡量这个“差距”最常用的指标之一，就是均方误差（MSE）：

$$\displaystyle MSE = \frac{1}{n} \sum_{i=1}^{n} (y_i - \hat{y}_i)^2$$

1. 样本总数 $n$：用来训练模型的数据行数，也就是厨房试菜的顾客人数。

$$n$$

2. 单样本偏差 $y_i - \hat{y}_i$：第 $i$ 个顾客的真实评价 $y_i$ 与模型预测 $\hat{y}_i$ 的差距。

$$y_i - \hat{y}_i$$

3. 平方 $(y_i - \hat{y}_i)^2$：为了防止正负偏差相互抵消，先平方再计算。偏差 3 分会被放大成 9 分，惩罚“离谱的预测”。

$$(y_i - \hat{y}_i)^2$$

4. 平均 $\frac{1}{n}\sum_{i=1}^{n}$：对所有样本的平方误差求和再取平均，得到一个整体的“平均翻车程度”。

$$\frac{1}{n} \sum_{i=1}^{n}$$

对小林来说，这种“点菜式”工作流的震撼在于：以前从训练到上线要经过导出、清洗、训练、调参、部署、写接口等七八个环节，现在全被浓缩进了这三条 SQL。厨房里的服务员（BigQuery 引擎）会帮你把后端一切打理妥当，你要做的只是“说清楚需求”。

-- 第一步：下单——训练模型CREATE MODEL `sales.sales_forecast`OPTIONS(model_type='LINEAR_REG', input_label_cols=['amount'])AS SELECT date, promotion, temperature, amount FROM `sales.orders`; -- 第二步：试吃——评估模型质量SELECT * FROM ML.EVALUATE(MODEL `sales.sales_forecast`); -- 第三步：打包——预测未来销量SELECT date, predicted_amount FROM ML.PREDICT(MODEL `sales.sales_forecast`, ...);

特征工程：厨房里的备菜刀工

再好的食材，不经过刀工处理也很难下锅。小林发现，数据仓库里的原始字段往往“块头”太大：比如“购买金额”取值范围从几块到上万块，而“促销标志”只有 0 和 1。如果直接把这样的数据扔给模型，数值大的特征会像一头大象一样把其他特征踩在脚下。在机器学习里，这种“处理原始数据、让它更好被模型消化”的手段，就叫特征工程——相当于厨房里的备菜与刀工。

BigQuery ML 的体贴之处在于，它把这套刀工也搬进了 SQL 世界。你可以在 CREATE MODEL 里用 TRANSFORM 子句声明特征的加工方式，也可以直接对数据做 SQL 变换。最经典的一招是标准化（Standardization）：把每个特征减去均值、除以标准差，让所有特征都变成“均值 0、方差 1”的规范身材：

$$\displaystyle x' = \frac{x - \mu}{\sigma}$$

1. 标准化后的特征 $x'$：处理完的“规范身材”特征值，以后模型看到的是它，而不是原始数值。

$$x'$$

2. 均值 $\mu$：该特征在所有样本上的平均值，相当于“大家平均的身材”。

$$\mu = \frac{1}{n} \sum_{i=1}^{n} x_i$$

3. 标准差 $\sigma$：衡量特征值波动的幅度，相当于“人群身高的离散程度”。

$$\sigma = \sqrt{\frac{1}{n} \sum_{i=1}^{n} (x_i - \mu)^2}$$

除了标准化，BigQuery ML 还支持分箱（把连续值切成“低/中/高”几个档位）、取对数压缩长尾、以及自动特征工程——对于一些模型类型，系统会自动做缩放和交叉特征。小林理解到：备菜刀工的好坏，直接决定了菜品（模型）的天花板。哪怕你用的是同一个 LINEAR_REG，特征处理好坏，预测效果可能天差地别。

海量数据处理：大型厨房的流水线

小林供职的连锁超市，一天就能产生上亿条销售记录。刚开始他担心：这么多数据，BigQuery ML 不会把仓库“撑爆”吗？后来他才知道，这座中央厨房的后厨远比想象中庞大——它由成千上万台机器组成的“流水线”协作处理，数据本身也被切成了许多“分片”（Shard）存储在不同的机器上。

这就是 BigQuery 的分布式计算架构：查询和训练任务被自动拆分成无数小任务，分派到集群中的各台机器上并行执行，最后再把结果汇总。这就像大型中央厨房同时开一百个灶台，有的切菜、有的焯水、有的掌勺，效率自然远超一个人独撑。更妙的是，这个集群规模会根据任务量弹性扩展：数据多时自动调更多机器，空闲时自动缩容，你不用为峰值预先买服务器。

不过，并行计算也有它的“物理极限”。计算机科学家 Amdahl 早就发现，一个任务里如果有一部分必须串行执行，那么加再多机器，加速效果也会碰到天花板。这个规律叫 Amdahl 定律：

$$\displaystyle S_{speedup} = \frac{1}{(1-p) + \frac{p}{N}}$$

1. 加速比 $S_{speedup}$：加了多少台机器后，任务变快了几倍。

$$S_{speedup}$$

2. 并行占比 $p$：任务中能同时让多台机器一起干活的部分所占比例。比如做饭时，切 100 个土豆可以分给 100 个人，这部分就是并行。

$$p$$

3. 串行部分 $1-p$：必须一个人干完的环节，比如最后“下锅调味”只能由主厨把关。

$$1-p$$

4. 机器数 $N$：后厨同时开工的灶台数。注意即使 $N$ 趋于无穷，加速比也不会无限增大。

$$N$$

明白了这个道理，小林在写训练 SQL 时开始有意识地做分区裁剪、只选需要的列。BigQuery ML 的分布式引擎虽然能扛住海量数据，但“聪明地喂数据”永远比“无脑堆机器”更省钱、更快。

对比传统 ML 管线：从搬菜回家到直接点菜

在认识 BigQuery ML 之前，小林走的是标准“回家做饭”路线：先从中央仓库（数仓）把食材搬到家里（本地或 Spark 集群），再自己洗菜切菜（特征工程）、开火做饭（模型训练）、最后端盘上桌（部署 API）。每一步之间都隔着“搬运”——不仅是数据的复制，还有环境转换的摩擦成本。

搬食材是有成本的。数据越多、搬得越远，成本越高。这个直觉可以写成一个小公式：

$$\displaystyle C_{move} = \alpha \cdot V_{data} \cdot d$$

1. 搬运成本 $C_{move}$：传输、存储副本、环境迁移带来的总开销。

$$C_{move}$$

2. 数据量 $V_{data}$：要搬的数据体积。数据是“重物”，搬 100 斤自然比搬 1 斤累多了。

$$V_{data}$$

3. 搬运距离 $d$：源系统和目标系统之间的“距离”——可能是网络传输，也可能是格式转换的复杂度。

$$d$$

4. 系数 $\alpha$：单位搬运成本，取决于网络带宽、磁盘 IO 等硬件条件。

$$\alpha$$

与传统 ML Pipeline 相比，BigQuery ML 的优势不只是省了搬运费。它同时消灭了“独立环境”：不需要单独开一台训练服务器，不用手动安装 TensorFlow 或 XGBoost，也不用操心线上模型怎么部署；因为模型本体就存放在数据仓库里，通过 ML.PREDICT 即可直接调用。流程从“导出→清洗→训练→部署→运维”五步，缩短为“建模型→评估→预测”三步，迭代速度自然大幅提升。就好比以前吃顿饭要自己买菜、洗菜、切菜、炒菜、洗碗，现在直接对着中央厨房下单，动嘴就行。

实战落地场景：餐厅的经营日常

掌握了一门手艺，终究要回到经营。小林用 BigQuery ML 解决了不少实际业务问题，其中最有成就感的是销售预测。超市总部每周都要决定各门店的订货量，以前靠经验拍脑袋，经常出现鲜奶缺货或面包滞销。现在，他用日期、历史销量、促销计划做特征，训练了一个时间序列模型，每周自动跑一次预测，准确率明显提升。

这类预测问题的核心，是找到销量与各影响因素之间的函数关系。用一个简化公式来表达：

$$\displaystyle \widehat{Sales}_{t+1} = w_1 \cdot Sales_t + w_2 \cdot Promo_t + b$$

1. 下一期预测 $\widehat{Sales}_{t+1}$：想预测的“下一周的牛奶销量”。

$$\widehat{Sales}_{t+1}$$

2. 当前销量带动 $w_1 \cdot Sales_t$：销量往往有惯性，上周卖得好，下周通常也不错。$w_1$ 就是这种惯性的强度。

$$w_1 \cdot Sales_t$$

3. 促销拉动 $w_2 \cdot Promo_t$：促销投入对销量的边际拉动作用。$w_2$ 越大说明促销越有效。

$$w_2 \cdot Promo_t$$

4. 基础销量 $b$：不打折、没天气影响时，雷打不动的“底量”。

$$b$$

除了销售预测，小林还把同样的套路用在了用户画像上：用逻辑回归判断某个顾客下月是否可能流失；用异常检测模型盯住每日交易金额的离群值，及时发现有问题的订单；配合其他服务做实时推荐，在用户点开 App 的瞬间返回“猜你喜欢”。这些场景的共同点是：数据就在 BigQuery 里，SQL 一遍，模型即刻可用。对分析师和数据工程师来说，这简直是“一鱼多吃”。

最佳实践：厨房管理智慧

餐厅好不好，除了菜色，还看管理。BigQuery ML 用起来简单，但要在企业里稳定、安全、省成本地长期跑，小林总结了几条“厨房管理规范”。

第一是权限与安全。数据是餐厅的命根子，必须用 IAM（身份与访问管理）精细化管控：谁能看原始数据、谁能训练模型、谁能调用预测接口，都要按角色隔离。别让一个实习生权限大到能把整个客户表导出去。第二是成本管理。BigQuery 按扫描数据量和槽位（Slot）计费，训练模型前尽量先做特征裁剪和分区过滤，同时可以给项目设置槽位配额，防止某个同事“一键训练全表”把账单跑爆。第三是模型评估优化：每次训练完，别急着上线，用 ML.EVALUATE 看几个关键指标，比如分类任务里经常用到的 F1 分数：

$$\displaystyle F_1 = 2 \cdot \frac{P \cdot R}{P + R}$$

1. 精确率 $P$：模型预测“是”的样本里，真正“是”的比例。好比推荐的菜里有多少是顾客真点的。

$$P = \frac{TP}{TP + FP}$$

2. 召回率 $R$：所有真正“是”的样本里，被模型找出来的比例。好比所有想吃的菜里，菜单上覆盖了多少。

$$R = \frac{TP}{TP + FN}$$

3. 调和平均 $F_1$：对 $P$ 和 $R$ 取加权平均，防止某一项特别差时总分还很高。

$$F_1 = 2 \cdot \frac{P \cdot R}{P + R}$$

最后，别忘了把模型训练纳入 CI/CD 流程：用定时任务或 Cloud Build 在每周固定时间重训模型，并用自动化脚本评估新模型是否优于线上版本，再决定是否切换。这样，这座“中央厨房”就从“能做菜”升级成了“稳定运营的星级餐厅”——既有拿手菜，又有严格的管理制度。

AI 应用场景

Python 演示

"""BigQuery ML 示例：用 SQL 在数据仓库内训练并预测销量。"""from google.cloud import bigquery PROJECT = "my-gcp-project"REGION = "US"DATASET = "sales"MODEL = "sales_forecast" client = bigquery.Client(project=PROJECT) # 1. 确保数据集存在dataset_ref = bigquery.DatasetReference(PROJECT, DATASET)dataset = bigquery.Dataset(dataset_ref)dataset.location = REGIONclient.create_dataset(dataset, exists_ok=True)print("数据集就绪") # 2. 用 CREATE MODEL 训练时间序列模型（ARIMA_PLUS）sql_train = """CREATE OR REPLACE MODEL `{project}.{dataset}.{model}`OPTIONS(  model_type='ARIMA_PLUS',  time_series_timestamp_col='sale_date',  time_series_data_col='amount') ASSELECT sale_date, amountFROM `{project}.{dataset}.daily_sales`""".format(project=PROJECT, dataset=DATASET, model=MODEL)client.query(sql_train).result()print("模型训练完成") # 3. 用 ML.EVALUATE 查看模型质量sql_eval = """SELECT * FROM ML.EVALUATE(  MODEL `{project}.{dataset}.{model}`,  (    SELECT sale_date, amount    FROM `{project}.{dataset}.daily_sales`  ))""".format(project=PROJECT, dataset=DATASET, model=MODEL)for row in client.query(sql_eval).result():    print("评估结果:", dict(row)) # 4. 用 ML.PREDICT 预测未来 7 天销量sql_predict = """SELECT * FROM ML.PREDICT(  MODEL `{project}.{dataset}.{model}`,  (    SELECT CURRENT_DATE() + i AS sale_date    FROM UNNEST(GENERATE_ARRAY(1, 7)) AS i  ))""".format(project=PROJECT, dataset=DATASET, model=MODEL)for row in client.query(sql_predict).result():    print("预测值:", row["forecast_value"])

1. client = bigquery.Client(project=PROJECT)建立与 BigQuery 的连接，后续所有 SQL 都发往这里执行。
2. client.create_dataset(dataset, exists_ok=True)确保训练使用的数据集存在，exists_ok=True 表示已存在时也不报错。
3. model_type='ARIMA_PLUS'指定使用 BigQuery ML 内置的时间序列模型，适合销售预测这类“按时间变化”的数据。
4. sql_train` 中的 `CREATE OR REPLACE MODEL训练模型的核心 SQL，数据不发生任何迁移，原地完成学习。
5. client.query(sql_train).result()同步等待训练完成，result() 会阻塞直到查询结束。
6. ML.EVALUATE(...)用保留或全量数据评估模型误差指标，相当于“试吃环节”。
7. ML.PREDICT(...)传入未来日期进行预测，forecast_value 是 ARIMA_PLUS 输出的预测列名。

常见误区

面试题