Usare dati di riferimento da un database SQL per un processo di Analisi di flusso di Azure

I dati di riferimento sono un insieme di dati statico o soggetto a variazioni lente che viene unito ai dati in streaming per arricchirli, ad esempio aggiungendo dettagli del prodotto a un flusso di eventi di vendita. Analisi di flusso di Azure supporta database SQL di Azure come fonte di dati di riferimento, così puoi consultare e combinare questi dati con il tuo input in tempo reale.

Questo articolo ti mostra come configurare un database SQL di Azure come input dati di riferimento per un lavoro di Stream Analytics, utilizzando sia il portale Azure che Visual Studio con strumenti di Stream Analytics.

Aggiungi i dati di riferimento del database SQL utilizzando il portale Azure

Utilizza i seguenti passaggi per aggiungere database SQL di Azure come sorgente di input di riferimento utilizzando il portale Azure:

Prerequisiti del portale

1. Creare un processo di Analisi di flusso.

2. Crea un account di archiviazione per il lavoro di Stream Analytics da utilizzare.

   Importante

   Analisi di flusso di Azure conserva istantanee all'interno di questo account di archiviazione. Quando configuri la politica di retention, assicurati che il periodo di tempo scelto includa la durata di recupero desiderata per il tuo lavoro di Stream Analytics.

3. Crea il tuo database SQL di Azure con un dataset che il lavoro di Stream Analytics utilizza come dati di riferimento.

Definire l'input dei dati di riferimento del database SQL

1. Nel tuo job di Analisi di flusso, seleziona Input in Topologia processo. Seleziona Aggiungi input di riferimento, poi seleziona Database SQL.

   

2. Compila la configurazione di input di Stream Analytics. Scegli il nome del database, il nome del server e le credenziali di accesso per il database. Per aggiornare periodicamente i dati di riferimento inseriti, seleziona On e specifica la frequenza di aggiornamento in DD:HH:MM. Per grandi dataset con frequenza di aggiornamento breve, la query delta traccia le modifiche all'interno dei dati di riferimento recuperando tutte le righe nel database SQL inserite o eliminate tra un orario di inizio, @deltaStartTime, e un orario di fine, @deltaEndTime.

   Per maggiori informazioni, vedi delta query.

   

3. Testare la query snapshot nell'editor di query SQL. Per ulteriori informazioni, consulta Usa l'editor di query SQL del portale Azure per connettere e interrogare i dati.

Specifica l'account di archiviazione nella configurazione del lavoro

Vai nelle impostazioni dell'account di archiviazione sotto Configura, poi seleziona Aggiungi account di archiviazione.

Avvia l'attività

1. Dopo aver configurato gli altri input, output e query, avvia il lavoro di Stream Analytics.

Aggiungi dati di riferimento del database SQL usando Visual Studio

Utilizza i seguenti passaggi per aggiungere database SQL di Azure come sorgente di input di riferimento utilizzando Visual Studio:

Prerequisiti di Visual Studio

1. Installare gli strumenti di Analisi di flusso per Visual Studio. Gli strumenti di Stream Analytics supportano le seguenti versioni di Visual Studio:

   • Visual Studio 2015
   • Visual Studio 2019

2. Familiarizza con la guida introduttiva di Stream Analytics Tools per Visual Studio.

3. Creare un account di archiviazione.

   Importante

   Analisi di flusso di Azure conserva istantanee all'interno di questo account di archiviazione. Quando configuri la politica di retention, assicurati che il periodo di tempo scelto includa la durata di recupero desiderata per il tuo lavoro di Stream Analytics.

Creare una tabella di database SQL

Usare SQL Server Management Studio per creare una tabella in cui archiviare i dati di riferimento. Per informazioni dettagliate, vedere Progettare la prima database SQL di Azure usando SSMS.

La seguente affermazione crea la tabella di esempio:

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

Scegliere la sottoscrizione in uso

1. In Visual Studio dal menu Visualizza scegliere Esplora server.

2. Seleziona e tieni premuto (o clicca destro) su Azure, seleziona Collegati a Microsoft Azure Subscription e accedi con il tuo account Azure.

Creare un progetto di Analisi di flusso

1. Selezionare File>Nuovo progetto.

2. Nella lista dei modelli, seleziona Stream Analytics e poi Analisi di flusso di Azure Application.

3. Inserisci il nome del progetto, la posizione e il nome della soluzione, poi seleziona OK.

   

Definire l'input dei dati di riferimento del database SQL

1. Crea un nuovo input.

   

2. Apri Input.json in Esplora soluzioni.

3. Compilare Stream Analytics Input Configuration. Inserisci il nome del database, il nome del server, il tipo di aggiornamento e la frequenza di aggiornamento. Specificare la frequenza di aggiornamento nel formato DD:HH:MM.

   

   Se scegli Esegui solo una volta o Esegui periodicamente, Visual Studio genera un file SQL CodeBehind chiamato [Input Alias].snapshot.sql nel progetto sotto il nodo Input.json file.

   

   Se scegli Aggiorna periodicamente con Delta, Visual Studio genera due file SQL CodeBehind: [Input Alias].snapshot.sql e [Input Alias].delta.sql.

   

4. Aprire il file SQL nell'editor e scrivere la query SQL.

5. Se stai usando Visual Studio 2019 e hai installato SQL Server Data Tools, puoi testare la query selezionando Esegui. Si apre un assistente per aiutarti a connetterti al database SQL, e il risultato della query appare nella finestra in basso.

Specificare l'account di archiviazione

Apri JobConfig.json per specificare l'account di archiviazione per memorizzare snapshot di riferimento SQL.

Testare in locale e distribuire in Azure

Prima di distribuire il lavoro su Azure, puoi testare localmente la logica di query contro dati di input in tempo reale. Per altre informazioni su questa funzionalità, consulta Testare i dati in tempo reale in locale con gli strumenti di Analisi di flusso di Azure per Visual Studio (Anteprima). Quando hai finito di testare, seleziona Invia ad Azure. Per imparare come iniziare il lavoro, consulta il lavoro Create a Stream Analytics utilizzando gli strumenti Analisi di flusso di Azure per Visual Studio quickstart.

Query delta

Quando usi la query delta, usa le tabelle temporali in database SQL di Azure.

1. Creare una tabella temporale nel database SQL di 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. Crea la query di snapshot.

   Usa il parametro @snapshotTime per istruire il runtime di Stream Analytics a recuperare il set di dati di riferimento dalla tabella temporale di SQL Database valida in corrispondenza dell'ora di sistema. Se non fornisci questo parametro, rischi di ottenere un dataset di riferimento base inaccurato a causa di sbalze di clock. L'esempio seguente mostra una query di snapshot completa:

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

3. Crea la query delta.

   Questa query recupera tutte le righe nel database SQL che sono state inserite o eliminate entro un orario di inizio, @deltaStartTime, e un orario di fine, @deltaEndTime. La query delta deve restituire le stesse colonne della query snapshot, nonché la colonna operation. Questa colonna definisce se la riga viene inserita o eliminata tra @deltaStartTime e @deltaEndTime. Le righe risultanti vengono contrassegnate con 1 se i record sono stati inseriti, con 2 se sono stati eliminati. La query deve anche aggiungere watermark sul lato SQL Server per assicurarsi che tutti gli aggiornamenti durante il periodo delta vengano acquisiti correttamente. L'uso di una query delta senza filigrana potrebbe portare a un dataset di riferimento errato.

   Per i record che sono stati aggiornati, la tabella temporale tiene traccia delle modifiche catturando un'operazione di inserimento e una di cancellazione. L'runtime di Stream Analytics applica quindi i risultati della query delta all'istantaneo precedente per mantenere aggiornati i dati di riferimento. Il seguente esempio mostra una query 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
   

   Il runtime di Stream Analytics potrebbe eseguire periodicamente la query di snapshot, oltre alla query delta, per memorizzare i checkpoint.

   Importante

   Quando si usano query delta per i dati di riferimento, non apportare aggiornamenti identici più volte alla tabella temporale dei dati di riferimento. Questo potrebbe produrre risultati errati. Ecco un esempio che potrebbe portare i dati di riferimento a produrre risultati errati:

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

   Esempio corretto:

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

   Questa condizione garantisce che non si verifichino aggiornamenti duplicati.

Verifica la query

Verifica che la tua query restituisca il dataset atteso che il lavoro di Stream Analytics utilizza come dati di riferimento. Per testare la tua query, vai su Input nella sezione Topologia del lavoro nel portale. Poi seleziona Dati di esempio nel tuo input di riferimento SQL Database. Dopo che il campione è disponibile, puoi scaricare il file e verificare se i dati restituiti sono come previsto. Per ottimizzare le tue iterazioni di sviluppo e test, usa gli strumenti Stream Analytics per Visual Studio. Puoi anche usare qualsiasi altro strumento tu preferisca per prima assicurarti che la query restituisca i risultati corretti dal tuo database SQL di Azure, e poi usare quella query nel tuo lavoro di Stream Analytics.

Testare la query con Visual Studio Code

Installare gli strumenti di Analisi di flusso di Azure e SQL Server (mssql) in Visual Studio Code e configurare il progetto ASA. Per ulteriori informazioni, vedere Avvio rapido: Creare un processo di Analisi di flusso di Azure in Visual Studio Code e l'esercitazione sull'estensione SQL Server (mssql).

1. Configura l'input di dati di riferimento SQL.

   

2. Seleziona l'icona di SQL Server e seleziona Aggiungi Connessione.

   

3. Immettere le informazioni di connessione.

   

4. Seleziona e tieni premuto (o clicca destro) su SQL di riferimento e seleziona Esegui Query.

   

5. Scegliere la connessione.

   

6. Esamini e verifichi il risultato della query.

   

Domande frequenti

Si incorre in costi aggiuntivi utilizzando l'input di dati di riferimento SQL in Analisi di flusso di Azure?

Non ci sono costi aggiuntivi per unità di streaming nel lavoro di Stream Analytics. Tuttavia, il processo di Analisi di flusso deve avere un account di archiviazione di Azure associato. Il processo di Stream Analytics interroga il Database SQL (all'avvio del processo e a ogni intervallo di aggiornamento) per recuperare il set di dati di riferimento e memorizza tale snapshot nell'account di archiviazione. Memorizzare questi snapshot comporta costi aggiuntivi dettagliati nella pagina dei prezzi per l'account di archiviazione Azure.

Come posso sapere se uno snapshot di dati di riferimento viene consultato da SQL Database e utilizzato nel lavoro di Analisi di flusso di Azure?

Due metriche, filtrate per Nome Logico (sotto Metriche nel portale Azure), ti permettono di monitorare lo stato dei dati di riferimento del database SQL.

• InputEvents: Questa metrica misura il numero di record caricati dal dataset di riferimento del database SQL.
• InputEventBytes: questa metrica misura le dimensioni dello snapshot dei dati di riferimento caricato in memoria del processo di Analisi di flusso.

Insieme, entrambe le metriche indicano se il lavoro interroga SQL Database per recuperare il dataset di riferimento e poi lo carica in memoria.

Ho bisogno di un tipo speciale di database SQL di Azure?

Analisi di flusso di Azure funziona con qualsiasi tipo di database SQL di Azure. Tuttavia, la frequenza di aggiornamento che imposti per il tuo input di riferimento potrebbe influenzare il carico delle query. Per utilizzare l'opzione di query delta, usa le tabelle temporali in database SQL di Azure.

Perché Analisi di flusso di Azure memorizza gli snapshot in un account di Archiviazione di Azure?

Stream Analytics garantisce l'elaborazione degli eventi una sola volta e il recapito degli eventi almeno una volta. Se problemi temporanei influenzano il tuo lavoro, è necessario un piccolo rigioco per ripristinare lo stato. Per abilitare la riproduzione, questi snapshot devono essere memorizzati in un account Archiviazione di Azure. Per altre informazioni sulla riproduzione dei checkpoint, vedi Concetti relativi ai checkpoint e alla riproduzione nei processi di Analisi di flusso di Azure.

Contenuti correlati

• Informazioni sugli output di Analisi di flusso di Azure
• Output di Analisi di flusso di Azure verso database SQL di Azure
• Aumentare le prestazioni in termini di velocità effettiva verso Database SQL di Azure da Analisi di flusso di Azure
• Usare le identità gestite per accedere al database SQL di Azure o ad Azure Synapse Analytics da un processo di Analisi di flusso di Azure
• Aggiornare o unire record in database SQL di Azure con Funzioni di Azure
• Guida introduttiva: Creare un processo di Azure Stream Analytics usando il portale di Azure