Usar datos de referencia de Azure SQL Database en un trabajo de Azure Stream Analytics

Los datos de referencia son conjuntos de datos estáticos o que cambian lentamente y que se complementan con tus datos de streaming para enriquecerlos, como añadir detalles de productos a una cadena de eventos de ventas. Azure Stream Analytics soporta Azure SQL Database como fuente de datos de referencia, para que puedas buscar y combinar estos datos con tu entrada en tiempo real.

Este artículo te muestra cómo configurar una Azure SQL Database como entrada de datos de referencia para un trabajo de Stream Analytics, utilizando tanto el portal de Azure como Visual Studio con herramientas de Stream Analytics.

Añadir datos de referencia de la base de datos SQL utilizando el portal de Azure

Utiliza los siguientes pasos para añadir Azure SQL Database como fuente de entrada de referencia utilizando el portal de Azure:

Requisitos previos del Portal

1. Cree un trabajo de Stream Analytics.

2. Crea una cuenta de almacenamiento para que el trabajo de Stream Analytics la utilice.

   Importante

   Azure Stream Analytics conserva instantáneas dentro de esta cuenta de almacenamiento. Cuando configures la política de retención, asegúrate de que el periodo de tiempo elegido incluya la duración de recuperación que deseas para tu trabajo de Stream Analytics.

3. Crea tu Azure SQL Database con un conjunto de datos que el trabajo de Stream Analytics use 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. Selecciona Añadir entrada de referencia y luego selecciona Base de datos SQL.

   

2. Rellena la configuración de entrada de Stream Analytics. Elige el nombre de la base de datos, el nombre del servidor y las credenciales de inicio de sesión para la base de datos. Para actualizar periódicamente la entrada de datos de referencia, selecciona Encendido y especifica la tasa de refresco en DD:HH:MM. Para conjuntos de datos grandes con una tasa de refresco corta, la consulta delta rastrea los cambios dentro de tus datos de referencia recuperando todas las filas en la base de datos SQL que se insertaron o eliminaron entre una hora de inicio, @deltaStartTime, y una hora de finalización, @deltaEndTime.

   Para obtener más información, consulte Consulta delta.

   

3. Pruebe la consulta de instantánea en el editor de consultas SQL. Para más información, consulte Utilizar el editor de consultas SQL del portal Azure para conectar y consultar datos.

Especifica la cuenta de almacenamiento en la configuración del trabajo

Ve a Configuración de cuentade almacenamiento en Configurar y luego selecciona Añadir cuenta de almacenamiento.

Inicio del trabajo

1. Después de configurar las otras entradas, salidas y consultas, inicia el trabajo de Análisis de Flujos.

Añadir datos de referencia de la base de datos SQL usando Visual Studio

Utiliza los siguientes pasos para añadir Azure SQL Database como fuente de entrada de referencia usando Visual Studio:

Requisitos previos de Visual Studio

1. Instale las herramientas de Stream Analytics para Visual Studio. Las herramientas de Análisis de Flujos soportan las siguientes versiones de Visual Studio:

   • Visual Studio 2015
   • Visual Studio 2019

2. Conozca el inicio rápido de las herramientas de Stream Analytics para Visual Studio.

3. Cree una cuenta de almacenamiento.

   Importante

   Azure Stream Analytics conserva instantáneas dentro de esta cuenta de almacenamiento. Cuando configures la política de retención, asegúrate de que el periodo de tiempo elegido incluya la duración de recuperación que deseas para tu 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 siguiente sentencia crea la tabla de ejemplo:

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. Selecciona y mantén pulsado (o haz clic derecho) en Azure, selecciona Conectarse a la suscripción de Microsoft Azure e inicia sesión con tu cuenta de Azure.

Crear un proyecto de Stream Analytics

1. Seleccione Archivo>Nuevo proyecto.

2. En la lista de plantillas, selecciona Stream Analytics y luego Azure Stream Analytics Application.

3. Introduce el nombre del proyecto, la ubicación y el nombre de la solución, y luego selecciona OK.

   

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

1. Cree una nueva entrada.

   

2. Abre Input.json en Explorador de soluciones.

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

   

   Si eliges Ejecutar solo una vez o Ejecutar periódicamente, Visual Studio genera un archivo SQL CodeBehind llamado [Input Alias].snapshot.sql en el proyecto bajo el nodo de archivoInput.json.

   

   Si eliges Actualizar periódicamente con Delta, Visual Studio genera dos archivos SQL CodeBehind: [Input Alias].snapshot.sql y [Input Alias].delta.sql.

   

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

5. Si usas Visual Studio 2019 y has instalado SQL Server Data Tools, puedes probar la consulta seleccionando Ejecutar. Se abre un asistente para ayudarte a conectarte a la base de datos SQL, y el resultado de la consulta aparece en la ventana inferior.

Definición de la cuenta de almacenamiento

Abre JobConfig.json para especificar la cuenta de almacenamiento que almacenará instantáneas de referencia SQL.

Prueba local e implementación en Azure

Antes de desplegar el trabajo en Azure, puedes probar la lógica de consulta localmente contra datos de entrada en vivo. Para más información sobre esta función, consulta Probar datos en vivo localmente usando las herramientas de Azure Stream Analytics para Visual Studio (Preview). Cuando termines de hacer pruebas, selecciona Enviar a Azure. Para saber cómo crear el trabajo, consulta 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.

Consulta delta

Cuando uses la consulta delta, utiliza tablas temporales en 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. Redacte la consulta de instantánea.

   Utiliza el parámetro @snapshotTime para instruir al entorno de ejecución de Stream Analytics que obtenga el conjunto de datos de referencia de la tabla temporal de la base de datos SQL válido en el momento del sistema. Si no proporcionas este parámetro, corres el riesgo de obtener un conjunto de datos de referencia base inexacto debido a desfases de reloj. El siguiente ejemplo muestra una consulta de instantánea completa:

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

3. Cree una consulta delta.

   Esta consulta recupera todas las filas en la base de datos SQL que fueron insertadas o eliminadas dentro de una hora de inicio, @deltaStartTime, y 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 watermark en el lado de SQL Server para asegurarse de que todas las actualizaciones del período delta se capturen correctamente. Usar una consulta delta sin marca de agua podría resultar en un conjunto de datos de referencia incorrecto.

   Para los registros que se actualizaron, la tabla temporal lleva un registro al capturar una operación de inserción y otra de eliminación. El entorno de ejecución de Stream Analytics aplica los resultados de la consulta delta a la instantánea anterior para mantener los datos de referencia actualizados. El siguiente ejemplo muestra una 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
   

   El runtime de Stream Analytics puede ejecutar periódicamente la consulta de instantáneas además de la consulta delta para almacenar puntos de control.

   Importante

   Cuando uses consultas delta de datos de referencia, no hagas actualizaciones idénticas a la tabla de datos de referencia temporal varias veces. Esto podría producir resultados incorrectos. Aquí tienes un ejemplo que podría hacer que los datos de referencia produzcan 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);
   

   Esta condición garantiza que no se produzcan actualizaciones duplicadas.

Pruebe su consulta

Verifica que tu consulta devuelva el conjunto de datos esperado que el trabajo de Stream Analytics utiliza como datos de referencia. Para probar tu consulta, ve a Entradas en la sección de Topología de Trabajos en el portal. Luego selecciona Datos de muestra en tu entrada de referencia de la base de datos SQL. Cuando la muestra esté disponible, puedes descargar el archivo y comprobar si los datos devueltos son los esperados. Para optimizar tus iteraciones de desarrollo y prueba, utiliza las herramientas de Stream Analytics para Visual Studio. También puedes usar cualquier otra herramienta que prefieras para asegurarte primero de que la consulta devuelva los resultados correctos de tu Azure SQL Database, y luego usar esa consulta en tu 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.

   

2. Selecciona el icono de SQL Server y selecciona Añadir conexión.

   

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

   

4. Mantén presionado el SQL de referencia (o haz clic con el botón derecho en él) y selecciona Ejecutar consulta.

   

5. Elija la conexión.

   

6. Revise y compruebe el resultado de la consulta.

   

Preguntas más frecuentes

¿Tengo que asumir costes adicionales al usar datos de referencia SQL introducidos en Azure Stream Analytics?

No hay coste extra por unidad de streaming 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 del trabajo y el intervalo de actualización) para recuperar el conjunto de datos de referencia y almacena esa instantánea en la cuenta de almacenamiento. Almacenar estas instantáneas implica cargos adicionales detallados en la página de precios para la cuenta de almacenamiento de Azure.

¿Cómo sé si se está consultando una instantánea de datos de referencia desde SQL Database y se utiliza en el trabajo de Azure Stream Analytics?

Dos métricas, filtradas por Nombre Lógico (en Métricas en el portal de Azure), te permiten monitorizar el estado de la entrada de datos de referencia de la base de datos SQL.

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

En conjunto, ambas métricas indican si el trabajo consulta SQL Database para obtener el conjunto de datos de referencia y luego lo carga en la memoria.

¿Necesito un tipo especial de Azure SQL Database?

Azure Stream Analytics funciona con cualquier tipo de Azure SQL Database. Sin embargo, la tasa de refresco que configures para la entrada de datos de referencia puede afectar a la carga de consultas. Para usar la opción de consulta delta, utiliza tablas temporales en Azure SQL Database.

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

Stream Analytics garantiza el procesamiento de eventos exactamente una vez y la entrega de eventos al menos una vez. Si los problemas transitorios afectan a tu tarea, será necesario volver a ejecutar una pequeña parte para restablecer el estado. Para permitir la reproducción, estas instantáneas deben almacenarse en una cuenta de Azure Storage. Para obtener más información sobre la reproducción de puntos de control, consulta Conceptos de puntos de control y reproducción en trabajos de Azure Stream Analytics.

Contenido relacionado

• Comprender las salidas de Azure Stream Analytics
• Salida de Azure Stream Analytics a Azure SQL Database
• Incremento del rendimiento de Azure SQL Database desde Azure Stream Analytics
• Uso de identidades administradas para acceder a Azure SQL Database o Azure Synapse Analytics desde un trabajo de Azure Stream Analytics
• Actualización o combinación de registros en Azure SQL Database con Azure Functions
• Inicio rápido: Creación de un trabajo de Stream Analytics mediante Azure Portal