Практикум: грузим капстон в BigQuery

Три предыдущих урока модуля были про теорию: что такое Snowflake, что такое BigQuery, как считать стоимость. Сейчас — руки. Ты возьмёшь звезду из капстона (NYC Taxi: fct_trips плюс dim_date, dim_location, dim_payment_type, dim_vendor) и зальёшь её в настоящий облачный DWH. Бесплатно. Без кредитной карты.

Почему именно BigQuery, а не Snowflake? У BigQuery есть sandbox — режим, где ты пользуешься продуктом без привязки billing-аккаунта и без карты. Тебе дают бесплатную квоту: 10 GB активного storage и 1 TB просканированных байтов в месяц. Для звезды на 3 месяца NYC Taxi этого хватает с большим запасом. Snowflake free trial тоже бесплатен, но требует подтверждения личности и через 30 дней (или 400 USD кредитов) выключается. Для нулевого новичка “без карты вообще” — это BigQuery.

Что мы построим

Конечная цель: те же таблицы, что ты построил локально в капстоне, но в настоящем serverless DWH. Плюс три навыка, которые спрашивают junior DE на собеседовании: партиционирование с проверкой стоимости, native vs external tables, базовый IAM.

Шаг 1. Google-аккаунт и BigQuery sandbox

Нужен любой Google-аккаунт (личный gmail подойдёт). Дальше:

1. Открой https://console.cloud.google.com/bigquery.
2. Если просит — создай проект. Назови его, например, de-fundamentals-sandbox. Project ID будет вида de-fundamentals-sandbox-123456 — он понадобится дальше, запиши его.
3. В верху Console увидишь баннер “Sandbox”. Это значит, что billing не подключён и карта не нужна. Именно то, что нам надо. НЕ нажимай “Upgrade” и не добавляй billing-аккаунт.

Ожидаемый результат: ты в BigQuery Console, видишь левую панель Explorer с твоим проектом и баннер Sandbox сверху.

Шаг 2. Установка gcloud и bq CLI

bq — это CLI для BigQuery, он идёт в составе Google Cloud SDK (gcloud). Через него мы будем грузить файлы и гонять запросы из терминала, как настоящий инженер, а не кликать мышкой.

macOS (через Homebrew):

brew install --cask google-cloud-sdk

Windows — работаем внутри WSL2 (Ubuntu), так все команды унифицируются с Linux:

# внутри WSL2 Ubuntu
sudo apt-get update && sudo apt-get install -y apt-transport-https ca-certificates gnupg curl
curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo gpg --dearmor -o /usr/share/keyrings/cloud.google.gpg
echo "deb [signed-by=/usr/share/keyrings/cloud.google.gpg] https://packages.cloud.google.com/apt cloud-sdk main" | sudo tee /etc/apt/sources.list.d/google-cloud-sdk.list
sudo apt-get update && sudo apt-get install -y google-cloud-cli

Linux (Debian/Ubuntu) — те же команды, что для WSL2 выше. Для других дистрибутивов — https://cloud.google.com/sdk/docs/install.

После установки авторизуйся и привяжи проект:

gcloud auth login
gcloud config set project de-fundamentals-sandbox-123456
gcloud auth application-default login

Первая команда логинит CLI, вторая запоминает project, третья кладёт OAuth-токен в место, откуда его подхватит dbt (это application-default credentials, ADC).

Проверка установки:

bq version

Ожидаемый вывод (версия может отличаться):

This is BigQuery CLI 2.0.101

Шаг 3. Создаём dataset

Dataset в BigQuery — это контейнер для таблиц, аналог schema в Postgres. Создаём его командой bq mk:

bq --location=US mk --dataset de-fundamentals-sandbox-123456:nyc_taxi

Ожидаемый вывод:

Dataset 'de-fundamentals-sandbox-123456:nyc_taxi' successfully created.

Регион (--location=US) фиксируется навсегда. Для sandbox используй US или EU — это мульти-регионы с бесплатной квотой. Если запустить команду повторно, получишь Dataset already exists и ненулевой код возврата — это нормально, dataset уже на месте.

Шаг 4. Экспорт витрин и загрузка через bq load

Сначала выгрузи звезду из локального капстона в файлы. Если капстон на DuckDB, это одна команда COPY на таблицу:

duckdb data/warehouse.duckdb -c "COPY fct_trips TO 'export/fct_trips.parquet' (FORMAT parquet)"
duckdb data/warehouse.duckdb -c "COPY dim_date TO 'export/dim_date.parquet' (FORMAT parquet)"
duckdb data/warehouse.duckdb -c "COPY dim_location TO 'export/dim_location.parquet' (FORMAT parquet)"
duckdb data/warehouse.duckdb -c "COPY dim_payment_type TO 'export/dim_payment_type.parquet' (FORMAT parquet)"
duckdb data/warehouse.duckdb -c "COPY dim_vendor TO 'export/dim_vendor.parquet' (FORMAT parquet)"

Теперь грузим в BigQuery. Parquet несёт схему в себе, поэтому --autodetect не нужен — типы берутся из файла:

bq load --source_format=PARQUET nyc_taxi.fct_trips export/fct_trips.parquet
bq load --source_format=PARQUET nyc_taxi.dim_date export/dim_date.parquet
bq load --source_format=PARQUET nyc_taxi.dim_location export/dim_location.parquet
bq load --source_format=PARQUET nyc_taxi.dim_payment_type export/dim_payment_type.parquet
bq load --source_format=PARQUET nyc_taxi.dim_vendor export/dim_vendor.parquet

Ожидаемый вывод на каждую таблицу:

Waiting on bqjob_r1a2b3c4...   (1s) Current status: DONE

Альтернатива через CSV, если Parquet недоступен (тогда нужен --autodetect, чтобы BigQuery угадал схему по содержимому, и --skip_leading_rows=1, чтобы пропустить заголовок):

bq load --source_format=CSV --autodetect --skip_leading_rows=1 \
  nyc_taxi.dim_payment_type export/dim_payment_type.csv

Проверим, что строки доехали:

bq query --use_legacy_sql=false \
  'SELECT COUNT(*) AS rows FROM `nyc_taxi.fct_trips`'

Ожидаемый вывод (число зависит от того, сколько месяцев ты грузил):

+---------+
|  rows   |
+---------+
| 8460312 |
+---------+

Шаг 5. Направляем dbt на BigQuery

Главная мысль: dbt-модели из капстона не меняются. Меняется только profiles.yml — он говорит dbt, в какой DWH писать. Был DuckDB — стал BigQuery. SQL твоих моделей остаётся тем же (с точностью до пары диалектных функций, о них ниже).

Поставь BigQuery-адаптер dbt в то же окружение:

pip install dbt-bigquery

Профиль для BigQuery с авторизацией через OAuth (тот самый application-default login из Шага 2 — никаких ключей и JSON-файлов с секретами):

# ~/.dbt/profiles.yml
nyc_taxi:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: de-fundamentals-sandbox-123456
      dataset: nyc_taxi
      location: US
      threads: 4
      priority: interactive

Запусти полную сборку (dbt build = dbt run плюс dbt test в правильном порядке):

dbt build

Ожидаемый хвост вывода:

Finished running 5 table models, 12 tests in 0 hours 0 minutes and 41.20 seconds.
Completed successfully
Done. PASS=17 WARN=0 ERROR=0 SKIP=0 TOTAL=17

Шаг 6. Запрос 1 — партиционирование, кластеризация и проверка стоимости

Это самый важный навык на собесе. В BigQuery нет индексов. Есть партиционирование (физическое деление таблицы по дате) и кластеризация (сортировка внутри партиции по колонкам). Без них любой запрос читает всю таблицу и платит за весь объём.

Пересоздадим fct_trips с партиционированием по дате пикапа и кластеризацией по zone:

CREATE OR REPLACE TABLE `nyc_taxi.fct_trips_partitioned`
PARTITION BY DATE(pickup_datetime)
CLUSTER BY pickup_location_id AS
SELECT * FROM `nyc_taxi.fct_trips`;

Теперь главный трюк: --dry_run. Он не выполняет запрос, а только считает, сколько байтов будет просканировано. Это твоя стоимость до того, как ты её заплатил. Сравним два запроса — по непартиционированной и по партиционированной таблице:

bq query --use_legacy_sql=false --dry_run \
  'SELECT COUNT(*) FROM `nyc_taxi.fct_trips`
   WHERE DATE(pickup_datetime) = "2024-01-15"'

Ожидаемый вывод (сканирует ВСЮ таблицу, потому что нет партиций):

Query successfully validated. Assuming the tables are not modified,
running this query will process 487266816 bytes of data.

Теперь по партиционированной таблице:

bq query --use_legacy_sql=false --dry_run \
  'SELECT COUNT(*) FROM `nyc_taxi.fct_trips_partitioned`
   WHERE DATE(pickup_datetime) = "2024-01-15"'

Ожидаемый вывод (сканирует ОДНУ партицию — на порядки меньше):

Query successfully validated. Assuming the tables are not modified,
running this query will process 15728640 bytes of data.

487 MB против 15 MB — разница в 30 раз на ровном месте. На on-demand pricing (около 6.25 USD за TB) это прямая экономия денег. Запомни связку: --dry_run показывает bytes scanned ДО оплаты, партиционирование + WHERE по партиционирующей колонке = partition pruning = меньше байтов = меньше денег.

Шаг 7. Запрос 2 — native vs external tables

fct_trips, которую мы загрузили через bq load, — это native table: данные физически лежат в Colossus (storage Google), в формате Capacitor, BigQuery ими полностью владеет. Быстро, но storage занимает квоту.

External table — это таблица, где данные остаются снаружи (в Google Cloud Storage, GCS), а BigQuery только читает их по запросу. Метаданные в BigQuery, данные в файле. Это паттерн “data lake”: сырьё лежит в дешёвом object store, а DWH читает его как таблицу.

Минимальный пример: создаём external table поверх Parquet-файла, который лежит в GCS-staging:

CREATE OR REPLACE EXTERNAL TABLE `nyc_taxi.fct_trips_external`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-de-staging-bucket/fct_trips/*.parquet']
);

После этого SELECT по fct_trips_external читается так же, как по обычной таблице, но данные тянутся из GCS на лету. Когда что выбирать:

• Native — горячие данные, частые запросы, нужна скорость и партиции. Это наша звезда.
• External — холодные/сырые данные, редкие запросы, не хочется дублировать storage, или данные шарятся между несколькими движками (BigQuery, Spark, Trino читают один и тот же файл в GCS).

Шаг 8. Запрос 3 — базовый IAM и доступ к dataset

На собесе junior DE спрашивают “как ты дашь аналитику доступ только на чтение к одному dataset”. В BigQuery доступ управляется через IAM-роли, выданные на уровне dataset.

Декларативный способ — через GRANT прямо в SQL (роль roles/bigquery.dataViewer = только чтение):

GRANT `roles/bigquery.dataViewer`
ON SCHEMA `nyc_taxi`
TO "user:[email protected]";

Ожидаемый вывод:

Access granted.

То же самое через CLI-патч access-политики dataset:

bq update --dataset \
  --grant_access_role=READER \
  [email protected] \
  de-fundamentals-sandbox-123456:nyc_taxi

Ключевые роли BigQuery, которые junior должен знать:

• bigquery.dataViewer — читать данные таблиц в dataset.
• bigquery.dataEditor — читать и писать (создавать/менять таблицы).
• bigquery.jobUser — запускать запросы (тратить compute). Отдельно от доступа к данным.
• bigquery.admin — всё, включая управление доступом. Не раздавай налево.

Сводка: что ты сделал

Это уже сильная строчка в pet-project: “загрузил dimensional model в BigQuery, настроил партиционирование с проверкой стоимости через dry-run, разграничил доступ через IAM”. Добавь в README капстона раздел “Cloud DWH” со скриншотом BigQuery Console — это отличает кандидата, который читал про облако, от того, кто реально его трогал.

Попробуй сам

1. Запусти bq query --dry_run по своей fct_trips со SELECT * и потом с SELECT pickup_location_id, total_amount. Сравни bytes scanned. Объясни себе, почему колоночность даёт разницу даже без партиций.
2. Создай партиционированную копию dim_date по году. Подумай: имеет ли смысл партиционировать маленькое измерение? (Подсказка: нет — партиции окупаются только на больших таблицах, мелкие справочники сканируются целиком и так дёшево.)
3. Выдай dataViewer на dataset тестовому email-адресу через bq update, потом отзови через bq update --revoke_access_user. Проверь access-политику командой bq show --format=prettyjson nyc_taxi.
4. Положи dim_payment_type.csv в seeds/ dbt-проекта и сделай dbt seed против BigQuery. Сравни ощущение с bq load: где удобнее для маленьких справочников?