Vista indexada para la agregación de métricas

Microsoft Azure SQL es una plataforma de base de datos robusta y completamente gestionada diseñada para consultas de alto rendimiento, almacenamiento de datos relacionales y análisis. Para una aplicación web típica con un backend, es una buena elección cuando queremos considerar una base de datos gestionada que pueda escalar tanto vertical como horizontalmente.

Un software de aplicación genera métricas de usuario a diario, que se pueden utilizar para informes o análisis. Azure SQL es una excelente opción a considerar para almacenar y consultar estos datos bajo ciertas condiciones:

1. Las consultas analíticas requieren uniones con otras tablas (aplicando filtros en la interfaz de usuario)
2. Deseas combinar datos históricos y transaccionales
3. El volumen de datos no es extremadamente grande, y el rendimiento de las consultas se puede gestionar mediante ajustes

Consideremos un ejemplo de un sitio de reserva de hoteles que utiliza Azure SQL en el backend. Queremos ver un panel de control de interfaz de usuario para hacer un seguimiento de la actividad del usuario, como clics en el sitio, visitas a la página de descripción del hotel, reservas realizadas, etc.

Supongamos que todos estos datos de telemetría se vuelcan para cada usuario en una base diaria en almacenamiento no estructurado, y estamos extrayendo estos datos en nuestra base de datos mediante trabajos en segundo plano, como Apache Airflow.

A continuación se muestra el esquema de la tabla de usuarios y una tabla para almacenar métricas diarias.

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

);

​

Se pueden obtener muchas ideas a partir de las tablas anteriores.

Consideremos un ejemplo particular. Necesitamos agregar métricas de actividad diaria agrupadas por fecha en orden descendente para clientes de entre 30 y 40 años ubicados en la ciudad de Nueva York. A continuación se muestra la 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

Ahora, podemos analizar las tendencias diarias de este grupo de usuarios que están en Nueva York y tienen entre 30 y 40 años. La tabla es en su mayoría eficiente y podemos realizar fácilmente consultas de rango distribuidas en varios meses. Con el tiempo, nuestros requisitos crecen. Ahora queremos rastrear el comportamiento del usuario en un rango semanal o mensual. Pero nuestra tabla almacena los datos diariamente. Ahora tenemos dos opciones:

• Consultar la tabla y agrupar la date_created semanal o mensualmente, dependiendo de la solicitud.
• Crear un par de vistas que agreguen los datos de forma semanal o mensual por usuario. Consulte la consulta a continuación:

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

Sin embargo, algo importante a considerar es que las vistas solo proporcionan una abstracción a la consulta subyacente que simplemente consulta la tabla subyacente. Las Vistas Materializadas son la próxima idea que viene a la mente. Sin embargo, necesitan actualizarse manualmente o según un horario, lo que hace que los datos en tiempo real no estén disponibles.

Para abordar estos problemas, Azure SQL Server ofrece una excelente característica conocida como Vista Indexada. Una Vista Indexada es una representación física de una vista almacenada en la base de datos con un índice agrupado único. Los cambios en las tablas subyacentes actualizan automáticamente la vista indexada para mantenerla sincronizada. Utiliza un índice agrupado que organiza los datos en la vista en función del orden de las claves del índice.

La vista indexada es ideal para escenarios en los que necesitamos datos en tiempo real, y nuestra consulta implica complejas uniones de múltiples tablas. También es adecuada para nuestro caso de uso donde los datos existentes rara vez se actualizan pero se consultan a menudo, y tenemos consultas basadas en rangos y queremos realizar recuperaciones ordenadas.

Hay algunas cosas a considerar antes de decidir si desea optar por vistas indexadas. Las vistas indexadas no pueden tener funciones no deterministas. Una función no determinista es una función que no siempre devuelve el mismo resultado para la misma entrada, incluso cuando se ejecuta con argumentos idénticos y bajo las mismas condiciones de la base de datos. Además, una vista indexada es una estructura real que requiere almacenamiento, similar a una Vista Materializada.

La sintaxis para crear una Vista Indexada es similar a la consulta de creación de Vista anterior. Sin embargo, no podemos tener funciones no deterministas al crear una vista indexada. La línea DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start en la consulta de la vista anterior depende de la configuración específica de la sesión SET DATEFIRST, que determina el primer día de la semana. Esto se considera no determinista ya que producirá diferentes resultados para diferentes condiciones.

Manteniendo en cuenta las cosas anteriores, podemos proceder a eliminar la computación no determinística haciendo que la columna sea determinística. Agregamos una columna week_start a la tabla subyacente y precalculamos y llenamos el valor week_start en la tabla para extracciones de datos diarios. Entonces, las filas con fechas D1 a D7 pertenecen a W1, D8 a D14 pertenecen a W2, y así sucesivamente.

Ahora, podemos proceder a crear una vista indexada con el SQL a continuación.

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

Después de que se cree esta vista indexada, podemos consultarla de la siguiente manera:

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

Conclusión

Una vista indexada tendrá un tiempo de consulta significativamente más rápido que una vista básica o consultar la tabla directamente para conjuntos de datos grandes donde los datos han crecido hasta alcanzar un millón de filas o más. El usuario final tendrá una experiencia de baja latencia y las consultas a la base de datos estarán optimizadas. Dado que hemos agregado una semana de datos en 1 fila, hemos reducido el tiempo de agregación a 1/7.