Массовое обновление таблиц в Google BigQuery

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

Если читатель часто и много работает с BigQuery, то читатель наверняка хотя бы раз сталкивался с необходимостью обновить данные в таблице. Для этого диалект Standard SQL поддерживает Data Manipulation Language, позволяющий вставлять, удалять и обновлять данные. Но что, если читателю требуется изменить данные сразу в большом датасете с кучей таблиц за несколько месяцев? В этом случае документация BigQuery читателя пошлет:

DML statements that modify partitioned tables are not yet supported.

-- BigQuery

А я в данной статье рассказываю, как просто решить эту задачу.

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

Запрос на обновление

Сперва нужен конечно сам запрос 😆
В качестве примера возьму задачу, с которой столкнулся недавно сам - уберем НДС с расходов по Яндекс.Директ, чтобы было как у Adwords.

UPDATE
  `project.dataset.table_20180101`
SET sum = sum/1.18
WHERE utm_source = 'yandex' and utm_medium = 'cpc'

Автоматизация через API BigQuery

Раз BigQuery не дает написать запрос сразу для всех таблиц, применим его к каждой индивидуально. В это поможет мой любимый Apps Script. Для того чтобы магия случилась, нужно сперва подключить BigQuery API. Делается это во вкладке Ресурсы - Дополнительные функции Google.., там же Google попросит включить API и в консоли. Теперь смотрите, как легко из Apps Script отправить запрос в Bigquery:

// название проекта
var projectId = 'project'
var request = {
  // текст запроса
  query:
    "UPDATE `project.dataset.table_20180101` SET sum = sum/1.18 WHERE utm_source = 'yandex' and utm_medium = 'cpc'",
  // выбор диалекта
  useLegacySql: false,
}
// отправка запроса
var queryResults = BigQuery.Jobs.query(request, projectId)

Осталось добавить цикл, чтобы наш запрос отправлялся ко всем таблицам поочереди. В моем случае обработать было оптимально по месяцам из-за особенностей нейминга таблиц, а еще, чтобы не париться с кодом, ничего не пропустить и не превысить лимиты apps script на выполнение одного скрипта.

function updateTables() {
  var projectId = 'project'

  for (var tS = 20180101; tS <= 20180131; tS++) {
    var request = {
      query:
        'UPDATE `project.dataset.table_' +
        tS +
        "` SET sum = sum/1.18 WHERE utm_source = 'yandex' and utm_medium = 'cpc'",
      useLegacySql: false,
    }
    var queryResults = BigQuery.Jobs.query(request, projectId)
  }
}

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

Подробнее о работе с BigQuery API
Подробнее о лимитах Apps Script

средний

инструмент

sql