Как выгружать данные с вложенной структурой из Google BigQuery на примере пользовательских параметров Google Analytics

Google BigQuery — популярная облачная база данных, которой пользуются компании по всему миру. Она особенно удобна для работы с “сырыми” данными Google Analytics: в GA 360 интеграция с BigQuery настраивается в несколько кликов, а для бесплатной версии существуют сторонние скрипты и модули.

В “сырых” данных Google Analytics каждая запись (строка) соответствует сеансу. Внутри такой записи находятся вложенные поля, которые соответствуют хитам сеанса:


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

На примере пользовательских параметров Google Analytics я постараюсь “на пальцах” объяснить, как хранятся вложенные данные в Google BigQuery и как их можно выгружать.

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

• Выгрузка строк
• Выгрузка с сохранением структуры вложенности
• Пример замены значений пользовательских параметров

Основы

BigQuery поддерживает 2 диалекта SQL: Legacy и Standard. Google рекомендует использовать более новый SQL Standard, на нем мы и будем писать запросы для выгрузки.
Все, кто хоть немного работал с SQL, знают стандартную конструкцию запроса:

SELECT
    *Что хотим выбрать*
FROM
    *Из какой таблицы*
WHERE
    *Условия фильтрации*

Такая конструкция работает, если структура таблицы простая, без других, вложенных в ячейки полей:


Мы же рассматриваем таблицы с вложенными полями. Структура такой таблицы (например, пользовательские параметры Google Analytics):

Пользовательские параметры GA в BQ

В Google BigQuery у такой таблицы будут следующие названия столбцов (разделитель "." показывает структуру вложенности):


Так как же нам выгрузить данные из вложенных полей?

Выгрузка строк

Вернемся к таблице с примером пользовательских параметров GA в BQ.

Столбцы customDimensions.index и customDimensions.value — это индексы и значения сессионных и пользовательских Custom Dimensions.

Столбцы hits.customDimensions.index и hits.customDimensions.value — индексы и значения хитовых Custom Dimensions.

В Google BigQuery есть еще один уровень действия пользовательских параметров — товар. Названия и значения товарных Custom Dimensions в Google BigQuery находятся в столбцах hits.product.customDimensions.index и hits.product.customDimensions.value. Они выгружаются по аналогии с хитовыми пользовательскими параметрами, необходимо лишь учесть еще один уровень вложенности.

Пользовательские параметры сессионного и пользовательского уровня

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

Для ответа на вопрос давайте еще раз внимательнее посмотрим на таблицу с пользовательскими параметрами GA в BQ.
В ней видно, что значения ячеек столбца customDimensions представляют из себя еще одну таблицу:


Достаточно сделать подзапрос к этой таблице в основном запросе:

SELECT
  -- выбираем даты
  date,
  -- выбираем значения столбца value
  (SELECT value
  -- из таблицы customDimensions, которая вложена в таблицу t
  FROM t.customDimensions
  -- фильтр по нужному индексу пользовательского параметра
  WHERE index = 1) AS customDimensions1
FROM
  -- называем основную таблицу t для обращения к ней
  `project.dataset.tablename` AS t

На выходе получаем таблицу:


Если нам нужно добавить столбец со значением другого пользовательского параметра — делаем еще один подзапрос:

SELECT
  date,
  (SELECT value FROM t.customDimensions WHERE index = 1) AS customDimensions1,
  -- еще один подзапрос к таблице customDimensions
  (SELECT value FROM t.customDimensions WHERE index = 2) AS customDimensions2
FROM
  `project.dataset.tablename` AS t

Получаем следующее:

Пользовательские параметры хитового уровня

Хитовые пользовательские параметры выгружаются аналогично сессионным (пользовательским), за исключением того, что подзапрос нужно делать к вложенной таблице hits. Другими словами, значения ячеек столбца hits в таблице “сырых” данных Google Analytics представляет из себя вложенную таблицу, в которую вложена таблица customDimensions:


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

SELECT
  -- выбираем даты
  date,
  -- выбираем значения столбца value
  (SELECT value
  -- из таблицы customDimensions, которая вложена в таблицу h
  FROM h.customDimensions
  -- фильтр по нужному индексу пользовательского параметра
  WHERE index = 3) AS customDimensions3
FROM
  -- называем основную таблицу t для обращения к ней
  `project.dataset.tablename` AS t,
  -- вложенную таблицу t.hits называем h для обращения к ней
  t.hits AS h

Результатом выполнения запроса станет таблица:


Можно выгрузить несколько хитовых пользовательских параметров и добавить параметр hitNumber (порядковый номер хита в сессии):

SELECT
  date,
  h.hitNumber AS hitNumber,
  (SELECT value FROM h.customDimensions WHERE index = 3) AS customDimensions3,
  -- делаем еще один подзапрос к вложенной таблице h.customDimensions
  (SELECT value FROM h.customDimensions WHERE index = 4) AS customDimensions4
FROM
  `project.dataset.tablename` AS t,
  t.hits AS h

Получим таблицу:

Сессионные (пользовательские) + хитовые пользовательские параметры

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

SELECT
  date,
  h.hitNumber AS hitNumber,
  -- выгружаем сессионные пользовательские параметры
  (SELECT value FROM t.customDimensions WHERE index=1) AS customDimensions1,
  (SELECT value FROM t.customDimensions WHERE index=2) AS customDimensions2,
  -- выгружаем хитовые пользовательские параметры
  (SELECT value FROM h.customDimensions WHERE index=3) AS customDimensions3,
  (SELECT value FROM h.customDimensions WHERE index=4) AS customDimensions4
FROM
  `project.dataset.tablename` AS t,
  t.hits AS h

Таблица, которая будет получена в результате выполнения запроса:

Выгрузка с сохранением структуры вложенности

Такая выгрузка может понадобиться при замене значений какого-либо пользовательского параметра в Google BigQuery.

Пример
В Google Analytics в сессионный пользовательский параметр с индексом 12 и в хитовой пользовательский параметр с индексом 25 для пользователей из России передается название страны в полном формате: RUSSIA. Необходимо поменять формат страны на сокращенный: RUS.

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

Порядок решения задачи:

1. Выгружаем все данные с сохранением структуры вложенности
2. Заменяем значение пользовательского параметра со страной
3. Перезаписываем таблицу

Для выгрузки данных с сохранением структуры вложенности необходимо использовать функцию ARRAY и конструкцию SELECT AS STRUCT. Разберемся, что это такое.

Синтаксис функции ARRAY следующий:

ARRAY(*подзапрос*)

Она возвращает массив элементов.

Сравнение массива с построчной записью:

Слева — массив, справа — построчная запись

Если мы хотим сохранить вложенную структуру и выгрузить массив с несколькими колонками, необходимо использовать ARRAY(SELECT AS STRUCT …):

Массив с вложенной структурой

Пользовательские параметры сессионного и пользовательского уровня

Для выгрузки с сохранением структуры сессионных (пользовательских) Custom Dimensions используем запрос:

SELECT
  date,
  -- используем ARRAY(SELECT AS STRUCT...) для сохранения вложенности
  ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions) AS customDimensions
FROM
  `project.dataset.tablename` AS t

В результате его выполнения получается таблица, в которой сохранена структура вложенности “сырых” данных Google Analytics:

Пользовательские параметры хитового уровня

Для выгрузки значений хитовых пользовательских параметров из Google BigQuery с сохранением структуры вложенности важно учесть, что таблица customDimensions вложена в таблицу hits. Другими словами, необходимо 2 раза сделать подзапрос ARRAY(SELECT AS STRUCT...): сначала к вложенной таблице hits, потом к вложенной в нее таблице customDimensions:

SELECT
  date,
  -- подзапрос к таблице t.hits
  ARRAY(SELECT AS STRUCT hitNumber,
    -- подзапрос к таблице h.customDimensions
    ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions) AS customDimensions
  FROM t.hits AS h ) AS hits
FROM
  `project.dataset.tablename` AS t

Результатом такого запроса будет таблица:

Сессионные (пользовательские) + хитовые пользовательские параметры

Как и при построчной выгрузке, нам необходимо объединить в одном запросе подзапросы ARRAY(SELECT AS STRUCT...) к нужным вложенным таблицам:

SELECT
  date,
  -- сессионные (пользовательские) Custom Dimensions
  ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions ) AS customDimensions,
  -- хитовые Custom Dimensions
  ARRAY(SELECT AS STRUCT hitNumber,
    ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions ) AS customDimensions
  FROM
    t.hits AS h) AS hits
FROM
  `project.dataset.tablename` AS t

Что получается в результате:

Пример замены значений пользовательских параметров

Вернемся к нашему примеру.
В предыдущем разделе мы получили запрос для выгрузки сессионных (пользовательских) и хитовых пользовательских параметров Google Analytics с сохранением структуры вложенности.
Дополним этот запрос конструкциями SELECT *REPLACE для выгрузки с заменой и CASE для обновления значений нужных пользовательских параметров:

-- выгружаем всё с заменой колонок t.customDimensions и t.hits
SELECT *REPLACE(
  -- сессионные (пользовательские) Custom Dimensions
  ARRAY(SELECT AS STRUCT index,
        -- меняем значение нужного пользовательского параметра
        CASE WHEN index=12  AND value='RUSSIA' THEN 'RUS' ELSE value END AS value
        FROM t.customDimensions) AS customDimensions,
  -- хитовые Custom Dimensions
  -- выгружаем колонку t.hits с заменой ее вложенных полей h.customDimensions
  ARRAY(SELECT AS STRUCT *REPLACE(
        ARRAY(SELECT AS STRUCT index,
              -- меняем значение нужного пользовательского параметра
              CASE WHEN index=25 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value
              FROM h.customDimensions) AS customDimensions)
        FROM t.hits AS h) AS hits)
FROM
  `project.dataset.tablename` AS t

В результате выполнения данного запроса мы получим оригинальную таблицу с “сырыми” данными из Google Analytics. У неё полностью сохранится оригинальная структура вложенности, но значения нужных пользовательских параметров изменятся на новые.

Тема работы с вложенной структурой данных в Google BigQuery не относится к легким.

Надеюсь, у меня получилось внести ясность в этот вопрос. Но, напомню, лучший способ научиться делать что-то — это больше практиковаться.