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

    image

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

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

    image

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

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

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

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

    Основы


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

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

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

    image

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

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

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

    image

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

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


    Вернемся к таблице с примером пользовательских параметров 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 представляют из себя еще одну таблицу:

    image

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

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

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

    image

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

    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

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

    image

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


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

    image

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

    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

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

    image

    Можно выгрузить несколько хитовых пользовательских параметров и добавить параметр 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

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

    image

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


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

    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

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

    image

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


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

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


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

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

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

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

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

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

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

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

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

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

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

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


    Для выгрузки с сохранением структуры сессионных (пользовательских) 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:

    image

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


    Для выгрузки значений хитовых пользовательских параметров из 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

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

    image

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


    Как и при построчной выгрузке, нам необходимо объединить в одном запросе подзапросы 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

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

    image

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


    Вернемся к нашему примеру.
    В предыдущем разделе мы получили запрос для выгрузки сессионных (пользовательских) и хитовых пользовательских параметров 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 не относится к легким.

    Надеюсь, у меня получилось внести ясность в этот вопрос. Но, напомню, лучший способ научиться делать что-то — это больше практиковаться.
    • +12
    • 1,4k
    • 2
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 2

      0

      Why not to run simple query? You can easily join nested objects to parent rows


      SELECT ...
      FROM FROM `project.dataset.tablename` AS t
      LEFT JOIN t.customDimensions tcd
        0
        I showed one of the methods that I use myself. For example, you can also use UNNEST or your method.

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

      Самое читаемое