0x0b приемов работы с BigQuery на Standard SQL

18.03.2018 / Сева Миронович

Как шутят сами представители Google — когда у пользователя возникает проблема с BigQuery, он может написать в поддержку, позвонить персональному менеджеру или запостить вопрос на stackoverflow.com. Угадайте, в каком случае ответ появится быстрее?

Тонкости настройки аналитики в Telegram канале

Валидатор

Не забывайте включать валидатор, и читайте внимательно всё, что он пишет. Как правило там сказано, почему получается то, что получается, и что нужно, чтобы было, что нужно.

Лимит данных

Брин почти как Робин Гуд - отбирает у богатых и наживается на бедных. Не знаю, к какой категории причисляет себя читатель, но здравый смысл в любом случае подсказывает по возможности экономить и отлаживать запрос на небольшом наборе данных, для чего:

возьмите временной период поменьше
ограничьте данные условием limit и сохраните результат в отдельный dataset, потому что сам limit ничего не экономит
запросите только нужные из 200 customDimesions и так же сохраните в отдельный dataset, чтобы каждый раз не перебирать их все
сгенерите данные локально и загрузите csv в отдельную таблицу

Структура запроса

При изучении SQL я очень часто натыкался на сложночитаемые вложенные запросы примерно такой структуры

select *
from (
  select *
  from (
    select *
    from `dataset1`
  )
  where id in (
    select *
    from (
      select *
      from `dataset2`
      join (
        select *
        from (
          select *
          from `dataset3`
        )
      )
    )
  )
)

Иногда быстрее прочитать всю документацию задом-наперед, чем разобраться, что здесь происходит с данными. К счастью, в Standard SQL есть оператор with, и можно легко именовать все подзапросы.

-- что происходит на первом этапе
with step1 as (
  select *
  from project.dataset.table
),
-- второй этап
step2 as (
  select *
  from step1
),
-- еще подзапросы
pivot as (
  -- сводная таблица
)
--*/
select *
from pivot

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

Комбинация --*/ перед выводом позволяет на любом этапе отсечь хвост, который читатель еще не дописал, вставив открывающий комментарий /*

Таблица констант

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

with dates as(
  -- Диапазон дат
  select '20180101' as f, '20180131' as l
),
table1 as (
  select a, b
  from `project.dataset.table_*`, dates
  where _TABLE_SUFFIX between dates.f and dates.l
),
table2 as (
  select a, c
  from `project.dataset.table_*`, dates
  where _TABLE_SUFFIX between dates.f and dates.l
),
pivot as (
  select table1.a, table1.b, table2.c
  from table1
  left join table2
  on table1.a = table2.a
)

Визуализация в DataStudio

Если читатель хоть раз делал дашборды в Google DataStudio, то наверняка знает, что в качестве источника там можно выбрать BigQuery.

Но так делать не стоит!

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

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

Так же для удобства можно использовать бесплатное дополнение от OWOX, которое делает процесс еще проще и здорово экономит время, за что большое спасибо этим овощным ребятам.

Динамические периоды

Задачка:
10 жужар требуют у одного лямзика каждую неделю отчет о том, сколько хамсиков заплатило жужарам за последние 4 недели. Помогите лямзику быстрее закончить с отчетами и пойти с хрюнделями употреблять пивники.

select *
from `project.dataset.table_*`
where _table_suffix between
-- понедельник 4 недели назад
  replace (
    cast(
      date_sub(
        date_sub(
          current_date(),
          interval extract(
            dayofweek from current_date()
          ) - 2 day
        ),
        -- количество недель
        interval 4 week
      ) as string
    ), "-", ""
  )
  and
  -- прошлое воскресенье
  replace (
    cast(
      date_sub(
        current_date(),
        interval extract(
          dayofweek from current_date()
        ) - 1 day
      ) as string
    ), "-", ""
  )

Вычисление функций

Когда требуется сочинить какое-то сложное преобразование, которое в процессе еще надо несколько раз протестить, откройте BigQuery в другой вкладке, сделайте обычный select и запустите с Ctrl + Enter

select
0x0b,
replace (
  cast(
    date_sub(
      date_sub(
        current_date(),
        interval extract(
          dayofweek from current_date()
        ) - 2 day
      ),
      interval 10 week
    ) as string
  ),
  "-",
  ""
)

DAU, WAU, MAU

Еще один набор метрик, который присутствует практически в каждом дашборде. Если с DAU все оказывается легко, как только вы загуглите, что это такое, то запрос WAU и MAU будет немножко tricky, когда нужна их динамика по дням. Я привожу оба, чтобы читатель ни в чем себе не отказывал.

usersWeekly as (
  select
  a.dateEvent,
  count(distinct b.idUser) as active
  from
    usersActive as a,
    usersActive as b
  where
    b.dateEvent <= a.dateEvent
    and b.dateEvent > date_sub(a.dateEvent, interval 7 day)
  group by a.dateEvent
  order by a.dateEvent
),
usersMonthly as (
  select
    a.dateEvent,
    count(distinct b.idUser) as active
  from
    usersActive as a,
    usersActive as b
  where
    b.dateEvent <= a.dateEvent
    and b.dateEvent > date_sub(a.dateEvent, interval 30 day)
  group by a.dateEvent
  order by a.dateEvent
)

Поменьше запятых

Я не раз встречал запросы, где авторы перечисляли в from кучу таблиц, из которых им нужны данные, через запятую. В этом случае запятая аналогична cross join, что дает нам декартово произведение множеств. Две таблицы по 10 строк превращаются в 100 строк, а три уже в тысячу. Если читатель действует так же, то читателю рекомендуется присмотреться к оператору join и по возможности объединять данные по ключу.

Конкатенация

Казалось бы, полезность concat() переоценить невозможно, но я попробую.
Эта функция незаменима, когда дело касается какого-то уникального идентификатора.

Например,

-- id сессии
concat(fullVisitorId, cast(visitStartTime as string))

-- id хита
concat(fullVisitorId, cast(visitStartTime * 1000 + hits.time as string))

-- id юзера уникального в рамках недели
concat(fullVisitorId, format_date('%Y%W', parse_date('%Y%m%d', date))) as w

Транспонирование таблицы

Случается, что читателю требуется посмотреть сочетание нескольких пользовательских параметров или же вывести сводную таблицу с их агрегатами, что сделать довольно затруднительно, а в экспорте из Firebase даже события со всеми параметрами записываются отдельными строками и их просто так не получить. В этом случае приходится использовать вот такую конструкцию:

  sum(case videoviewpercentage.percent when '0' then 1 else 0 end) as zero,
  sum(case videoviewpercentage.percent when '10' then 1 else 0 end) as ten,
  sum(case videoviewpercentage.percent when '20' then 1 else 0 end) as twenty,
  sum(case videoviewpercentage.percent when '30' then 1 else 0 end) as thirty,
  sum(case videoviewpercentage.percent when '40' then 1 else 0 end) as forty,
  sum(case videoviewpercentage.percent when '50' then 1 else 0 end) as fifty,
  sum(case videoviewpercentage.percent when '60' then 1 else 0 end) as sixty,
  sum(case videoviewpercentage.percent when '70' then 1 else 0 end) as seventy,
  sum(case videoviewpercentage.percent when '80' then 1 else 0 end) as eighty,
  sum(case videoviewpercentage.percent when '90' then 1 else 0 end) as ninety,
  sum(case videoviewpercentage.percent when '100' then 1 else 0 end) as hundred

Полезные ссылки

Документация по Standard SQL
Схема экспорта данных GA в BigQuery
Много полезного об оптимизации запросов можно почерпнуть из выступлений представителей Google в этом видео

https://www.youtube.com/watch?v=UueWySREWvk

продвинутый

sql