Amazon Redshift マテリアライズドビューで ELT と BI クエリを高速化

Amazon Redshift のマテリアライズドビュー機能は、Amazon QuickSight などのビジネスインテリジェンス (BI) ツールからのダッシュボードクエリといった反復または予測可能なワークロードで、大幅に高速なクエリパフォーマンスを実現するために役立ちます。これは、ETL (抽出、変換、ロード) データ処理も高速化およびシンプル化します。マテリアライズドビューは、頻繁に使用される事前計算を保存し、それらをシームレスに使用して後続の分析クエリでの低レイテンシーを達成するために利用できます。

この記事では、マテリアライズドビューを作成する、データ取り込み後にビューを更新する、および BI ワークロードを高速化する方法を説明します。

サンプルデータセットのセットアップ

このチュートリアルでは、Amazon Customer Reviews Dataset を使用します。これは、us-east-1 リージョンに保存されている公開データセットです。以下の 3 つのテーブルを作成します。

product_reviews – 特定の製品に関するお客様のレビューが含まれます
customer – お客様のプロファイルデータが含まれます
customer_address – お客様の住所情報が含まれます

以下の図は、これら 3 つのテーブルの関係を示すものです。

スクリプトをダウンロードしてテーブルをセットアップするために、mv_blog.sql を選択します。

マテリアライズドビューの作成と使用

このユースケースでは、あるマーケティングチームが、その会社の製品を好むお客様の人数を州ごとに示すレポートの構築を希望しています。また、必要に応じて各製品カテゴリーをドリルダウンしたいとも考えています。

この最初のステップでは、標準ビューを作成します。以下のコードを参照してください。

CREATE VIEW v_reviews_byprod_and_state AS
SELECT PR.product_category,
       A.ca_state AS customer_state,
       count(PR.star_rating) AS cnt
FROM product_reviews PR,
     customer C,
     customer_address A
WHERE PR.customer_id = C.c_customer_sk
  AND C.c_current_addr_sk = A.ca_address_sk
  AND PR.marketplace = 'US'
GROUP BY 1,
         2;

以下のコードは、州ごとの製品レビュー数を分析するレポートです。

SELECT customer_state,
       sum(cnt)
FROM v_reviews_byprod_and_state
GROUP BY 1
ORDER BY 2;

以下のコードは、特定のカテゴリーに関する州ごとの製品レビュー数を分析するレポートです。

SELECT customer_state,
       sum(cnt)
FROM v_reviews_byprod_and_state
WHERE product_category IN ('Home',
                           'Grocery')
GROUP BY 1
ORDER BY 2;

前出のレポートは、実行に約 4 秒かかります。より多くの製品を販売し、より多くのレビューを受けるに従って、この経過時間も次第に長くなります。これらのレポートを高速化するため、レビュー数を製品カテゴリー別、および州別に事前計算するマテリアライズドビューを作成できます。以下のコードを参照してください。

CREATE MATERIALIZED VIEW mv_reviews_byprod_and_state AS
SELECT PR.product_category,
       A.ca_state AS customer_state,
       count(PR.star_rating) AS cnt
FROM product_reviews PR,
     customer C,
     customer_address A
WHERE PR.customer_id = C.c_customer_sk
  AND C.c_current_addr_sk = A.ca_address_sk
  AND PR.marketplace = 'US'
GROUP BY 1,
         2;

以下のコードは、マテリアライズドビューに照らして製品レビューを分析するためのレポートです。

SELECT customer_state,
       sum(cnt)
FROM mv_reviews_byprod_and_state
GROUP BY 1
ORDER BY 2;

SELECT customer_state,
       sum(cnt)
FROM mv_reviews_byprod_and_state
WHERE product_category IN ('Home',
                           'Grocery')
GROUP BY 1
ORDER BY 2;

同じレポートでも、マテリアライズドビューを使うと 200 ミリ秒もかかりません。これは、新しいクエリが複数の大規模なベーステーブルではなく、事前計算された結合、フィルター、グループ、および小計にアクセスするためです。

ELT データ処理の高速化およびシンプル化

マテリアライズドビューを使用せずに同様のパフォーマンスを得るため、多くのユーザーが CREATE TABLE AS (CTAS) コマンドを使用します。しかし、新しいデータの挿入、更新、または削除でテーブルを更新すると CTAS テーブルが古くなります。このため、ベーステーブルからの最新の変更でテーブルを最新状態に保つために、これらを再度作成する必要がありますが、Amazon Redshift のマテリアライズドビューでは、対応する SQL でマテリアライズドビューを効率的かつ増分的に更新することによって、この問題を克服できます。たとえば、以下のコードは追加 10,000 件のレビューを取り込みます。

INSERT INTO product_reviews
SELECT   marketplace,
  cast(customer_id as bigint) customer_id,
  review_id,
  product_id,
  cast(product_parent as bigint) product_parent,
  product_title,
  star_rating,
  helpful_votes,
  total_votes,
  vine,
  verified_purchase,
  review_headline,
  review_body,
  review_date,
  year,
  product_category
FROM demo.products_reviews
WHERE review_date = '2015-07-01' LIMIT 10000;

そうすると、マテリアライズドビューが古くなります。マテリアライズドビューを更新するには、次のコードを入力します。

REFRESH MATERIALIZED VIEW mv_reviews_byprod_and_state;

200 ミリ秒未満で、マテリアライズドビューが再び最新状態になります。レポートクエリも、同一の一貫的で迅速なパフォーマンスを備えています。

以下のスクリーンショットは、クエリパフォーマンスを示すクエリログです。ログには、最上部から新しい順にステートメントが示されています。

マテリアライズドビューの更新は、増分的であることから大幅に速くなります。Amazon Redshift は、ベーステーブルからマテリアライズドビュー全体を計算し直すのではなく、新しいデータだけを使ってマテリアライズドビューを更新します。詳細については、マテリアライズドビューの更新を参照してください。

マテリアライズドビューは ELT もシンプル化し、より簡単かつ効率的にします。マテリアライズドビューがなければ、ELT ジョブを作成し、CTAS を使用して製品分析データの事前計算を行うかもしれません。ダッシュボードのレイテンシー要件を満たすために、ELT ジョブは新しいデータが取り込まれた後でこのデータを再計算し、そのデータを事前計算された製品分析テーブルに保存します。

具体的に言うと、ETL ジョブは、データを取り込むたびに、事前計算された製品分析テーブルをドロップして再作成します。以下のコードを参照してください。

BEGIN;
    DROP TABLE IF EXISTS latest_product_analysis;
    CREATE TABLE latest_product_analysis as SELECT ...;
END;

マテリアライズドビューを使うと、マテリアライズドビューを 1 度作成し、更新して最新状態を保つだけですみます。新しいデータの取り込み後にマテリアライズドビューを更新するには、ELT のデータ取り込みスクリプトに REFRESH MATERIALIZED VIEW を追加します。Redshift が自動でマテリアライズドビューの増分的な最新化を行います。

BI ダッシュボードのより高速なパフォーマンスの実現

マテリアライズドビューは、BI チームによる製品動向の分析用ダッシュボードの構築にも役立てることができます。

たとえば、customer および customer_address のディメンションテーブルを結合し、レビューと評価を再計算するマテリアライズドビューを作成するには、以下のコードを入力します。

CREATE MATERIALIZED VIEW mv_product_analysis 
    sortkey(product_category, Customer_State, review_date) 
AS
SELECT PR.product_category,
       A.ca_state AS Customer_State,
       PR.review_date AS Review_Date,
       PR.product_title,
       COUNT(1) AS review_total,
       SUM(PR.star_rating) AS rating
FROM product_reviews PR,
     customer C,
     customer_address A
WHERE PR.customer_id = C.c_customer_sk
  AND C.c_current_addr_sk = A.ca_address_sk
  AND marketplace = 'US'
GROUP BY 1,2,3,4;

マテリアライズドビューには、標準的なテーブルと同じようにアクセスします。このチュートリアルでは、マテリアライズドビューを Amazon QuickSight データセットのソースとして選択します。これは、以下のスクリーンショットに示されています。

マテリアライズドビューのデータは Amazon QuickSight でプレビューすることが可能で、ダッシュボードの構築にどの情報を使用できるかを把握できます。以下のスクリーンショットは、mv_product_analysis のサンプルデータを示しています。

お客様によるレビューの経時的な投稿数を追跡するには、review_date を X 軸、Sum(review_total) を Y 軸として使用します。以下のグラフは、これを視覚化したものです。

以下のスクリーンショットは、最上位の製品カテゴリー、および製品の州別の人気などを分析する、完全な「製品動向」ダッシュボードを示しています。

マテリアライズドビューを使用しているため、製品動向ダッシュボードは数秒でロードされ、常に最新です。最新の洞察を得る、お客様の購買行動を理解する、およびビジネス機会と最適化を特定することが可能です。

マテリアライズドビューのパフォーマンスは、標準ビューの使用、および CTAS の使用などのその他可能な選択肢と比較することができます。以下のグラフは、製品動向ダッシュボードの全体的なクエリ実行を示しています。マテリアライズドビューは、標準ビューの使用と比較してクエリパフォーマンスが桁違いに高くなるだけでなく、増分的な更新時間がベーステーブルにおける変更の差分に比例することから、CTAS を使用する場合よりもメンテナンスコストが低くなります。それに対し、CTAS の再作成アプローチは、ベーステーブルにあるすべてのデータを処理する必要があります。

以下のアニメーション GIF は、直接クエリモードの Amazon QuickSight を使用して構築された製品動向ダッシュボードの実際の応答時間を示しています。

まとめ

この記事では、1 つ、または複数のベーステーブルを使って Amazon Redshift マテリアライズドビューを作成し、BI クエリと ELT の両方を高速化する方法を紹介しました。効率的なデータ処理パイプラインを簡単に構築して維持し、マテリアライズドビューの低レイテンシーのクエリ実行メリットを、データ分析にシームレスに拡張することができます。

著者について

Juan Yu は AWS のデータウェアハウススペシャリストソリューションアーキテクトです。

Jose Kunnackal John は、AWS のクラウドネイティブな完全マネージド型 BI サービスである Amazon QuickSight のプリンシパルプロダクトマネージャーです。Jose は彼のキャリアを Motorola でスタートさせ、電気通信およびファーストレスポンダーシステムのソフトウェアを作成していました。その後、Trilibis Mobile のエンジニアリング担当ディレクターとして就任し、AWS のサービスを使用して SaaS モバイルウェブプラットフォームを構築しました。Jose は、クラウドテクノロジーの可能性に意気込みを感じており、クラウドへの移行においてお客様を支援することを心待ちにしています。

Amazon Web Services ブログ