メトリック集約のためのインデックス付きビュー

Microsoft 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歳のユーザーグループから日次トレンドを分析できます。このテーブルは主にパフォーマンスが良く、複数の月にわたる範囲クエリを容易に実行できます。しかし、最終的に私たちの要件は増大します。今度は、ユーザー行動を週単位または月単位で追跡したいと思っています。しかし、私たちのテーブルはデータを日次で保存しています。現在、2つの選択肢があります：

• テーブルをクエリして、要求に応じて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はインデックス付きビューとして知られる素晴らしい機能を提供しています。 Iインデックス付きビューは、一意のクラスター化インデックスを持つデータベースに保存されたビューの物理的な表現です。基になるテーブルに対する変更は、インデックス付きビューを自動的に更新し、同期を保ちます。これは、インデックスキーの順序に基づいてビュー内のデータを整理するクラスター化インデックスを使用します。

インデックス付きビューは、リアルタイムデータが必要で、クエリが複雑なマルチテーブル結合を含むシナリオに最適です。また、既存のデータがあまり更新されず、頻繁にクエリされる私たちの使用ケースや、範囲ベースのクエリを持ち、順序付きの取得を行いたい場合にも適しています。

インデックス付きビューを選択する前に考慮すべきことがいくつかあります。インデックス付きビューには非決定的関数を使用することはできません。非–決定的関数は、同じ入力に対して常に同じ結果を返さない関数であり、同一の引数と同じデータベース条件で実行しても異なる結果が得られます。また、インデックス付きビューは、マテリアライズドビューと同様にストレージを必要とする実際の構造です。

インデックス付きビューを作成するための構文は、上記のビュー作成クエリと似ています。ただし、インデックス付きビューを作成する際には非決定的関数を使用することはできません。上記のビュークエリの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

結論

インデックス付きビューは、基本ビューやテーブルを直接クエリする場合と比べて、大規模データセット（データが100万行以上に成長した場合）でのクエリ時間が大幅に短縮されます。エンドユーザーは低遅延の体験を得ることができ、データベースへのクエリが最適化されます。1行に1週間分のデータを集約したため、集約にかかる時間を1/7に短縮しました。