Что нужно учитывать при работе с Bigquerry?

Что нужно учитывать при работе с BigQuery

BigQuery — это полностью управляемое облачное хранилище и query engine от Google. Это один из самых популярных data warehouse'ов, но он имеет множество особенностей и подводных камней.

1. Ценообразование: анализ vs хранение

BigQuery заряжает за две вещи:

1. Анализ: $7 за 1 TB просканированных данных
   - Сканируются ВСЕ столбцы в SELECT, даже если не используются
   - Если query сканирует 100GB, платишь $0.70
   - Это может быть дорого для частых больших queries

2. Хранение: $0.02-0.04 за 1 GB/месяц
   - Активное (последние 90 дней): $0.02
   - Долгосрочное (> 90 дней): $0.01
   - Экспортированные данные: бесплатно

Практический пример:

Table: events (1 TB)
Columns: 50 (event_id, user_id, timestamp, ... 47 others)

Query 1 (ПЛОХО):
SELECT * FROM events WHERE timestamp > '2024-01-01'
- Сканирует все 50 columns = 1 TB = $7 cost

Query 2 (ХОРОШО):
SELECT event_id, user_id, timestamp 
FROM events 
WHERE timestamp > '2024-01-01'
- Сканирует только 3 columns = ~60 GB = $0.42 cost
- Экономия: $6.58 за один query!

Правило: Always specify column names, никогда не используй SELECT *

2. Партиционирование и кластеризация

Партиционирование по time

CREATE TABLE events_partitioned (
    event_id STRING,
    user_id STRING,
    timestamp TIMESTAMP,
    data JSON
)
PARTITION BY DATE(timestamp)  -- Разбить по датам
CLUSTER BY user_id;  -- Дополнительно кластеризовать

-- Когда query'шь, укажи WHERE на partition column
SELECT COUNT(*) 
FROM events_partitioned
WHERE DATE(timestamp) = '2024-03-26'  -- Сканирует только этот день!
-- Стоимость падает с $7 на $0.01 (вместо сканирования года)

Кластеризация для частых joins

CREATE TABLE users_clustered (
    user_id STRING,
    name STRING,
    email STRING
)
CLUSTER BY user_id;  -- Физически группирует rows

-- Join будет быстрее и дешевле
SELECT e.event_id, u.name
FROM events_partitioned e
JOIN users_clustered u ON e.user_id = u.user_id
WHERE DATE(e.timestamp) = '2024-03-26';

Best Practice:

• Всегда партиционируй большие таблицы (> 1GB)
• Partition column обычно timestamp или date
• Кластеризуй по columns, которые используются в WHERE и JOIN

3. Slots vs Query pricing

On-demand (default)

$7 per TB scanned
- Гибко
- Для unpredictable workloads
- Но может быть дорого если много queries

BigQuery Slots (reservation)

Ежегодное/ежемесячное обязательство:
- Annual: $0.04 per slot per hour = ~$350/slot/month
- Monthly: $0.05 per slot per hour = ~$365/slot/month
- 1 Slot = ~15 GB/sec processing capacity

Прибыльно если:
- Monthly query volume > $400
- Predictable workload
- Multiple concurrent queries

Когда использовать:

Small team (< 10 people): On-demand
Medium organization (100 queries/day): 100-500 slots
Large organization (1000+ queries/day): 1000+ slots

4. Dataset и Table Expiration

Случайная потеря данных = худшее

from google.cloud import bigquery

client = bigquery.Client()
dataset = client.dataset('my_dataset')
dataset.location = 'US'

# НЕ устанавливай default expiration!
# dataset.default_table_expiration_ms = 0  # GOOD: таблицы не удаляются

# Вместо этого явно управляй TTL
table = client.get_table(dataset.table('temp_table'))
table.expires_at = None  # Никогда не удалять
# или
table.expires_at = datetime.utcnow() + timedelta(days=7)  # Удалить через 7 дней

client.update_table(table, ['expires_at'])

Temp tables для промежуточных результатов

-- Автоматически удаляются через 24 часа
CREATE TEMP TABLE temp_users AS
SELECT user_id, COUNT(*) as event_count
FROM events
GROUP BY user_id;

-- Используй для промежуточных joins
SELECT *
FROM temp_users tu
JOIN other_table ot ON tu.user_id = ot.user_id;

5. Query Optimization

Ошибка 1: GROUP BY на высокую cardinality column

-- ПЛОХО (медленно)
SELECT user_id, COUNT(*) 
FROM events
GROUP BY user_id  -- Может быть миллионы unique users!
-- BigQuery должен создать huge hash table

-- ХОРОШО (быстро)
SELECT user_id, COUNT(*) 
FROM events
WHERE timestamp > '2024-03-20'  -- Сначала фильтруй
GROUP BY user_id
-- Меньше rows -> быстрее GROUP BY

Ошибка 2: Nested loop joins

-- ПЛОХО
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id
WHERE u.country = 'US'  -- Фильтр ПОСЛЕ JOIN!
-- Join все, потом фильтруй

-- ХОРОШО
SELECT *
FROM (
    SELECT * FROM users WHERE country = 'US'  -- Фильтруй ПЕРВЫМ
) u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
-- Меньше rows при join -> быстрее

Ошибка 3: String operations на большие columns

-- ПЛОХО (BigQuery должен распарсить JSON для каждой row)
SELECT 
    JSON_EXTRACT_SCALAR(data, '$.custom_field') as value,
    COUNT(*)
FROM events
GROUP BY JSON_EXTRACT_SCALAR(data, '$.custom_field')

-- ХОРОШО (предварительно распарсенный column)
SELECT 
    custom_field,  -- Уже был распарсен при load
    COUNT(*)
FROM events
GROUP BY custom_field

6. Loading данных

Batch load (рекомендуется)

from google.cloud import bigquery

client = bigquery.Client()

# Загрузить из CSV
job_config = bigquery.LoadJobConfig(
    source_format=bigquery.SourceFormat.CSV,
    skip_leading_rows=1,
    autodetect=True,  # Автоматически определи schema
)

load_job = client.load_table_from_uri(
    "gs://my-bucket/data.csv",
    "my_dataset.my_table",
    job_config=job_config,
)

load_job.result()  # Ждем завершения

# БЕСПЛАТНО при загрузке из GCS!

Streaming insert (избегай для batch)

# ПЛОХО: используй для real-time streaming только
errors = client.insert_rows_json(
    "my_dataset.my_table",
    [{"user_id": "123", "value": 456}],
)

if errors:
    print(f"Errors: {errors}")

# Проблемы:
# - Медленнее, чем batch load
# - Дорого если много rows
# - Задержка может быть 1-2 минуты

7. Форматы данных

Parquet (рекомендуется)

- Columnar format
- Отличная compression
- Быстро для analytics
- Поддержка nested types

Стоимость сканирования: НИЗКАЯ
Основной формат для big tables

Avro (для ETL)

- Row-based, schema included
- Хорошо для Kafka integration
- Schema evolution support

Стоимость сканирования: СРЕДНЯЯ
Для промежуточных데이터

JSON (избегай для больших объемов)

- Human readable
- Медленнее других
- Большой размер

Стоимость сканирования: ВЫСОКАЯ
Только для мелких datasets

8. Управление доступом и безопасность

# IAM roles
roles = {
    "roles/bigquery.dataEditor": "Может читать и писать таблицы",
    "roles/bigquery.dataViewer": "Только читать",
    "roles/bigquery.admin": "Полный доступ"
}

# Column-level security (в Enterprise только)
# Скрыть sensitive columns от некоторых users

9. Мониторинг и управление квотами

# Setup quotas
project_id = 'my-project'

# Limit: max 1000 queries в day
# Setup alert если превышено

# Мониторь с помощью Cloud Monitoring
from google.cloud import monitoring_v3

client = monitoring_v3.MetricServiceClient()
project_name = f"projects/{project_id}"

# Fetch query count metric
# Alert если costs растут unexpectedly

10. Common Mistakes

❌ SELECT * from tables Всегда указывай нужные columns

❌ No partitioning на large tables Партиционируй по timestamp или другому колхозу

❌ Не использование WHERE clause Фильтруй как можно раньше в query

❌ Cross joins без причины Часто неправильно joined tables

❌ Scheduled queries без мониторинга costs Мог неожиданный spike в billing

❌ Не использование reservation при predictable workload Could сэкономить деньги

Best Practices Summary

1. Партиционируй большие таблицы (по timestamp обычно)
2. Кластеризуй по join/filter columns
3. Всегда specify column names (не SELECT *)
4. Filter как можно раньше (в WHERE, не в aggregation)
5. Используй Parquet для storage
6. Batch load из GCS (бесплатно vs paid insert)
7. Мониторь costs и используй reservation если нужно
8. Используй materialized views для часто используемых queries
9. Тестируй на sample data перед production queries
10. Документируй big tables в data catalog

BigQuery мощный, но требует дисциплины в оптимизации. При правильном использовании может быть и быстро и дешево.