Data Engineering Zoomcamp Week 1 作业实战：Terraform 环境准备与纽约出租车数据的 SQL 查询

Data Engineering Zoomcamp Week 1 作业实战Terraform 环境准备与纽约出租车数据的 SQL 查询【免费下载链接】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本文以 2022 年第一期cohort 2022第一周作业 homework.md 为主体完整梳理该作业的全部任务线从 Google Cloud SDK 与 Terraform 的环境搭建到将纽约出租车数据载入 Postgres再到用 SQL 回答四道数据分析问题。读完本文你将具备复现整套 Week 1 作业环境的能力并掌握日期过滤、聚合分组、多表 JOIN 等实际数据管道中高频使用的 SQL 技巧。一、作业全景这一周要完成什么Week 1 是整个 Data Engineering Zoomcamp 的起点其作业目标是准备环境 练习 Terraform 与 SQL具体拆解为五条任务线安装 Google Cloud SDK 并验证版本创建 Google Cloud 账户与项目安装 Terraform在week_1_basics_n_setup/1_terraform_gcp/terraform目录下依次执行terraform init、terraform plan、terraform apply启动 Postgres 并把 2021 年 1 月的黄色出租车数据yellow taxi trips与纽约区域表zones lookup载入数据库基于上述数据用 SQL 回答四个问题Question 36涉及记录计数、聚合最大值、热门目的地与平均价格分析。作业覆盖了后续课程反复使用的基础设施即代码IaC工具 Terraform 和关系型数据库查询能力这两项技能分别对应本仓库 terraform 示例 与 docker-sql 模块。二、环境准备Google Cloud SDK 与项目创建2.1 安装 Google Cloud SDK 并确认版本作业第一步是安装 Google Cloud SDKgcloud 命令行工具然后用以下命令确认安装结果gcloud --version该命令会输出当前 SDK 的版本号同时列出随 SDK 附带的组件版本如 bq、gsutil 等。由于作业要求把版本号填入提交表单这一输出需要保留。这是后续使用bqBigQuery 命令行与gsutilGCS 命令行的基础Week 3 数据仓库模块将大量依赖它们。2.2 创建 Google Cloud 账户与项目完成 SDK 安装后需要在 Google Cloud 控制台注册账户并新建一个项目。这个项目将成为后续所有云资源GCS 存储桶、BigQuery 数据集的归属容器。创建项目后建议立即完成本地认证便于 Terraform 以应用默认凭据ADC方式访问 GCP。仓库中 terraform/README.md 给出的标准认证命令为# 为本会话刷新服务账号的认证令牌 gcloud auth application-default login如果组织策略禁止直接下载服务账号密钥文件README 还提供了模拟身份impersonation的备选方案先给自己授予roles/iam.serviceAccountTokenCreator角色再在 Terraform 配置中加入google_service_account_access_token数据源获取临时令牌详见 terraform/README.md 的 Fallback 小节。三、Terraform 实战初始化、计划与应用3.1 三个核心命令进入作业指定的 terraform 目录后依次执行terraform init terraform plan terraform applyterraform init初始化工作目录下载所需的 provider 插件本仓库示例使用hashicorp/google并建立.tfstate状态文件体系terraform plan对比当前配置与现有资源输出将要创建的变更计划不会真正改动任何云资源terraform apply按计划实际创建云资源执行完成后会打印完整的资源创建输出。作业要求把从terraform init到apply结束的完整输出粘贴到提交表单因此不要只复制最后几行。3.2 读懂基础版 Terraform 配置仓库 terraform_basic/main.tf 展示了 Week 1 对应的最简配置骨架terraform { required_providers { google { source hashicorp/google version 4.51.0 } } } provider google { # 若未设置 GOOGLE_APPLICATION_CREDENTIALS 环境变量才需要在此填写 credentials # credentials project Your Project ID region us-central1 } resource google_storage_bucket data-lake-bucket { name Your Unique Bucket Name location US storage_class STANDARD uniform_bucket_level_access true versioning { enabled true } lifecycle_rule { action { type Delete } condition { age 30 } # 30 天后自动删除对象 } force_destroy true } resource google_bigquery_dataset dataset { dataset_id The Dataset Name You Want to Use project Your Project ID location US }配置要点provider 版本required_providers锁定了hashicorp/google的版本保证不同机器的执行环境一致凭证来源优先读取环境变量GOOGLE_APPLICATION_CREDENTIALS指向的服务账号 JSON注释掉的credentials字段仅供未设置该变量时兜底GCS 存储桶开启版本控制与 30 天生命周期清理规则force_destroy true允许销毁时强制删除非空桶BigQuery 数据集作为后续数据仓库模块Week 3的表容器。如果项目中变量较多可以改用仓库 terraform_with_variables/main.tf 的变量化写法把 project、region、bucket 名、数据集名全部抽取为var.*引用避免硬编码。3.3 收尾清理课程建议在作业完成后及时销毁资源避免产生不必要的云费用terraform destroy需要提醒的是作业提交环节不会要求你保留云资源所以完成截图与提交后执行destroy是标准做法。四、准备 Postgres 与数据加载4.1 用 Docker Compose 一键拉起 Postgres 与 pgAdminWeek 1 课程使用 Docker 部署本地数据库。仓库 docker-compose.yaml 定义了pgdatabase与pgadmin两个服务services: pgdatabase: image: postgres:18 environment: POSTGRES_USER: root POSTGRES_PASSWORD: root POSTGRES_DB: ny_taxi volumes: - ny_taxi_postgres_data:/var/lib/postgresql ports: - 5432:5432 pgadmin: image: dpage/pgadmin4 environment: PGADMIN_DEFAULT_EMAIL: adminadmin.com PGADMIN_DEFAULT_PASSWORD: root volumes: - pgadmin_data:/var/lib/pgadmin ports: - 8085:80 volumes: ny_taxi_postgres_data: pgadmin_data:数据库账号密码均为root库名为ny_taxi宿主机端口 5432数据通过命名卷ny_taxi_postgres_data持久化容器删除后数据不丢失pgAdmin 通过http://localhost:8085/browser/访问登录邮箱adminadmin.com密码root。课程讲义 10-sql-refresher.md 提醒查询前若看不到新表需要右键刷新数据库。启动方式docker compose up -d4.2 下载作业数据集作业指定使用 2021 年 1 月的黄色出租车数据与区域表wget https://s3.amazonaws.com/nyc-tlc/tripdata/yellow_tripdata_2021-01.csv wget https://s3.amazonaws.com/nyc-tlc/misc/taxi_zone_lookup.csvyellow_tripdata_2021-01.csv1 月全部黄色出租车行程明细含上下车时间、上下车区域 ID、费用与金额字段taxi_zone_lookup.csv区域 ID 到 Borough/Zone 名称的映射表Question 5 和 Question 6 需要用它把PULocationID/DOLocationID翻译成区域名。注意第二个 URL 中taxi_zone_lookup.csv的号是亚马逊 S3 对空格的历史编码请按原样使用。4.3 用 ingest 脚本把 CSV 灌入 Postgres仓库提供了完整的摄取脚本 ingest_data.py它用click定义命令行参数、用pandas分块读取 CSV、用SQLAlchemy写库。核心设计值得关注显式声明列类型dtype将VendorID、passenger_count、PULocationID、DOLocationID等声明为Int64金额类字段声明为float64避免 pandas 自动推断造成 NULL 或类型偏差日期解析parse_dates对tpep_pickup_datetime与tpep_dropoff_datetime做时间类型解析这是 Question 35 中按日期过滤的前提分块写入以chunksize默认 100000为单位迭代读入首块用if_existsreplace建表后续块用append追加避免大文件一次性载入内存。脚本默认参数为--pg-userroot --pg-passroot --pg-hostlocalhost --pg-port5432 --pg-dbny_taxi并会从 GitHub Release 自动下载yellow_tripdata_2021-01.csv.gz。也可以直接用 Docker 运行见 docker-ingest.shdocker run -it --rm \ --networkpg-network \ taxi_ingest:v001 \ --year2021 --month1 \ --pg-userroot --pg-passroot \ --pg-hostpgdatabase --pg-port5432 \ --pg-dbny_taxi \ --chunksize100000 \ --target-tableyellow_taxi_trips若采用本地 wget 脚本方式也可以把下载好的 CSV 传给 pandas 读取只需将target-table指定为便于查询的表名如yellow_taxi_trips或默认的yellow_taxi_data后续所有 SQL 查询都要使用与实际建表一致的名称。五、SQL 作业精讲Question 36以下四个问题的完整 SQL 均基于yellow taxi 行程表 zones 区域表双表模型。JOIN 写法参考课程讲义 10-sql-refresher.md其中显式JOIN ... ON比隐式 WHERE 连接更推荐。5.1 Question 31 月 15 日共有多少行程题目要求只统计 1 月 15 日开始上车时间在该日的行程即对上车时间做日期过滤后计数SELECT COUNT(1) AS trips_on_jan15 FROM yellow_taxi_trips WHERE tpep_pickup_datetime::date DATE 2021-01-15;要点::date是 Postgres 的快捷类型转换写法等价于CAST(tpep_pickup_datetime AS date)它会把时间戳截断到日从而匹配DATE 2021-01-15字面量千万不要用tpep_pickup_datetime 2021-01-15直接比较因为时间戳带有时刻部分等值匹配几乎必然返回 0 行正确做法是转成日期后比较。5.2 Question 4每天的最大 tip 出现在哪天题目要求按上车时间算出每一天的最大小费并找出 1 月中小费最大的那一天。先按天分组聚合再整体排序即可SELECT tpep_pickup_datetime::date AS day, MAX(tip_amount) AS max_tip FROM yellow_taxi_trips WHERE tpep_pickup_datetime 2021-01-01 AND tpep_pickup_datetime 2021-02-01 GROUP BY day ORDER BY max_tip DESC;结果第一行即为答案day字段是该日期max_tip是该日最大小费金额。5.3 Question 51 月 14 日 Central Park 上车乘客最常去的区域此题需要把行程表与 zones 表做两次连接一次把上车区域 ID 翻译成区域名一次把下车区域 ID 翻译成区域名SELECT zdo.Zone AS dropoff_zone, COUNT(1) AS trip_count FROM yellow_taxi_trips t JOIN zones zpu ON t.PULocationID zpu.LocationID JOIN zones zdo ON t.DOLocationID zdo.LocationID WHERE zpu.Zone Central Park AND t.tpep_pickup_datetime::date DATE 2021-01-14 GROUP BY zdo.Zone ORDER BY trip_count DESC LIMIT 1;要点zpu与zdo是同一张zones表的两个别名这是自连接的标准写法若 Central Park 区域名拼写与数据不完全一致例如课程数据集中的实际写法请先执行SELECT DISTINCT Zone FROM zones WHERE Zone LIKE %Central%;核对准确名称区域 ID 在主表中一定存在对应记录因此用JOIN足够若担心脏数据导致漏行可改用LEFT JOIN并在结果中用COALESCE(zdo.Zone, Unknown)兜底——作业明确要求区域名未知时填 Unknown。5.4 Question 6平均价格最高的 pickup-dropoff 组合题目要求基于total_amount计算每种上车区/下车区组合的平均车费输出最大的那一对格式为Jamaica Bay / Clinton East这种区域A / 区域B的斜杠分隔写法未知区域一律写作UnknownSELECT CONCAT(zpu.Zone, / , zdo.Zone) AS zone_pair, AVG(t.total_amount) AS avg_total_amount FROM yellow_taxi_trips t JOIN zones zpu ON t.PULocationID zpu.LocationID JOIN zones zdo ON t.DOLocationID zdo.LocationID WHERE t.tpep_pickup_datetime 2021-01-01 AND t.tpep_pickup_datetime 2021-02-01 GROUP BY zpu.Zone, zdo.Zone ORDER BY avg_total_amount DESC LIMIT 1;要点分组键是区域名对而非 LocationID 对CONCAT负责拼出题目要求的斜杠格式若希望把所有未知区域都聚合进 Unknown可以把JOIN换成LEFT JOIN并配合COALESCECONCAT中再对空值做兜底例如CONCAT(COALESCE(zpu.Zone,Unknown), / , COALESCE(zdo.Zone,Unknown))AVG只统计非 NULL 的total_amount符合业务直觉。六、提交与截止时间作业通过 Google Form 提交每个问题含 Question 1 的 gcloud 版本号、Question 2 的完整 apply 输出以及 Question 36 的答案分别填写表单支持多次提交系统只采纳最后一次提交的结果2022 年第一期的截止时间为1 月 26 日周三22:00 CET。作业原文档提供了 Question 36 的官方解答视频见 homework.md 末尾的 Solution 小节完成并自查后再对照视频核对结果即可。七、自查清单完成整套作业后建议按以下清单自查gcloud --version输出版本号且gcloud auth application-default login认证成功terraform init→plan→apply三步无报错apply输出完整保留并已复制到表单docker compose up -d后pgdatabase与pgadmin均处于 running 状态pgAdmin 能在localhost:8085登录并看到新表yellow_taxi_trips或自定义表名与zones两张表均已就位可通过SELECT COUNT(1) FROM yellow_taxi_trips;快速验证数据行数四道 SQL 的答案逻辑与上述写法一致日期过滤用::date、聚合用GROUP BY、区域翻译用双 JOIN云资源已通过terraform destroy清理避免产生持续费用。完成以上步骤你就完整走通了 Week 1 作业的全流程也同时掌握了后续 Week 2工作流编排、Week 3数据仓库所需的基础设施与 SQL 功底。【免费下载链接】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创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考