Indizierte Ansicht zur Aggregation von Metriken

Microsoft Azure SQL ist eine robuste, vollständig verwaltete Datenbankplattform, die für leistungsstarke Abfragen, relationale Datenspeicherung und Analysen konzipiert ist. Für eine typische Webanwendung mit einem Backend ist es eine gute Wahl, wenn wir eine verwaltete Datenbank in Betracht ziehen möchten, die sowohl vertikal als auch horizontal skalieren kann.

Eine Anwendungssoftware generiert täglich Benutzermetriken, die für Berichte oder Analysen verwendet werden können. Azure SQL ist eine großartige Wahl für die Speicherung und Abfrage dieser Daten unter bestimmten Bedingungen:

1. Die analytischen Abfragen erfordern Joins mit anderen Tabellen (Anwendung von Filtern in der Benutzeroberfläche)
2. Sie möchten historische und transaktionale Daten kombinieren
3. Das Datenvolumen ist nicht extrem groß, und die Abfrageleistung kann durch Tuning gesteuert werden

Lassen Sie uns ein Beispiel für eine Hotelbuchungswebsite betrachten, die Azure SQL im Backend ausführt. Wir möchten ein UI-Dashboard zur Verfolgung der Benutzeraktivität sehen, wie Klicks auf der Website, Besuche auf der Hotelseitenbeschreibung, Buchungen usw.

Annehmen, dass all diese Telemetriedaten für jeden Benutzer auf einer täglichen Basis in unstrukturiertem Speicher abgelegt werden und wir diese Daten mithilfe von Hintergrundjobs wie Apache Airflow in unsere Datenbank ziehen.

Im Folgenden finden Sie das Schema für die Benutzertabelle und eine Tabelle zur Speicherung täglicher Metriken.

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

);

​

Aus den obigen Tabellen können Sie viele Erkenntnisse ziehen.

Lassen Sie uns ein spezielles Beispiel betrachten. Wir müssen tägliche Aktivitätsmetriken nach Datum absteigend für Kunden zwischen 30 und 40 Jahren in New York City gruppieren. Hier ist die Abfrage:

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

Jetzt können wir die täglichen Trends dieser Benutzergruppe analysieren, die sich in New York befinden und zwischen 30 und 40 Jahre alt sind. Die Tabelle ist größtenteils leistungsstark, und wir können problemlos Bereichsabfragen über mehrere Monate hinweg durchführen. Schließlich wachsen unsere Anforderungen. Wir möchten das Benutzerverhalten jetzt in einem wöchentlichen oder monatlichen Zeitraum verfolgen. Aber unsere Tabelle speichert die Daten auf täglicher Basis. Wir haben jetzt zwei Optionen:

• Die Tabelle abfragen und das Feld date_created wöchentlich oder monatlich gruppieren, je nach Bedarf.
•  Ein paar Ansichten erstellen, die die Daten wöchentlich oder monatlich pro Benutzer aggregieren. Sehen Sie sich die Abfrage unten an:

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

Ein wichtiger Punkt zu beachten ist jedoch, dass Ansichten nur eine Abstraktion zur zugrunde liegenden Abfrage bieten, die einfach die zugrunde liegende Tabelle abfragt. Materialisierte Ansichten sind der nächste Gedanke, der einem in den Sinn kommt. Sie müssen jedoch manuell oder nach einem Zeitplan aktualisiert werden, wodurch Echtzeitdaten nicht verfügbar sind.

Um diese Probleme anzugehen, bietet Azure SQL Server eine großartige Funktion namens Indexed View. Eine Indexed View ist eine physische Darstellung einer Ansicht, die mit einem eindeutigen gruppierten Index in der Datenbank gespeichert ist. Änderungen an den zugrunde liegenden Tabellen aktualisieren die Indexed View automatisch, um sie synchron zu halten. Sie verwendet einen gruppierten Index, der die Daten in der Ansicht basierend auf der Reihenfolge der Indexschlüssel organisiert.

Die Indexed View ist ideal für Szenarien, in denen Echtzeitdaten benötigt werden und unsere Abfrage komplexe Mehrfachtabellen-Joins umfasst. Sie ist auch für unseren Anwendungsfall geeignet, wenn vorhandene Daten selten aktualisiert, aber oft abgefragt werden und wir Bereichsabfragen haben und eine geordnete Abfrage durchführen möchten.

Es gibt einige Dinge zu berücksichtigen, bevor Sie sich entscheiden, ob Sie Indexed Views verwenden möchten. Indexed Views können keine nichtdeterministischen Funktionen haben. Eine nicht–deterministische Funktion ist eine Funktion, die nicht immer das gleiche Ergebnis für dieselbe Eingabe liefert, auch wenn sie mit identischen Argumenten und unter denselben Datenbankbedingungen ausgeführt wird. Außerdem handelt es sich bei einer Indexed View um eine tatsächliche Struktur, die Speicherplatz benötigt, ähnlich wie bei einer Materialized View.

Die Syntax zur Erstellung einer Indexed View ist ähnlich wie die Abfrage zur Erstellung einer View oben. Wir können jedoch keine nichtdeterministischen Funktionen haben, während wir eine Indexed View erstellen. Die Zeile DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start in der obigen View-Abfrage hängt von der sitzungsspezifischen Einstellung SET DATEFIRST ab, die den ersten Tag der Woche bestimmt. Dies wird als nichtdeterministisch betrachtet, da es für unterschiedliche Bedingungen unterschiedliche Ergebnisse liefert.

Bei der Umsetzung der obigen Punkte können wir fortfahren, indem wir die nichtdeterministische Berechnung eliminieren und die Spalte deterministisch machen. Wir fügen eine week_start-Spalte zur zugrunde liegenden Tabelle hinzu und berechnen und füllen den Wert week_start in der Tabelle für tägliche Datenabrufe vor. Somit gehören die Zeilen mit den Daten D1 bis D7 zu W1, die Daten D8 bis D14 zu W2 und so weiter.

Jetzt können wir fortfahren und eine indizierte Ansicht mit dem folgenden SQL erstellen.

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

Nachdem diese indizierte Ansicht erstellt wurde, können wir sie wie folgt abfragen:

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

Schlussfolgerung

Eine indizierte Ansicht wird bei der Abfrage von großen Datensätzen, bei denen die Daten auf eine Million Zeilen oder mehr angewachsen sind, eine deutlich schnellere Abfragezeit haben als eine einfache Ansicht oder die direkte Abfrage der Tabelle. Der Endbenutzer wird ein geringes Latenzerlebnis haben, und Abfragen an die Datenbank werden optimiert. Da wir eine Woche Daten in 1 Zeile aggregiert haben, haben wir die für die Aggregation benötigte Zeit um 1/7 reduziert.