지표를 집계하는 인덱스된 뷰

마이크로소프트 Azure SQL은 고성능 쿼리, 관계형 데이터 저장 및 분석을 위해 설계된 견고한 완전 관리형 데이터베이스 플랫폼입니다. 백엔드를 갖춘 전형적인 웹 응용 프로그램의 경우, 수직 및 수평으로 확장할 수 있는 관리형 데이터베이스를 고려할 때 좋은 선택입니다.

응용 프로그램 소프트웨어는 매일 사용자 메트릭을 생성하며, 이는 보고서나 분석에 사용할 수 있습니다. Azure SQL은 다음 조건 하에 이 데이터를 저장하고 쿼리하는 데 좋은 선택입니다:

1. 분석 쿼리가 다른 테이블과 조인이 필요한 경우(UI에서 필터 적용)
2. 역사적 및 거래 데이터를 결합하려는 경우
3. 데이터 양이 매우 크지 않고 쿼리 성능을 튜닝하여 관리할 수 있는 경우

백엔드에서 Azure SQL을 실행 중인 호텔 예약 사이트의 예를 살펴보겠습니다. 사용자 활동을 추적하는 UI 대시보드를 보고 싶어합니다. 예를 들어, 사이트에서의 클릭, 호텔 설명 페이지 방문, 예약 등.

모든 이 텔레메트리 데이터가 매일 사용자별로 구조화되지 않은 저장소에 덤프되어 있고, 이 데이터를 매일 하나의 기준으로 배경 작업(예: 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는 인덱스 뷰라는 훌륭한 기능을 제공합니다. 인덱스 뷰는 고유한 클러스터형 인덱스와 함께 데이터베이스에 저장된 뷰의 물리적 표현입니다. 기본 테이블에 대한 변경 사항은 인덱스 뷰를 자동으로 업데이트하여 동기화 상태를 유지합니다. 이는 인덱스 키의 순서에 따라 뷰의 데이터를 구성하는 클러스터형 인덱스를 사용합니다.

인덱스 뷰는 우리가 실시간 데이터가 필요하고 쿼리가 복잡한 다중 테이블 조인을 포함하는 시나리오에 적합합니다. 또한 기존 데이터가 거의 업데이트되지 않지만 자주 쿼리되는 경우, 범위 기반 쿼리가 있고 정렬된 검색을 원할 때 우리의 사용 사례에 적합합니다.

인덱스 뷰를 선택하기 전에 고려해야 할 몇 가지 사항이 있습니다. 인덱스 뷰는 비결정적 함수(non-deterministic functions)를 가질 수 없습니다. 비–결정적 함수는 동일한 인수와 동일한 데이터베이스 조건에서 실행되더라도 항상 동일한 결과를 반환하지 않는 함수입니다. 또한, 인덱스 뷰는 물질화된 뷰와 유사하게 저장이 필요한 실제 구조입니다.

인덱스 뷰를 생성하는 구문은 위의 뷰 생성 쿼리와 유사합니다. 그러나 인덱스 뷰를 생성할 때 비결정적 함수를 사용할 수 없습니다. 위의 뷰 쿼리에서 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행으로 집계했기 때문에 집계에 소요되는 시간을 1/7로 줄였습니다.