Кога идват бъговете? ML моделиране в BigQuery за прогнозиране на следващата епидемия от грешки

Първо, Честита Нова Година 🎉 С навлизането на лятото в Австралия ние започнахме 2020 г. с малко забавление, опитвайки се да отговорим на въпроса, който всички си мислят в Австралия по това време на годината (особено жена ми и децата ми!), който е — кога идват бъговете?! Това е прогнозиране на епидемии от хлебарки и комари (известни още като мози).

Проектът за прогнозиране на грешки всъщност се събра през октомври. Достатъчно време, за да проучваме, итерираме и тестваме, преди летният сезон — и съответно грешките — да се задействат. В тази публикация в блога ще споделим техническото решение, което следвахме, и как би изглеждало решение на високо ниво.

Всички използват Google Cloud BigQuery, като се възползват от географските, аналитични и ML функции на BigQuery.

Кратко въведение за тези от вас, които не ни познават

Аз съм Дрю. Работя в Google от известно време. Настоящата ми роля е като инженер по решения за клиенти, който помага на нашите рекламодатели да извлекат най-доброто от своите уебсайтове, приложения и данни. Всичко от бюрото ми в красивия Сидни.

Аз съм Денис. Бях анализатор и консултант в Google в Сидни през последните 6 години, работейки върху прозрения, стратегия и решения за рекламни клиентски проблеми, използвайки данни, и в тази роля развих истинска страст към машинното обучение.

Времето казвате?

Да, времето! Метеорологичните модели се превърнаха в нашата основна характеристика в този модел, тъй като е доказано, че „имат ефект върху популацията на насекомите“. Използвайки исторически данни за времето, можем да търсим корелации между моделите във времето и избухванията на грешки. Имайки предвид температурата, валежите/валежите, влажността... (и потенциала за много повече).

Исторически данни за времето

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

Освен това, тъй като се интересуваме само от метеорологичните тенденции в Австралия, ще намалим значително този набор, като изброяваме само местните метеорологични станции (въпреки че би било забавно един ден да погледнем това в световен мащаб).

…и voilà, таблица с данни за australia_historical_weather, размер от приблизително 1,36 MB / 17 088 реда, готова за игра.

Щатски граници на Австралия

Обикновено данните за времето се свързват с местоположението на метеорологичната станция по ширина/дължина. Какъвто е случаят с данните на NCAR. Така че първото истинско предизвикателство е картографирането и преместването на тези местоположения по ширина/дължина до по-значими географски местоположения, т.е. като град, град или щат.

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

Уебсайтът на Австралийското статистическо бюро (abs.gov.au) поддържа местоположения на „Статистическа зона“. С помощта на ABS Maps можем да изтеглим .shp файла „State and Territory (STE) ASGS Ed 2016 Digital Boundaries in ESRI Shapefile Format“, който подробно описва границите за всички австралийски щати (подобно на тази екранна снимка на щатска граница в Google Maps).

Тези данни включват също площта (km2) на всеки щат. хубаво! Нека се уверим, че ще запазим и това, ще бъде полезна функция по-късно 👍.

Веднъж изтеглен, можем да конвертираме Shapefile (.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

Страхотната средна публикация от Lak Lakshmanan за „как да заредите географски данни като шейп файлове в BigQuery“ покрива добре това.

След като CSV е наличен, той може да бъде качен в BigQuery, създавайки таблица australia_state_boundaries за използване, с размер 87,9 MB / 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

В същото време създава максимални, минимални и средни стойности в SQL. Нека наречем тази нова таблица australia_state_historical_weather.

Google Тенденции за огнища на грешки

Google Trends е инструмент за анализ на заявките за търсене в Google във времето. Разглеждайки термина „Хлебарка“, става ясно, че данните за тенденциите са полезен индикатор за огнища, можете да видите (на тази екранна снимка, направена от Google Tends), търсенията за темата се увеличават с наближаването на лятото в Австралия.

Съответно това е стойността, която ще се опитаме да предвидим, т.е. етикетът, използван в нашия ML модел. Инструментът за тенденции ви позволява да филтрирате по състояние и да изтегляте информацията по CSV.

Присъединете се към това с таблицата australia_state_historical_weather и вече имаме набор от данни, съдържащ последните няколко години за времето и тенденциите в грешките.

Накратко. Инструментът за тенденции също има раздел „Свързани заявки“, показващ какво са търсили и потребителите (примерна екранна снимка)…

но каквото и да правите, не гледайте в този раздел, когато сте на страницата с тенденциите на хлебарки... и когато не я гледате, определено не виждате свързаната заявка за мляко от хлебарки! и каквото и да правите, не позволявайте на любопитството да надделее над вас и търсете, за да разберете, че това е нова мания за здравето 🙈! Никога няма да гледам на света по същия начин 🤮.

Обучение на нашия „бъги“ ML модел

Това е „бъги“, а не „бъги“, модел 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% квантил, за да избегнете луди пикове, които ще объркат ML модела („APPROX_QUANTILES“ е страхотен за това).
• Не спирайте просто да агрегирате назад 1 седмица наведнъж (според SQL). Моделът ML може да открие модели в агрегациите за времето през други периоди от време, например 12 седмици.
• В SQL ще видите, че има възможност за създаване и игра с нови функции, напр. MAX и MIN валежи. Вижте какво можете да намерите, за да подобрите точността на ML модела.
• Не забравяйте, че събирате минали данни и трябва да се уверите, че най-старата разглеждана дата има данни преди да се обобщи, например за 1 седмични прозорци в клаузата WHERE, гарантирайте ден › (SELECT DATE_ADD(MIN(ден), INTERVAL 7 DAY).

Това е готов наборът от данни за ML. Нова таблица australia_state_historical_weather_datasetготова за обучение на ML модел. Ура

BigQuery ML

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))

Уведомете ни как се справяте.

Благодаря за четенето.