Visualização Indexada para Agregar Métricas

O Microsoft Azure SQL é uma plataforma de banco de dados robusta e totalmente gerenciada, projetada para consultas de alto desempenho, armazenamento de dados relacionais e análises. Para uma aplicação web típica com um backend, é uma boa escolha quando queremos considerar um banco de dados gerenciado que pode escalar tanto verticalmente quanto horizontalmente.

Um software de aplicativo gera métricas de usuário diariamente, que podem ser usadas para relatórios ou análises. O Azure SQL é uma ótima escolha a ser considerada para armazenar e consultar esses dados sob certas condições:

1. As consultas analíticas requerem junções com outras tabelas (aplicando filtros na IU)
2. Você deseja combinar dados históricos e transacionais
3. O volume de dados não é extremamente grande, e o desempenho da consulta pode ser gerenciado por ajustes

Vamos considerar um exemplo de um site de reserva de hotel executando o Azure SQL no backend. Queremos ver um painel de IU para rastrear a atividade do usuário, como cliques no site, visitas à página de descrição do hotel, reservas feitas, etc.

Vamos supor que todos esses dados de telemetria são despejados para cada usuário em uma base diária em armazenamento não estruturado, e estamos puxando esses dados para nosso banco de dados usando trabalhos em segundo plano, como Apache Airflow.

Abaixo está o esquema para a tabela de usuários e uma tabela para armazenar métricas diárias.

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

);

​

Você pode obter muitos insights a partir das tabelas acima.

Vamos considerar um exemplo específico. Precisamos agregar métricas de atividade diárias agrupadas por data em ordem decrescente para clientes com idades entre 30 e 40 anos localizados na cidade de Nova York. Abaixo está a consulta:

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

Agora, podemos analisar as tendências diárias deste grupo de usuários que estão em Nova York e têm idades entre 30 e 40 anos. A tabela é em sua maioria performática e somos capazes de realizar facilmente consultas de intervalo espalhadas por vários meses. Eventualmente, nossos requisitos crescem. Agora queremos rastrear o comportamento do usuário em um intervalo semanal ou mensal. Mas nossa tabela armazena os dados diariamente. Agora temos duas opções:

• Consultar a tabela e agrupar a date_created semanalmente ou mensalmente, dependendo da solicitação.
• Criar um par de visualizações que agreguem os dados em uma base semanal ou mensal por usuário. Veja a consulta abaixo:

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

No entanto, uma coisa importante a considerar é que as visualizações apenas fornecem uma abstração para a consulta subjacente que simplesmente consulta a tabela subjacente. Visualizações Materializadas são o próximo pensamento que vem à mente. No entanto, elas precisam ser atualizadas manualmente ou em um cronograma, devido ao qual os dados em tempo real não estão disponíveis.

Para lidar com essas questões, o Azure SQL Server oferece um ótimo recurso conhecido como Visualização Indexada. Uma Visualização Indexada é uma representação física de uma visualização armazenada no banco de dados com um índice clusterizado único. Alterações nas tabelas subjacentes atualizam automaticamente a visualização indexada para mantê-la sincronizada. Ela utiliza um índice clusterizado que organiza os dados na visualização com base na ordem das chaves do índice.

A visualização indexada é ideal para cenários em que precisamos de dados em tempo real e nossa consulta envolve junções complexas de várias tabelas. Também é adequada para nosso caso de uso em que os dados existentes raramente são atualizados, mas consultados com frequência, e temos consultas baseadas em intervalos e desejamos uma recuperação ordenada.

Há algumas coisas a se considerar antes de decidir se deseja usar visualizações indexadas. As visualizações indexadas não podem ter funções não determinísticas. Uma função não–determinística é uma função que não retorna sempre o mesmo resultado para a mesma entrada, mesmo quando executada com argumentos idênticos e sob as mesmas condições do banco de dados. Além disso, uma visualização indexada é uma estrutura real que requer armazenamento, semelhante a uma Visualização Materializada.

A sintaxe para criar uma Visualização Indexada é semelhante à consulta de criação de Visualização acima. No entanto, não podemos ter funções não determinísticas ao criar uma visualização indexada. A linha DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start na consulta de visualização acima depende da configuração SET DATEFIRST específica da sessão, que determina o primeiro dia da semana. Isso é considerado não determinístico, pois produzirá resultados diferentes para diferentes condições.

Mantendo as coisas acima em mente, podemos prosseguir para eliminar a computação não determinística tornando a coluna determinística. Adicionamos uma coluna week_start à tabela subjacente e pré-calculamos e preenchemos o valor week_start na tabela para extrações de dados diárias. Assim, as linhas com datas D1 a D7 pertencem a W1, D8 a D14 pertencem a W2, e assim por diante.

Agora, podemos prosseguir para criar uma visualização indexada com o SQL abaixo.

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

Depois que esta visualização indexada for criada, podemos consultá-la da seguinte forma:

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

Conclusão

Uma visualização indexada terá um tempo de consulta significativamente mais rápido do que uma visualização básica ou consultar a tabela diretamente para conjuntos de dados grandes onde os dados cresceram para um milhão de linhas ou mais. O usuário final terá uma experiência de baixa latência, e as consultas ao banco de dados serão otimizadas. Uma vez que agregamos uma semana de dados em 1 linha, reduzimos o tempo de agregação para 1/7.