Использование эталонных данных из Базы данных SQL для задания Azure Stream Analytics

  • Статья

Azure Stream Analytics поддерживает базу данных SQL Azure в качестве источника эталонных входных данных. Базу данных SQL можно использовать как источник эталонных данных для задания Stream Analytics на портале Azure и в Visual Studio с помощью средств Stream Analytics. В этой статье показано, как реализовать оба этих варианта.

Портал Azure

Чтобы добавить базу данных SQL Azure в качестве источника эталонных входных данных с помощью портала Azure, выполните приведенные ниже действия.

Предварительные требования при использовании портала

  1. Создание задания Stream Analytics.

  2. Создайте учетную запись хранения, которая будет использоваться заданием Stream Analytics.

    Важно!

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

  3. Создайте базу данных SQL Azure с набором данных, который будет использоваться как источник эталонных данных в задании Stream Analytics.

Определение Базы данных SQL в качестве источника эталонных входных данных

  1. В задании Stream Analytics в разделе Топология задания выберите Входные данные. Щелкните Добавить ссылочный вход и выберите База данных SQL.

    Inputs is selected in the left navigation pane. On Inputs, + Add reference input is selected, revealing a drop-down list that shows the values Blob storage and SQL Database.

  2. Заполните данные конфигурации для входных данных Stream Analytics. Выберите имя базы данных и сервера, а также имя пользователя и пароль. Если необходимо, чтобы эталонные входные данные периодически обновлялись, щелкните "Вкл.", чтобы задать частоту обновления в формате ДД:ЧЧ:MM. Если у вас есть большие наборы данных, которые часто обновляются. Разностный запрос позволяет отслеживать изменения в эталонных данных путем получения всех строк в Базе данных SQL, которые были вставлены или удалены в пределах времени начала @deltaStartTime и времени окончания @deltaEndTime.

См. разностный запрос.

When SQL Database is selected, the SQL Database New input page appears. There is a configuration form in the left pane, and a Snapshot query in the right pane.

  1. Проверьте запрос моментального снимка в редакторе SQL-запросов. Дополнительные сведения см. в статье Краткое руководство. Использование редактора SQL-запросов на портале Azure для подключения к данным и их запроса.

Выбор учетной записи хранения в конфигурации задания

В разделе Настройка выберите пункт Параметры учетной записи хранения и щелкните Добавить учетную запись хранения.

Storage account settings is selected in the left pane. There is an Add storage account button in the right pane.

Запуск задания

После того как вы настроите другие входные и выходные данные, а также запрос, можно запустить задание Stream Analytics.

Инструменты для Visual Studio

Чтобы добавить базу данных SQL Azure в качестве источника эталонных входных данных с помощью Visual Studio, выполните приведенные ниже действия.

Предварительные требования при использовании Visual Studio

  1. Установите инструменты Stream Analytics для Visual Studio. Поддерживаются следующие версии Visual Studio:

    • Visual Studio 2015
    • Visual Studio 2019

  2. Ознакомьтесь со статьей Краткое руководство. Создание задания Stream Analytics с использованием инструментов Azure Stream Analytics для Visual Studio.

  3. Создание учетной записи хранения.

    Важно!

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

Создание таблицы базы данных SQL

Создайте таблицу для хранения эталонных данных, используя SQL Server Management Studio. Дополнительные сведения см. в статье Разработка первой Базы данных SQL Azure с использованием SSMS.

Пример таблицы, используемый в следующем примере, был создан с помощью следующей инструкции:

create table chemicals(Id Bigint,Name Nvarchar(max),FullName Nvarchar(max));

Выберите свою подписку

  1. В Visual Studio в меню Вид выберите Обозреватель серверов.

  2. Щелкните пункт Azure правой кнопкой мыши, выберите Connect to Microsoft Azure Subscription (Подключиться к подписке Microsoft Azure) и войдите в систему с помощью учетной записи Azure.

Создание проекта Stream Analytics

  1. Выберите Файл > Новый проект.

  2. Из списка шаблонов слева выберите Stream Analytics и щелкните Azure Stream Analytics Application (Приложение Azure Stream Analytics).

  3. Введите значения в поля Имя, Расположение и Имя решения для проекта, а затем нажмите кнопку ОК.

    The Stream Analytics template is selected, Azure Stream Analytics Application is selected, and the Name, Location, and Solution names boxes are highlighted.

Определение Базы данных SQL в качестве источника эталонных входных данных

  1. Создайте новые входные данные.

    On Add New Item, Input is selected.

  2. Дважды щелкните файл Input.json в обозревателе решений.

  3. Заполните конфигурацию входных данных Stream Analytics. Выберите имя базы данных, имя сервера, тип и частоту обновления. Укажите частоту обновления в формате DD:HH:MM.

    In Stream Analytics Input Configuration, values are entered or selected from drop-down lists.

    Если вы выберете параметр Execute only once (Выполнить только один раз) или Execute periodically (Выполнять периодически), в узле файла Input.json проекта будет создан файл SQL с программным кодом, который называется [Псевдоним входных данных].snapshot.sql.

    The SQL CodeBehind file Chemicals.snapshot.sql is highlighted.

    Если вы выберете параметр Refresh Periodically with Delta (Периодическое разностное обновление), будет создано два файла SQL с программным кодом: [Псевдоним входных данных].snapshot.sql и [Псевдоним входных данных].delta.sql.

    The SQL CodeBehind files Chemicals.delta.sql and Chemicals.snapshot.sql are highlighted.

  4. Откройте файл SQL в редакторе и напишите SQL-запрос.

  5. Если вы используете Visual Studio 2019 и установили SQL Server Data Tools, запрос можно проверить, щелкнув Выполнить. Откроется окно мастера, которое поможет подключиться к Базе данных SQL, а результат запроса будет показан в окне внизу.

Определение учетной записи хранения

Откройте файл JobConfig.json, чтобы указать учетную запись хранения для хранения моментальных снимков эталонных данных SQL.

Stream Analytics Job Configure Configuration is shown with default values. The Global Storage Settings are highlighted.

Локальное тестирование и развертывание в Azure

Прежде чем развернуть задание в Azure, вы можете локально протестировать логику запроса с использованием реальных входных данных. Дополнительные сведения об этой функции см. в статье Тестирование реальных данных в локальной среде с помощью инструментов Azure Stream Analytics для Visual Studio (предварительная версия). После завершения тестирования щелкните Отправить в Azure. Дополнительные сведения о создании задания см. в статье Краткое руководство. Создание задания Stream Analytics с использованием инструментов Azure Stream Analytics для Visual Studio.

Разностный запрос

Вместе с разностным запросом рекомендуется использовать темпоральные таблицы в базе данных SQL Azure.

  1. Создайте временную таблицу в Базе данных SQL Azure.

       CREATE TABLE DeviceTemporal
   (
      [DeviceId] int NOT NULL PRIMARY KEY CLUSTERED
      , [GroupDeviceId] nvarchar(100) NOT NULL
      , [Description] nvarchar(100) NOT NULL
      , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
      , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
   )
   WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DeviceHistory));  -- DeviceHistory table will be used in Delta query

  2. Создайте запрос моментального снимка.

    Используйте параметр @snapshotTime, чтобы среда выполнения Stream Analytics получала набор эталонных данных из темпоральной таблицы Базы данных SQL, допустимой на момент времени системы. Если этот параметр не указан, существует риск получения неточного базового набора эталонных данных из-за отклонений часов. Ниже показан пример полного запроса моментального снимка.

       SELECT DeviceId, GroupDeviceId, [Description]
   FROM dbo.DeviceTemporal
   FOR SYSTEM_TIME AS OF @snapshotTime

  3. Создайте разностный запрос.

    Этот запрос извлекает из Базы данных SQL все строки, которые были вставлены или удалены в период между временем запуска (@deltaStartTime) и временем окончания (@deltaEndTime). Разностный запрос должен возвращать те же столбцы, что и запрос моментального снимка, а также столбец operation. Этот столбец определяет, будет ли строка вставлена или удалена в период между @deltaStartTime и @deltaEndTime. Итоговые строки помечены как 1, если записи были вставлены, или 2, если они были удалены. Запрос также должен добавлять водяной знак на стороне SQL Server, чтобы гарантировать правильное сохранение всех обновлений за период внесения изменений. Использование разностного запроса без водяного знака может привести к нарушениям в эталонном наборе данных.

    Темпоральная таблица ведет учет обновленных записей путем записи операций вставки и удаления. Затем среда выполнения Stream Analytics применит результаты разностного запроса к предыдущему моментальному снимку, чтобы обновить эталонные данные. Ниже показан пример разностного запроса.

       SELECT DeviceId, GroupDeviceId, Description, ValidFrom as _watermark_, 1 as _operation_
   FROM dbo.DeviceTemporal
   WHERE ValidFrom BETWEEN @deltaStartTime AND @deltaEndTime   -- records inserted
   UNION
   SELECT DeviceId, GroupDeviceId, Description, ValidTo as _watermark_, 2 as _operation_
   FROM dbo.DeviceHistory   -- table we created in step 1
   WHERE ValidTo BETWEEN @deltaStartTime AND @deltaEndTime     -- record deleted

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

    Важно!

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

     UPDATE myTable SET VALUE=2 WHERE ID = 1;
 UPDATE myTable SET VALUE=2 WHERE ID = 1;

    Правильный пример:

     UPDATE myTable SET VALUE = 2 WHERE ID = 1 and not exists (select * from myTable where ID = 1 and value = 2);

    Это гарантирует отсутствие повторяющихся обновлений.

Тестирование запроса

Важно убедиться, что запрос возвращает ожидаемый набор данных, который будет использоваться заданием Stream Analytics в качестве эталонных данных. Чтобы проверить запрос, перейдите к элементу "Ввод" в разделе "Топология задания" на портале. Теперь выберите "Образец данных" для эталонных входных данных Базы данных SQL. Когда образец станет доступен, скачайте файл и проверьте, правильно ли возвращаются данные. Если вы хотите оптимизировать итерации разработки и тестирования, мы рекомендуем применить средства Stream Analytics для Visual Studio. Вы можете использовать любой другой инструмент, но сначала убедитесь, что запрос возвращает из Базы данных SQL Azure правильные результаты, а затем применяйте его в задании Stream Analytics.

Протестируйте запрос с помощью Visual Studio Code

Установите средства Azure Stream Analytics и SQL Server (mssql) на Visual Studio Code и настройте проект ASA. Дополнительные сведения см. в разделе Краткое руководство. Создание задания Azure Stream Analytics в Visual Studio Code и в Руководстве по расширению SQL Server (mssql).

  1. Настройте входные эталонные данные SQL.

    A Visual Studio Code editor (tab) shows ReferenceSQLDatabase.json.

  2. Выберите значок SQL Server и нажмите кнопку Добавить подключение.

    + Add Connection appears in the left pane and is highlighted.

  3. Заполните раздел сведений о подключении.

    The two boxes for database and server information are highlighted.

  4. Щелкните правой кнопкой мыши "эталонный SQL" и выберите команду Выполнить запрос.

    Execute Query is highlighted in the context menu.

  5. Выберите подключение.

    The dialog box says

  6. Просмотрите и проверьте результат запроса.

    The query search results are in a VS Code editor tab.

Вопросы и ответы по

Будет ли взиматься дополнительная плата за использование входных эталонных данных SQL в Azure Stream Analytics?

В задании Stream Analytics нет дополнительных затрат за единицу потоковой передачи. Однако задание Stream Analytics должно иметь связанную учетную запись хранения Azure. Задание Stream Analytics запрашивает базу данных SQL (во время запуска задания и интервала обновления), чтобы получить набор эталонных данных, и сохраняет моментальный снимок в учетной записи хранения. За хранение этих моментальных снимков будет взиматься дополнительная плата, указанная на странице цен на учетную запись хранения Azure.

Как узнать, что моментальный снимок эталонных данных запрашивается из базы данных SQL и используется в задании Azure Stream Analytics?

Для мониторинга состояния эталонных входных данных Базы данных SQL можно использовать две метрики с фильтрацией по логическому имени (в разделе метрик на портале Azure).

  • InputEvents. Эта метрика измеряет количество записей, загруженных из набора эталонных данных Базы данных SQL.
  • InputEventBytes: эта метрика измеряет размер моментального снимка эталонных данных, загруженного в память задания Stream Analytics.

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

Нужен ли специальный тип базы данных SQL Azure?

Azure Stream Analytics будет работать с любым типом базы данных SQL Azure. Однако важно понимать, что частота обновления, заданная для эталонных входных данных, может повлиять на загрузку запросов. Чтобы использовать разносные запросы, рекомендуется использовать темпоральные таблицы в базе данных SQL Azure.

Почему Azure Stream Analytics хранит моментальные снимки в учетной записи хранения Azure?

Она гарантирует обработку событий только один раз и по крайней мере одну доставку событий. В случаях, когда временные проблемы влияют на задание, для восстановления состояния необходимо выполнить небольшое количество воспроизведений. Чтобы включить воспроизведение, эти моментальные снимки должны храниться в учетной записи хранения Azure. Дополнительные сведения о воспроизведении контрольных точек см. в статье Концепции контрольных точек и воспроизведения в Azure Stream Analytics.

Следующие шаги