Gegroepeerde weergave voor het samenvatten van metingen

Microsoft Azure SQL is een robuust, volledig beheerd databaseplatform dat is ontworpen voor high-performance querying, relationele dataopslag en analytics. Voor een typische webtoepassing met een backend is het een goede keuze wanneer we een beheerd database willen overwegen dat zowel verticaal als horizontaal kan schalen.

Een toepassingssoftware genereert dagelijks gebruikersstatistieken, die kunnen worden gebruikt voor rapporten of analytics. Azure SQL is een uitstekende keuze om te overwegen voor het opslaan en queryen van deze gegevens onder bepaalde omstandigheden:

1. De analytische queries vereisen joins met andere tabellen (toepassen van filters op de UI)
2. U wilt historische en transactionele gegevens combineren
3. De datavolume is niet extreem groot en queryprestaties kunnen worden beheerd door afstemming

Laten we een voorbeeld bekijken van een hotelboekingssite die Azure SQL in de backend uitvoert. We willen een UI-dashboard zien voor het volgen van gebruikersactiviteiten, zoals klikken op de site, bezoeken aan de hotelpagina, gemaakte boekingen, enz.

Laten we aannemen dat al deze telemetriegegevens voor elke gebruiker op een dagelijkse basis in ongestructureerde opslag worden gedumpt, en we halen deze gegevens in onze database met behulp van achtergrondtaken, zoals Apache Airflow.

Hieronder staat het schema voor de gebruikerstabel en een tabel om dagelijkse statistieken op te slaan.

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

);

​

U kunt veel inzichten halen uit de bovenstaande tabellen.

Laten we één specifiek voorbeeld bekijken. We moeten dagelijkse activiteitsstatistieken groeperen op datum in aflopende volgorde voor klanten tussen de 30 en 40 jaar oud in New York City. Hieronder staat de 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

Nu kunnen we de dagelijkse trends analyseren van deze groep gebruikers die zich in New York bevinden en tussen de 30 en 40 jaar oud zijn. De tabel presteert over het algemeen goed en we kunnen gemakkelijk bereikquery’s uitvoeren die zich over meerdere maanden uitstrekken. Uiteindelijk groeien onze eisen. We willen nu het gebruikersgedrag volgen in een wekelijks of maandelijks bereik. Maar onze tabel slaat de gegevens dagelijks op. We hebben nu twee opties:

• De tabel bevragen en de date_created wekelijks of maandelijks groeperen, afhankelijk van de vraag.
•  Een paar weergaven maken die de gegevens op een wekelijkse of maandelijkse basis per gebruiker groeperen. Zie de query hieronder:

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

Maar een belangrijk punt om te overwegen is dat weergaven slechts een abstractie bieden van de onderliggende query die eenvoudigweg de onderliggende tabel bevraagt. Gematerialiseerde weergaven zijn de volgende gedachte die in je opkomt. Echter, deze moeten handmatig vernieuwd worden of op een schema, waardoor real-time gegevens niet beschikbaar zijn.

Om deze problemen aan te pakken, biedt Azure SQL Server een geweldige functie die bekend staat als Geïndexeerd Weergave. Een Geïndexeerd Weergave is een fysieke representatie van een weergave die in de database is opgeslagen met een unieke geclusterd index. Wijzigingen in de onderliggende tabellen werken automatisch de geïndexeerde weergave bij om deze synchroon te houden. Het gebruikt een geclusterd index die de gegevens in de weergave organiseert op basis van de volgorde van de indexsleutels.

De geïndexeerde weergave is ideaal voor scenario’s waar we real-time gegevens nodig hebben en onze query complexe multi-tabel joins omvat. Het is ook geschikt voor onze use case waar bestaande gegevens zelden worden bijgewerkt maar vaak worden opgevraagd, en we hebben op bereik gebaseerde queries en willen geordende opvraging doen.

Er zijn enkele dingen om te overwegen voordat je beslist of je voor geïndexeerde weergaven wilt gaan. Geïndexeerde weergaven kunnen geen niet-deterministische functies hebben. Een niet–deterministische functie is een functie die niet altijd hetzelfde resultaat retourneert voor dezelfde invoer, zelfs niet wanneer deze wordt uitgevoerd met identieke argumenten en onder dezelfde database-omstandigheden. Bovendien is een geïndexeerde weergave een echte structuur die opslag vereist, vergelijkbaar met een Gematerialiseerde Weergave.

De syntaxis voor het maken van een Geïndexeerde Weergave is vergelijkbaar met de weergave-creatiequery hierboven. We kunnen echter geen niet-deterministische functies hebben bij het maken van een geïndexeerde weergave. De regel DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start in de weergave-query hierboven hangt af van de sessiespecifieke SET DATEFIRST instelling, die de eerste dag van de week bepaalt. Dit wordt als niet-deterministisch beschouwd, omdat het verschillende resultaten zal opleveren voor verschillende voorwaarden.

Met de bovenstaande zaken in gedachten kunnen we verder gaan met het elimineren van de niet-deterministische berekening door de kolom deterministisch te maken. We voegen een week_start kolom toe aan de onderliggende tabel en berekenen en vullen de week_start waarde in de tabel voor dagelijkse gegevensopvragingen. Dus, de rijen met datums D1 tot en met D7 behoren tot W1, D8 tot D14 behoren tot W2, enzovoort. 

Nu kunnen we doorgaan met het maken van een geïndexeerde weergave met de volgende SQL-code.

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

Na het maken van deze geïndexeerde weergave kunnen we deze als volgt opvragen:

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

Conclusie

Een geïndexeerde weergave zal aanzienlijk snellere querytijden hebben dan een basisweergave of het rechtstreeks opvragen van de tabel voor grote datasets waar de gegevens zijn gegroeid tot een miljoen rijen of meer. De eindgebruiker zal een lage latentie-ervaring hebben en de query’s naar de database zullen geoptimaliseerd zijn. Aangezien we een week aan gegevens in 1 rij hebben geaggregeerd, hebben we de aggregatietijd teruggebracht tot 1/7e.