BigQuery 数据分析：无服务器数仓与 ML
BigQuery 数据分析完全指南：无服务器数仓与内置机器学习
BigQuery 是 Google Cloud 推出的全托管、无服务器数据仓库，能让你在几秒内分析 TB 甚至 PB 级数据，且无需管理任何基础设施。本教程面向初学者，从零开始带你掌握 BigQuery 数据分析与内置的 BigQuery ML 机器学习功能。
1. 理解 BigQuery 的无服务器数仓本质
传统数据仓库需要预先分配计算和存储资源，处理扩容、缩容、索引维护等繁重工作。BigQuery 将存储与计算完全分离，你只需为实际扫描的数据量和使用的计算资源付费，无需配置任何服务器。
- 无服务器：没有节点、实例概念，系统自动处理分布式查询和资源调度。
- 列式存储：数据按列压缩存储，分析查询只需读取相关列，极大降低 I/O 和成本。
- 标准 SQL：兼容 ANSI SQL，多数分析人员可直接上手。
- 内置机器学习：通过 SQL 语句即可训练和部署模型，无需导出数据或额外迁移。
2. 准备工作：访问 BigQuery 控制台
开始前，你需要一个 Google Cloud 项目并启用 BigQuery API（大多数项目默认启用）。直接访问 console.cloud.google.com/bigquery 即可进入 BigQuery 工作室。
2.1 认识 BigQuery 工作室界面
- 资源管理器：左侧显示项目、数据集和表。你可以固定、搜索并查看表的 schema。
- 查询编辑器：编写和执行 SQL 的地方。
- 结果面板：展示查询结果、执行计划及作业历史。
- 数据洞察：自动生成图表，快速探索数据分布。
3. 第一个查询：探索公共数据集
Google 提供了大量公共数据集，如 bigquery-public-data。以下示例查询纽约市出租车行程记录。
SELECT
pickup_datetime,
trip_distance,
fare_amount,
tip_amount
FROM
`bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`
WHERE
pickup_datetime BETWEEN '2015-01-01' AND '2015-01-07'
LIMIT 10;
点击“运行”，BigQuery 会自动分配计算资源，几秒内返回结果。界面顶部会显示扫描的数据量，帮助你预估成本。
3.1 关键概念：数据集、表、视图
- 数据集：容纳表、视图、机器学习模型的容器，通常对应一个业务领域或环境（开发 / 生产）。
- 表：数据物理存储的单位。BigQuery 支持原生表（从文件或流加载）、外部表（查询 Cloud Storage 或 Bigtable 的数据）等。
- 视图：保存的 SQL 查询，不存储数据，每次查询实时计算。
4. 数据分析核心 SQL 技术
BigQuery 支持完整的标准 SQL 语法，并扩展了实用函数。掌握以下要点即可应对大多数分析场景。
4.1 聚合与分组
计算每日行程总量和平均费用：
SELECT
DATE(pickup_datetime) AS trip_date,
COUNT(*) AS total_trips,
AVG(fare_amount) AS avg_fare,
SUM(tip_amount) / SUM(fare_amount) * 100 AS tip_percentage
FROM
`bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`
WHERE
pickup_datetime BETWEEN '2015-01-01' AND '2015-01-31'
GROUP BY
trip_date
ORDER BY
trip_date;
4.2 窗口函数
窗口函数让你在不折叠行的前提下进行排名、滚动聚合等操作。例如，找出每天消费最高的一笔行程：
SELECT
pickup_datetime,
trip_distance,
fare_amount,
ROW_NUMBER() OVER(PARTITION BY DATE(pickup_datetime) ORDER BY fare_amount DESC) AS rank
FROM
`bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`
WHERE
DATE(pickup_datetime) BETWEEN '2015-01-01' AND '2015-01-05';
常用窗口函数：RANK()、LAG()、SUM(...) OVER(ORDER BY ... ROWS BETWEEN ...)。
4.3 处理嵌套与重复数据
BigQuery 原生支持 STRUCT 和 ARRAY 列，适合存储 JSON 或事件日志。使用 UNNEST 展开数组：
SELECT
visitor_id,
hit.product.v2ProductName AS product_name
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) AS hit
WHERE
_TABLE_SUFFIX = '20170701'
LIMIT 100;
4.4 时间分区与聚类
为优化性能和成本，通常在创建表时指定分区列（如日期）和聚簇列（如用户 ID）。示例建表语句：
CREATE OR REPLACE TABLE my_dataset.trips_partitioned
PARTITION BY DATE(pickup_datetime)
CLUSTER BY vendor_id
AS
SELECT * FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`;
对分区表查询时，添加过滤条件可以只扫描必要分区，大幅降低成本。
5. BigQuery ML：用 SQL 进行机器学习
BigQuery ML（BQML）让分析师在现有数据仓库内直接构建、评估和部署机器学习模型，无需将数据导出到 Python 环境或管理模型服务器。
5.1 支持的模型类型
- 线性回归 / 逻辑回归（
LINEAR_REG,LOGISTIC_REG） - 增强型时间序列（
ARIMA_PLUS） - K-Means 聚类（
KMEANS） - 矩阵分解（
MATRIX_FACTORIZATION推荐系统） - 深度神经网络（
DNN_CLASSIFIER、DNN_REGRESSOR） - AutoML Tables（
AUTOML_CLASSIFIER、AUTOML_REGRESSOR） - 预训练模型（如
TENSORFLOW导入），及远程模型（调用 Vertex AI）
5.2 训练第一个模型：预测出租车小费金额
我们将使用同一出租车数据集，学习如何根据行程特征预测小费（线性回归）。
步骤 1：创建模型
CREATE OR REPLACE MODEL `my_dataset.tip_prediction_model`
OPTIONS(model_type='linear_reg', input_label_cols=['tip_amount']) AS
SELECT
trip_distance,
fare_amount,
passenger_count,
EXTRACT(HOUR FROM pickup_datetime) AS pickup_hour,
ABS(MOD(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING)), 4)) = 0 AS is_training_split
FROM
`bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`
WHERE
pickup_datetime BETWEEN '2015-01-01' AND '2015-06-30';
这里用 FARM_FINGERPRINT 将数据随机分为训练集（标记为 TRUE）和评估集。BQML 默认将 80% 用于训练，剩余用于评估。
步骤 2：查看训练结果
训练完成后，运行：
SELECT * FROM ML.EVALUATE(MODEL `my_dataset.tip_prediction_model`);
输出包括平均绝对误差、均方误差等指标，帮助你判断模型质量。
步骤 3：查看特征权重
SELECT
processed_input AS feature,
weight
FROM
ML.WEIGHTS(MODEL `my_dataset.tip_prediction_model`)
ORDER BY ABS(weight) DESC;
步骤 4：预测新数据
SELECT
predicted_tip_amount,
tip_amount AS actual_tip
FROM
ML.PREDICT(MODEL `my_dataset.tip_prediction_model`,
(SELECT *
FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`
WHERE pickup_datetime = '2015-07-01 08:00:00'
LIMIT 10)
);
5.3 时间序列预测：预报每日行程量
BigQuery ML 的 ARIMA_PLUS 模型可以自动识别周期、节假日效应并进行预测。
-- 准备每日聚合视图
CREATE OR REPLACE VIEW `my_dataset.daily_trips` AS
SELECT
DATE(pickup_datetime) AS trip_date,
COUNT(*) AS num_trips
FROM
`bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`
GROUP BY trip_date;
-- 训练时间序列模型
CREATE OR REPLACE MODEL `my_dataset.trips_forecast`
OPTIONS(
model_type='ARIMA_PLUS',
time_series_timestamp_col='trip_date',
time_series_data_col='num_trips',
auto_arima=TRUE,
data_frequency='DAILY',
holiday_region='US'
) AS
SELECT
trip_date,
num_trips
FROM
`my_dataset.daily_trips`
WHERE trip_date < '2015-07-01';
-- 预测未来 30 天
SELECT *
FROM
ML.FORECAST(MODEL `my_dataset.trips_forecast`,
STRUCT(30 AS horizon, 0.9 AS confidence_level));
结果包含预测值、上限、下限等信息，可直接导出或用于 BI 工具。
5.4 使用 AutoML Tables 自动构建模型
如果你想尝试更高级的自动化特征工程和模型选择，可以使用 AUTOML_CLASSIFIER 或 AUTOML_REGRESSOR。只需指定目标列，BigQuery 会自动搜索最佳模型。注意 AutoML 训练时间较长（数小时），费用也更高。
CREATE OR REPLACE MODEL `my_dataset.tip_automl`
OPTIONS(
model_type='AUTOML_REGRESSOR',
input_label_cols=['tip_amount'],
budget_hours=1.0
) AS
SELECT
*
FROM
`bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`
WHERE
pickup_datetime BETWEEN '2015-01-01' AND '2015-03-31';
6. 最佳实践与成本控制
6.1 查询成本优化
- 使用分区和聚簇：只要可能，就限制扫描分区。
- 避免
SELECT *：仅选择需要的列，减少扫描量。 - 预览数据：使用预览功能查看数据，不产生费用。
- 保持查询简洁：需要重复查询的中间结果物化为表或物化视图。
- 设置成本控制：在查询设置中限定最大计费字节数或使用自定义配额。
6.2 机器学习模型治理
- 模型生命周期：定期重新训练或使用
ML.EVALUATE监控性能衰减。 - 模型版本管理：用带有时间戳的模型名称实现简单版本化。
- 特征存储：结合 BigQuery 视图和表构建特征集，确保训练 / 服务时一致性。
6.3 协作与权限
BigQuery 资源（项目、数据集、表）遵循 IAM 权限，可以精细控制读 / 写 / 管理层级。常用角色：roles/bigquery.dataViewer、roles/bigquery.dataEditor、roles/bigquery.jobUser。
7. 总结与下一步
通过本教程你已掌握：
- BigQuery 无服务器架构的优势
- 如何使用标准 SQL 进行探索性分析
- 利用分区、窗口函数、嵌套数据处理复杂数据
- 训练线性回归、时间序列等模型，并直接通过 SQL 预测
下一步建议：
- 深入学习 BigQuery 官方文档
- 尝试加载你自己的数据集（CSV、JSON 等），并构建端到端分析管道
- 将 BigQuery 数据连接到 Google Data Studio（Looker Studio）或第三方 BI 工具，创建仪表盘
- 探索 BigQuery ML 的高级功能，如特征工程转换函数（
ML.FEATURE_CROSS）和模型导出
BigQuery 让数据分析与机器学习在同一个 SQL 环境中无缝融合，大幅降低了从数据到洞察的门槛。现在就去查询编辑器，亲手实践吧。