クエリ フォールディングの例

この記事では、クエリ フォールディングで考えられる 3 つの結果のそれぞれについて、いくつかのシナリオ例を示します。 また、クエリ フォールディング メカニズムを最大限に活用する方法と、クエリに与える可能性のある効果に関する提案もいくつか含まれています。

シナリオ

Azure Synapse Analytics SQL データベース用の Wide World Importers データベースを使用して、Power Query でクエリを作成し、fact_Sale テーブルに接続し、次のフィールドのみを含む過去 10 件の売上を取得するシナリオを考えてみましょう。

• 販売キー
• 顧客キー
• 請求書日付キー
• Description
• 数量

注

デモンストレーションの目的で、この記事では、Wide World Importers データベースを Azure Synapse Analytics に読み込むチュートリアルで説明されているデータベースを使用します。 この記事の主な違いは、 fact_Sale テーブルには 2000 年のデータのみが保持され、合計は 3,644,356 行です。

結果は、Azure Synapse Analytics ドキュメントのチュートリアルに従って得られる結果と正確には一致しない場合があります。この記事の目的は、クエリ フォールディングがクエリに与える可能性のある主要な概念と影響を紹介することです。

この記事では、異なるレベルのクエリ フォールディングで同じ出力を実現する 3 つの方法を紹介します。

• クエリフォールディングなし
• 部分クエリ フォールディング
• フル クエリ フォールディング

クエリの折りたたみの例がない

Important

非構造化データ ソースのみに依存するクエリや、CSV ファイルや Excel ファイルなどのコンピューティング エンジンがないクエリには、クエリ フォールディング機能がありません。 これは、Power Query が Power Query エンジンを使用して必要なすべてのデータ変換を評価することを意味します。

データベースに接続し、fact_Sale テーブルに移動した後、[ホーム] タブの [行の削減] グループ内にある [下位行の保持] 変換を選択します。

この変換を選択すると、新しいダイアログが表示されます。 この新しいダイアログでは、保持する行の数を入力できます。 この場合は、値 10 を入力し、[ OK] を選択します。

ヒント

この場合、この操作を実行すると、過去 10 件の売上の結果が得られます。 ほとんどのシナリオでは、テーブルに並べ替え操作を適用することで、最後に考慮される行を定義する、より明示的なロジックを提供することをお勧めします。

次に、[ホーム] タブの [列の管理] グループ内にある [列の選択] 変換を選択します。その後、テーブルから保持する列を選択し、残りの列を削除できます。

最後に、[列の 選択 ] ダイアログで、 Sale Key、 Customer Key、 Invoice Date Key、 Description、および Quantity 列を選択し、[ OK] を選択します。

次のコード サンプルは、作成したクエリの完全な M スクリプトです。

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Kept bottom rows" = Table.LastN(Navigation, 10),
    #"Choose columns" = Table.SelectColumns(
        #"Kept bottom rows",
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    )
in
    #"Choose columns""

クエリフォールディングが行われない: クエリの評価を理解する

Power Query エディターの [ 適用されたステップ ] で、[ 下位の行を保持 する] と [列の選択] のクエリ フォールディング インジケーターが、データ ソースの外部または Power Query エンジンによって評価されるステップとしてマークされていることに注意してください。

クエリの最後のステップである [ 列の選択] を右クリックし、[ クエリ プランの表示] オプションを選択できます。 クエリ プランの目的は、クエリの実行方法の詳細なビューを提供することです。 この機能の詳細については、「 クエリ プラン」を参照してください。

前の画像の各ボックスは ノードと呼ばれます。 ノードは、このクエリを実行するための操作の内訳を表します。 前の例の SQL Server や Value.NativeQuery ノードなど、データ ソースを表すノードは、クエリのどの部分がデータ ソースにオフロードされているかを表します。 残りのノード (この例では、前の図の四角形で強調表示されている Table.LastN と Table.SelectColumns ) は、Power Query エンジンによって評価されます。 これら 2 つのノードは、追加した 2 つの変換である [ 下の行を保持] と [列の選択] を表します。 残りのノードは、データ ソース レベルで発生する操作を表します。

データ ソースに送信された正確な要求を表示するには、 ノードで Value.NativeQuery] を選択します。

このデータ ソース要求は、データ ソースのネイティブ言語です。 この場合、その言語は SQL であり、このステートメントは、 fact_Sale テーブルのすべての行とフィールドに対する要求を表します。

このデータ ソース要求のコンサルティングは、クエリ プランが伝えようとしているストーリーをより深く理解するのに役立ちます。

• Sql.Database: このノードは、データ ソース アクセスを表します。 データベースに接続し、その機能を理解するためにメタデータ要求を送信します。
• Value.NativeQuery: クエリを実行するために Power Query によって生成された要求を表します。 Power Query は、ネイティブ SQL ステートメントのデータ要求をデータ ソースに送信します。 この場合、 fact_Sale テーブルのすべてのレコードとフィールド (列) を表します。 このシナリオでは、テーブルに何百万もの行が含まれており、関心が最後の 10 行のみであるため、このケースは望ましくありません。
• Table.LastN: Power Query は、 fact_Sale テーブルからすべてのレコードを受信すると、Power Query エンジンを使用してテーブルをフィルター処理し、最後の 10 行のみを保持します。
• Table.SelectColumns: Power Query は、 Table.LastN ノードの出力を使用し、テーブルから保持する特定の列を選択する Table.SelectColumns という新しい変換を適用します。

その評価のために、このクエリでは、 fact_Sale テーブルからすべての行とフィールドをダウンロードする必要がありました。 このクエリは、Power BI データフローの標準インスタンス (データフローへのデータの評価と読み込みを考慮) で処理するのに平均 6 分 1 秒かかりました。

部分的なクエリ フォールディングの例

データベースに接続し、 fact_Sale テーブルに移動したら、まずテーブルから保持する列を選択します。 [ホーム] タブの [列の管理] グループ内にある [列の選択] 変換を選択します。この変換は、テーブルから保持する列を明示的に選択し、残りの列を削除するのに役立ちます。

[ 列の選択 ] ダイアログで、 Sale Key、 Customer Key、 Invoice Date Key、 Description、列の Quantity を選択し、[ OK] を選択します。

次に、テーブルを並べ替えて、テーブルの下部に最後の売上を配置するロジックを作成します。 テーブルの主キーと増分シーケンスまたはインデックスである Sale Key 列を選択します。 列のコンテキスト メニューから、このフィールドのみを使用してテーブルを昇順で並べ替えます。

次に、テーブルのコンテキスト メニューを選択し、[ 下の行を保持] 変換を選択します。

[ 下の行を保持] に値 10 を入力し、[ OK] を選択します。

次のコード サンプルは、作成したクエリの完全な M スクリプトです。

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
    #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
    #"Kept bottom rows"

部分的なクエリフォールディングの例: クエリ評価を理解する

適用されたステップ ペインを確認すると、追加した最後の変換 ( Kept bottom rows) が、データ ソースの外部、つまり Power Query エンジンによって評価されるステップとしてマークされていることがクエリ フォールディング インジケーターに表示されていることがわかります。

クエリの最後のステップ ( Kept bottom rows) を右クリックし、[ クエリ プラン ] オプションを選択すると、クエリの評価方法を理解しやすくなります。

前の画像の各ボックスは ノードと呼ばれます。 ノードは、クエリを評価するために (左から右に) 発生する必要があるすべてのプロセスを表します。 これらのノードの中には、データ ソースで評価できるノードもあります。また、Table.LastN ステップで表現されるのノードのように、Power Query エンジンを使用して評価されるものもあります。

データ ソースに送信された正確な要求を表示するには、 ノードで Value.NativeQuery] を選択します。

この要求は、データ ソースのネイティブ言語で行われます。 この場合、その言語は SQL であり、このステートメントはすべての行に対する要求を表し、fact_Sale フィールドによって並べ替えられたSale Key テーブルの要求されたフィールドのみが含まれます。

このデータ ソース要求を参照すると、完全なクエリ プランが伝えようとしているストーリーをより深く理解するのに役立ちます。 ノードの順序は、データ ソースからデータを要求することによって開始されるシーケンシャル プロセスです。

• Sql.Database: データベースに接続し、その機能を理解するためにメタデータ要求を送信します。
• Value.NativeQuery: クエリを実行するために Power Query によって生成された要求を表します。 Power Query は、ネイティブ SQL ステートメントのデータ要求をデータ ソースに送信します。 この場合、すべてのレコードを表します。データベース内の fact_Sale テーブルからの要求されたフィールドのみが、 Sales Key フィールドの昇順で並べ替えられます。
• Table.LastN: Power Query は、 fact_Sale テーブルからすべてのレコードを受信すると、Power Query エンジンを使用してテーブルをフィルター処理し、最後の 10 行のみを保持します。

その評価のために、このクエリでは、 fact_Sale テーブルからすべての行と必須フィールドのみをダウンロードする必要がありました。 Power BI データフローの標準インスタンス (データフローへのデータの評価と読み込みを考慮) で処理するのに平均 3 分 4 秒かかりました。

完全なクエリ フォールディングの例

データベースに接続し、 fact_Sale テーブルに移動したら、まずテーブルから保持する列を選択します。 [ホーム] タブの [列の管理] グループ内にある [列の選択] 変換を選択します。この変換は、テーブルから保持する列を明示的に選択し、残りの列を削除するのに役立ちます。

[ 列の選択] で、 Sale Key、 Customer Key、 Invoice Date Key、 Description、および Quantity 列を選択し、[ OK] を選択します。

次に、テーブルを並べ替えて、テーブルの先頭に最後の売上を表示するロジックを作成します。 テーブルの主キーと増分シーケンスまたはインデックスである Sale Key 列を選択します。 列のコンテキスト メニューから降順にこのフィールドを使用してのみテーブルを並べ替えます。

次に、テーブルのコンテキスト メニューを選択し、[ 先頭行の保持 ] 変換を選択します。

[ 先頭行を保持する] に値 10 を入力し、[ OK] を選択します。

次のコード サンプルは、作成したクエリの完全な M スクリプトです。

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
    #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
    #"Kept top rows"

クエリの完全フォールディングの例: クエリ評価の理解

適用されたステップ ペインをチェックすると、追加した変換、[ 列の選択]、[ 並べ替えられた行]、および [上位の行を保持] がデータ ソースで評価されるステップとしてマークされているクエリ フォールディング インジケーターが表示されることに注意してください。

クエリの最後のステップである [ 上位の行を保持する] を右クリックし、 クエリ プランを読み取るオプションを選択できます。

'Sales Key' フィールドを使用して並べ替えられた 'fact_Sale' テーブルの上位 10 レコードの要求を表す 'Value.NativeQuery' 内にある SQL ステートメントのスクリーンショット。フィールドは 'Sale Key'、'Customer Key'、'Invoice Date Key'、'Description'、'Quantity' のみです。

この要求は、データ ソースのネイティブ言語で行われます。 この場合、その言語は SQL であり、このステートメントは、 fact_Sale テーブルのすべての行とフィールドに対する要求を表します。

このデータ ソース クエリを参照すると、完全なクエリ プランが伝えようとしているストーリーをより深く理解するのに役立ちます。

• Sql.Database: データベースに接続し、その機能を理解するためにメタデータ要求を送信します。
• Value.NativeQuery: クエリを実行するために Power Query によって生成された要求を表します。 Power Query は、ネイティブ SQL ステートメントのデータ要求をデータ ソースに送信します。 この場合、fact_Sale フィールドを使用して降順で並べ替えられた後の必須フィールドのみを含む、Sale Key テーブルの上位 10 レコードに対する要求のみを表します。

注

T-SQL 言語でテーブルの下の行を選択するために使用できる句はありませんが、テーブルの上位行を取得する TOP 句があります。

評価のために、このクエリでは 10 行のみがダウンロードされ、 fact_Sale テーブルから要求したフィールドのみがダウンロードされます。 このクエリは、Power BI データフローの標準インスタンス (データフローへのデータの評価と読み込みを考慮) で処理するのに平均 31 秒かかりました。

パフォーマンスの比較

クエリ フォールディングがこれらのクエリに与える影響をより深く理解するために、クエリを更新し、各クエリを完全に更新するのにかかる時間を記録し、それらを比較することができます。 わかりやすくするために、この記事では、サービス レベルとして DW2000c を使用して専用の Azure Synapse Analytics 環境に接続するときに、Power BI データフロー更新メカニズムを使用してキャプチャされた平均更新タイミングについて説明します。

各クエリの更新時間は次のとおりです。

Example	ラベル	時間 (秒)
クエリフォールディングなし	None	361
部分クエリ フォールディング	Partial	184
フル クエリ フォールディング	完全	31

多くの場合、データ ソースに完全に折り返されるクエリは、データ ソースに完全に折り返されない同様のクエリよりも優ています。 その理由はさまざまです。 これらの理由は、クエリが実行する変換の複雑さから、インデックスや専用コンピューティング、ネットワーク リソースなど、データ ソースで実装されるクエリの最適化まで多岐に及びます。 それでも、クエリ フォールディングが使用しようとする 2 つの特定の重要なプロセスがあり、両方のプロセスが Power Query に与える影響を最小限に抑えます。

• 転送中のデータ
• Power Query エンジンによって実行される変換

以降のセクションでは、前述のクエリでこれら 2 つのプロセスが及ぼす影響について説明します。

転送中のデータ

クエリが実行されると、最初の手順の 1 つとしてデータ ソースからデータをフェッチしようとします。 データ ソースからフェッチされるデータは、クエリ フォールディング メカニズムによって定義されます。 このメカニズムは、データ ソースにオフロードできるクエリのステップを識別します。

次の表に、データベースの fact_Sale テーブルから要求された行の数を示します。 この表には、データ ソースからそのようなデータを要求するために送信された SQL ステートメントの簡単な説明も含まれています。

Example	ラベル	要求された行	Description
クエリフォールディングなし	None	3644356	fact_Sale テーブルのすべてのフィールドとすべてのレコードに対する要求
部分クエリ フォールディング	Partial	3644356	すべてのレコードを要求しますが、fact_Sale フィールドで並べ替えられた後、Sale Key テーブルの必須フィールドのみを要求します
フル クエリ フォールディング	完全	10	fact_Sale フィールドで降順に並べ替えられた後、Sale Key テーブルの必須フィールドと TOP 10 レコードのみを要求する

データ ソースからデータを要求する場合、データ ソースは要求の結果を計算してから、要求元にデータを送信する必要があります。 コンピューティング リソースは既に説明されていますが、データ ソースから Power Query にデータを移動し、Power Query でデータを効果的に受信し、ローカルで発生する変換に備えるネットワーク リソースは、データのサイズによっては時間がかかる場合があります。

ショーケースの例では、Power Query では、クエリ フォールディングなしと部分的なクエリ フォールディングの例について、データ ソースから 360 万行を超える行を要求する必要がありました。 完全なクエリ フォールディングの例では、要求された行は 10 行のみです。 要求されたフィールドに関しては、「クエリフォールディングなし」の例では、テーブルからすべての利用可能なフィールドがリクエストされました。 部分クエリ フォールディングと完全なクエリ フォールディングの両方の例は、必要なフィールドに対してのみ要求を送信しました。

注意事項

大量のデータを含むクエリまたはテーブルにクエリ フォールディングを使用する増分更新ソリューションを実装することをお勧めします。 Power Query のさまざまな製品統合では、実行時間の長いクエリを終了するためのタイムアウトが実装されます。 また、一部のデータ ソースでは、実行時間の長いセッションでタイムアウトが実装され、サーバーに対して高価なクエリを実行しようとしています。 詳細情報: データフローでの増分更新とセマンティック モデルの増分更新の使用

Power Query エンジンによって実行される変換

この記事では、クエリ プランを使用して 、クエリ の評価方法をより深く理解する方法について説明しました。 クエリ プラン内では、Power Query エンジンによって実行された変換操作の正確なノードを確認できます。

次の表は、Power Query エンジンによって評価された以前のクエリのクエリ プランのノードを示しています。

Example	ラベル	Power Query エンジンの変換ノード
クエリフォールディングなし	None	Table.LastN、Table.SelectColumns
部分クエリ フォールディング	Partial	Table.LastN
フル クエリ フォールディング	完全	—

この記事で紹介する例では、完全なクエリ フォールディングの例では、必要な出力テーブルがデータ ソースから直接取得されるため、Power Query エンジン内で変換を行う必要はありません。 これに対し、他の 2 つのクエリでは、Power Query エンジンで何らかの計算を行う必要があります。 これら 2 つのクエリで処理する必要があるデータの量のため、これらの例のプロセスには、完全なクエリ フォールディングの例よりも時間がかかります。

変換は、次のカテゴリにグループ化できます。

演算子の種類	Description
遠隔	データ ソース ノードである演算子。 これらの演算子の評価は、Power Query の外部で行われます。
ストリーミング	演算子はパススルー演算子です。 たとえば、単純なフィルターを使用した Table.SelectRows は、通常、演算子を通過する際に結果をフィルター処理でき、データを移動する前にすべての行を収集する必要はありません。 Table.SelectColumns Table.ReorderColumnsは、このような演算子の他の例です。
フル スキャン	データがチェーン内の次の演算子に進む前に、すべての行を収集する必要がある演算子。 たとえば、データを並べ替えるには、Power Query ですべてのデータを収集する必要があります。 フル スキャン演算子の他の例としては、 Table.Group、 Table.NestedJoin、 Table.Pivotがあります。

ヒント

パフォーマンスの観点から見ると、すべての変換が同じであるわけではありませんが、ほとんどの場合、変換の数を減らした方が良いです。

考慮事項と提案

• Power Query のベスト プラクティスに記載されているように、新しいクエリを作成する際 のベスト プラクティスに従います。
• クエリの折りたたみインジケーターを使用して、クエリの折りたたみを妨げているステップを確認します。 必要に応じてそれらを並べ替えて、折りたたみを増やします。
• クエリ プランを使用して、特定の手順で Power Query エンジンで発生している変換を特定します。 手順を並べ替えて、既存のクエリを変更することを検討してください。 次に、クエリの最後のステップのクエリ プランをもう一度確認し、クエリ プランが前の手順よりも適切かどうかを確認します。 たとえば、新しいクエリ プランのノード数は前のプランよりも少なく、ほとんどのノードは "フル スキャン" ではなく "ストリーミング" ノードです。 折りたたみをサポートするデータ ソースの場合、 Value.NativeQuery およびデータ ソース アクセス ノード以外のクエリ プラン内のノードは、フォールドされなかった変換を表します。
• 使用可能な場合は、 ネイティブ クエリの表示 (または データ ソース クエリの表示) オプションを使用して、クエリをデータ ソースに折り返すことができます。 このオプションがステップで無効になっており、通常は有効にするソースを使用している場合は、クエリの折りたたみを停止するステップを作成しました。 このオプションをサポートしていないソースを使用している場合は、クエリ フォールディング インジケーターとクエリ プランに依存できます。
• クエリ診断ツールを使用して、コネクタでクエリ フォールディング機能を使用できる場合にデータ ソースに送信される要求をより深く理解します。
• 複数のコネクタを使用してソース化されたデータを結合すると、Power Query は、各データ ソースに定義されているプライバシー レベルに準拠しながら、できるだけ多くの作業を両方のデータ ソースにプッシュしようとします。
• データ プライバシー ファイアウォール エラーからクエリが実行されないように、 プライバシー レベル に関する記事をお読みください。
• 他のツールを使用して、データ ソースによって受信された要求の観点からクエリ フォールディングを確認します。 この記事の例に基づいて、Microsoft SQL Server Profiler を使用して、Power Query によって送信され、Microsoft SQL Server によって受信された要求を確認できます。
• 完全に折りたたまれたクエリに新しいステップを追加し、新しいステップもフォールドする場合、Power Query は、前の結果のキャッシュされたバージョンを使用するのではなく、新しい要求をデータ ソースに送信する可能性があります。 実際には、このプロセスにより、プレビューで予想よりも更新に時間がかかる少量のデータに対する一見単純な操作が発生する可能性があります。 この更新が長くなるのは、Power Query がデータのローカル コピーを処理するのではなく、データ ソースに再クエリを実行するためです。