Индексиранный вид для агрегации метрик

Microsoft Azure SQL – это мощная, полностью управляемая платформа баз данных, разработанная для высокопроизводительных запросов, хранения реляционных данных и аналитики. Для типичного веб-приложения с бэкендом это хороший выбор, когда мы хотим использовать управляемую базу данных, которая может масштабироваться как вертикально, так и горизонтально.

Приложение генерирует метрики пользователей ежедневно, которые могут быть использованы для отчетов или аналитики. Azure SQL – отличный выбор для хранения и запросов этих данных при определенных условиях:

1. Аналитические запросы требуют объединения с другими таблицами (применение фильтров на пользовательском интерфейсе)
2. Вы хотите объединить исторические и транзакционные данные
3. Объем данных не очень большой, и производительность запросов может быть управляема путем настройки

Рассмотрим пример сайта бронирования отелей, работающего на Azure SQL на бэкенде. Мы хотим увидеть панель управления пользовательской активностью, такую как клики на сайте, посещения страницы с описанием отеля, сделанные бронирования и т. д.

Допустим, все данные телеметрии сбрасываются для каждого пользователя ежедневно в неструктурированное хранилище, и мы извлекаем эти данные в нашу базу данных с помощью фоновых задач, таких как Apache Airflow.

Ниже приведена схема таблицы пользователей и таблицы для хранения ежедневных метрик.

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

);

​

Из вышеперечисленных таблиц можно извлечь много полезной информации.

Давайте рассмотрим один конкретный пример. Нам нужно агрегировать ежедневные метрики активности, сгруппированные по дате в порядке убывания для клиентов в возрасте от 30 до 40 лет, находящихся в Нью-Йорке. Ниже приведен запрос:

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

Теперь мы можем проанализировать ежедневные тенденции в этой группе пользователей, которые находятся в Нью-Йорке и в возрасте от 30 до 40 лет. Таблица в основном работает быстро, и мы легко можем выполнять диапазонные запросы, охватывающие несколько месяцев. В конечном итоге наши требования растут. Теперь мы хотим отслеживать поведение пользователей в недельном или месячном диапазоне. Но наша таблица хранит данные ежедневно. Теперь у нас есть два варианта:

• Запросить таблицу и сгруппировать date_created еженедельно или ежемесячно, в зависимости от запроса.
• Создать несколько представлений, которые агрегируют данные еженедельно или ежемесячно для каждого пользователя. См. запрос ниже:

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

Однако важно учитывать, что представления просто предоставляют абстракцию к базовому запросу, который просто запрашивает данные из базовой таблицы. Материализованные представления – следующая мысль, которая приходит на ум. Однако их необходимо обновлять вручную или по расписанию, из-за чего реальные данные не доступны в реальном времени.

Чтобы решить эти проблемы, Azure SQL Server предлагает отличную функцию, известную как Индексированный Вид.  Янексированный Вид — это физическое представление вида, хранящееся в базе данных с уникальным кластерным индексом. Изменения в подлежащих таблицах автоматически обновляют индексированный вид, чтобы поддерживать его синхронизацию. Он использует кластерный индекс, который организует данные в виде на основе порядка ключей индекса.

Индексированный вид идеально подходит для сценариев, где нам нужны данные в реальном времени, а наш запрос включает сложные соединения нескольких таблиц. Он также подходит для нашего случая, когда существующие данные редко обновляются, но часто запрашиваются, и у нас есть запросы на основе диапазона и необходимость упорядоченного извлечения.

Существует несколько моментов, которые следует учитывать, прежде чем решать, хотите ли вы использовать индексированные виды. Индексированные виды не могут содержать недетерминированные функции. Недетерминированная–функция — это функция, которая не всегда возвращает один и тот же результат для одного и того же входа, даже если она выполняется с идентичными аргументами и в одних и тех же условиях базы данных. Кроме того, индексированный вид является фактической структурой, требующей хранения, аналогично Материализованному Виду.

Синтаксис для создания Индексированного Вида похож на запрос на создание Вида, приведенный выше. Однако мы не можем использовать недетерминированные функции при создании индексированного вида. Строка DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start в запросе вида выше зависит от специфической для сеанса настройки SET DATEFIRST, которая определяет первый день недели. Это считается недетерминированным, так как будет давать разные результаты в различных условиях.

Имея в виду вышеизложенное, мы можем приступить к устранению недетерминированных вычислений, сделав столбец детерминированным. Мы добавляем столбец week_start к базовой таблице, предварительно вычисляем и заполняем значение week_start в таблице для ежедневного извлечения данных. Таким образом, строки с датами D1 по D7 принадлежат W1, с D8 по D14 – W2 и так далее.

Теперь мы можем приступить к созданию индексированного представления с помощью следующего SQL-кода.

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

После создания этого индексированного представления, мы можем запрашивать его следующим образом:

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

Заключение

Индексированное представление будет иметь значительно более быстрое время запроса, чем обычное представление или запрос к таблице напрямую для больших наборов данных, где данные выросли до миллиона строк или более. Конечный пользователь получит низкую задержку, и запросы к базе данных будут оптимизированы. Поскольку мы агрегировали данные за неделю в 1 строку, мы сократили время агрегации до 1/7.