Vue indexée pour l’agrégation des métriques

Microsoft Azure SQL est une plateforme de base de données robuste entièrement gérée, conçue pour des requêtes à haute performance, le stockage de données relationnelles et l’analyse. Pour une application web typique avec un backend, c’est un bon choix lorsque nous voulons envisager une base de données gérée qui peut évoluer à la fois verticalement et horizontalement.

Un logiciel d’application génère des métriques utilisateur sur une base quotidienne, qui peuvent être utilisées pour des rapports ou des analyses. Azure SQL est un excellent choix à considérer pour stocker et interroger ces données dans certaines conditions :

1. Les requêtes analytiques nécessitent des jointures avec d’autres tables (application de filtres sur l’interface utilisateur)
2. Vous souhaitez combiner des données historiques et transactionnelles
3. Le volume de données n’est pas extrêmement important, et les performances des requêtes peuvent être gérées en les ajustant

Prenons l’exemple d’un site de réservation d’hôtel utilisant Azure SQL en backend. Nous voulons voir un tableau de bord d’interface utilisateur pour suivre l’activité des utilisateurs, tels que les clics sur le site, les visites de la page de description de l’hôtel, les réservations effectuées, etc.

Supposons que toutes ces données de télémétrie sont stockées pour chaque utilisateur sur une base quotidienne dans un stockage non structuré, et que nous extrayons ces données dans notre base de données à l’aide de tâches en arrière-plan, telles que Apache Airflow.

Voici le schéma de la table des utilisateurs et d’une table pour stocker les métriques quotidiennes.

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

);

​

Vous pouvez tirer de nombreuses informations des tables ci-dessus.

Considérons un exemple particulier. Nous devons regrouper les métriques d’activité quotidienne par date dans l’ordre décroissant pour les clients âgés de 30 à 40 ans situés à New York. Voici la requête :

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

Maintenant, nous pouvons analyser les tendances quotidiennes de ce groupe d’utilisateurs à New York et âgés de 30 à 40 ans. La table est principalement performante, et nous pouvons facilement effectuer des requêtes de plage étalées sur plusieurs mois. Finalement, nos besoins évoluent. Nous voulons maintenant suivre le comportement des utilisateurs sur une plage hebdomadaire ou mensuelle. Mais notre table stocke les données sur une base quotidienne. Nous avons maintenant deux options :

• Interroger la table et regrouper la colonne date_created de manière hebdomadaire ou mensuelle, en fonction de la demande.
• Créer quelques vues qui agrègent les données de manière hebdomadaire ou mensuelle par utilisateur. Voir la requête ci-dessous :

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

Cependant, une chose importante à considérer est que les vues fournissent simplement une abstraction à la requête sous-jacente qui interroge simplement la table sous-jacente. Les Vues Matérialisées sont la prochaine idée qui vient à l’esprit. Cependant, elles doivent être actualisées manuellement ou selon un calendrier, ce qui fait que les données en temps réel ne sont pas disponibles.

Pour résoudre ces problèmes, Azure SQL Server propose une excellente fonctionnalité appelée Vue Indexée. Une Vue Indexée est une représentation physique d’une vue stockée dans la base de données avec un index cluster unique. Les modifications apportées aux tables sous-jacentes mettent automatiquement à jour la vue indexée pour la maintenir synchronisée. Elle utilise un index clusterisé qui organise les données dans la vue en fonction de l’ordre des clés d’index.

La vue indexée est idéale pour les scénarios où nous avons besoin de données en temps réel, et notre requête implique des jointures complexes entre plusieurs tables. Elle est également adaptée à notre cas d’utilisation où les données existantes sont rarement mises à jour mais fréquemment interrogées, et nous avons des requêtes basées sur des plages et souhaitons effectuer une récupération ordonnée.

Il y a quelques éléments à prendre en compte avant de décider si vous voulez opter pour les vues indexées. Les vues indexées ne peuvent pas contenir de fonctions non déterministes. Une fonction non–déterministe est une fonction qui ne renvoie pas toujours le même résultat pour la même entrée, même lorsqu’elle est exécutée avec des arguments identiques et dans les mêmes conditions de base de données. De plus, une vue indexée est une structure réelle qui nécessite du stockage, similaire à une Vue Matérialisée.

La syntaxe pour créer une Vue Indexée est similaire à la requête de création de Vue ci-dessus. Cependant, nous ne pouvons pas avoir de fonctions non déterministes lors de la création d’une vue indexée. La ligne DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start dans la requête de vue ci-dessus dépend du paramètre de session spécifique SET DATEFIRST, qui détermine le premier jour de la semaine. Ceci est considéré comme non déterministe car il produira des résultats différents pour différentes conditions.

En gardant à l’esprit les éléments ci-dessus, nous pouvons procéder à l’élimination du calcul non déterministe en rendant la colonne déterministe. Nous ajoutons une colonne week_start à la table sous-jacente et précalculons et remplissons la valeur week_start dans la table pour les extractions de données quotidiennes. Ainsi, les lignes avec les dates D1 à D7 appartiennent à la semaine W1, D8 à D14 appartiennent à W2, et ainsi de suite.

Maintenant, nous pouvons procéder à la création d’une vue indexée avec le code SQL ci-dessous.

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);    

Après la création de cette vue indexée, nous pouvons la consulter comme suit :

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

Conclusion

Une vue indexée aura un temps de requête considérablement plus rapide qu’une vue de base ou interroger directement la table pour des ensembles de données volumineux où les données ont atteint un million de lignes ou plus. L’utilisateur final bénéficiera d’une expérience à faible latence, et les requêtes à la base de données seront optimisées. Étant donné que nous avons agrégé une semaine de données en 1 ligne, nous avons réduit le temps d’agrégation à 1/7.