Как можно оптимизировать поиск данных в Bigquerry?

Оптимизация поиска данных в BigQuery

Введение

BigQuery — это бессерверное хранилище данных от Google, оптимизированное для аналитических запросов. Оптимизация поиска (performance tuning) критична для снижения затрат и ускорения анализа.

1. Понимание модели оплаты и производительности BigQuery

На что влияет производительность:

• Время выполнения — зависит от объёма сканируемых данных
• Стоимость — оплачивается по объёму отсканированных данных (за 1 Тб = ~$6.25 на запрос)
• Параллелизм — BigQuery автоматически распределяет запрос на тысячи слотов

# Пример: запрос без оптимизации
SELECT * FROM `project.dataset.events`  -- сканирует ВСЕ колонки
WHERE event_date >= '2024-01-01'
LIMIT 100;

# Это дорого! Даже с LIMIT, BigQuery сканирует весь датасет

2. Партиционирование (Partitioning)

Суть: Разделить таблицу на логические части по одному полю (обычно дата). BigQuery сканирует только нужные партиции.

-- Создание партиционированной таблицы
CREATE OR REPLACE TABLE `project.dataset.events` 
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
AS
SELECT 
    event_timestamp,
    user_id,
    event_type,
    event_value
FROM `project.dataset.raw_events`
WHERE event_timestamp >= '2024-01-01';

-- Теперь запрос сканирует только нужные дни
SELECT COUNT(*) FROM `project.dataset.events`
WHERE DATE(event_timestamp) >= '2024-03-01'
  AND user_id = 'user_123';  -- сканирует только март + фильтр по user_id

Преимущества:

• Снижает объём сканируемых данных до 90%
• Фильтры по дате/времени (partition key) не требуют полного сканирования

# Проверка стоимости запроса (dry run)
from google.cloud import bigquery

client = bigquery.Client()
job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
query = """
SELECT COUNT(*) FROM `project.dataset.events`
WHERE DATE(event_timestamp) = '2024-03-15'
"""
job = client.query(query, job_config=job_config)
print(f"Будет отсканировано: {job.total_bytes_processed / (1024**3):.2f} GB")

3. Кластеризация (Clustering)

Суть: Физически отсортировать данные по нескольким колонкам. BigQuery быстрее находит данные в отсортированных блоках.

-- Кластеризация по часто используемым фильтрам
CREATE OR REPLACE TABLE `project.dataset.orders`
PARTITION BY DATE(order_date)
CLUSTER BY customer_id, region, product_category
AS
SELECT 
    order_id,
    customer_id,
    region,
    product_category,
    amount,
    order_date
FROM `project.dataset.raw_orders`;

-- Запрос с кластеризацией работает НАМНОГО быстрее
SELECT * FROM `project.dataset.orders`
WHERE customer_id = 'cust_456'
  AND region = 'US'
  AND product_category = 'Electronics';

Правила выбора полей для кластеризации:

1. Поля, часто используемые в WHERE
2. Поля для GROUP BY и JOIN
3. Поля с высокой кардинальностью (много уникальных значений)
4. Максимум 4 поля на кластер

4. Выбор нужных колонок (Column Projection)

Суть: BigQuery платит только за отсканированные колонки. Не выбирай SELECT *.

# ПЛОХО: сканирует ВСЕ колонки (дорого и медленно)
SELECT * FROM `project.dataset.user_events`;

# ХОРОШО: выбираешь только нужные
SELECT 
    user_id,
    event_timestamp,
    event_type
FROM `project.dataset.user_events`
WHERE event_timestamp >= '2024-01-01';

Почему это работает:

• BigQuery хранит данные в columnar format (ORC/Parquet)
• Каждая колонка сжата и индексирована отдельно
• Чтение одной колонки из 100 — это чтение 1%, не 100%

5. Использование денормализованных таблиц (Denormalization)

Суть: Вместо JOIN'ов многих таблиц создай одну денормализованную таблицу.

-- МЕДЛЕННО: 3 JOIN'а
SELECT 
    o.order_id,
    o.amount,
    c.customer_name,
    c.city,
    p.product_name,
    p.category
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id
JOIN `project.dataset.products` p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';

-- БЫСТРО: одна читает таблица со всеми данными
CREATE OR REPLACE TABLE `project.dataset.orders_denormalized`
PARTITION BY DATE(order_date)
CLUSTER BY customer_id, product_category
AS
SELECT 
    o.order_id,
    o.amount,
    o.order_date,
    c.customer_name,
    c.city,
    p.product_name,
    p.category AS product_category
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id
JOIN `project.dataset.products` p ON o.product_id = p.product_id;

-- Теперь запрос мгновенен
SELECT * FROM `project.dataset.orders_denormalized`
WHERE order_date >= '2024-01-01' AND customer_id = 'cust_123';

6. Материализованные представления (Materialized Views)

Суть: Pre-computed результаты сложных запросов, обновляемые автоматически.

-- Создание материализованного view
CREATE MATERIALIZED VIEW `project.dataset.daily_revenue`
PARTITION BY DATE(date)
CLUSTER BY product_category
AS
SELECT 
    DATE(order_date) AS date,
    product_category,
    SUM(amount) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*) AS order_count
FROM `project.dataset.orders_denormalized`
GROUP BY date, product_category;

-- Запрос становится мгновенным (читает материализованный view)
SELECT * FROM `project.dataset.daily_revenue`
WHERE date >= '2024-03-01' AND product_category = 'Electronics';

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

• Сложные агрегации (SUM, COUNT, AVG)
• Часто повторяющиеся запросы
• Размер результата < 1% от исходных данных

7. Использование слотов (Slots) для гарантированной производительности

from google.cloud import bigquery

client = bigquery.Client()

# Назначить запрос на выделенные слоты
job_config = bigquery.QueryJobConfig(
    labels={"env": "production"},
    priority=bigquery.QueryPriority.BATCH,  # BATCH — дешевле, но медленнее
)

job_config.use_legacy_sql = False
job_config.maximum_bytes_billed = 1000000000  # 1 GB максимум

query = "SELECT COUNT(*) FROM `project.dataset.events`"
job = client.query(query, job_config=job_config)
result = job.result()

8. Кэширование результатов

BigQuery автоматически кэширует результаты на 24 часа.

# Кэш работает, если:
# 1. Запрос идентичен (включая пробелы и комментарии)
# 2. Таблицы не изменились
# 3. Используется стандартный SQL (не legacy)

# Это НЕ сканирует данные (читает кэш)
SELECT COUNT(*) FROM `project.dataset.events`;
# 0 байт отсканировано

# Отключить кэш (для тестирования)
job_config.use_query_cache = False

9. Оптимизация JOIN'ов

-- ХОРОШО: Broadcast join (маленькая таблица слева)
SELECT o.*, p.product_name
FROM `project.dataset.products` p  -- маленькая таблица (< 1GB)
JOIN `project.dataset.orders` o ON o.product_id = p.product_id;

-- ПЛОХО: большая таблица нужно жертвовать для broadcast
SELECT o.*, p.product_name
FROM `project.dataset.orders` o  -- большая таблица
JOIN `project.dataset.products` p ON o.product_id = p.product_id;

-- РЕКОМЕНДАЦИЯ: использовать ARRAY_AGG для denormalization
SELECT 
    o.order_id,
    ARRAY_AGG(STRUCT(
        p.product_name,
        oi.quantity,
        oi.price
    )) AS items
FROM `project.dataset.orders` o
JOIN `project.dataset.order_items` oi ON o.order_id = oi.order_id
JOIN `project.dataset.products` p ON oi.product_id = p.product_id
GROUP BY o.order_id;

10. Мониторинг и анализ производительности

from google.cloud import bigquery
import pandas as pd

client = bigquery.Client()

# Получить информацию о последних запросах
query = """
SELECT 
    job_id,
    user_email,
    ROUND(total_bytes_billed / POW(10,9), 2) AS gb_billed,
    ROUND(total_bytes_processed / POW(10,9), 2) AS gb_scanned,
    ROUND(total_slot_ms / 1000, 2) AS slot_seconds,
    statement_type,
    total_logical_bytes_read
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY creation_time DESC
LIMIT 100
"""

result = client.query(query).to_dataframe()
print(result)

# Найти самые дорогие запросы
top_expensive = result.nlargest(10, 'gb_billed')
print("\nТоп 10 дорогих запросов:")
print(top_expensive[['job_id', 'gb_billed', 'statement_type']])

Чеклист оптимизации

• ✅ Используй партиционирование по дате
• ✅ Добавь кластеризацию по часто фильтруемым полям
• ✅ Выбирай только нужные колонки (не SELECT *)
• ✅ Избегай множественных JOIN'ов (используй денормализацию)
• ✅ Используй материализованные view для сложных агрегаций
• ✅ Мониторь стоимость запросов через INFORMATION_SCHEMA
• ✅ Кэшируй результаты повторяющихся запросов
• ✅ Для экспериментов используй BATCH priority (дешевле)

Типичные результаты оптимизации

Вывод

Оптимизация в BigQuery — это балансировка между:

• Скоростью запросов
• Стоимостью обработки (GB отсканировано)
• Сложностью поддержания структуры данных

Сочетание партиционирования, кластеризации и денормализации может снизить стоимость в 50-100 раз при одновременном ускорении запросов в 100+ раз.