Запуск SQL-запросов по расписанию в BigQuery через Schedule Query

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

Дашборды без ETL

Представим, что у нас есть данные в BigQuery. Чтобы построить отчет за сутки, нам нужно обработать 0,5 Гбайт данных. Допустим мы строим дашборд из 5-ти страниц и на каждой размещаем по 3 диаграммы с динамикой за 14 дней. Пускай дашборд будут смотреть 10 человек в компании по одному разу в сутки, тогда за месяц (20 рабочих дней) мы обработаем данных:

0,5 Гбайт * 5 страниц * 3 диаграммы * 14 дней * 10 человек * 20 дней = 21 000 Гбайт = 21 Тбайт

Дашборды с применением ETL

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

Запрос к агрегированной таблице в среднем составит максимум 10 Мбайт. Тогда за месяц, при аналогичных условиях использования дашбордов, мы обработаем данных:

0,01 Гбайт * 5 страниц * 3 диаграммы * 14 дней * 10 человек * 20 дней = 420 Гбайт = 0,42 Тбайт

При формировании самих агрегированных таблиц обработаем:

0,5 Гбайт * 30 дней = 15 Гбайт = 0,015 Тбайт

Преимущества использования ETL

• быстрое обновление данных в отчетах;
• возможность просмотра динамики показателей за большой период времени;
• снижение объема обрабатываемых данных более чем в 50 раз.

Как реализовать ETL прямо в интерфейсе BigQuery?

Совсем недавно Google анонсировал возможность запускать SQL-запросы по расписанию. Покажу на примере, как создать агрегированную таблицу, в новую партицию которой каждое утро будут записываться обработанные данные за вчерашний день:

1. Пишем SQL-запрос, который установим на расписание. В примере ниже запрос считает количество записей за день публичного датасета Firebase.
2. Нажимаем на «Schedule Query».
3. В поле «Display name» указываем название запроса.
4. В поле «Destination dataset» указываем датасет, в котором хотим создать агрегированную таблицу.
5. В поле «Destination table» указываем название агрегированной таблицы (создавать ее не нужно, она появится автоматически). В примере используем колоночное партиционирование (для табличного нужно знак доллара «$» заменить на нижнее подчеркивание «_»)
6. В поле «Write preference» выбираем «WRITE_APPEND», что означает добавление результата запроса к таблице без перезаписи уже существующих данных.
7. Жмем «Add».

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

Разработчики предусмотрели это и добавили функционал ручного запуска запросов. Просто выбираем свой запрос из списка в разделе «Scheduled Queries» и нажимаем «Start manual runs».

После чего указываем диапазон дат, за который мы хотим видеть историю агрегированных данных.

Как видите процедура довольно простая. Настроить ETL теперь можно самостоятельно без внешних инструментов.

Подписывайтесь на мой канал BigQuery Insights. Там я делюсь интересными материалами по аналитике в BigQuery и не только.

+ Полезные материалы:

Примеры работы в BigQuery с данными:

- Firebase (ссылка 1, ссылка 2, ссылка 3 и ссылка 4),

- Google Analytics (ссылка 1, ссылка 2, ссылка 3, ссылка 4, ссылка 5) и почему метрики в интерфейсе могут отличаться.