Когда появятся баги? Моделирование машинного обучения в BigQuery для прогнозирования следующей вспышки ошибок

Во-первых, с Новым годом 🎉 Поскольку лето в Австралии набирает обороты, мы начали 2020 год с небольшого веселья, пытаясь ответить на вопрос, который все думают в Австралии в это время года (особенно моя жена и дети!), а именно — когда будут баги?! Это предсказывает вспышки тараканов и комаров (также известных как москиты).

Проект по прогнозированию ошибок фактически собрался еще в октябре. У нас было достаточно времени для исследования, итерации и тестирования, прежде чем летний сезон — и, соответственно, ошибки — вступили в силу. В этом сообщении блога мы поделимся техническим решением, которое мы использовали, и тем, как могло бы выглядеть решение высокого уровня.

Все они используют Google Cloud BigQuery, пользуясь преимуществами BigQuery Geography, Analytic и ML Functions.

Краткое введение для тех, кто нас не знает

Я Дрю. Я работаю в Google некоторое время. В настоящее время я работаю инженером по работе с клиентами, помогая нашим рекламодателям максимально эффективно использовать свои веб-сайты, приложения и данные. Все с моего стола в прекрасном Сиднее.

Я Денис. Я был аналитиком и консультантом в Google в Сиднее в течение последних 6 лет, работая над идеями, стратегией и решениями для рекламы проблем клиентов с использованием данных, и в этой роли у меня появилась настоящая страсть к машинному обучению.

Погода, говоришь?

Ага, погода! Погодные условия стали нашей главной особенностью в этой модели, поскольку было показано, что они влияют на популяцию насекомых. Используя исторические данные о погоде, мы можем искать корреляции между закономерностями во времени и вспышками ошибок. Учитывая температуру, осадки/дождь, влажность… (и многое другое).

Исторические данные о погоде

Поскольку это забавный эксперимент, мы поиграем с общедоступным набором данных BigQuery Глобальная историческая климатическая сеть (GHCN). Данные о погоде NCAR содержат отчеты с более чем 80 000 станций в 180 странах за последние десять лет. Его даже проверили на качество. Доступно в BigQuery, готово к работе. Идеально.

Кроме того, поскольку нас интересуют только погодные тенденции в Австралии, мы значительно сократим этот набор, перечислив только местные метеостанции (хотя было бы забавно когда-нибудь взглянуть на это глобально).

…и вуаля, таблица данных australia_historical_weather размером примерно 1,36 МБ / 17 088 строк готова к работе.

Границы штата Австралия

Обычно данные о погоде связаны с широтой/долготой местоположения метеостанции. Что касается данных NCAR. Таким образом, первая реальная задача — отобразить и преобразовать эти географические координаты широты и долготы в более значимые географические местоположения, например, город, город или штат.

В этом посте мы выберем уровень состояния (учитывая информацию о тенденциях, которую мы скоро обсудим), чтобы делать прогнозы появления ошибок.

Веб-сайт Австралийского статистического бюро (abs.gov.au) поддерживает местоположения статистической области. Используя ABS Maps, мы можем загрузить файл Цифровые границы штата и территории (STE) ASGS Ed 2016 в формате шейп-файла ESRI .shp, в котором подробно описаны границы всех австралийских штатов (аналогично этому скриншоту границы штата в Карты Гугл).

Эти данные также включают размер площади (км2) каждого штата. Хороший! Обязательно сохраним и это, это пригодится позже 👍.

После загрузки мы можем преобразовать шейп-файл (.shp) в .csv, который может использовать BigQuery. Примером такого инструмента может быть ogr2ogr, например…

ogr2ogr -f csv -dialect sqlite -sql “select AsGeoJSON(geometry) AS geom, * from SA4_2016_AUST” SA4_2016_AUST.csv SA4_2016_AUST.shp

Об этом хорошо рассказывает замечательный пост Лака Лакшманана на тему Как загружать географические данные, такие как шейп-файлы, в BigQuery.

Когда CSV-файл будет доступен, его можно будет загрузить в BigQuery, создав для использования таблицу australia_state_boundaries размером 87,9 МБ / 107 строк.

Совет: когда вы загружаете данные в CSV, не используйте автоопределение. Вместо этого укажите схему, чтобы столбец геометрии мог иметь тип GEOGRAPHY. Это избавит нас от необходимости использовать ST_GeogFromGeoJson в приведенном ниже запросе.

Сопоставление данных о погоде с состоянием

Теперь у нас есть таблица australia_state_boundaries, с которой можно поиграться, это самое простое. Географические функции BigQuery упрощают работу, позволяя нам агрегировать данные о погоде, которые являются ST_WITHIN государственной границей.

WITH australia_state_boundaries AS (
  SELECT
    geom AS polygon,
    STATE_NAME_2016 AS stateLocation
  FROM `[project].[dataset].australia_state_boundaries`
)
SELECT
  australia_state_boundaries.stateLocation,
  day,
  AVG(avgTemperature) AS avgTemperature,
  AVG(avgTd) AS avgTd,
  MAX(maxTemperature) AS maxTemperature,
  MIN(minTemperature) AS minTemperature,
  AVG(precipitation) AS precipitation
FROM
  `[project].[dataset].australia_historical_weather`,
  australia_state_boundaries
WHERE
  ST_WITHIN(
    ST_GEOGPOINT(longitude, latitude),
    australia_state_boundaries.polygon)
GROUP BY sa4Location, day

При этом создавая значения max, min и avg в SQL. Назовем эту новую таблицу australia_state_historical_weather.

Google Trends для вспышек ошибок

Google Trends — это инструмент для анализа поисковых запросов Google с течением времени. Глядя на термин таракан, становится ясно, что данные о тенденциях являются полезным индикатором вспышек, вы можете видеть (на этом снимке экрана, взятом из Google Tends), поиски по этой теме нарастают по мере приближения лета в Австралии.

Соответственно, это значение, которое мы попытаемся предсказать, то есть метка, используемая в нашей модели ML. Инструмент тенденций позволяет фильтровать данные по состоянию и загружать информацию в формате CSV.

Соедините это с таблицей australia_state_historical_weather, и теперь у нас есть набор данных, содержащий данные о погоде за последние несколько лет и тенденциях ошибок.

На заметку. В инструменте трендов также есть раздел «Похожие запросы», показывающий, что также искали пользователи (пример скриншота)…

но что бы вы ни делали, не смотрите в этом разделе, когда находитесь на странице трендов тараканов… и когда вы не смотрите на нее, определенно не видите связанный запрос для молока тараканов! и что бы вы ни делали, не позволяйте любопытству взять верх над вами и ищите, чтобы узнать, что это новое увлечение здоровьем 🙈 ! Я никогда не буду смотреть на мир так же, как 🤮.

Обучение нашей «глючной» модели машинного обучения

Это глючная, а не глючная модель ML.

Мы разобьем набор данных на ряд оконных функций, агрегируя модели поведения с течением времени. Чтобы лучше понять эту концепцию, ознакомьтесь с моим постом структурирование данных для прогнозирования будущего.

Поскольку данные достаточно просты, мы можем сделать все это в BigQuery. Для этого мы будем использовать аналитическую функцию BigQuery PARTITION BY для агрегирования данных.

В качестве примера приведенный ниже SQL создает набор данных для комаров.

• Он собирает данные за 1 неделю каждый день, создавая черты прошлых моделей. РАЗДЕЛЕНИЕ ПО sa4Location В ПОРЯДКЕ ПО ДНЯМ МЕЖДУ 7 ПРЕДЫДУЩИМИ И 0 ПОСЛЕДУЮЩИМИ.
• Он также смотрит на 4 недели вперед, чтобы создать этикетку, которая научит модель смотреть в будущее. РАЗБИВКА ПО sa4Location ПО ДНЯМ СТРОК МЕЖДУ 29 СЛЕДУЮЩИМИ И 29 СЛЕДУЮЩИМИ.

SELECT
  state,
  EXTRACT(MONTH FROM day) AS month,
  EXTRACT(DAYOFYEAR FROM day) AS dayOfYear,
  EXTRACT(WEEK FROM day) AS weekOfYear,
  — Label
  SUM(mosquitoSearchVolume) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 29 FOLLOWING AND 29 FOLLOWING) AS mosquitoSearchVolume4WeekLabel,
  — 1 Week Volume Features
  mosquitoSearchVolume / AVG(mosquitoSearchVolume) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) AS percentageChangeFromAvgMosquitoSearchVolume1Week,
  SUM(mosquitoSearchVolume) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) / areaSize AS mosquitoSearchVolumePerAreaSize1Week,
  — 1 Week Weather Features
  AVG(avgTemperature) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) AS avgTemperature1Week,
  AVG(avgTd) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) AS avgTd1Week,
  MAX(maxTemperature) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) AS maxTemperature1Week,
  MIN(minTemperature) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) AS minTemperature1Week,
  AVG(precipitation) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) AS avgPrecipitation1Week,
  — 1 Week Area Size Features (remember earlier we decided to keep this!)
  AVG(avgTemperature) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) / areaSize AS avgTemperaturePerAreaSize1Week,
  AVG(avgTd) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) / areaSize AS avgTdPerAreaSize1Week,
  AVG(precipitation) OVER (PARTITION BY sa4Location ORDER BY day ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING) / areaSize AS avgPrecipitationPerAreaSize1Week
FROM…

Несколько советов…

• В зависимости от того, как выглядят данные Google Trends, может быть полезно удалить квантиль 99%, чтобы избежать каких-либо сумасшедших всплесков, которые могут запутать модель машинного обучения (APPROX_QUANTILES отлично подходит для этого).
• Не останавливайтесь на агрегировании по одной неделе за раз (в соответствии с SQL). Модель ML может находить закономерности в агрегатах погоды за другие периоды времени, например, за 12 недель.
• В SQL вы увидите возможность создавать и использовать новые функции, например, MAX и MIN Precipitation. Посмотрите, что вы можете найти, чтобы повысить точность модели машинного обучения.
• Помните, что вы агрегируете прошлые данные и должны убедиться, что самая старая рассматриваемая дата имеет данные до того, как она будет агрегирована, например, для 1-недельных окон в предложении WHERE обеспечить день › (SELECT DATE_ADD(MIN(day), INTERVAL 7 DAY).

Это готовый набор данных ML. Новая таблица australia_state_historical_weather_dataset готова для обучения модели машинного обучения. Ура.

Машинное обучение BigQuery

На нем можно использовать BigQuery ML, чтобы построить модель и начать делать прогнозы. С помощью нескольких строк SQL (начиная с CREATE MODEL) у вас будет модель для экспериментов.

CREATE OR REPLACE MODEL `[project].when_are_the_bugs_coming`
OPTIONS(model_type=’linear_reg’,
        input_label_cols=[‘mosquitoSearchVolume4WeekLabel’]) AS
SELECT *
FROM australia_state_historical_weather_dataset

Это созданная модель. Теперь вы можете создать снимок australia_state_historical_weather_dataset, содержащий свежие данные за неделю, и начать делать прогнозы.

SELECT
  month,
  state,
  mosquitoSearchVolume4WeekLabel AS prediction
FROM
  ML.PREDICT(
    MODEL `[project].when_are_the_bugs_coming`
    (SELECT * EXCEPT mosquitoSearchVolume4WeekLabel
    FROM australia_state_snapshot_weather_dataset))

Дайте нам знать, как вы поживаете.

Спасибо за чтение.