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

Справочные данные — это статический или медленно меняющийся набор данных, который вы объединяете с потоковыми данными для их обогащения, например, добавления информации о продукте в поток событий продаж. Azure Stream Analytics поддерживает База данных SQL Azure как источник справочных данных, поэтому вы можете искать и объединять эти данные с вашим вводом в реальном времени.

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

Добавляйте ссылки на SQL Database с помощью портала 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».

    Скриншот панели Stream Analytics Inputs с выбранным вводом Add reference, показывающий выпадающий список со значениями Blob storage и SQL Database.

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

    Дополнительные сведения см. в разделе разностный запрос.

    Скриншот новой входной страницы SQL Database с формой конфигурации в левой панели и запросом к снимку в правой.

  3. Проверьте запрос моментального снимка в редакторе SQL-запросов. Для получения дополнительной информации см. раздел «Используйте редактор SQL-запросов портала Azure для подключения и запроса данных».

Укажите аккаунт хранения в конфигурации задания

Перейдите в настройки аккаунта хранения в разделе «Конфигурировать», затем выберите «Добавить аккаунт хранения».

Скриншот панели настроек аккаунта хранения с кнопкой «Добавить аккаунт для хранения» в правой панели.

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

  1. После настройки других входов, выходов и запросов начните работу Stream Analytics.

Добавляйте справочные данные SQL Database с помощью Visual Studio

Используйте следующие шаги, чтобы добавить База данных SQL Azure в качестве источника справочного входа с помощью Visual Studio:

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

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

    • Visual Studio 2015 г.
    • Visual Studio 2019

  2. Ознакомьтесь с кратким руководством по инструментам 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, выберите «Подключиться к Microsoft Azure Subscription» и войдите в систему через свой аккаунт Azure.

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

  1. Выберите Файл>Создать проект.

  2. В списке шаблонов выберите Stream Analytics, а затем Azure Stream Analytics Application.

  3. Введите имя проекта, местоположение и решение и выберите OK.

    Скриншот диалога New Project с выбранным шаблоном Stream Analytics и приложением Azure Stream Analytics, а также выделенными окнами имени Name, Location и Solution.

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

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

    Скриншот диалога «Добавить новый элемент» с выбранным «Введение».

  2. Открыть Input.jsonв Обозреватель решений.

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

    Скриншот конфигурации ввода Stream Analytics с введёнными или выбранными значениями из выпадающих списков.

    Если вы выберете «Выполнить только один раз» или «Выполнить» периодически, Visual Studio генерирует один SQL CodeBehind файл с названием [Input Alias].snapshot.sql в проекте под узлом Input.json файла.

    Снимок экрана окна Обозреватель решений с выделенным файлом SQL CodeBehind Chemicals.snapshot.sql.

    Если вы выберете Периодически обновлять с Delta, Visual Studio создаст два SQL-файла CodeBehind: [Input Alias].snapshot.sql и [Input Alias].delta.sql.

    Скриншот Обозреватель решений с выделенными файлами SQL CodeBehind Chemicals.delta.sql и Chemicals.snapshot.sql.

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

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

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

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

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

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

Перед развертыванием задачи в Azure можно локально протестировать логику запроса по живым входным данным. Для получения дополнительной информации об этой функции см. раздел «Локальное тестирование живых данных с использованием инструментов Azure Stream Analytics для Visual Studio (Preview). Когда закончите тестирование, выберите «Отправить в 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 Database, действительной на указанный системный момент времени. Если вы не предоставите этот параметр, вы рискуете получить неточный базовый набор данных из-за смещения тактового сигнала. В следующем примере приведен запрос полного снимка:

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

  3. Создайте дельта-запрос.

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

    Темпоральная таблица ведет учет обновленных записей путем записи операций вставки и удаления. Затем 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.

    Скриншот вкладки редактора Visual Studio Code, где отображается файл ReferenceSQLDatabase.json.

  2. Выберите значок SQL Server и выберите «Добавить соединение».

    Скриншот левой панели с выделенной опцией Add Connection.

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

    Скриншот формы подключения с выделенными пунктами данных базы данных и сервера.

  4. Нажмите и удерживайте (или щелкните правой кнопкой мыши) в справочнике SQL и выберите Выполнить запрос.

    Скриншот контекстного меню с выделенной опцией «Выполнить запрос».

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

    Снимок экрана диалогового окна с текстом «Создать профиль подключения из списка ниже», в котором выделен один элемент списка.

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

    Скриншот результатов поиска запроса во вкладке редактора Visual Studio Code.

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

Повлечет ли использование входных справочных данных SQL в Azure Stream Analytics дополнительные расходы?

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

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

Две метрики, отфильтрованные по логическому имени (в разделе Metrics в портале Azure), позволяют отслеживать состояние входных справочных данных SQL Database.

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

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

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

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

Почему Azure Stream Analytics хранит снимки в аккаунте служба хранилища Azure?

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

Связанные материалы

  • Last updated on