Как сократить расходы на BigQuery: 10 проверенных лайфхаков для оптимизации

Google BigQuery – это мощный инструмент для анализа больших объемов данных, но с ростом ваших данных становится все важнее оптимизировать его производительность.

Вот несколько хитростей, которые помогут уменьшить расходы на BigQuery и обеспечить эффективный и экономный анализ данных.

Избегайте SELECT в запросах

Раньше использование SELECT* и выбор конкретных полей не имело большого значения, поскольку считывался весь набор данных (и за это вы платили полностью), а затем данные фильтровались и отображались вам.

В BigQuery выбор столбцов напрямую влияет на ваши расходы. Выбирая только нужные поля, вы можете существенно снизить затраты на запросы и улучшить их производительность.

Заместо этого:

SELECT *

FROM `project.dataset.events`

Используйте:

SELECT event_date, event_name, user_pseudo_id

FROM `project.dataset.events`

Выбирая конкретные поля, вы уменьшаете объем обрабатываемых данных и повышаете скорость выполнения запросов.

Используйте приблизительные подсчеты для больших агрегаций

Не всегда нужно иметь абсолютно точное число, когда работаете с большими наборами данных. Например, вместо точного 10,601,340, большинство анализов будет столь же полезна с приближенным числом, как 10,600,000. Такой компромисс может значительно снизить затраты.

BigQuery предлагает функции для приближенных подсчетов, такие как HLL_COUNT_DISTINCT и APPROX_COUNT_DISTINCT, которые используют HyperLogLog++ для оценки уникальных значений. HyperLogLog++ – это умный алгоритм, который оценивает количество уникальных значений, не просматривая все данные. Он быстрый, потребляет меньше памяти и обычно точен с небольшой погрешностью. Эта погрешность зависит от настроек, но обычно обеспечивает относительную ошибку около 1% для больших наборов данных, что делает его достаточно надежным для большинства аналитических задач.

Например, представьте себе набор данных GA4, где нужно подсчитать уникальных пользователей:

SELECT APPROX_COUNT_DISTINCT(user_pseudo_id) AS approx_user_count

FROM `project.analytics_123456.events_*`

WHERE _table_suffix BETWEEN ‘2024-01-01’ AND ‘2024-01-31’

Этот запрос оценит количество уникальных пользователей за этот период, снижая вычислительные затраты по сравнению с использованием COUNT(DISTINCT user_pseudo_id).

Начинайте с запусков запросов с выборочными данными

Часто опытные аналитики запускают запросы, обрабатывающие петабайты данных, лишь чтобы понять, что пропустили несколько полей, и им пришлось запускать все заново.

Впрочем, мы рекомендуем использовать TABLESAMPLE, где вы можете запросить только определенный процент вашей таблицы, а когда вы усовершенствуете свой запрос, просто закомментируйте одну строку.

SELECT event_date, user_pseudo_id

FROM `project.dataset.events`

СИСТЕМА ТАБЛИЦЫ (1 ПЕРИОД)

ГДЕ event_date = ‘2024-01-01’

Добавляя всего лишь TABLESAMPLE SYSTEM (1 PERCENT), ваш счет за этот запрос в BigQuery уменьшится до 1% от полного запроса.

Мы также должны предупредить, что это возвращает только 1% данных, поэтому это отлично подходит для запросов в процессе работы, но не для производственных запросов.

Внедрение семантического слоя

Семантический слой – это промежуточное звено между вашим дашбордом и BigQuery. Он позволяет создавать правила для кэширования, предоставляя уже запрошенные данные без повторного обращения к BigQuery.

Предположим, что вы запустили довольно тяжелый запрос за последние 30 дней, и он вернул терабайты данных. BigQuery взимает плату за каждый запрос, поэтому если трое ваших коллег запустят один и тот же запрос, ваш проект будет оплачиваться четыре раза за один и тот же запрос.

Семантический слой добавляет слой кэширования между дашбордами и BigQuery. Вы можете настроить его так, чтобы запрос выполнялся только один раз для проекта, а все последующие обращения получали кэшированные результаты, которые не запускаются в BigQuery. Это экономит три дополнительных выполнения запроса и соответствующие расходы. [Если интересно, как это работает на практике, создается своего рода “псевдо-склад”, из которого вы получаете данные, а не непосредственно из BigQuery].

BigQuery имеет собственную историю кэширования, но она работает на уровне отдельного пользователя, а не на уровне проекта. Поэтому, если несколько пользователей запрашивают одни и те же данные, они не получают никакого прироста производительности. Только когда один и тот же пользователь делает запрос к тем же данным, можно увидеть улучшение производительности.

Looker (не Looker Studio) имеет собственный семантический слой под названием LookML, где можно добавить немало контекста к данным, чтобы упростить получение ответов для нетехнических пользователей.

Если вы не используете Looker, есть несколько новых игроков, которые хорошо работают с семантическими слоями. DBT и Cube делают большие шаги в этом направлении.

Разделяйте и кластеризуйте свои таблицы

Чтобы ваши данные GA4 работали на максимум, стоит понять, что оригинальная структура данных GA4 не совсем соответствует лучшим практикам. Разделение данных на части (когда создается новая таблица для каждого дня) не позволяет в полной мере использовать преимущества разделения, что является отличной техникой оптимизации для баз данных, таких как BigQuery.

Чтобы исправить это, я рекомендую перестроить таблицы GA4 в единую объединенную таблицу и добавить следующие оптимизации:

• Разделение: Разделение организует таблицу по конкретному полю, что помогает легче извлекать данные. Для GA4 я обычно разделяю по полю event_date, а для крупных клиентов – иногда по event_timestamp (почасово). Эта настройка оптимизирует запросы для ежедневных или почасовых данных. Если вы используете одну таблицу, убедитесь, что каждый запрос имеет фильтр разделения, чтобы избежать полных сканирований таблицы и снизить затраты на запросы.
  
• Кластеризация: Кластеризация группирует похожие значения в таблице. Я минимально кластеризую по user_pseudo_id и event_name. Это позволяет оптимизировать запросы при анализе путей пользователя или выборе конкретных событий.

Под время создания таблицы мы также распаковываем несколько полезных точек данных, таких как page_location, и добавляем новые измерения, такие как landing_page и page_path. Эти улучшения повышают производительность запросов и обеспечивают более насыщенные инсайты.

Используйте инкрементальные стратегии

Если вы загружаете данные в BigQuery с помощью стороннего коннектора, важно убедиться, что загружаются только новые данные, а не заменяются уже существующие. Этот подход, известный как инкрементальная стратегия, может значительно улучшить производительность и снизить затраты.

Поверьте, это чрезвычайно важно. Мы видели ситуации, когда перегружали петабайты данных, что означало замену нескольких лет данных, тогда как нужно было лишь добавить данные за вчерашний день. Используя инкрементальный подход, вы экономите время, место для хранения и затраты на обработку.

Предварительно агрегируйте данные

Данные, такие как GA4, не оптимизированы для аналитики из коробки. Предварительное агрегирование часто используемых метрик может значительно улучшить производительность и уменьшить затраты на запросы. Например, можно создать сводные таблицы для сохранения предварительно вычисленных метрик, таких как:

• Количество событий (например, общее количество событий за день)
• Доход от покупок (например, общий доход, сгруппированный по дню или кампании)
• Количество новых пользователей (например, уникальные пользователи, определенные при первом посещении)
• Количество активных пользователей (например, ежедневные или ежемесячные активные пользователи)

Хотя предварительное агрегирование отлично подходит для таких метрик, стоит быть осторожными с данными пользователей, которые зависят от количества сеансов или уникальных пользователей за определенный период. Агрегации на основе уже агрегированных данных, например, расчет “сеансов на пользователя” за месяц, могут приводить к неточностям из-за удвоения пользователей в разных периодах.

Этот метод помогает упростить запросы и ускорить создание отчетов для часто используемых метрик, обеспечивая точность и экономическую эффективность.

Включите оптимизации на основе истории

BigQuery может постепенно улучшать выполнение запросов благодаря оптимизациям на основе истории. Чтобы эта функция работала, убедитесь, что она включена в ваших настройках, что позволит BigQuery использовать исторические планы выполнения и статистику для повышения производительности.

Чтобы активировать эту функцию на уровне проекта, вы можете воспользоваться общим шаблоном или конкретным примером:

ALTER PROJECT `ИМЯ’Я_ПРОЕКТУ`

SET OPTIONS (

`region-ЛОКАЦИЯ.default_query_optimizer_options` = ‘adaptive=on’

);

Если у вас есть проект с названием growth-runnerв регионе ЕС, ваш запрос будет выглядеть так:

ALTER PROJECT `growth-runner`

Установить опции (

`region-eu.default_query_optimizer_options` = ‘adaptive=on’

);

Это включит адаптивные оптимизации для ваших запросов, что повысит эффективность. Подробнее об оптимизациях на основе истории можно узнать в документации Google Cloud.

Физический и логический учет хранения

BigQuery взимает плату за физическое и логическое хранение, поэтому важно понять разницу между ними:

• Физическое хранение касается фактического пространства, которое занимают ваши данные, включая сжатые данные и исторические снимки (time travel).
• Логическое хранение – это размер данных после полной распаковки, включающий все колонки, даже если они заполнены частично.

Функция time travel, позволяющая запрашивать предыдущие состояния таблицы (включая случайно удаленные данные), может значительно увеличить физическое хранение. Хотя это мощная возможность, стоит периодически просматривать исторические снимки и решать, необходимо ли их сохранять. Например, снижение окна time travel с 7 дней до 1 дня может сократить расходы на физическое хранение без значительного влияния на большинство рабочих процессов.

Настройки управляются на уровне набора данных, и их можно изменить, просто нажав “редактировать” и найдя соответствующие параметры в этих разделах.

Принимая во внимание эти различия, вы сможете более эффективно управлять затратами, используя мощные функции BigQuery. Больше информации о time travelможно найти в документации Google Cloud.

Использование слотов BigQuery для контроля расходов

Если ваши расходы на BigQuery превышают $1,500 в месяц, возможно, стоит инвестировать в слоты BigQuery. Слоты обеспечивают модель фиксированной цены, предлагающую предсказуемые расходы и достаточную вычислительную мощность для больших нагрузок.

С слотами вы покупаете определенное количество вычислительной мощности BigQuery, измеряемой в слот-часах, за фиксированную ежемесячную плату. Например:

• Почасовая оплата: Запросы оплачиваются за объем обработанных данных. Для крупномасштабной аналитики это может привести к непредсказуемым и высоким затратам.
  
• Фиксированная цена: Взамен вы платите фиксированную сумму (например, примерно $1,500 в месяц за 50 слотов) и ограничены использованием слотов для выполнения запросов.

Пример:

Если ваша команда ежедневно выполняет много сложных запросов GA4, обрабатывая сотни терабайт данных, ваши ежемесячные расходы могут превысить $2,000. Но, приобретя 50 слотов за ~$1,500 в месяц, вы получите предсказуемый расход каждый месяц. Обратите внимание, что с использованием слотов запросы могут выполняться немного дольше во время пиковых нагрузок, поскольку мощность делится между всеми пользователями.

Чтобы узнать больше о слотах BigQuery и определить подходящую мощность для ваших потребностей, просмотрите документацию о ценообразовании слотов BigQuery.

Выводы

Оптимизация анализа данных GA4 с помощью BigQuery требует сочетания технических лучших практик и стратегического планирования. Применив эти методики, вы сможете значительно улучшить производительность запросов, снизить затраты и получить более глубокие инсайты из ваших данных.

Если вам нужна какая-либо поддержка, не стесняйтесь связаться с нами, мы с радостью поможем.