分散クエリを使用したテナント間レポート

  • [アーティクル]

適用対象:Azure SQL Database

このチュートリアルでは、テナント データベース セット全体を対象に、レポート用の分散クエリを実行します。 これらのクエリによって、Wingtip Tickets SaaS アプリの日常業務データに埋もれている洞察を抽出できます。 これを行うには、追加のレポート データベースをカタログ サーバーにデプロイし、エラスティック クエリを使用して分散クエリを有効にします。

このチュートリアルで学習する内容は次のとおりです。

  • レポート データベースをデプロイする方法
  • すべてのテナント データベースにわたって分散クエリを実行する方法
  • 各データベースのグローバル ビューがテナント間の効率的なクエリを可能にする方法

このチュートリアルを完了するには、次の前提条件を満たしておく必要があります。

テナント間のレポート パターン

cross-tenant distributed query pattern

SaaS アプリケーションの使用による優れた機会の 1 つは、クラウドに保存されている膨大なテナント データを使用して、アプリケーションの操作と使用状況に関する洞察を得られることです。 このような洞察は、機能の開発、操作性の改良、アプリやサービスへのその他の投資に役立ちます。

単一のマルチテナント データベース内にあるこのデータにアクセスすることは簡単ですが、数千ものデータベースにわたる規模で分散されている場合は簡単ではありません。 1 つのアプローチはエラスティック クエリを使用するものであり、この方法では、分散した一連のデータベースにわたって共通スキーマでクエリを行えます。 これらのデータベースは、別のリソース グループとサブスクリプションに分散できますが、共通のログインを共有する必要があります。 エラスティック クエリは、分散 (テナント) データベース内のテーブルまたはビューをミラー化する外部テーブルが定義されている単一の "ヘッド" データベースを使用します。 このヘッド データベースに送信されたクエリはコンパイルされ、分散クエリ プランが生成されます。このクエリの一部は、必要に応じてテナント データベースにプッシュダウンされます。 エラスティック クエリでは、カタログ データベースのシャード マップを使用して、すべてのテナント データベースの場所を特定します。 ヘッド データベースのセットアップとクエリは、標準的な Transact-SQL を使用して簡単に行うことができ、Power BI や Excel などのツールからのクエリがサポートされます。

テナント データベース全体でクエリを分散することで、エラスティック クエリはライブ プロダクション データを短時間で洞察できます。 エラスティック クエリは潜在的に多数のデータベースからデータを引き出すため、1 つのマルチテナント データベースに同じようなクエリを送信する場合と比較して待機時間が長くなる可能性があります。 クエリは、ヘッド データベースに返されるデータを最小限に抑えるように設計します。 頻繁に使用される、あるいは複雑な分析クエリ/レポートとは対照的に、エラスティック クエリは多くの場合、少量のリアルタイム データの問い合わせに最適です。 クエリの動作に問題がある場合、実行プランを見て、クエリのどの部分がリモート データベースにプッシュダウンされ、どのくらいのデータが返されているかを確認してください。 複雑な集計や分析処理を必要とするクエリは、分析クエリのために最適化されたデータベースまたはデータ ウェアハウスにテナント データを抽出することで、効率的に処理できる可能性があります。 このパターンはテナント分析のチュートリアルで説明されています。

Wingtip Tickets SaaS Database Per Tenant アプリケーション スクリプトを入手する

Wingtip Tickets SaaS マルチテナント データベースのスクリプトとアプリケーション ソース コードは、WingtipTicketsSaaS-DbPerTenant GitHub リポジトリで入手できます。 Wingtip Tickets SaaS スクリプトをダウンロードし、ブロックを解除する手順については、一般的なガイダンスに関する記事をご覧ください。

チケット売り上げデータを作成する

より興味深いデータ セットにクエリを実行する目的で、チケットジェネレーターを実行し、チケット売り上げデータを作成します。

  1. PowerShell ISE で、...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 スクリプトを開き、次の値を設定します。
    • $DemoScenario = 1, すべての会場のイベントのチケットを購入.
  2. F5 キーを押してスクリプトを実行し、チケットの売り上げを生成します。 スクリプトが実行されている間、このチュートリアルの手順を続行します。 チケット データは、アドホック分散クエリの実行セクションで照会され、チケットジェネレーターが完了するまで待機します。

グローバル ビューを詳しく知る

Wingtip Tickets SaaS Database Per Tenant アプリケーションでは、各テナントに 1 つのデータベースが指定されています。 そのため、データベース テーブルに含まれるデータは、単一テナントのパースペクティブにスコープを設定しています。 ただし、すべてのデータベースにクエリを実行するときは、エラスティック クエリがデータを、テナントによりシャードされた 1 つの論理データベースにデータが含まれるかのように処理できることが重要です。

このパターンをシミュレートするために、'グローバル' ビュー セットがテナント データベースに追加されます。このビューは、グローバルにクエリ実行される各テーブルにテナント ID を与えます。 たとえば、VenueEvents ビューは、計算した VenueId を Events テーブルから与えられた列に追加します。 同様に、VenueTicketPurchases と VenueTickets ビューは、それぞれのテーブルから与えられた計算済みの VenueId 列を追加します。 これらのビューは、VenueId 列が存在する場合、クエリを並列化して適切なリモート テナント データベースにそれらをプッシュダウンするために、エラスティック クエリによって使用されます。 これにより返されるデータの量が劇的に減り、クエリがたくさんあってもパフォーマンスが相当上がります。 これらのグローバル ビューはすべてのテナント データベースで事前作成されています。

  1. SSMS を開き、tenants1-<USER> サーバーに接続します。

  2. [データベース] を展開し、 [contosoconcerthall] を右クリックして、 [新しいクエリ] を選択します。

  3. 次のクエリを実行して、シングル テナント テーブルとグローバル ビューの違いを調べます。

    -- The base Venue table, that has no VenueId associated.
SELECT * FROM Venue

-- Notice the plural name 'Venues'. This view projects a VenueId column.
SELECT * FROM Venues

-- The base Events table, which has no VenueId column.
SELECT * FROM Events

-- This view projects the VenueId retrieved from the Venues table.
SELECT * FROM VenueEvents

これらのビューでは、VenueId は Venue 名のハッシュとして計算されましたが、一意の値はどのような方法で求めてもかまいません。 この方法は、カタログで使用するためにテナント キーを計算する方法に似ています。

Venues ビューの定義を調べるには:

  1. オブジェクト エクスプローラーで、 [contosoconcerthall]>[ビュー] の順に展開します。

    Screenshot shows the contents of the Views node, including four types of Venue d b o.

  2. [dbo.Venues] を右クリックします。

  3. [ビューをスクリプト化]>[新規作成]>[新しいクエリ エディター ウィンドウ] の順に選択します。

他の Venue ビューをスクリプト化し、VenueId の追加方法を確認します。

分散クエリに使用されるデータベースをデプロイする

この演習では、adhocreporting データベースをデプロイします。 これは、すべてのテナント データベースに対してクエリを実行する場合に使用されるスキーマが含まれるヘッド データベースです。 このデータベースは既存のカタログ サーバーにデプロイされます。サンプル アプリのすべての管理関連データベースに使用されるサーバーです。

  1. PowerShell ISE で ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 を開きます。

  2. $DemoScenario = 2, "アドホック レポート データベースをデプロイする" を設定します。

  3. F5 キーを押してスクリプトを実行し、adhocreporting データベースを作成します。

次のセクションでは、分散クエリの実行に利用できるように、データベースにスキーマを追加します。

分散クエリを実行するための "ヘッド" データベースを構成する

この演習では、adhocreporting データベースにスキーマ (外部データ ソースと外部テーブル定義) を追加して、すべてのテナント データベースでクエリを実行できるようにします。

  1. SQL Server Management Studio を開き、前の手順で作成したアドホック レポート データベースに接続します。 データベースの名前は adhocreporting になります。

  2. SSMS で、...\Learning Modules\Operational Analytics\Adhoc Reporting\ Initialize-AdhocReportingDB.sql を開きます。

  3. SQL スクリプトを確認し、次の点に注意します。

    エラスティック クエリは、データベース スコープの資格情報を使用して、各テナント データベースにアクセスします。 この資格情報は、すべてのデータベースで使用できる必要があり、通常、これらのクエリを有効にするために必要な最小限の権限が付与されている必要があります。

    create credential

    カタログ データベースを外部データ ソースとして使用することで、クエリの実行時にカタログに登録されているすべてのデータベースにクエリが分散されます。 デプロイごとにサーバー名が異なるため、このスクリプトでは、スクリプトが実行される現在のサーバー (@@servername) からカタログ サーバーの場所を取得します。

    create external data source

    外部テーブルは、前のセクションで説明したグローバル ビューを参照します。DISTRIBUTION = SHARDED(VenueId) で定義されています。 各 VenueId は個々のデータベースにマッピングされるため、次のセクションで示すように、多くのシナリオでパフォーマンスが改善されます。

    create external tables

    作成され、データが設定されているローカル テーブル VenueTypes。 この参照データ テーブルは、すべてのテナント データベースで共通です。ここではローカル テーブルとして表すことができます。また、共通データを入力できます。 クエリによっては、このテーブルをヘッド データベースに定義すると、ヘッド データベースに移動する必要があるデータの量が減少する可能性があります。

    create table

    この方法で参照テーブルを追加する場合、テナント データベースを更新するときは必ず、テーブルのスキーマとデータを更新します。

  4. F5 キーを押してスクリプトを実行し、adhocreporting データベースを初期化します。

これで分散クエリを実行し、すべてのテナントを対象に洞察を収集できます。

分散クエリを実行する

これで adhocreporting データベースが設定されたので、分散クエリをいくつか実行します。 クエリ処理の場所をより良く理解できるように実行プランを含めます。

実行プランを調べるとき、プラン アイコンにカーソルを合わせると詳細が表示されます。

外部データ ソースの定義時に DISTRIBUTION = SHARDED(VenueId) を設定したことで、多くのシナリオでパフォーマンスが改善します。これは重要なことなのでご留意ください。 各 VenueId は個々のデータベースにマッピングされるため、フィルター処理はリモートで簡単に実行されます。必要なデータのみが返されます。

  1. SSMS で、...\Learning Modules\Operational Analytics\Adhoc Reporting\ Demo-AdhocReportingQueries.sql を開きます。

  2. adhocreporting データベースに接続していることを確認します。

  3. [クエリ] メニューを選択し、 [実際の実行プランを含める] をクリックします。

  4. [Which venues are currently registered?] クエリを強調表示し、F5 を押します。

    クエリは会場一覧全体を返します。すべてのテナントにクエリを実行し、各テナントからデータを返すことがいかに簡単かわかります。

    プランを調べ、コスト全体がリモート クエリであることを確認します。各テナント データベースがクエリをリモートで実行し、その会場情報をヘッド データベースに返しています。

    SELECT * FROM dbo.Venues

  5. 次のクエリを選択し、F5 を押します。

    このクエリは、テナント データベースとローカル VenueTypes テーブル (adhocreporting データベースのテーブルであり、ローカル) のデータを結合します。

    プランを調べ、コストの大半がリモート クエリであることを確認します。 各テナント データベースが会場情報を返し、ローカル VenueTypes テーブルでローカル結合を行い、フレンドリ名を表示します。

    Join on remote and local data

  6. 次に On which day were the most tickets sold? クエリを選択し、F5 を押します。

    このクエリは、少しばかり複雑な結合と集計を行います。 ほとんどの処理は、リモートで発生します。 各会場の 1 日のチケット販売数を含む行のみがヘッド データベースに返されます。

    query

次のステップ

このチュートリアルで学習した内容は次のとおりです。

  • すべてのテナント データベースにわたって分散クエリを実行する
  • レポート データベースをデプロイし、分散クエリを実行するために必要なスキーマを定義する

それでは、テナント分析チュートリアルをお試しください。個別の分析データベースにデータを抽出することで、より複雑な分析を処理できます。

その他のリソース