Data Engineering Zoomcamp Module 3 作业实战指南：BigQuery 数据仓库与 GCS 外部表全流程演练-迪斯科星球

Data Engineering Zoomcamp Module 3 作业实战指南：BigQuery 数据仓库与 GCS 外部表全流程演练

【免费下载链接】data-engineering-zoomcampData Engineering Zoomcamp is a free 9-week course on building production-ready data pipelines. Join the course here 👇🏼项目地址: https://gitcode.com/GitHub_Trending/da/data-engineering-zoomcamp

本篇实战指南以 Data Engineering Zoomcamp 2027 队列 Module 3（Data Warehousing & BigQuery）的课后作业为核心骨架，完整覆盖从纽约出租车数据上传 GCS、创建外部表、构建普通表与分区聚簇表，到逐题解答九道 BigQuery 查询作业的全过程。读完本文，你将掌握 BigQuery 外部表与物化表的区别、列式存储与扫描字节数估算的底层原理、分区与聚簇的选型策略，并能在自己的 GCP 项目中复现这套数据仓库实验。

作业概览与数据集说明

本次作业的核心素材是2024 年 1 月至 6 月的 Yellow Taxi Trip Records（注意：并非全年数据，仅上半年 6 个月）。数据以 Parquet 格式提供，来源为纽约市出租车与礼车委员会（NYC TLC）公开数据集。

作业目标可拆解为四个层次：

数据装载：将 6 个月 Parquet 文件下载并上传到自己的 GCS Bucket；
表结构搭建：在 BigQuery 中创建外部表（external table）与普通物化表（non-partitioned table）；
查询实战：通过九道题目验证对计数、列式存储、分区聚簇、字节估算等核心概念的理解；
提交验收：将解答（SQL、Shell 或代码）提交到公开代码托管仓库，并在作业表单中提供仓库链接。

需要特别注意的约束

作业明确要求：如果使用 Kestra、Mage、Airflow、Prefect 等编排工具，不要通过编排器把数据写入 BigQuery——数据加载应直接完成，避免引入不必要的中间环节干扰实验结果。
开始前务必确认GCS Bucket 中已出现全部 6 个文件。
创建外部表时，必须使用PARQUET 格式选项（与课程中演示的 CSV 外部表不同）。

将数据装载到 GCS Bucket

方案一：使用 Python 脚本`load_yellow_taxi_data.py`

作业目录提供了开箱即用的加载脚本 load_yellow_taxi_data.py。该脚本的核心流程如下：

鉴权：通过服务账号 JSON 文件初始化 GCS 客户端（默认读取当前目录下的gcs.json）；如果你已通过 Google Cloud SDK 登录，可以注释掉这两行，改用storage.Client(project='...')方式。
创建 Bucket：调用create_bucket()检查 Bucket 是否存在、是否属于当前项目，不存在则自动创建；若 Bucket 名称已被占用且不可访问，脚本会退出并提示更换名称。
并发下载：使用ThreadPoolExecutor(max_workers=4)从https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-<month>.parquet并行下载 1-6 月共 6 个 Parquet 文件到本地。
并发上传：再次以 4 线程并发将文件上传到 GCS，设置 8 MB 分块大小（CHUNK_SIZE），并带 3 次重试与存在性校验（verify_gcs_upload）。

使用前需要修改的关键配置：

# 替换为你自己的 Bucket 名称 BUCKET_NAME = "dezoomcamp_hw3_2025" # 若已通过 GCP SDK 鉴权，可注释掉下面两行 CREDENTIALS_FILE = "gcs.json" client = storage.Client.from_service_account_json(CREDENTIALS_FILE)

运行方式（在脚本所在目录执行）：

python load_yellow_taxi_data.py

运行结束后，GCS Bucket 中应出现 6 个文件：yellow_tripdata_2024-01.parquet至yellow_tripdata_2024-06.parquet。

方案二：使用 DLT 笔记本`DLT_upload_to_GCP.ipynb`

如果你更习惯 Notebook 方式，可以使用同一目录下的 DLT_upload_to_GCP.ipynb，基于 dlt（data load tool）库完成同样的下载与上传流程。两种方案任选其一即可。

权限前提：无论采用哪种方案，都需要生成一个具备 GCS Admin 权限的服务账号，或已通过 Google Cloud SDK 完成鉴权。

方案三（备选）：`extras/`中的独立脚本

模块还提供了不依赖编排器的备选工具 extras/，其将 NYC TLC 的 CSV 下载后转为 Parquet 并上传 GCS，使用uv管理依赖：

cd cohorts/2027/03-data-warehouse/extras uv sync uv run python web_to_gcs_with_progress_bar.py # 带进度条版本 uv run python web_to_gcs.py # 简洁版本

BigQuery 环境搭建：外部表与普通表

数据就绪后，在 BigQuery 中完成两张基础表的创建。

第一步：创建外部表（External Table）

外部表的特点是数据仍存放在 GCS，BigQuery 只保存元数据与 Schema。创建外部表后打开表详情可以看到：长期存储为 0 字节、表大小为 0 字节、行数为 0——因为数据根本不在 BigQuery 内部（参见 01-data-warehouse-and-bigquery.md 中对外部表特性的讲解）。

CREATE OR REPLACE EXTERNAL TABLE `你的项目.你的数据集.external_yellow_tripdata` OPTIONS ( format = 'PARQUET', uris = ['gs://你的bucket名/yellow_tripdata_2024-*.parquet'] );

注意与课程示例 big_query.sql 中 CSV 外部表的差异：本次作业数据集是 Parquet 文件，因此format必须写为'PARQUET'。

第二步：创建普通物化表（非分区非聚簇）

CREATE OR REPLACE TABLE `你的项目.你的数据集.yellow_tripdata_non_partitioned` AS SELECT * FROM `你的项目.你的数据集.external_yellow_tripdata`;

这条语句会把 GCS 中的数据真正复制进 BigQuery 存储，因此执行需要一定时间。不要对该表做分区或聚簇，它将成为后续对比实验的“对照组”。

九道作业逐题解析

说明：以下题目均为选择题或简答题，具体答案依赖你的实际数据，建议结合运行结果验证。下文给出每道题的考察点、参考 SQL 与解题思路。

Question 1：统计记录总数

题目：2024 年 Yellow Taxi 数据共有多少条记录？备选项为 65,623 / 840,402 / 20,332,093 / 85,431,289。

参考 SQL：

SELECT count(*) FROM `你的项目.你的数据集.external_yellow_tripdata`;

在外部表上执行COUNT(*)，BigQuery 会扫描全部 6 个月 Parquet 文件并返回总行数。六个月的纽约黄色出租车数据总量在千万级，本题答案约为20,332,093（请以你自己表上的实际查询结果为准）。

Question 2：外部表与普通表的数据读取量估算

题目：对整份数据集统计PULocationID的 distinct 数量，分别在外部表和普通表上执行，估算的扫描数据量各是多少？

参考 SQL：

SELECT COUNT(DISTINCT PULocationID) FROM `你的项目.你的数据集.external_yellow_tripdata`; SELECT COUNT(DISTINCT PULocationID) FROM `你的项目.你的数据集.yellow_tripdata_non_partitioned`;

考察点：理解外部表与物化表的扫描行为差异。外部表的数据在 GCS，BigQuery 需要把 Parquet 文件拉进来才能统计；而普通表是 BigQuery 原生存储，同样要全表扫描两列中的一列。正确答案为0 MB for the External Table and 155.12 MB for the Materialized Table——外部表上的估算值为 0（查询尚未真正执行时 BigQuery 无法精确预知 GCS 数据规模），普通表则需扫描约 155.12 MB。

Question 3：理解列式存储

题目：在普通表上分别查询PULocationID一列、以及PULocationID+DOLocationID两列，为什么两次的估算字节数不同？

正确答案：BigQuery 是列式（columnar）数据库，只扫描查询中涉及的具体列。查询两列（PULocationID, DOLocationID）需要读取的数据比查询一列（PULocationID）更多，因此估算的处理字节数更高。

原理深化：这一行为源于 BigQuery 的底层存储架构。课程 04-internals-of-bigquery.md 指出，BigQuery 采用列式存储（该组件被称为 Polymer）：每一列独立存放，一行的数据会散布在多个位置，而不是像 CSV 那样按行整体存放。列式存储使得按列聚合极快，而数据仓库场景下我们很少同时查询所有列——这正是SELECT *代价高昂的根本原因，也是最佳实践中“避免SELECT *、只取所需列”这一建议的底层逻辑（见 03-bigquery-best-practices.md）。

Question 4：统计零车费记录数

题目：fare_amount等于 0 的记录有多少条？备选项为 128,210 / 546,578 / 20,188,016 / 8,333。

参考 SQL：

SELECT count(*) FROM `你的项目.你的数据集.yellow_tripdata_non_partitioned` WHERE fare_amount = 0;

考察点：对物化表执行带过滤条件的聚合。结合 Q1 的总行数（约 2000 万），零车费记录数远小于总数，答案应为128,210（请以实际查询结果为准）。

Question 5：分区与聚簇的选型策略

题目：如果查询总是按tpep_dropoff_datetime过滤、并按VendorID排序，最佳建表策略是什么？

正确答案：Partition bytpep_dropoff_datetimeand Cluster onVendorID。

参考 SQL（创建新表）：

CREATE OR REPLACE TABLE `你的项目.你的数据集.yellow_tripdata_partitioned_clustered` PARTITION BY DATE(tpep_dropoff_datetime) CLUSTER BY VendorID AS SELECT * FROM `你的项目.你的数据集.external_yellow_tripdata`;

原理深化：为什么不能两者都做分区？课程 02-partitioning-vs-clustering.md 明确说明：分区只能基于单个列，而聚簇可指定最多 4 列。更关键的是选型逻辑：

成本可预知性：分区的收益在执行前已知——过滤分区列的查询只会读取部分分区；聚簇的收益要等查询运行完才知道。因此当你想用SETmax_bytes_billed`` 之类的上限控制成本时，只能依赖分区；
粒度：当需要比分区更细的过滤粒度时用聚簇；
管理能力：分区支持按分区删除、跨存储迁移等分区级管理，聚簇没有；
基数：列的分区基数（distinct 值数量）过高会触及每表 4000 个分区的上限，此时应改用聚簇。

本场景中，日期过滤适合分区（粒度是天然的），VendorID 基数低、适合作为聚簇键，故答案为“按tpep_dropoff_datetime分区 + 按VendorID聚簇”。

Question 6：分区带来的收益对比

题目：查询tpep_dropoff_datetime在 2024-03-01 至 2024-03-15（含边界）之间的 distinctVendorID。先在普通表上执行并记录估算字节数，再换成分区聚簇表执行，两次的估算值各是多少？

参考 SQL：

SELECT DISTINCT(VendorID) FROM `你的项目.你的数据集.yellow_tripdata_non_partitioned` WHERE DATE(tpep_dropoff_datetime) BETWEEN '2024-03-01' AND '2024-03-15'; SELECT DISTINCT(VendorID) FROM `你的项目.你的数据集.yellow_tripdata_partitioned_clustered` WHERE DATE(tpep_dropoff_datetime) BETWEEN '2024-03-01' AND '2024-03-15';

正确答案：310.24 MB for non-partitioned table and 26.84 MB for the partitioned table。

原理深化：普通表必须扫描全表 6 个月的数据（约 310 MB）；分区表只读取 3 月上半月对应的分区，扫描量骤降到约 26.84 MB——这就是**分区裁剪（partition pruning）**的直接体现。课程演示（big_query.sql）中 2019 年 6 月数据也呈现同样规律：非分区表扫描 1.6 GB，分区表仅扫描约 106 MB。

Question 7：外部表的数据存储位置

题目：外部表的数据实际存储在哪里？

正确答案：GCP Bucket。

原理深化：外部表只把 Schema 与元数据登记在 BigQuery，数据本体始终位于 GCS 的 Parquet 文件中。这也是为什么外部表详情页显示 0 字节、0 行——BigQuery 无法在不扫描的情况下获知外部数据的行数与大小。理解这一点对成本管理很重要：频繁查询外部表需要反复从 GCS 拉取数据，比查 BigQuery 原生存储更昂贵（03-bigquery-best-practices.md 中亦提示“外部数据源要适度使用，不要过度”）。

Question 8：聚簇是否总是最佳实践

题目：BigQuery 中“总是对数据做聚簇”是最佳实践吗？

正确答案：False。

原理深化：课程 02-partitioning-vs-clustering.md 明确指出聚簇并非免费：小于 1 GB 的小表上，分区和聚簇都无法带来显著的查询性能提升，反而会因元数据读取与维护增加额外成本。此外，聚簇/分区还会带来以下代价与限制：

分区上限为每表 4000 个分区，基数过大会触及上限；
聚簇列必须是顶层非重复列，且类型限于 DATE、BOOLEAN、GEOGRAPHY、INT64、NUMERIC、STRING、DATETIME；
频繁写入（如每几分钟）且波及大部分分区的变更操作，会削弱聚簇的排序收益（虽然 BigQuery 会自动在后台执行重聚簇（reclustering）以恢复排序特性，该过程不收费、不影响查询性能）。

因此正确姿势是：根据查询模式按需选择，小表与低价值场景完全可以不做分区聚簇。

Question 9（不计分）：理解表扫描

题目：在普通表上执行SELECT count(*)，BigQuery 估算会读取多少字节？为什么？

参考 SQL：

SELECT count(*) FROM `你的项目.你的数据集.yellow_tripdata_non_partitioned`;

解题思路：本题虽不计分，但直指 BigQuery 的元数据优化核心。COUNT(*)这类统计行数的查询，BigQuery 可以直接读取表的元数据（行数信息）给出结果，无需扫描任何列数据，因此估算扫描字节数为 0 或极小。这与 Q2 中COUNT(DISTINCT PULocationID)必须真实扫描数据形成鲜明对比——distinct 计数需要读取列值才能去重，而纯行数统计走的是元数据捷径。这是理解“估算字节数 ≠ 表大小”的最佳案例。

提交与学习分享

完成九道题后，将解答整理并提交：

若解答以SQL 或 Shell 命令形式为主，直接在仓库的 README 中给出即可；
若涉及 Python 等代码文件，将代码放入公开仓库（GitHub 或其他代码托管平台）；
提交作业时一并附上仓库链接。

作业表单地址与 2027 队列的提交入口以课程官方通知为准（历史上该模块提交地址为 DataTalks Club 课程站点的 Homework 3 表单）。

课程方鼓励“Learning in Public”分享。作业文档中提供了 LinkedIn 与 Twitter/X 的示例文案模板，核心要点是：总结本周学到的能力（创建 GCS 外部表、构建 BigQuery 物化表、分区与聚簇调优、理解列式存储与查询优化、基于 2000 万+ 记录的 NYC 出租车数据做规模化分析），并附上自己的作业仓库链接。你也可以参考 README.md 中社区成员分享的笔记风格。

参考资料与延伸阅读

本模块完整讲义：01-data-warehouse-and-bigquery.md（外部表、分区、聚簇的图文讲解）
分区与聚簇选型：02-partitioning-vs-clustering.md
成本与性能最佳实践：03-bigquery-best-practices.md
BigQuery 内部原理：04-internals-of-bigquery.md
课程配套 SQL：big_query.sql
历史作业参考：big_query_hw.sql
数据加载脚本：load_yellow_taxi_data.py、DLT_upload_to_GCP.ipynb、extras/

创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考

企业官网建设流程全解析