多段ビューのテーブル化によるクエリの効率化とコスト削減

はじめに

データ分析基盤の設計は非常に重要な役割を果たしており、データエンジニアは、初期設計時にしばしば多段ビュー（nested views）を利用します。
しかし、これらの多段ビューは、パフォーマンスやコストの観点から問題を引き起こすことがあります。
本記事では、多段ビューの課題を解決するためにテーブル化を検討する方法について詳細に説明します。

多段ビューの利点と課題

多段ビューの利点

1. 柔軟性と再利用性: 多段ビューは、複数のビジネスロジックを再利用可能な形で定義することができます。これにより、変更が容易になり、メンテナンスの手間が減少します。

2. 動的クエリの実行: ビューは動的にクエリが実行されるため、最新のデータをリアルタイムで取得することが可能です。

多段ビューの課題

1. パフォーマンスの低下: 多段ビューのネストが深くなると、クエリの実行時間が大幅に増加します。これにより、応答時間が遅延してユーザーエクスペリエンスが低下する可能性があります。

2. コストの増加: クラウド環境で多段ビューを使用すると、データの読み込み回数や計算リソースの使用量が増加し、結果としてコストが増大することがあります。

多段ビュー

テーブル化のメリット

パフォーマンス向上

多段ビューをテーブルに変換することで、クエリの実行時間を大幅に短縮できます。これは、計算済みのデータが静的なテーブルに格納され、クエリ実行時に即座にアクセス可能になるためです。これにより、クエリの複雑さが軽減され、データベースの負荷が減少します。

コスト削減

クラウドベースのデータプラットフォームでは、クエリ実行のたびに発生するリソース使用量に基づいてコストが算出されます。
多段ビューをテーブル化することで、頻繁にアクセスされるデータを事前に計算して格納することができます。これにより、多段ビューのように複数のビューを経由してデータを取得するのではなく、単一のテーブルに対して直接クエリを実行するだけで済むため、クエリ実行のコストを削減できます。特に、データが静的である場合や、データ更新頻度が低い場合には、コスト削減効果が顕著です。

テーブル化

テーブル化の実装戦略

インクリメンタルロードとフルロード

1. インクリメンタルロード: データの変更部分のみを更新する方法です。これにより、データ更新の効率が向上し、リソース消費を抑えることができます。例えば、データのタイムスタンプを使用して、前回の更新以降に変更されたデータのみを抽出し、テーブルを更新します。
   サービスによっては、スケジュールクエリもあります。

2. フルロード: データ全体を定期的に再計算し、テーブルを更新する方法です。データの完全性を保証するために必要な場合がありますが、リソース消費が大きくなることに注意が必要です。

マテリアライズドビューの活用

マテリアライズドビューは、ビューの結果を物理的なテーブルとして保存する方法です。これにより複雑なクエリの結果を事前に計算し、ストレージに格納することでクエリ実行時のパフォーマンスを大幅に向上させることができます。
マテリアライズドビューは定期的にリフレッシュすることで最新のデータを保持しつつ、クエリ実行コストを削減できます。

以下の記事でも触れています。

ビューからテーブルへの移行例

移行プロセス

1. データプロファイリング: 最初に、現行の多段ビューを分析し、クエリパフォーマンスのボトルネックを特定します。

2. 重要データの特定: 次に、頻繁にアクセスされる重要なデータを特定し、テーブル化の対象とします。

3. テーブル設計: パフォーマンス向上のため、適切なインデックスとパーティショニングを設計します。

4. データ移行: インクリメンタルロードとフルロードを組み合わせて、段階的にテーブル化を進めます。

5. テストと検証: 移行後、クエリパフォーマンスとデータ整合性をテストし、必要な調整を行います。

現状のViewのSQL

下記はイメージを掴みやすいように具体的なSQLを書きました。

①1つ目のビュー（sales_view）

CREATE VIEW sales_view AS
SELECT
    s.sale_id,
    s.store_id,
    s.product_id,
    s.sale_date,
    s.quantity,
    s.total_amount,
    p.product_name,
    p.category
FROM
    sales s
JOIN
    products p ON s.product_id = p.product_id;

②2つ目のビュー（detailed_sales_view）

CREATE VIEW detailed_sales_view AS
SELECT
    sv.sale_id,
    sv.sale_date,
    sv.quantity,
    sv.total_amount,
    sv.product_name,
    sv.category,
    st.store_name,
    st.location
FROM
    sales_view sv
JOIN
    stores st ON sv.store_id = st.store_id;

テーブル化のSQL

多段ビューをテーブルに変換することでパフォーマンスを向上させます。
まず、最終結果を保存するテーブルを作成し、次に定期的にテーブルを更新するためのスクリプトを示します。

①テーブル作成

CREATE TABLE detailed_sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    quantity INT,
    total_amount DECIMAL(10, 2),
    product_name VARCHAR(255),
    category VARCHAR(255),
    store_name VARCHAR(255),
    location VARCHAR(255)
);

②データの初期ロード

INSERT INTO detailed_sales (sale_id, sale_date, quantity, total_amount, product_name, category, store_name, location)
SELECT
    sv.sale_id,
    sv.sale_date,
    sv.quantity,
    sv.total_amount,
    sv.product_name,
    sv.category,
    st.store_name,
    st.location
FROM
    sales_view sv
JOIN
    stores st ON sv.store_id = st.store_id;

③データのインクリメンタルロード

DECLARE @last_updated_date DATE;
SET @last_updated_date = '2024-06-01';  -- 例として日付を設定

INSERT INTO detailed_sales (sale_id, sale_date, quantity, total_amount, product_name, category, store_name, location)
SELECT
    sv.sale_id,
    sv.sale_date,
    sv.quantity,
    sv.total_amount,
    sv.product_name,
    sv.category,
    st.store_name,
    st.location
FROM
    sales_view sv
JOIN
    stores st ON sv.store_id = st.store_id
WHERE
    sv.sale_date > @last_updated_date;

MERGE INTO detailed_sales AS target
USING (
    SELECT
        sv.sale_id,
        sv.sale_date,
        sv.quantity,
        sv.total_amount,
        sv.product_name,
        sv.category,
        st.store_name,
        st.location
    FROM
        sales_view sv
    JOIN
        stores st ON sv.store_id = st.store_id
    WHERE
        sv.sale_date > @last_updated_date
) AS source
ON target.sale_id = source.sale_id
WHEN MATCHED THEN
    UPDATE SET
        target.sale_date = source.sale_date,
        target.quantity = source.quantity,
        target.total_amount = source.total_amount,
        target.product_name = source.product_name,
        target.category = source.category,
        target.store_name = source.store_name,
        target.location = source.location;

以下の記事が参考になります。

BigQueryでのデータ追記処理における冪等化の取り組み - ZOZO TECH BLOG

（余談ですが）
上記のクエリを更に効率良く実装できるサービスが登場してきています。
例えば、dbtやGoogle Dataform等にはインクリメンタルロードの設定をすることでわざわざmerge処理を書かなくても実装できます。

Configure incremental models | dbt Developer Hub

Configure an incremental table  |  Dataform  |  Google Cloud

結論

多段ビューを使用することは、初期設計フェーズで有益ですが、パフォーマンスやコストの観点から課題が生じることがあります。テーブル化は、これらの課題を解決するための有効なアプローチです。

それぞれのメリットとデメリットを理解しながら、初期設計では多段ビュー、問題が発生するとテーブル化を試みることが良いでしょう。

関連書籍

データアーキテクチャの設計やETLパイプライン、データモデリングについての記載があります。この本の内容を理解できれば、データエンジニアリング業務で必要な単語や概念は一通り浚うことができるはず。

データエンジニアリングの基礎 ―データプロジェクトで失敗しないために

データモデリングを学ぶ1冊目には丁度良い本です。分析基盤だけでなく、基幹系システムで数十年間、不変な技術として存在し続けています。

実践的データモデリング入門