Этот пост является продолжением Части первой, в которой мы рассмотрели, как безопасно подключить парк промышленных устройств IoT, передающих данные телеметрии в вашу среду Google Cloud через IoT Core и Pub/Sub. и Часть вторая, где эти данные были плавно перемещены из Pub/Sub в BigQuery через Dataflow, а затем визуализированы с помощью Data Studio.

Если вы следили за моими предыдущими статьями, вам понравилось устанавливать датчики температуры по всему дому и наблюдать, как их данные в режиме реального времени беспрепятственно передаются в GCP, в конечном итоге попадая в службу хранилища данных Google BigQuery. Что теперь? Как мы можем использовать эти данные на практике?

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

Если вы не читали предыдущие статьи, не бойтесь! Я сделал свой набор данных доступным на Kaggle; не стесняйтесь использовать это, когда будете следовать дальше.

Обзор машинного обучения BigQuery

В отличие от других служб хранилища данных, обучение и развертывание машинного обучения встроены прямо в BigQuery. И обучение, и развертывание выполняются с помощью SQL-подобных команд, которые легко создать.

С помощью всего нескольких строк кода в стиле SQL вы можете указать тип создаваемой модели, например модели, основанные на логистической или линейной регрессии, кластеризации k-средних, глубоких нейронных сетях и т. д. Или вы можете оставить это на усмотрение Google, создав модель AutoML Tables, как мы сделаем в этой статье. Для большинства моделей не требуется никаких спецификаций, кроме предоставления столбцов меток и функций.

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

Цель и методология машинного обучения

В то время, когда я пишу эту статью (февраль 2021 г.), в Орегоне было холодно 48 градусов по Фаренгейту. Обогреватель пыхтит, поддерживая меня в хорошем состоянии, пока я работаю, но время от времени я хотел бы оставить окно открытым, чтобы освежить воздух в доме, особенно с учетом того, что у меня есть девятинедельный корги, который не t полностью приучен к горшку!

Что я хотел бы сделать, так это иметь возможность открывать окно, и если оно остается открытым слишком долго, чтобы GCP напоминал мне, что оно открыто. Не хочу тратить слишком много электроэнергии! Если возможно, я бы хотел, чтобы датчики температуры, разбросанные по всему основному жилому пространству моего дома, также сообщали мне какое конкретное окно закрыть.

Есть три датчика, которые передают данные телеметрии температуры в мой проект GCP, и каждый из них расположен возле своего окна в главной гостиной моего дома.

Два датчика находятся очень близко к своим окнам, а третий находится примерно в 8 футах от ближайшего окна. Вы можете увидеть близость датчика к окнам, отраженную в данных, где один датчик (device_id) сообщает значения на несколько градусов теплее, чем два других:

Чтобы построить модель машинного обучения, которая может определять, когда открыто определенное окно, нам нужно выполнить несколько шагов преобразования данных, чтобы получить конечную таблицу, готовую для обучения модели ML. Для обучения модели требуется:

  1. Отслеживание временных рамок, когда окно открыто. Я вручную ввел время начала и окончания «окно № 1/2/3 открыто» в Excel. Предполагается, что неотслеживаемые временные рамки имеют закрытые окна. Эта таблица в конечном итоге будет преобразована в CSV и загружена как таблица BigQuery.
  2. Преобразование таблицы необработанных потоковых данных о температуре, где каждая строка содержит значение температуры для конкретного датчика за определенную секунду, в сводную таблицу, в которой каждая отдельная секунда имеет свою собственную выделенную строку данных. Каждая посекундная строка будет содержать столбцы, в которых хранится температура каждого устройства на данный момент, а также столбцы, показывающие разницу между текущим значением датчика и его значением x количество секунд назад. Целью здесь является отслеживание текущего значения температуры каждого датчика, а также того, насколько это значение изменилось по сравнению с некоторым интервалом времени в прошлом: 10 минут назад, 5 минут, 3 минуты, 1 минута и так далее до 5 секунд назад. . Эти столбцы будут действовать как отличные прогностические функции.
  3. Таблица BQ, определяющая временные рамки, когда окна открыты и закрыты, должна быть объединена со сводной таблицей температуры, что даст нам окончательную таблицу, содержащую наши столбцы характеристик прогнозируемой температуры и Столбец открытое окно/все окна закрыты.

Давайте быстро рассмотрим, как выполнить каждый из этих шагов.

Отслеживание таймфреймов открытых окон и их загрузка в BigQuery

Когда я открывал одно из трех окон в своем доме, я вручную отслеживал точный момент открытия определенного окна в таблице Excel:

Обратите внимание, что значения ObjectCode от первого до третьего представляют каждое конкретное окно, тогда как неуказанное значение, равное нулю, будет использоваться позже для представления всех закрытых окон.

Собрав за 1,5 месяца то, что мне показалось достаточным, я экспортировал это в CSV, загрузил этот CSV в корзину облачного хранилища (GCS), а затем выполнил следующую команду, чтобы создать из нее новую таблицу BigQuery с помощью схема автоматически обнаружена:

Поскольку потоковые данные:

  1. Хранится в формате UTC, а не в значении PST, которое я записал вручную, и
  2. Хранение значений даты и времени, а не отдельных значений даты и времени, которые я записал вручную

Я выполнил следующую команду SQL, чтобы преобразовать точки времени начала и окончания открытого окна в стандартное время универсального времени:

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

Примечание: если вы работаете с необработанными данными о температуре из моего набора данных Kaggle, а не с вашими собственными потоковыми данными, используйте следующее, чтобы импортировать этот CSV в свой проект, прежде чем двигаться дальше:

Создание сводной таблицы температуры с датой и временем

Сведение таблицы необработанных данных температуры для каждого датчика в секунду в посекундные строки со всеми присутствующими значениями датчиков можно выполнить с помощью следующего SQL. Обратите внимание, что в этом запросе я также исключаю строки данных, в которых один или несколько датчиков не смогли записать значение. Было много случаев, когда отключалось электричество — весь мой дом на час в день Рождества, Roomba натыкался на шнур питания датчика, Maple опрокидывал датчик… ожидайте неожиданного!

Вам может сойти с рук приписывание разумных значений по умолчанию для этих нулевых значений (например, усреднение значений этого датчика за последние 60 секунд). Однако в моем наборе данных есть ~ 10,5 млн строк необработанных данных, чего более чем достаточно для обучения модели. Вероятно, лучше полностью исключить строки с нулевыми значениями из обучения модели, чем пытаться угадать разумные значения по умолчанию.

Мы почти готовы начать обучение нашей модели! Нам нужно только создать еще одну таблицу, содержащую следующее:

  1. Слияние временных точек с известными состояниями открытого окна. Все моменты времени, в которых отсутствует состояние открытого окна, по умолчанию получают значение закрытого состояния.
  2. Создание столбцов ретроспективных значений температуры. Хотя полезно знать температуру устройства в данную секунду, было бы полезнее включить сравнение текущего значения с различными прошлыми временными точками.

Для достижения этих целей я запускаю последний оператор SQL, создавая окончательную таблицу BigQuery, которая будет служить входными данными для нашего процесса обучения машинному обучению. Хочу отметить, что есть гораздо более быстрый, хотя и значительно более дорогой способ получения этих ретроспективных значений, но об этом позже.

С этой итоговой таблицей у нас наконец есть все необходимое для обучения модели машинного обучения BigQuery.

Обучение машинному обучению BigQuery

Следующие четыре строки SQL-подобного кода сообщают BigQuery:

  • Обучите модель машинного обучения с помощью алгоритма Google AutoML Tables. Если это займет больше 24 часов, сократите обучение и работайте с лучшей моделью, созданной на данный момент.
  • Используйте «object_code» (состояние открытия/закрытия окна) в качестве столбца для прогнозирования.
  • Все остальные столбцы должны использоваться как функции, кроме даты и времени.

Запустите это, подождите, пока оно завершится, и все. Шутки в сторону!

Учитывая, что сводной набор данных довольно велик — 25 столбцов и ~3,2 млн строк или около 80 млн ячеек данных, а также зная, что AutoML постоянно обучает и оценивает множество вычислительно интенсивных глубоких нейронных сетей, это займет некоторое время.

Обучение BigQuery ML: предупреждение о расходах

На момент написания этой статьи функция AutoML в BigQuery оплачивается по цене 5 долларов США за ТБ плюс стоимость обучения платформы ИИ. Вы можете ожидать, что это будет довольно дешевая операция обучения, учитывая, что сводная таблица, на которой мы тренируемся, имеет размер 659 МБ:

Однако AutoML создает, а затем сканирует множество временных наборов данных в рамках процесса обучения DNN. Когда 24-часовая модель, наконец, закончила генерацию, она обработала (и выставила счет!) более 89 ТБ данных:

Это обошлось в 445,85 долларов США, не считая обфусцированных сборов за обработку, понесенных за кулисами BigQuery с использованием платформы ИИ для обучения, в результате чего общая стоимость составила около 500 долларов США.

Если вы собираетесь протестировать BQ ML, не тратя много денег, будьте осторожны с тем, сколько часов обучения вы планируете. Изменение BUDGET_HOURS с 24,0 на 1,0 часа дает модель, которая завершается за 1 час 41 минуту и ​​обрабатывает только 3,87 ТБ, или примерно 19,35 долларов США (плюс скрытые затраты на обучение):

Результаты обучения BigQuery ML

После ожидания в течение 24 часов модель завершила генерацию. Результаты весьма впечатляющие! Общие показатели оценки, такие как точность, воспроизводимость, полнота, оценка F1 и ROC AUC, все дают значения ≥0,99:

Несмотря на исключительно несбалансированное представительство среди классов меток — окна остаются в закрытом состоянии гораздо чаще, чем в открытом, так как я не хочу слишком увеличивать свой счет за электроэнергию — каждое открытое состояние окна, тем не менее, обладает исключительно высокими истинными положительными показателями. Даже третье окно, ближайший датчик которого находится на расстоянии около восьми футов, а не дюймов, удалось обеспечить истинный положительный уровень обнаружения › 97%:

Генерация модели с отсечкой в ​​один час дает пригодную для использования модель, хотя показатели TP для отдельных классов ниже, и существенно ниже для третьего окна с его более удаленным датчиком температуры:

Довольно круто, как легко построить функциональную модель машинного обучения с помощью BQ… но полезно ли это? Действительно ли 24-часовая модель эффективна? Как мы можем получить прогнозы, используя эту модель?

Развернуть модель в BigQuery и получить прогнозы так же просто, как запустить две строки кода SQL после форматирования необработанных данных обратно в формат сводной таблицы.

Развертывание BigQuery ML и прогнозы

Следующий SQL форматирует необработанные временные потоки в формат сводной таблицы, а затем передает их в модель BQ ML для прогнозов. В конечном итоге запрос возвращает прогнозы открытых/закрытых окон за последние 600 секунд.

Если бы этот запрос выполнялся из Облачной функции (бессерверная служба кода) каждые 60 секунд с использованием Облачного планировщика (служба заданий cron) и если ≥95% прогнозов за последние 10 минут вызывались в -zero, вы можете настроить эту облачную функцию на немедленную отправку электронного письма или текстового сообщения с указанием того, что окно x было открыто слишком долго и его необходимо закрыть. Такая система оповещения позволяет кратковременно открывать окно и уведомлять, когда оно остается открытым слишком долго.

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

Давайте посмотрим, как эта модель работает, когда все окна закрыты:

Девять секунд спустя, большая часть которых была потрачена на поворот таблицы данных, а не на предсказание модели:

Совсем неплохо! Похоже, нам не хватает последних 13 секунд потоковой передачи значений температуры с одного или нескольких устройств, а остальные 587 секунд правильно идентифицированы как находящиеся в закрытом состоянии.

Открыв окно № 1 и подождав около 15 секунд, я повторно запустил скрипт прогнозирования:

Вуаля! В течение нескольких секунд после открытия модель начала идентифицировать это конкретное окно как открытое. Примерно через полторы минуты снова запустим скрипт предсказания:

Мы видим, что открытое состояние четко и постоянно идентифицируется!

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

Оповещение по электронной почте с помощью облачной функции

К сожалению, Google Cloud не предлагает услуги SMS или электронной почты, вместо этого перенаправляя клиентов на использование сторонних компаний. Демонстрация электронной почты или SMS через облачную функцию с внешней службой была бы дорогой и удлинила бы эту и без того длинную статью, поэтому мы прервем руководство здесь.

Если у вас есть служба электронной почты, сценарий, подобный следующему, должен хорошо работать для оповещения через облачную функцию Python:

Быстрое извлечение ретроспективных температур с помощью внешней таблицы BigTable

Хороший кусок SQL преобразования данных сводной таблицы предназначен для получения значений «текущая минус предыдущая температура» для каждой отдельной секунды. Выполнение этих обращенных в прошлое ВНУТРЕННИХ СОЕДИНЕНИЙ полностью с помощью BigQuery, хотя и выполнимо, не будет высокопроизводительным в масштабе. BigQuery лучше всего оптимизирован для крупномасштабной аналитики в одной таблице. Как и во всех хранилищах данных, он не может поддерживать индексы из-за возможности хранения и аналитики в петабайтном масштабе, и это делает операции JOIN очень ресурсоемкими с точки зрения вычислений, и их следует по возможности избегать.

Возможно, вы захотите рассмотреть возможность настройки экземпляра BigTable, масштабируемой службы базы данных NoSQL Google Cloud, которая предлагает однозначные миллисекунды времени отклика для отдельных запросов строк и дублирования хранения ваших необработанных данных о температуре в этой службе. В BigQuery вы можете настроить экземпляр BigTable как внешнюю таблицу, а затем выполнять запросы к этой таблице, как если бы это была таблица BigQuery.

Если бы вы настроили второе задание Dataflow, которое перемещает ваши данные IoT, попадающие в подписку PubSub, в BigTable, убедившись, что вы используете первичный ключ, сочетающий идентификатор устройства и дату и время, вы сможете получить отдельные моменты времени для определенного устройства и времени. точка намного быстрее, чем BQ SQL, когда полностью извлекает данные из нативных таблиц BigQuery.

При таком подходе вы затем обновите свой SQL для создания сводной таблицы до INNER JOIN для внешней таблицы BigTable, используя комбинацию устройство-дата-время в качестве ключа JOIN ON.

Теоретически этот рабочий процесс должен быть более масштабируемым и высокопроизводительным, но он также будет значительно дороже. Даже «дешевый» экземпляр с одним узлом для тестирования разработки будет стоить вам 468 долларов в месяц в us-central1. Тем не менее вам следует попробовать реализовать этот подход, если вы собираетесь развертывать операции IoT в масштабе.

Вывод

Поздравляем, что вы застряли в этом глубоком погружении в IoT и ML на GCP. Я надеюсь, что это было поучительно и помогло вам ускорить ваш путь к получению, анализу и реальному использованию крупномасштабных наборов данных в облаке, используя как можно больше полностью управляемых, автоматически масштабируемых бессерверных сервисов, доступных для того, чтобы свести к минимуму время, затрачиваемое на стресс из-за времени безотказной работы кластера, и максимально увеличить время, затрачиваемое на выполнение значимой работы.

В награду за ваше терпение и настойчивость, самое меньшее, что я могу сделать, это предложить вам последнее фото милого щенка!