Як скоротити витрати на 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`

TABLESAMPLE SYSTEM (1 PERCENT)

WHERE 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`

SET OPTIONS (

 `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 вимагає поєднання технічних найкращих практик і стратегічного планування. Застосувавши ці методики, ви зможете значно покращити продуктивність запитів, знизити витрати та отримати глибші інсайти з ваших даних.

Якщо вам потрібна будь-яка підтримка, не соромтеся зв’язатися з нами, ми з радістю допоможемо.