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

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

DML statements that modify partitioned tables are not yet supported.
BigQuery

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

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

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

1234
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:

 1 2 3 4 5 6 7 8 910
// название проекта
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 на выполнение одного скрипта.

 1 2 3 4 5 6 7 8 91011
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

comments powered by Disqus