Uso de datos de referencia de una instancia de SQL Database para un trabajo de Azure Stream Analytics

  • Artículo

Azure Stream Analytics admite Azure SQL Database como origen de la entrada de datos de referencia. Puede usar SQL Database como datos de referencia para su trabajo de Stream Analytics en Azure Portal y en Visual Studio con herramientas de Stream Analytics. En este artículo se muestra cómo realizar ambos métodos.

Portal de Azure

Use los pasos siguientes para agregar Azure SQL Database como un origen de entrada de referencia mediante Azure Portal:

Requisitos previos de Azure Portal

  1. Cree un trabajo de Stream Analytics.

  2. Cree una cuenta de almacenamiento para que la use Stream Analytics.

    Importante

    Azure Stream Analytics conserva las instantáneas dentro de esta cuenta de almacenamiento. Al configurar la directiva de retención, es imperativo asegurarse de que el intervalo de tiempo elegido abarca eficazmente la duración de recuperación deseada para el trabajo de Stream Analytics.

  3. Cree una base de datos de Azure SQL con un conjunto de datos para que el trabajo de Stream Analytics la pueda usar como datos de referencia.

Definición de la entrada de datos de referencia de SQL Database

  1. En su trabajo de Stream Analytics, seleccione Entradas en Topología de trabajo. Haga clic en Agregar entrada de referencia y elija SQL Database.

    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. Rellene la configuración de entrada de Stream Analytics. Elija el nombre de la base de datos, el nombre del servidor, el nombre de usuario y la contraseña. Si desea que la entrada de datos de referencia se actualice periódicamente, elija "Activar" para especificar la frecuencia de actualización con el formato DD:HH:MM. Si tiene conjuntos de datos grandes con una frecuencia de actualización breve. La consulta delta le permite realizar un seguimiento de los cambios dentro de los datos de referencia recuperando todas las filas de SQL Database que se insertaron o eliminaron entre una hora de inicio, @deltaStartTime, y una hora de finalización @deltaEndTime.

Consulte Consulta delta.

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. Pruebe la consulta de instantánea en el editor de consultas SQL. Para más información, vea Uso del Editor de consultas SQL de Azure Portal para conectarse a datos y consultarlos.

Especificación de la cuenta de almacenamiento en la configuración de trabajos

Vaya a Configuración de cuenta de almacenamiento en Configurar y seleccione Agregar cuenta de almacenamiento .

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

Inicio del trabajo

Una vez haya configurado otras entradas, salidas y consultas, puede iniciar el trabajo de Stream Analytics.

Herramientas para Visual Studio

Use los pasos siguientes para agregar Azure SQL Database como un origen de entrada de referencia mediante Visual Studio:

Requisitos previos de Visual Studio

  1. Instale las herramientas de Stream Analytics para Visual Studio. Las siguientes versiones de Visual Studio son compatibles:

    • Visual Studio 2015
    • Visual Studio 2019

  2. Familiarícese con la guía de inicio rápido de las herramientas de Stream Analytics para Visual Studio.

  3. Cree una cuenta de almacenamiento.

    Importante

    Azure Stream Analytics conserva las instantáneas dentro de esta cuenta de almacenamiento. Al configurar la directiva de retención, es imperativo asegurarse de que el intervalo de tiempo elegido abarca eficazmente la duración de recuperación deseada para el trabajo de Stream Analytics.

Creación de una tabla de SQL Database

Use SQL Server Management Studio para crear una tabla para almacenar los datos de referencia. Consulte Diseño de la primera base de datos de Azure SQL mediante SSMS para obtener información detallada.

La tabla de ejemplo utilizada en el ejemplo siguiente se creó a partir de la siguiente instrucción:

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

Elija una suscripción

  1. En Visual Studio, en el menú Ver, seleccione Explorador de servidores.

  2. Haga clic con el botón derecho en Azure, seleccione Connect to Microsoft Azure Subscription (Conectarse a la suscripción de Microsoft Azure) e inicie sesión con su cuenta de Azure.

Creación de un trabajo de Stream Analytics

  1. Seleccione Archivo > Nuevo proyecto.

  2. En la lista de plantillas de la izquierda, seleccione Stream Analytics y Aplicación de Azure Stream Analytics.

  3. Escriba el nombre del proyecto, la ubicación y el nombre de la solución y seleccione Aceptar.

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

Definición de la entrada de datos de referencia de SQL Database

  1. Cree una entrada.

    On Add New Item, Input is selected.

  2. Haga doble clic en Input.json en el Explorador de soluciones.

  3. Rellene la configuración de entrada de Stream Analytics. Elija el nombre de la base de datos, el nombre del servidor, el tipo de actualización y la frecuencia de actualización. Especifique la frecuencia de actualización con el formato DD:HH:MM.

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

    Si elige "Ejecutar solo una vez" o "Ejecutar periódicamente", un archivo de código subyacente de SQL denominado [Alias de entrada].snapshot.sql se genera en el proyecto en el nodo del archivo Input.json.

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

    Si elige "Actualizar periódicamente con Delta", se generarán dos archivos de código subyacentes de SQL: [Input Alias].snapshot.sql y [Input Alias].delta.sql.

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

  4. Abra el archivo SQL en el editor y escriba la consulta SQL.

  5. Si usa Visual Studio 2019 y tiene herramientas de datos de SQL Server instaladas, puede probar la consulta al hacer clic en Ejecutar. Se abrirá una ventana del asistente para ayudarle a conectarse a SQL Database y el resultado de la consulta aparecerá en la ventana de la parte inferior.

Definición de la cuenta de almacenamiento

Abra JobConfig.json para especificar la cuenta de almacenamiento para almacenar las instantáneas de referencia SQL.

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

Prueba local e implementación en Azure

Antes de implementar el trabajo en Azure, puede probar la lógica de consulta localmente con los datos de entrada activos. Para más información sobre esta característica, vea Prueba local de datos activos mediante herramientas de Azure Stream Analytics para Visual Studio (versión preliminar). Cuando haya terminado las pruebas, haga clic en Enviar a Azure. Consulte la guía de inicio rápido Creación de un trabajo de Stream Analytics mediante las herramientas de Azure Stream Analytics para Visual Studio para obtener información sobre cómo iniciar el trabajo.

Consulta delta

Cuando se usa la consulta delta, se recomiendan las tablas temporales de Azure SQL Database.

  1. Cree una tabla temporal en Azure SQL Database.

       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. Cree la consulta de instantánea.

    Use el parámetro @snapshotTime para indicar al entorno de ejecución de Stream Analytics que obtenga el conjunto de datos de referencia de una tabla temporal válida de SQL Database a la hora del sistema. Si no proporciona este parámetro, corre el riesgo de obtener un conjunto de datos de referencia base impreciso debido al desfase del reloj. A continuación, se muestra un ejemplo de consulta de instantánea completa:

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

  3. Cree la consulta delta.

    Esta consulta recupera todas las filas de SQL Database que se insertaron o eliminaron en una hora de inicio, deltaStartTime y en una hora de finalización deltaEndTime. La consulta delta debe devolver las mismas columnas que la consulta de instantánea, además de la columna operation. Esta columna define si la fila se inserta o elimina entre deltaStartTime y deltaEndTime. Las filas resultantes se marcan como 1 si se insertaron los registros, o como 2 si estos se eliminaron. La consulta también debe agregar un límite del lado de SQL Server para asegurarse de que todas las actualizaciones del período delta se capturan adecuadamente. El uso de consultas delta sin límite puede devolver un conjunto de resultados de referencia incorrecto.

    En el caso de los registros actualizados, las tablas temporales realiza la contabilidad mediante la captura de una operación de inserción y eliminación. Después, el tiempo de ejecución de Stream Analytics aplicará los resultados de la consulta delta a la instantánea anterior para mantener actualizados los datos de referencia. A continuación, se muestra un ejemplo de consulta delta:

       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

    Tenga en cuenta que el tiempo de ejecución de Stream Analytics puede ejecutar periódicamente la consulta de instantánea, además de la consulta delta para almacenar los puntos de control.

    Importante

    Al usar consultas delta de datos de referencia, no realice actualizaciones idénticas a la tabla de datos de referencia temporal varias veces. Esto podría hacer que se generen resultados incorrectos. Este es un ejemplo que puede hacer que los datos de referencia generen resultados incorrectos:

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

    Ejemplo correcto:

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

    Esto garantiza que no se realicen actualizaciones duplicadas.

Prueba de la consulta

Es importante comprobar que la consulta devuelve el conjunto de datos esperado que el trabajo de Stream Analytics usará como datos de referencia. Para probar la consulta, vaya a Entrada en la sección Topología de trabajo en el portal. Luego, puede seleccionar los datos de ejemplo en la entrada Referencia de base de datos SQL. Una vez que el ejemplo está disponible, puede descargar el archivo y comprobar si los datos se devuelven según lo esperado. Si quiere optimizar las iteraciones de prueba y desarrollo, le recomendamos usar las Herramientas de Stream Analytics para Visual Studio. También puede usar cualquier otra herramienta que prefiera para asegurarse primero de que la consulta devuelve los resultados adecuados desde la base de datos de Azure SQL y, luego, usarla en el trabajo de Stream Analytics.

Prueba de la consulta con Visual Studio Code

Instale las Herramientas de Azure Stream Analytics y SQL Server (mssql) en Visual Studio Code y configure su proyecto de ASA. Para más información, consulte Inicio rápido: Creación de un trabajo de Azure Stream Analytics en Visual Studio Code y el tutorial de la extensión de SQL Server (mssql).

  1. Configure la entrada de datos de referencia de SQL.

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

  2. Seleccione el icono de SQL Server y haga clic en Agregar conexión.

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

  3. Rellene la información de conexión.

    The two boxes for database and server information are highlighted.

  4. Haga clic con el botón derecho en SQL de referencia y seleccione Ejecutar consulta.

    Execute Query is highlighted in the context menu.

  5. Elija la conexión.

    The dialog box says

  6. Revise y compruebe el resultado de la consulta.

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

Preguntas más frecuentes

¿Puedo incurrir en costos adicionales al usar la entrada de datos de referencia SQL en Azure Stream Analytics?

No se aplica ningún costo por unidad de streaming adicional en el trabajo de Stream Analytics. Sin embargo, el trabajo de Stream Analytics debe tener una cuenta de Azure Storage asociada. El trabajo de Stream Analytics consulta la base de datos SQL (durante el inicio de trabajo y el intervalo de actualización) para recuperar el conjunto de datos de referencia y almacena dicha instantánea en la cuenta de almacenamiento. El almacenamiento de estas instantáneas incurrirá en los cargos adicionales detallados en la página de precios de la cuenta de Azure Storage.

¿Cómo puedo saber si se realiza una consulta de la instantánea de los datos de referencia desde la base de datos SQL y si se usa en el trabajo de Azure Stream Analytics?

Hay dos métricas filtradas por nombre lógico (en las métricas de Azure Portal) que puede usar para supervisar el estado de la entrada de datos de referencia de SQL Database.

  • InputEvents: esta métrica mide el número de registros que se cargan desde el conjunto de datos de referencia de SQL Database.
  • InputEventBytes: esta métrica mide el tamaño de la instantánea de datos de referencia cargada en memoria del trabajo de Stream Analytics.

La combinación de ambas métricas se puede utilizar para deducir si el trabajo consulta SQL Database para capturar el conjunto de datos de referencia y si después lo carga en memoria.

¿Necesito un tipo especial de base de datos de Azure SQL?

Azure Stream Analytics funcionará con cualquier tipo de base de datos de Azure SQL. Sin embargo, es importante comprender que el conjunto de frecuencia de actualización de la entrada de datos de referencia podría afectar a la carga de la consulta. Para usar la opción de la consulta delta, se recomienda usar las tablas temporales de Azure SQL Database.

¿Por qué Azure Stream Analytics almacena las instantáneas en la cuenta de Azure Storage?

Stream Analytics garantiza un procesamiento de eventos y al menos una entrega de eventos. En los casos en los que los problemas transitorios afectan a su trabajo, se necesita una pequeña cantidad de reproducción para restaurar el estado. Para habilitar la reproducción, es necesario que estas instantáneas estén almacenadas en una cuenta de Azure Storage. Para más información sobre la reproducción del punto de comprobación, vea Conceptos de punto de control y reproducción en trabajos de Azure Stream Analytics.

Pasos siguientes