Автор: Guts
BigQuery – потужний інструмент для аналізу та обробки даних, але споживання ресурсів може швидко зрости, якщо не контролювати процес. У моєму випадку виникла проблема з надмірним споживанням слотів, де щоденне використання досягало 400 слотів на годину. Завдяки серії оптимізацій мені вдалося знизити споживання слотів до 100 на годину. Нижче я поділюся кроками, та практичними рекомендаціями для оптимізації витрат у BigQuery.
Крок 1: Визначення проблеми та вибір відповідного білінгового плану
Першим кроком завжди є виявлення проблеми. Чи полягає вона у кількості слотів, що споживаються, чи у обсязі оброблюваних даних? Відповідь на це питання залежить від вашого білінгового плану — чи сплачуєте ви за обсяг даних (гігабайти) або за слоти.
Крок 2: Аналіз джобів
Після визначення білінгового плану наступний крок — це виявлення проблем у запитах. Для аналізу джобів можна скористатися двома ключовими інструментами:
1. Jobs Explorer в GCP: Перейдіть до «Jobs Explorer» у консолі GCP, щоб отримати детальні відомості про виконання джобів.
2. Аналіз білінгу за допомогою логів: Я налаштував передачу логів GCP до окремого датасету та візуалізував інформацію для кращого аналізу витрат.
Завдяки цим інструментам можна виявити основні джерела проблем з оптимізацією, зокрема надмірне використання слотів або обробку великих обсягів даних.
Можливі проблеми та їх вирішення
Нижче наведено найпоширеніші проблеми та відповідні рішення:
1. Обробка зайвих даних через неефективні фільтри або JOIN-и:
- Проблема: Запити обробляють занадто багато даних через неправильно налаштовані фільтри або JOIN-и.
- Рішення: Перевірте правильність всіх умов і оптимізуйте фільтри.
Приклад:
SELECT * FROM large_table lt
JOIN small_table st ON lt.id = st.id
WHERE lt.date >= '2023-01-01';2. Використання SELECT * замість зазначення конкретних колонок:
- Проблема: Виклик усіх колонок із великої таблиці призводить до обробки зайвих даних.
- Рішення: Вибирайте лише необхідні колонки.
Приклад:
SELECT id, name FROM large_table;3. Використання ресурсомістких виразів:
- Проблема: Функції на кшталт REGEXP_CONTAINS можуть споживати багато ресурсів.
- Рішення: Замініть складні вирази простішими.
Приклад:
SELECT * FROM table WHERE column LIKE '%word%';4. Непотрібне використання ORDER BY або GROUP BY на великих наборах даних:
- Рішення: Уникайте цих операцій, якщо вони не є обов'язковими.
5. Зберігання надлишкових даних:
- Проблема: Зберігання зайвих даних підвищує витрати.
- Рішення: Регулярно чистіть застарілі або дублюючі дані. Проводьте аудит в BigQuery та Cloud Storage, щоб визначити які дані можна видалити.
6. Запуск довготривалих скриптів без зупинки:
- Проблема: Скрипти можуть працювати у фоновому режимі та споживати ресурси.
- Рішення: Використовуйте моніторинг Jobs Explorer і вчасно зупиняйте тривалі запити.
7. JOIN великих таблиць до малих:
- Проблема: Виконання JOIN великих таблиць до малих може бути неефективним.
- Рішення: Змінюйте порядок JOIN-ів або додавайте менші таблиці до більших.
Приклад:
SELECT * FROM large_table lt
JOIN small_table st ON st.id = lt.id;8. Часті запуски малих скриптів:
- Проблема: Навіть маленькі скрипти при частих запусках можуть призвести до значних витрат.
- Рішення: Перегляньте частоту запуску скриптів та їх доцільність.
9. Використання тимчасових таблиць:
- Проблема: Повторюваний код може перевантажувати систему.
- Рішення: Використовуйте тимчасові таблиці для збереження проміжних результатів.
10. Використання полів PARTITION та CLUSTER:
- Проблема: Скрипти обробляють надлишкові дані через відсутність розбиття на розділи (PARTITION) або кластеризацію (CLUSTER), що уповільнює запити.
- Рішення: Розбийте таблицю на PARTITION за датою та додайте кластеризацію за часто використовуваними полями.
CREATE TABLE dataset.partitioned_table
PARTITION BY DATE(timestamp)
CLUSTER BY id;Мій досвід оптимізації скриптів
Під час оптимізації скриптів у BigQuery я фокусуюся на тих, що споживають найбільше ресурсів. Спочатку аналізуємо кожен SELECT на найнижчому рівні, поступово піднімаючись до складніших частин запиту. Після виявлення проблеми потрібно перевірити коректність фільтрів і JOIN-ів, обмежити обсяги даних, додаючи фільтри за датами, і мінімізувати кількість колонок. Іноді навіть зміна порядку JOIN-ів, наприклад, додавання менших таблиць до більших, допомагає покращити ефективність.
З часом проблеми стають легшими для виявлення та їх вирішення, адже вони часто повторюються.
Крок 3: Вибір оптимального білінгового плану після оптимізації
Після оптимізації я протестував два білінгових плани — on-demand та план на основі слотів. Тести показали, що план зі слотами, обмежений в 1000 слотів, виявився дешевшим для мого проекту. Однак я усвідомлював ризики, адже нові, не оптимізовані скрипти могли збільшити витрати.
Після тестування було знижено ліміт до 800 слотів на годину під час пікових навантажень, що дозволило ще більше скоротити витрати.
Завжди пам’ятайте, що усі проекти унікальні, не вийде просто вибрати вирішення вашої проблеми так як у мене і щоб воно принесло вам результат. До цього потрібно пройти довгий шлях тестуючи різні варіанти білінгу та оптимізовуючи власні процеси.
Додаткові ресурси
- Кращі практики з оптимізації витрат у BigQuery:
Висновок
Дотримуючись цих рекомендацій, ви зможете суттєво знизити споживання ресурсів у BigQuery. Аналізуйте свої запити, оптимізуйте їхню структуру та використовуйте найкращі практики для досягнення максимальної ефективності та мінімізації витрат.
*цю статтю допомагав форматувати ChatGPT
