Vista Indicizzata per l’Aggregazione delle Metriche

Microsoft Azure SQL è una piattaforma database robusta e completamente gestita, progettata per interrogazioni ad alte prestazioni, archiviazione di dati relazionali e analisi. Per un’applicazione web tipica con un backend, è una buona scelta quando vogliamo considerare un database gestito che può scalare sia verticalmente che orizzontalmente.

Un software applicativo genera metriche utente su base giornaliera, che possono essere utilizzate per report o analisi. Azure SQL è una scelta eccellente da considerare per memorizzare e interrogare questi dati a determinate condizioni:

1. Le query analitiche richiedono join con altre tabelle (applicando filtri sull’interfaccia utente)
2. Vuoi combinare dati storici e transazionali
3. Il volume dei dati non è estremamente grande e le prestazioni delle query possono essere gestite tramite ottimizzazione

Consideriamo un esempio di un sito di prenotazione hotel che utilizza Azure SQL nel backend. Vogliamo vedere un dashboard dell’interfaccia utente per monitorare l’attività degli utenti, come i clic sul sito, le visite alla pagina di descrizione dell’hotel, le prenotazioni effettuate, ecc.

Supponiamo che tutti questi dati di telemetria siano raccolti per ogni utente su una base giornalierain uno storage non strutturato, e stiamo estraendo questi dati nel nostro database utilizzando lavori in background, come Apache Airflow.

Di seguito è riportato lo schema per la tabella utenti e una tabella per memorizzare le metriche giornaliere.

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(100) NOT NULL,       

    last_name VARCHAR(100) NOT NULL,        

    email VARCHAR(255) UNIQUE NOT NULL,     

    password_hash VARCHAR(255) NOT NULL, 

    age INT ,               

    city VARCHAR(100),                      

    country VARCHAR(100),                  

    currency VARCHAR(10),   

    last_login VARCHAR,

    hotel_preference VARCHAR(100)

);

​

CREATE TABLE daily_user_metrics (

    id BIGINT IDENTITY PRIMARY KEY,       -- Unique identifier for the record

    user_id BIGINT NOT NULL,              -- Foreign key to the users table

    clicks INT DEFAULT 0,                 -- Number of total site clicks

    visits INT DEFAULT 0,                 -- Number of visits to the hotel page

    bookings INT DEFAULT 0,               -- Number of bookings

    reviews INT DEFAULT 0,            -- Number of reviews

    cancellations INT DEFAULT 0,          -- Number of cancellations

​

    date_created DATE,            -- Daily metrics for each user

);

​

Puoi trarre molte informazioni utili dalle tabelle sopra.

Consideriamo un esempio specifico. Abbiamo bisogno di aggregare le metriche di attività giornaliera raggruppate per data in ordine decrescente per i clienti tra i 30 e i 40 anni situati a New York City. Di seguito è riportata la query:

SELECT

    date_created,

    SUM(clicks) AS total_clicks,

    SUM(visits) AS total_visits,

    SUM(bookings) AS total_bookings,

    SUM(reviews) AS total_reviews,

    SUM(cancellations) AS total_cancellations,

FROM

    daily_user_metrics m

INNER JOIN users u on m.user_id = u.id

WHERE

   u.age BETWEEN 30 and 40

   and u.city ='New York'   

   and m.date_created BETWEEN :startDate and :endDate

GROUP BY

    date_created 

ORDER BY

    date_created DESC

Ora possiamo analizzare le tendenze giornaliere di questo gruppo di utenti che si trovano a New York e hanno tra i 30 e i 40 anni. La tabella è per lo più performante e siamo in grado di eseguire facilmente query di intervallo distribuite su più mesi. Con il tempo, i nostri requisiti crescono. Ora vogliamo tracciare il comportamento dell’utente in un intervallo settimanale o mensile. Ma la nostra tabella memorizza i dati su base giornaliera. Ora abbiamo due opzioni:

• Eseguire la query sulla tabella e raggruppare la date_created settimanalmente o mensilmente, a seconda della richiesta.
•  Creare un paio di viste che aggregano i dati su base settimanale o mensile per utente. Vedere la query qui sotto:

CREATE VIEW weekly_user_metrics AS

SELECT

    DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start, -- Start of the week (Sunday)

    SUM(clicks) AS total_clicks,

    SUM(visits) AS total_visits,

    SUM(bookings) AS total_bookings,

    SUM(reviews) AS total_reviews,

    SUM(cancellations) AS total_cancellations,

FROM

    daily_user_metrics m

INNER JOIN users u on m.user_id = u.id

WHERE

   u.age BETWEEN 30 and 40

   and u.city ='New York'   

   and m.date_created BETWEEN :startDate and :endDate    

GROUP BY

    DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) -- Group by week start

ORDER BY

    DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) DESC -- Sort by latest week

Tuttavia, una cosa importante da considerare è che le viste forniscono solo un’astrazione della query di base che interroga semplicemente la tabella sottostante. Le viste materializzate sono il prossimo pensiero che viene in mente. Tuttavia, devono essere aggiornate manualmente o su base programmata, a causa della quale i dati in tempo reale non sono disponibili.

Per affrontare queste problematiche, Azure SQL Server offre una grande funzionalità nota come Vista Indicizzata. Una Vista Indicizzata è una rappresentazione fisica di una vista memorizzata nel database con un indice cluster unico. Le modifiche alle tabelle sottostanti aggiornano automaticamente la vista indicizzata per mantenerla sincronizzata. Utilizza un indice cluster che organizza i dati nella vista in base all’ordine delle chiavi dell’indice.

La vista indicizzata è ideale per scenari in cui abbiamo bisogno di dati in tempo reale e la nostra query coinvolge join complessi tra più tabelle. È anche adatta per il nostro caso d’uso in cui i dati esistenti vengono raramente aggiornati ma interrogati spesso, e abbiamo query basate su intervallo e vogliamo effettuare un recupero ordinato.

C’è da considerare alcune cose prima di decidere se optare per le viste indicizzate. Le viste indicizzate non possono avere funzioni non deterministiche. Una non–deterministica è una funzione che non restituisce sempre lo stesso risultato per lo stesso input, anche quando eseguita con argomenti identici e nelle stesse condizioni di database. Inoltre, una vista indicizzata è una struttura reale che richiede spazio di archiviazione, simile a una Vista Materializzata.

La sintassi per creare una Vista Indicizzata è simile alla query di creazione della Vista sopra. Tuttavia, non possiamo avere funzioni non deterministiche durante la creazione di una vista indicizzata. La riga DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start nella query della vista sopra dipende dall’impostazione specifica della sessione SET DATEFIRST, che determina il primo giorno della settimana. Questo è considerato non deterministico poiché produrrà risultati diversi in base a diverse condizioni.

Tenendo presente quanto detto sopra, possiamo procedere all’eliminazione del calcolo non deterministico rendendo la colonna deterministica. Aggiungiamo una colonna week_start alla tabella sottostante e precomputiamo e riempiamo il valore week_start nella tabella per i recupero dati giornalieri. Quindi, le righe con date D1 a D7 appartengono a W1, le date D8 a D14 appartengono a W2, e così via.

Ora, possiamo procedere a creare una vista indicizzata con il seguente SQL.

ALTER TABLE daily_user_metrics ADD week_start DATE;-- Populate this column with first day of the week going forward

​

CREATE VIEW dbo.weekly_user_metric_aggregations_view

WITH SCHEMABINDING

AS

SELECT

    user_id,

    week_start,

    SUM(clicks) AS total_clicks,

    SUM(visits) AS total_visits,

    SUM(bookings) AS total_bookings,

    SUM(reviews) AS total_reviews,

    SUM(cancellations) AS total_cancellations,

    COUNT_BIG(*) AS row_count --SQL Server requires COUNT_BIG(*) in indexed views to handle scenarios where the count exceeds the range of an INT data type.

FROM

    dbo.daily_user_metrics

GROUP BY

    user_id,

    week_start;

CREATE UNIQUE CLUSTERED INDEX IX_weekly_user_metric_aggregations_view

ON dbo.weekly_user_metric_aggregations_view (user_id, week_start);    

Dopo la creazione di questa vista indicizzata, possiamo interrogarla come segue:

SELECT

    week_start,

    SUM(total_clicks) AS total_clicks,

    SUM(total_visits) AS total_visits,

    SUM(total_bookings) AS total_bookings,

    SUM(total_reviews) AS total_reviews,

    SUM(total_cancellations) AS total_cancellations,

FROM

    weekly_user_metric_aggregations_view mv

INNER JOIN users u on mv.user_id = u.id

WHERE

   u.age BETWEEN 30 and 40

   and u.city ='New York'   

   and m.date_created BETWEEN :startDate and :endDate

GROUP BY

    week_created 

ORDER BY

    week_created DESC

Conclusione

Una vista indicizzata avrà un tempo di interrogazione significativamente più veloce rispetto a una vista di base o all’interrogazione diretta della tabella per grandi set di dati in cui i dati sono cresciuti fino a un milione di righe o più. L’utente finale avrà un’esperienza a bassa latenza e le interrogazioni al database saranno ottimizzate. Poiché abbiamo aggregato una settimana di dati in 1 riga, abbiamo ridotto il tempo di aggregazione di 1/7.