Amazon QuickSight データセットパラメータを使用したクエリの最適化

SELECT *
FROM nytaxidata
WHERE pickupdate = <<$pPickupDate>>

SELECT *
FROM nytaxidata
WHERE pickupdate in (<<$pPickupDates>>)

データセットパラメータのユースケース

このセクションでは、データセットパラメータを使用する一般的なユースケースとその利点について説明します。

直接クエリで最適化されたカスタム SQL

データセットパラメータを使用することで、カスタム SQL で得られる柔軟性と、最適化された SQL で得られるパフォーマンスの両方のメリットを享受することができます。パラメータ化されたデータセットはロードされる際、比較的小さな結果セットにフィルタリングされます。作成者や閲覧者は、分析やダッシュボードの初期表示時、パラメータのデフォルト値を使用することで高速に読み込むことができます。さらに、後ほどダッシュボードのフィルターコントロールを使用しデータを細かく分析する際にもパラメータが適用されます。データ所有者としても、データセットによりバックエンドのデータベースに対する処理負荷を軽減し、スケーラビリティやパフォーマンスの向上を図りユーザーの同時実行性を上げることができます。
特に副問合せ内でデータをフィルタリングするようなネスト化されたクエリなど、複雑なカスタム SQL を含む直接クエリの場合、パフォーマンス向上効果はより明確になります。

分析全体で再利用可能な汎用データセット

データセットのパラメータにより、データセットをさまざまな分析で広く再利用できるようになり、データ所有者がデータセットを準備して管理する労力を軽減できます。 SPICE データセットでも直接クエリデータセットでも、データセットパラメータを使用することにより、計算フィールドの参照パラメータを分析からデータセット側に移植できます。データセット所有者が作成したパラメータについて、分析作成者は複数の分析ごとに都度参照する計算フィールド作成するのではなく、データセット内にある計算フィールドとして再利用できるようになりました。
パラメータに依存する計算フィールドを分析からデータセット側に移植するオプションを選択すると、データセットに計算フィールドを作成して複数の分析で再利用することができます。これはガバナンスを重視するユースケースで有効です。データセット所有者は、パラメータに依存する計算フィールドを分析から分離し、分析の作成者が計算フィールドを変更できないようにすることでビジネスロジックを保護できます。

静的変数によるデータセットの保守性向上

カスタム SQL や計算フィールドの複数箇所で静的な値（プレースホルダー）を参照するデータセットがある場合、データセットパラメータを作成し複数箇所で再利用できるようになりました。これによりコードの保守性が向上可能です。
（ただし、カスタム SQL へのパラメータ挿入は直接クエリでのみ可能である点に注意してください。）

ソリューション概要

このシナリオでは、まずデータセットパラメータなしでカスタム SQL 直接クエリデータセットを作成し、最適化されていない SQL が生成されることを確認します。そしてデータセットパラメータを使用しない場合、カスタム SQL がどのように実行されていくかをデモを通して観察します。次に、カスタム SQL を変更してデータセットパラメータを追加し、データセットパラメータを使用した場合に同じデータセットに対し、最適化されたクエリが生成されることを示します。
なお、この例では、データベースとして Amazon RDS for PostgreSQL を使用しますが、この機能は QuickSightで利用可能なその他のSQLベースのデータソースでも動作します。

分析パラメータを使用してデータをクエリする

データソース、データセット、分析をセットアップするには以下手順を実行します。ご自身のデータを使用する場合は、次のセクションに進んでください。

1. QuickSight のデータソースを作成します。
   次のスクリーンショットはデータソース接続の詳細を例示しています。
2. 直接クエリのカスタム SQL データセットを作成します。
   今回、NYC OpenData で公開されているニューヨークのタクシー乗車データの部分集合である約100万件のレコードをサンプルとして使用します。データは nytaxidata と命名された RDS for PostgreSQL データベース上のテーブルにロードされています。
   
3. 作成したデータセットを使いサンプルの分析を作成します。 ビジュアルからテーブルを選択し、いくつかの項目をフィールドリストから追加します。
   
4. 分析をリロードして、 PostgreSQL データベース上で生成されたクエリを確認します。
   以下の RDS Performance Insights のスクリーンショットに示されている通り、データセット全体が読み込まれていることが分かります（ select * from nytaxidata ）。
   
5. QuickSight の分析にパラメータにリンクされたフィルターコントロールを追加します。その上で、このフィルターコントロールの値を任意のものに変更します。
   
   データセットで定義したカスタムSQLは副問い合わせで使用され、Where句がありません。フィルター用のパラメータは引き続き主問合せ側の WHERE 句として使用されているため、カスタム SQL は副問合せとして結果セット全体をフェッチしてしまいます。カスタム SQL クエリではなく、データベーステーブルそのものをデータセットとして使用した場合は、そうならない可能性があります。テーブルを直接基にしたデータセットでは、パラメータ値は WHERE 句でデータベース側に引き渡されます。
   では、カスタム SQL データセットの WHERE 句にパラメータを含められない課題を克服するにはどうすればよいでしょうか。データセットパラメータを使えばいいのです！

データセットパラメータを使用してクエリを最適化

データセットパラメータを使用して、より最適化されたクエリをデータベースに送信できるシナリオをいくつか見てみましょう。

1. まずデータセットパラメータ（例： pDSfareamount ）を作成し、カスタム SQL の等号演算子を使用した WHERE 句に追加します。データベースに渡された SQL に変化がないか確認します。
   今回は、副問合せ（ select * from nytaxidata where fare_amount=0 ）の WHERE 句にデフォルト値を使用して最適化されたSQLが生成されていることが分かります。これにより、直接クエリデータセットのクエリパフォーマンスが向上します。
   

データセットパラメータと分析パラメータの紐づけ

データセットパラメータは分析パラメータに紐づけることもでき、ダッシュボードのインタラクションからユーザーが選択した値をデータセットパラメータに引き渡すことができます。
また単一の分析パラメータを複数のデータセットパラメータに紐づけることもできます。親となる分析パラメータをフィルターコントロールまたはアクションと連携し、カスタム SQL に基づき複数のデータセットをフィルタリングできるようになりました。

このセクションでは、データセットパラメータを分析パラメータに紐づけ、フィルターコントロールと関連付けします。

1. まず、分析パラメータを作成し、それをデータセットパラメータに紐づけます（これまでに作成したデータセットパラメータを使用します）。
   
2. これで分析パラメータ（この例では pAfareamount ） が作成されます。パラメータコントロールを使用し、分析またはダッシュボードからデータセットのパラメータ値を動的に変更するために、コントロールオブジェクト Fare Amount を作成します。 pAfareamount を QuickSight のフィルターと関連付けることで、データセットパラメータに値を動的に渡すことができます。パラメータコントロールの値を変更すると、バックエンドのデータベースに対し副問合せ内に WHERE 句が含まれる最適化された SQL が生成されます。
   

データセットパラメータのさらなる使用例

これまで等号演算子を使用したデータセットパラメータの使い方を見てきましたが、データセットパラメータを使用する別のシナリオをいくつか見てみましょう。

1. 次のスクリーンショットは、カスタム SQL 内で比較演算子を用いたデータセットパラメータの使用方法を示しています。
   
2. 次の例は、2 つのデータセットパラメータを BETWEEN 述語とともに使用する方法を示しています。
   
3. 次の例は、計算フィールド内でデータセットパラメータを使用する方法を示しています。
   
4. データセットパラメータはユーザー定義したスカラー関数（ UDF ）で使用することもできます。次の例では、 pickupdate をパラメータとして受け取り、 pickupdate が祝日かどうかに基づき 0 または 1 のフラグを返す is_holiday(pickupdate) というスカラー関数を定義しています。
   
5. さらに、データセットパラメータを使用して計算項目を導出することもできます。次の例では、実行時に指定された値と乗客数に基づき動的に surcharge_amount を計算しています。データセットパラメータを CASE 文とともに使用することで求めるべき surcharge_amount を導出していることが分かります。
   
6. 最後の例は、分析でパラメータを使用している計算を、データセット側に移動して再利用する手順を示しています。
   

データセットパラメータの制約

QuickSight でデータセットパラメータを操作する際に生じる可能性のある既知の制約は次の通りです（この原文記事の執筆時点）。

• データセットパラメータは SPICE に保存されているデータセットのカスタム SQL には挿入できません。
• 動的デフォルトは、データセットを使用している分析の分析ページでのみ設定できます。データセットのレベルでは動的デフォルトを設定することはできません。
• データセットパラメータに紐づけられている分析パラメータの複数値コントロールではすべて選択オプションがサポートされていません（ただし、ワークアラウンド はあります）。
• データセットパラメータではカスケードコントロールがサポートされていません。
• データセットパラメータは、データセットが直接クエリを使用している場合にのみ、データセットフィルターとして使用できます。
• ダッシュボード閲覧者がレポートを電子メールで送信するようスケジュールする場合、選択したコントロールは電子メールに添付されたレポート内のデータセットパラメータに反映されません。代わりにパラメータのデフォルト値が使用されます。

より詳細な情報はAmazon QuickSight でのデータセットパラメータの使用をご参照ください。