Amazon Web Services ブログ

ROLLUP、CUBE、GROUPING SETS などの新しい SQL 構文を使用して Amazon Redshift のオンライン分析処理 (OLAP) クエリを簡略化

Amazon Redshift はフルマネージド、ペタバイトスケールの超並列データウェアハウスです。標準 SQL と既存のビジネスインテリジェンス (BI) ツールを使用して、すべてのデータを高速、簡単、費用対効果の高い方法で分析できます。

AWS は SQL 構造を簡略化し、新しい演算子を追加することで、Redshift で分析を簡単に行えるようにするための投資を継続的に行っています。 今回、ROLLUP、CUBE、GROUPING SETS が追加され、SQL 集計拡張機能が拡張されたことにより、1 つのステートメントで複数の集計操作を実行し、クエリに小計、合計、および小計のコレクションを簡単に含めることができます。

この投稿では、これらの追加された構文を使用して Amazon Redshift でのクエリを簡略化する方法について説明します。

ソリューションの概要

オンライン分析処理 (OLAP) は、今日のデータアナリストやビジネスアナリストにとって効果的なツールです。 OLAPより、ミッションクリティカルなメトリクスをさまざまなロールアップレベルで一元的に確認できます。 アナリストは OLAP 集計を使用して顧客を人口統計、地域、心理学的データごとにグループ化し、データを集計して傾向を調べることで、購買パターンを分析できます。 例として、購入の頻度、購入間の時間枠、購入される商品の種類の分析があります。 このような分析により、顧客の好みや行動に関するインサイトが得られ、マーケティング戦略や製品開発に役立つ情報が得られます。 たとえば、データアナリストは、データにクエリを実行して、7 月に米国で販売された企業の特定の種類の製品を示すスプレッドシートを表示し、収益の数値を 9 月の同じ製品の収益の数値と比較し、同じ期間における米国内の他の製品の販売状況を比較できます。

従来、ビジネスアナリストやデータアナリストは、SQL の 一連の UNION クエリを使用して、必要な詳細レベルとロールアップレベルを実現していました。 ただし、作成と保守には非常に時間がかかり、面倒な場合があります。 さらに、この方法で実現できる詳細と集計のレベルには制限があります。これは、ユーザーがさまざまな詳細レベルやロールアップレベルごとに複数のクエリを記述する必要があるためです。

多くのお客様が、OLAP 用の GROUP BY 句をサポートする他のデータウェアハウスシステムから Amazon Redshift への移行を検討しています。 この移行プロセスを可能な限りシームレスにするために、Amazon Redshift では ROLLUP、 CUBE、GROUPING SETS がサポートされるようになりました。 これにより、OLAPワークロードの移行がスムーズになり、書き換えが最小限に抑えられます。 最終的には、これにより Amazon Redshift への移行がより迅速かつ効率的になります。 ビジネスアナリストやデータアナリストは、複数の UNION クエリの処理を 1 つの SQL で行うことができるようになりました。

次のセクションでは、TPC-Hデータセットのサンプルサプライヤー残高データを使用し、ROLLUP、CUBE、およびGROUPING SETS の拡張機能の使用方法を示します。 このデータセットは、さまざまな地域や国のサプライヤーの口座残高で構成されています。 各国レベル、地域レベル、および両方の組み合わせで口座残高の小計と総計を見つける方法を示します。 ビジネスユーザーは、これらの分析に関するすべての質問に、単純な 1 行の SQL ステートメントを実行することで回答できます。 この投稿では、集計に加えて、その結果を小計の計算に用いられた要素までさかのぼる方法についても説明します。

データの準備

ユースケースを設定するには、次の手順を実行します。

  1. Amazon Redshift コンソールのナビゲーションペインで、クエリエディタ v2 を選択します。

クエリエディタ v2 が新しいブラウザタブで開きます。

  1. サプライヤーサンプルテーブルを作成し、サンプルデータを挿入します。
create table supp_sample (supp_id integer, region_nm char(25), nation_nm char(25), acct_balance numeric(12,2));

INSERT INTO public.supp_sample (supp_id,region_nm,nation_nm,acct_balance)
VALUES
(90470,'AFRICA ','KENYA ',1745.57),
(99910,'AFRICA ','ALGERIA ',3659.98),
(26398,'AMERICA ','UNITED STATES ',2575.77),
(43908,'AMERICA ','CANADA ',1428.27),
(3882,'AMERICA ','UNITED STATES ',7932.67),
(42168,'ASIA ','JAPAN ',343.34),
(68461,'ASIA ','CHINA ',2216.11),
(89676,'ASIA ','INDIA ',4160.75),
(52670,'EUROPE ','RUSSIA ',2469.40),
(32190,'EUROPE ','RUSSIA ',1119.55),
(19587,'EUROPE ','GERMANY ',9904.98),
(1134,'MIDDLE EAST ','EGYPT ',7977.48),
(35213,'MIDDLE EAST ','EGYPT ',737.28),
(36132,'MIDDLE EAST ','JORDAN ',5052.87);
SQL

TPC-H データセットに対して実行した、次のクエリの結果から上記のサンプルを取得しました。 次のクエリを使用してサンプルレコードを取得し、この記事で説明した SQL ステートメントを試すことができます。

select s_suppkey supp_id, r.r_name region_nm,n.n_name nation_nm, s.s_acctbal acct_balance
from supplier s, nation n, region r
where
s.s_nationkey = n.n_nationkey
and n.n_regionkey = r.r_regionkey
SQL

GROUPING SETS、ROLLUP、CUBE を使用して SQL を実行する前に、サンプルデータを確認してみましょう。

supp_sample テーブルは、世界中のさまざまな国や地域のサプライヤーの口座残高で構成されています。 属性定義は次のとおりです。

  • supp_id – 各サプライヤーの固有の識別子
  • region_nm – サプライヤーが事業を行っている地域
  • nation_nm – サプライヤーが事業を展開している国
  • acct_balance – サプライヤーの未払い口座残高

GROUPING SETS

GROUPING SETS は、クエリ結果を 1 つのステートメント内の 1 つ以上のカラムでグループ化する SQL 集計拡張機能です。 異なる GROUP BY キーを使用して複数の SELECT クエリを実行し、その結果をマージ (UNION) する代わりに、GROUPING SETS を使用できます。

このセクションでは、以下を検索する方法を示します。

  •  地域ごとに集計された口座残高
  •  国ごとに集計された口座残高
  •  両方の集計結果を統合した結果

GROUPING SETS を使用して次の SQL ステートメントを実行します。

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
GROUP BY GROUPING SETS (region_nm, nation_nm);
SQL

次のスクリーンショットに示すように、結果セットには、region_nm ごとに集計された口座残高が含まれ、その後に　nation_nm　が続き、両方の結果が1つの出力にまとめられます。

ROLLUP

ROLLUP 関数は、複数のグループレベルで集計結果を生成します。最も詳細なレベルからその次のレベルまで集計されます。 データを特定のカラムと小計を表す行でグループ化し、GROUP BY 対象のカラム間の階層構造の存在を仮定しています。

このセクションでは、以下を検索する方法を示します。

  • region_nm と nation_nm の各組み合わせの口座残高
  • region_nmごとの口座残高総計
  • すべての地域の口座残高総計

ROLLUP を利用した次の SQL ステートメントを使用してください。

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
GROUP BY ROLLUP (region_nm, nation_nm)
ORDER BY region_nm,nation_nm;
SQL

次の結果は、region_nm と nation_nm の各組み合わせから始まり、nation_nm から region_nm までの階層でロールアップされた値を示しています。 region_nm の値が存在し、nation_nm の値が NULL の行は、その地域の小計を表します (緑色で表示)。 region_nm と nation_nm の両方の値が NULL の行には、総計、つまりすべての地域のロールアップされた口座残高 (赤色で表示) が表示されます。

ROLLUP は、構造的には次の GROUPING SETSと同等です。

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
GROUP BY GROUPING SETS((region_nm, nation_nm), (region_nm), ())
ORDER BY region_nm,nation_nm;
SQL

前述の ROLLUP クエリは、GROUPING SETS を使用して書き換えることができます。 ただし、このユースケースでは ROLLUP を使用する方がはるかに簡単で読みやすい構成です。

CUBE

CUBE は、指定されたカラムでデータをグループ化し、グループ化された行に加えて、グループ化されたカラムのすべてのレベルの合計を表す追加の小計行を返します。 CUBE は ROLLUP と同じ行に加えて、ROLLUP でカバーされない全てのグループ化されたカラムの組み合わせごとに小計行を追加します。

このセクションでは、以下を検索する方法を示します。

  • 各 nation_nm ごとの口座残高小計
  • 各 region_nm ごとの口座残高小計
  • 各 region_nm と region_nm の組み合わせごとの口座残高小計
  • 全ての地域の口座残高合計

CUBE を使用して次の SQL ステートメントを実行します。

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
WHERE region_nm in ('AFRICA','AMERICA','ASIA') GROUP BY CUBE(region_nm, nation_nm)
ORDER BY region_nm, nation_nm;
SQL

上記のクエリでは、簡単な説明にするために結果を制限するフィルターを追加しました。 テストでこのフィルターを削除すると、すべての地域のデータを表示できます。

次の結果セットでは、地域レベル (緑色で表示) の小計が表示されます。 これらの小計レコードは、ROLLUP によって生成されるレコードと同じです。 さらに、CUBE は nation_nm ごとに小計を生成します（黄色で表示）。 最後に、クエリで指定した3つの地域すべての総計（赤色で表示）も表示されます。

CUBE は、構造的には次の GROUPING SETS クエリと同等です。

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
WHERE region_nm in ('AFRICA','AMERICA','ASIA') -- added the filter to limit results. You can remove this filter in your test to view data for all regions
GROUP BY GROUPING SETS((region_nm, nation_nm), (region_nm), (nation_nm), ())
ORDER BY region_nm;
SQL

前述の CUBE クエリは、GROUPING SETS を使用して書き換えることができます。 ただし、CUBE を使用する方が、この用途でははるかに簡単で読みやすい構成です。

NULL値

NULL は、GROUPING SETS、ROLLUP、および CUBE の対象のカラムにおいて集約対象になる値であり、結果セットのスキーマを満たすために形式的に追加された NULL 値とは区別されて集計されます。

注文した品目、品目の説明、品目の数量に関する詳細を含む注文表を作成してみましょう。

-- Create example orders table and insert sample records
CREATE TABLE orders(item_no int,description varchar,quantity int);
INSERT INTO orders(item_no,description,quantity)
VALUES
(101,'apples',10),
(102,null,15),
(103,'banana',20);

--View the data
SELECT * FROM orders;
SQL

次の ROLLUP クエリを使用して、item_no と description ごとに数量を集計します。

SELECT item_no, description, sum(quantity)
FROM orders
GROUP BY ROLLUP(item_no, description)
ORDER BY 1,2;
SQL

次の結果では、item_no 102 の出力行が 2 つあります。 緑色でマークされた行は入力内の実際のデータレコードで、赤でマークされた行は ROLLUP 関数によって追加された小計レコードです。

これは、入力の NULL 値が 集約系の SQL 構文 によって追加された NULL 値とは別のものであることを示しています。

GROUPING と GROUPING_ID 関数

GROUPING は、GROUP BY リスト内のカラムが集約されているかどうかを示します。 GROUPING (expr) は、タプルが expr でグループ化されている場合は 0 を返し、それ以外の場合は 1 を返します。 GROUPING_ID (expr1, expr2,…, exprN) は、GROUPING (expr1)、GROUPING (expr2)、…、GROUPING (exprN) からなるビットマップの整数表現を返します。

この機能は、ビジネスユーザーが分析を行う際に、集計データの細分化や細分化を明確に把握し、フィルターを適用するのに役立ちます。 また、生成された集計の監査機能も提供します。

例として、前述の supp_sampe テーブルを使用してみましょう。 次のロールアップクエリは、GROUPING 関数と GROUPING_ID 関数を利用します。

SELECT region_nm,
nation_nm,
sum(acct_balance) as total_balance,
GROUPING(region_nm) as gr,
GROUPING(nation_nm) as gn,
GROUPING_ID(region_nm, nation_nm) as grn
FROM supp_sample
GROUP BY ROLLUP(region_nm, nation_nm)
ORDER BY region_nm;
SQL

次の結果セットでは、nation_nm にロールアップされた行の gn の値が 1 になっています。 これは、total_balance がリージョン内のすべての nation_nm の値の集計値であることを示しています。 最後の行の gr 値は 1 です。 total_balance は、すべての国を含む地域レベルでの集計値であることがわかります。 grn はビットマップの整数表現です (バイナリ表現での 11 は integer 表現の 3 に変換されます)。

パフォーマンス評価

パフォーマンスは重要な要素であり、我々は Amazon Redshift で十分にパフォーマンスの高い SQL 機能を提供したいと考えました。 さまざまなサイズ (ra3.4xlarge 5ノード、ra3.4xlarge 2ノード、ra3.xplus 2ノード) の Amazon Redshift クラスターで 3TB TPC-H パブリックデータセットを使用してベンチマークを実行しました。 さらに、クエリ結果がキャッシュされないように、クエリキャッシュを無効にしました。 これにより、キャッシュから結果を提供する機能を使用せずに、データベースのパフォーマンスを測定できます。 結果は複数回の実行で一貫していました。

3TB のパブリックデータセットからサプライヤー、地域、国のファイルをロードし、次のコードに示すように、これらの 3 つのテーブルの上にビューを作成しました。 このクエリは 3 つのテーブルを結合して統合レコードを作成します。 結合されたデータセットは、パフォーマンス評価に使用されます。

create view v_supplier_balances as
select r.r_name region_nm,n.n_name nation_nm, s.s_acctbal acct_balance
from supplier s, nation n, region r
where
s.s_nationkey = n.n_nationkey
and n.n_regionkey = r.r_regionkey;
SQL

GROUPING SETS、CUBE、ROLLUP を使用して次の SELECT クエリの例を実行し、次の表のパフォーマンスメトリクスを取得しました。

ROLL UP:

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM v_supplier_balances
GROUP BY ROLLUP (region_nm, nation_nm)
ORDER BY region_nm;
SQL
Cluster Run 1 in ms Run 2 in ms Run 3 in ms
5-node-Ra3-4XL 120 118 117
2-node-Ra3-4XL 405 389 391
2-node-Ra3-XLPLUS 490 460 461

CUBE:

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM v_supplier_balances
GROUP BY CUBE(region_nm, nation_nm)
ORDER BY region_nm;
SQL
Cluster Run 1 in ms Run 2 in ms Run 3 in ms
5-node-Ra3-4XL 224 215 214
2-node-Ra3-4XL 412 392 392
2-node-Ra3-XLPLUS 872 798 793

GROUPING SETS:

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM v_supplier_balances
GROUP BY GROUPING SETS(region_nm, nation_nm)
ORDER BY region_nm;
SQL
Cluster Run 1 in ms Run 2 in ms Run 3 in ms
5-node-Ra3-4XL 210 198 198
2-node-Ra3-4XL 345 328 328
2-node-Ra3-XLPLUS 675 674 674

ROLLUP と CUBE に対して同じクエリセットを実行し、UNION ALL で実行したところ、ROLLUP と CUBE 機能を使用したほうがパフォーマンスが向上しました。

Cluster CUBE (run in ms) ROLLUP (run in ms) UNION ALL (run in ms)
5-node-Ra3-4XL 214 117 321
2-node-Ra3-4XL 392 391 543
2-node-Ra3-XLPLUS 793 461 932

クリーンアップ

リソースをクリーンアップするには、この記事の例に従って作成したテーブルとビューを削除します。

まとめ

この投稿では、Amazon Redshift に追加された新しい集計拡張機能の ROLLUP、CUBE、GROUPNG SETS について説明しました。 また、一般的なユースケース、実装例、パフォーマンス結果についても説明しました。 これらの新しい SQL 集計拡張機能を使用して既存の集計クエリを簡略化し、今後の開発でより単純で読みやすいクエリを構築するために使用できます。

著者について

Satesh Sonti はアトランタを拠点とする Senior Analytics Specialist Solutions Architect であり、エンタープライズデータプラットフォーム、データウェアハウス、分析ソリューションの構築を専門としています。 世界中の銀行や保険の顧客向けにデータ資産を構築し、複雑なデータプラットフォームプログラムを主導してきた16年以上の経験があります。

Yanzhu Ji は Amazon Redshift チームのプロダクトマネージャーです。 プロダクトマネージャーになる前は、Amazon Redshift チームでソフトウェアエンジニアとして働いていました。 彼女は、顧客向けの Amazon Redshift の機能を計画からリリースまで構築する方法について豊富な経験を持ち、常に顧客の要件を最優先事項として扱っています。 Yanzhu は私生活では、絵画、写真、テニスが好きです。

Dinesh Kumar は、データベース、データウェアハウス、アナリティクスの分野で10年以上の経験を持つデータベースエンジニアです。 仕事以外では、彼はさまざまな料理を試したり、家族や友人と時間を過ごしたりすることを楽しんでいます。

翻訳はソリューションアーキテクトの小役丸が担当しました。元記事はこちらです。