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 opção a considerar para armazenar e consultar esses dados sob certas condições:

1. As consultas analíticas requerem junções com outras tabelas (aplicando filtros na interface do usuário)
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 hotéis que utiliza o Azure SQL no backend. Queremos ver um painel de interface do usuário 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 das tabelas acima.

Vamos considerar um exemplo em particular. Precisamos agregar métricas de atividade diária agrupadas por data em ordem decrescente para clientes 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 entre 30 e 40 anos. A tabela é bastante eficiente, e conseguimos facilmente realizar consultas de intervalo espalhadas por vários meses. Eventualmente, nossas exigências crescem. Agora queremos acompanhar 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 data_criada semanal ou mensalmente, dependendo do solicitado.
• Criar algumas views que agregam 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 views apenas fornecem uma abstração para a consulta subjacente que simplesmente consulta a tabela subjacente. Views Materializadas são o próximo pensamento que vem à mente. No entanto, elas precisam ser atualizadas manualmente ou em uma programação, devido ao que os dados em tempo real não estão disponíveis.

Para resolver esses problemas, o Azure SQL Server oferece um ótimo recurso conhecido como Visão Indexada. Uma Visão Indexada é uma representação física de uma visão armazenada no banco de dados com um índice clusterizado único. Mudanças nas tabelas subjacentes atualizam automaticamente a visão indexada para mantê-la em sincronia. Ela utiliza um índice clusterizado que organiza os dados na visão com base na ordem das chaves do índice.

A visão indexada é ideal para cenários onde precisamos de dados em tempo real, e nossa consulta envolve junções complexas de múltiplas tabelas. Também é adequada para nosso caso de uso onde os dados existentes raramente são atualizados, mas são consultados frequentemente, e temos consultas baseadas em intervalo e queremos realizar recuperação ordenada.

Há algumas coisas a considerar antes de decidir se você deseja optar por visões indexadas. Visõ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 visão indexada é uma estrutura real que requer armazenamento, semelhante a uma Visão Materializada.

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

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

Agora, podemos proceder para criar uma visã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);    

Após a criação desta visão indexada, 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 visão indexada terá um tempo de consulta significativamente mais rápido do que uma visão básica ou consultar a tabela diretamente para grandes conjuntos de dados 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. Como agregamos uma semana de dados em 1 linha, reduzimos o tempo de agregação para 1/7.