![](https://habrastorage.org/getpro/habr/upload_files/ac8/d05/cd1/ac8d05cd1fd4be4691939cdf485b7b05.jpg)
Электронные таблицы используются везде. Это один из самых удобных инструментов для повышения производительности. С их помощью можно быстро упорядочить, рассчитать и представить данные. Google Таблицы – это приложение для работы с электронными таблицами в составе сервиса Google Workspace, с которым активно работают более 2 миллиардов пользователей.
Машинное обучение также стало важным бизнес-инструментом. Когда появилась недорогая возможность высокоточного прогнозирования на основе данных, рынок стал развиваться по новому пути. По оценкам, каждый год доля машинного обучения в бизнесе будет увеличиваться более чем на 40 %.
Это наталкивает на мысль о том, что машинное обучение было бы разумно применять для анализа данных в таблицах. И это так! Тем более теперь для этого есть все средства. О них мы и поговорим в этой статье.
![](https://habrastorage.org/getpro/habr/upload_files/6e0/1a8/eb8/6e01a8eb8a082029373c23db63400e07.gif)
Широкие возможности применения
Инструмент BigQuery ML, встроенный в BigQuery, позволяет создавать модели машинного обучения с помощью стандартных SQL-запросов. В этой статье мы рассмотрим, как создать модель прогнозирования временных рядов с помощью BigQuery ML. Входные данные модели временных рядов – это ряд значений, собранных за определенное время. Выходные данные – ряд спрогнозированных значений.
Мы выбрали этот тип модели, потому что она часто используется в таблицах. Можно придумать массу вариантов таблиц, в которых по порядку идут даты, а напротив них указаны числа. Это могут быть показатели продаж или деятельности компании, кадровый состав и многое другое. Подробный пример создания модели прогнозирования спроса с помощью BigQuery ML приведен в этой статье.
Принцип, о котором мы рассказываем здесь, можно применять к любой модели машинного обучения. BigQuery ML поддерживает множество моделей различного типа: нейросети, модели AutoML и даже TensorFlow. Вам не нужно знать, как создана модель – достаточно просто загрузить данные и выбрать подходящие параметры. Инструмент BigQuery ML сам создаст модель и будет управлять ею.
Чтобы создать модель, вам потребуется набор данных для обучения. К счастью, они уже есть в таблице. Если вы используете подключенные таблицы, доступ к большим объемам данных можно получать прямо внутри BigQuery. При этом у вас всегда будут защищенные и актуальные данные.
Для выполнения запросов BigQuery ML из Таблиц мы будем использовать Apps Script. Это встроенная облачная и безсерверная платформа JavaScript расширяющая возможности Google Workspace. Программа Apps Script извлечет входные данные из таблицы, выполнит запросы BigQuery ML для обучения и прогнозирования, а затем обновит таблицу, добавив в нее расчетные данные из модели.
Проблема
В нашем примере мы рассчитаем количество посещений сайта интернет-магазина с помощью данных Google Аналитики из общедоступных наборов данных BigQuery. В набор входят данные о трафике, контенте и транзакциях за 12 месяцев. Посмотрим на график посещений сайта по часам.
![](https://habrastorage.org/getpro/habr/upload_files/666/1da/68d/6661da68d09497aff92e7b883e4cf812.png)
Этот набор данных содержит ряд сложных шаблонов. Например, мы видим шаблон, который повторяется каждый день, а также каждую неделю (дневная и недельная сезонность). Но надежные механизмы прогнозирования в BigQuery ML хорошо справляются с такими шаблонами. Кроме того, мы видим несколько всплесков, которые могут нарушить алгоритм прогнозирования, но в BigQuery ML заложена автоматическое выявление и изоляция аномалий.
Использование данных из BigQuery
В нашем примере код будет работать с данными независимо от их источника. Если у вас нет доступа к бизнес-данным в BigQuery, его можно получить с помощью подключенных таблиц. Это несложно.
В меню "Данные" нажмите "Подключиться к BigQuery"
![](https://habrastorage.org/getpro/habr/upload_files/b90/8fc/7c2/b908fc7c2ea98f5ec1fb81df2bd6341b.png)
Выберите проект, набор данных и таблицу. Все готово! Ваша электронная таблица подключена.
![](https://habrastorage.org/getpro/habr/upload_files/7f3/e7b/613/7f3e7b6134d5805c454e458fbf9a55aa.png)
С помощью подключенных таблиц можно получать результаты анализа больших объемов данных, используя знакомые инструменты, например сводные таблицы, графики и формулы. Данные в них будут автоматически обновляться из BiqQuery. В нашем примере мы хотим создать модель и для ее обучения использовать срез данных, сделанный в определенный момент времени. Для этого нужно выбрать данные и нажать "Распаковать".
![](https://habrastorage.org/getpro/habr/upload_files/b14/dda/3ac/b14dda3ac66467752e535ebad1074c7d.png)
Использование BigQuery ML Model из Google Таблиц
Теперь рассмотрим, как выполнять команды BigQuery ML из интерфейса Таблиц. Для этого разберем пример кода из раздела ml-integration-samples в Google Workspace.
Открыв в таблице Редактор скриптов ("Инструменты > Редактор скриптов"), можно написать собственный код Apps Script и настроить интеграцию. В нашем примере включены две дополнительные службы (BigQuery и Таблицы), чтобы сценарий поддерживал соответствующие API.
![](https://habrastorage.org/getpro/habr/upload_files/cfd/3fb/b11/cfd3fbb11b119c26505d2f1fe1f1d0b6.png)
Сначала заглянем в меню, которое появилось в Таблицах, и посмотрим, как мы связали его с кодом в проекте.
![](https://habrastorage.org/getpro/habr/upload_files/ebc/f60/85c/ebcf6085c5628195f84725aed93a76d2.png)
Функция onOpen()
активируется, когда открывается электронная таблица. Как видно, она создает несколько пунктов меню, каждое из которых связано с определенной функцией. Например, функция train()
вызывается, если выбрать пункт меню "Обучение".
/**
* Create menu items linked to functions
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('BQML')
.addItem('Train', 'train')
.addItem('Forecast', 'forecast')
.addSeparator()
.addSubMenu(SpreadsheetApp.getUi().createMenu('Configure')
.addItem('Project', 'configureProject')
.addItem('Dataset', 'configureDataset'))
.addToUi();
}
Обучение модели
Теперь рассмотрим, как функция обучения работает с данными. Пользователь выбирает в таблице диапазон дат с данными для прогнозирования. Обучающий код извлекает значения из объекта Range.
![](https://habrastorage.org/getpro/habr/upload_files/e29/2d2/c66/e292d2c6657f95b88e717d001606f7b9.png)
Затем строки вставляются во временную таблицу BigQuery. После этого выполняется запрос на создание модели временного ряда на основе данных из таблицы.
/**
* Create a forecasting model based on the input data
*/
function train() {
// ...
// Populate temporary table in BigQuery with selected data from sheet
const inputs = SpreadsheetApp.getActiveRange().getValues();
populateTable(project, table, [DATETIME_COLUMN, DATA_COLUMN], inputs);
// Create a new model using training data in BigQuery
const request = {
query: 'CREATE OR REPLACE MODEL `' + model + '` ' +
getOptionsStr(CREATE_OPTIONS) + ' AS SELECT * FROM `' + table + '`',
useLegacySql: false,
};
runQuery(request, project);
}
Рассмотрим пример запроса, который может быть сформирован обучающим кодом. В нем указана модель типа ARIMA (для временного ряда), а также столбцы из временной таблицы с временными метками и данными. В BigQuery ML можно указать дополнительные параметры, например выходные дни, но в нашей выборке их нет.
CREATE OR REPLACE MODEL
`sheets_forecast.sheets_forecast_model` OPTIONS( MODEL_TYPE='ARIMA',
TIME_SERIES_TIMESTAMP_COL='datetime',
TIME_SERIES_DATA_COL='data') AS
SELECT
*
FROM
`sheets_forecast.sheets_forecast_training_data`
Прогнозирование
Чтобы рассчитать прогноз, выберите несколько строк в таблице и нажмите "Прогноз" в меню. Сценарий применит к модели функцию ML.FORECAST(), используя количество выбранных строк в качестве горизонта.
![](https://habrastorage.org/getpro/habr/upload_files/a6a/5f1/861/a6a5f1861422099a119eff203c1f5225.png)
Для каждого временного интервала из прогноза извлекаются значения двух полей:
Временная метка прогноза в виде строки с датой, временем и часовым поясом.
Расчетное значение для данной временной метки.
Если выбрано три строки, то запрос может выглядеть так:
SELECT
FORMAT_TIMESTAMP("%FT%T%Ez", forecast_timestamp),
forecast_value
FROM
ML.FORECAST(MODEL `sheets_forecast.sheets_forecast_model`,
STRUCT(3 AS horizon))
А теперь посмотрим на сам прогноз. Последняя неделя данных выделена синим цветом, а прогноз на последние три дня – красным.
![](https://habrastorage.org/getpro/habr/upload_files/788/65f/aff/78865faffdc680fd478726a35acf8806.png)
Собираем все вместе
Объединив Google Таблицы с BigQuery ML, можно применять машинное обучение к любым данным в электронных таблицах. Все, что требуется, – это достаточный объем данных высокого качества, на основе которых можно создать информативные шаблоны.
В примере кода показано, как процесс обучения может взаимодействовать с данными любого типа, в которых есть дата и числовое значение. Если вам нужна другая модель, макет таблицы или параметры, внесите в этот код соответствующие изменения. После этого, чтобы построить новую модель и прогноз по ней, будет достаточно открыть меню.
На первых этапах вам поможет шаблон таблицы, в которой уже установлен пример кода. В примере есть инструкции, с помощью которых вы сможете установить код самостоятельно. Тем, кто хочет получить больше практических навыков по работе с BigQuery ML, рекомендуем изучить вводное руководство. Используйте Таблицы вместе с BigQuery ML, и, надеемся, вы сможете решать больше задач, чем когда-либо.
Напоминаем что при первой регистрации в Google Cloud: вам доступны бонусы на сумму 300 долларов США, а более 20 бесплатных продуктов доступны всегда. Подробнее по специальной ссылке.
А так же выражаем благодарность за помощь в подготовке материала коллегам: Карл Вейнмейстер, Звиад Кардава