TripPin パート 10 - 基本的なクエリ フォールディング

このマルチパート チュートリアルでは、Power Query 用の新しいデータ ソース拡張機能の作成について説明します。 このチュートリアルは順番に行う予定です。各レッスンは、前のレッスンで作成したコネクタに基づいて構築され、コネクタに新しい機能を段階的に追加します。

このレッスンでは、次の操作を行います。

• $top
• $skip
• $count
• $select
• $orderby

M 言語の強力な機能の 1 つは、変換作業を 1 つ以上の基になるデータ ソースにプッシュできることです。 この機能は クエリ フォールディング と呼ばれます (他のツールやテクノロジも、述語プッシュダウンまたはクエリ委任と同様の機能を参照します)。

OData.Feed や Odbc.DataSource などの組み込みのクエリ フォールディング機能を持つ M 関数を使用するカスタム コネクタを作成すると、コネクタは自動的にこの機能を無料で継承します。

このチュートリアルでは、 Table.View 関数の関数ハンドラーを実装することで、OData の組み込みのクエリ フォールディング動作をレプリケートします。 このチュートリアルのこの部分では、実装が 容易な ハンドラー (つまり、式の解析と状態の追跡を必要としないハンドラー) をいくつか実装します。

OData サービスが提供する可能性があるクエリ機能の詳細については、 OData v4 URL 規則に関するページを参照してください。

クエリの折りたたみの詳細については、Power Query のクエリ評価とクエリ フォールディングの概要に関するページを参照してください。

Table.View の使用

Table.View 関数を使用すると、カスタム コネクタでデータ ソースの既定の変換ハンドラーをオーバーライドできます。 Table.View の実装は、サポートされているハンドラーの 1 つ以上の関数を提供します。 ハンドラーが実装されていない場合、または評価中に error を返す場合、M エンジンは既定のハンドラーにフォールバックします。

カスタム コネクタで 、Web.Contents などの暗黙的なクエリ フォールディングをサポートしていない関数を使用する場合、既定の変換ハンドラーは常にローカルで実行されます。 接続している REST API がクエリの一部としてクエリ パラメーターをサポートしている場合、 Table.View では、変換作業をサービスにプッシュできる最適化を追加できます。

Table.View 関数には、次のシグネチャがあります。

Table.View(table as nullable table, handlers as record) as table

メイン データ ソース関数を包むように実装されています。 Table.View には 2 つの必須ハンドラーがあります。

• GetType: クエリ結果の予想される table type を返します。
• GetRows: データ ソース関数の実際の table 結果を返します。

最も簡単な実装は、次の例のようになります。

TripPin.SuperSimpleView = (url as text, entity as text) as table =>
    Table.View(null, [
        GetType = () => Value.Type(GetRows()),
        GetRows = () => GetEntity(url, entity)
    ]);

TripPinNavTableではなく、TripPin.SuperSimpleViewを呼び出すGetEntity関数を更新します。

withData = Table.AddColumn(rename, "Data", each TripPin.SuperSimpleView(url, [Name]), type table),

単体テストを再実行しても、関数の動作は変更されません。 この場合、 Table.View の実装は、単に GetEntityへの呼び出しを通過します。 変換ハンドラーを実装していない (まだ) ため、元の url パラメーターはそのまま残ります。

Table.View の初期実装

Table.View の以前の実装は単純ですが、あまり役に立ちません。 次の実装はベースラインとして使用されます。折りたたみ機能は実装されていませんが、それを実現するための準備された構造が含まれています。

TripPin.View = (baseUrl as text, entity as text) as table =>
    let
        // Implementation of Table.View handlers.
        //
        // We wrap the record with Diagnostics.WrapHandlers() to get some automatic
        // tracing if a handler returns an error.
        //
        View = (state as record) => Table.View(null, Diagnostics.WrapHandlers([
            // Returns the table type returned by GetRows()
            GetType = () => CalculateSchema(state),

            // Called last - retrieves the data from the calculated URL
            GetRows = () => 
                let
                    finalSchema = CalculateSchema(state),
                    finalUrl = CalculateUrl(state),

                    result = TripPin.Feed(finalUrl, finalSchema),
                    appliedType = Table.ChangeType(result, finalSchema)
                in
                    appliedType,

            //
            // Helper functions
            //
            // Retrieves the cached schema. If this is the first call
            // to CalculateSchema, the table type is calculated based on
            // the entity name that was passed into the function.
            CalculateSchema = (state) as type =>
                if (state[Schema]? = null) then
                    GetSchemaForEntity(entity)
                else
                    state[Schema],

            // Calculates the final URL based on the current state.
            CalculateUrl = (state) as text => 
                let
                    urlWithEntity = Uri.Combine(state[Url], state[Entity])
                in
                    urlWithEntity
        ]))
    in
        View([Url = baseUrl, Entity = entity]);

Table.View の呼び出しを見ると、handlers レコード (Diagnostics.WrapHandlers) の周囲に追加のラッパー関数があります。 このヘルパー関数は、診断モジュール ( 診断の追加 のレッスンで紹介) にあります。個々のハンドラーによって発生したエラーを自動的にトレースする便利な方法を提供します。

GetType関数とGetRows関数は、CalculateSchemaとCalculateUrlの 2 つの新しいヘルパー関数を使用するように更新されます。 現時点では、これらの関数の実装はかなり簡単です。 GetEntity関数によって以前に実行されたものの一部が含まれていることに注意してください。

最後に、View パラメーターを受け取る内部関数 (state) を定義していることに注意してください。 より多くのハンドラーを実装すると、内部 View 関数が再帰的に呼び出され、更新され、そのまま state 渡されます。

TripPinNavTable関数をもう一度更新し、TripPin.SuperSimpleViewの呼び出しを新しいTripPin.View関数の呼び出しに置き換えて、単体テストを再実行します。 新しい機能はまだありませんが、テストのための強固なベースラインが得られます。

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

クエリを折りたためることができない場合、M エンジンは自動的にローカル処理にフォールバックするため、 Table.View ハンドラーが正しく動作していることを検証するための追加の手順を実行する必要があります。

折りたたみ動作を検証する手動の方法は、Fiddler などのツールを使用して単体テストで行われる URL 要求を監視することです。 または、 TripPin.Feed に追加した診断ログによって、実行されている完全な URL が出力されます。これには、ハンドラーが追加する OData クエリ文字列パラメーターが含まれている 必要があります 。

クエリフォールディングを検証する自動化された方法は、クエリが完全に折りたたまれていない場合に単体テストの実行を強制的に失敗することです。 クエリが完全に折りたたまれていないときに単体テストが失敗するようにするには、プロジェクトのプロパティを開き、 折りたたみエラーのエラー を True に設定します。 この設定を有効にすると、ローカル処理を必要とするクエリで次のエラーが発生します。

We couldn't fold the expression to the source. Please try a simpler expression.

この変更をテストするには、1 つ以上のテーブル変換を含む新しい Fact を単体テスト ファイルに追加します。

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
)

このチュートリアルの残りのセクションでは、それぞれ新しい Table.View ハンドラーを追加します。 テスト 駆動開発 (TDD) アプローチを採用しています。ここで、最初に失敗した単体テストを追加してから、M コードを実装して解決します。

次のハンドラー セクションでは、ハンドラーによって提供される機能、OData の同等のクエリ構文、単体テスト、実装について説明します。 前に説明したスキャフォールディング コードを使用して、各ハンドラーの実装に次の 2 つの変更が必要です。

• レコードを更新するstate に追加します。
• CalculateUrlを変更して、stateから値を取得し、URL またはクエリ文字列パラメーターに追加します。

Table.FirstN と OnTake の処理

OnTake ハンドラーは、count パラメーターを受け取ります。これは、GetRowsから取得する行の最大数です。 OData 用語では、これを $top クエリ パラメーターに変換できます。

次の単体テストを使用します。

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
),
Fact("Fold $top 0 on Airports", 
    #table( type table [Name = text, IataCode = text, Location = record] , {} ), 
    Table.FirstN(Airports, 0)
),

これらのテストでは、両方とも Table.FirstN を使用して、最初の X 行数に対して結果セットをフィルター処理します。 (既定値) に設定されたFalseでこれらのテストを実行すると、テストは成功するはずですが、Fiddler を実行する (またはトレース ログを確認する) 場合は、送信する要求に OData クエリ パラメーターが含まれていないことに注意してください。

[折りたたみエラー時のエラー] を [True] に設定すると、テストはPlease try a simpler expression.エラーで失敗します。 このエラーを修正するには、の最初の OnTake ハンドラーを定義する必要があります。

OnTake ハンドラーは次のコードのようになります。

OnTake = (count as number) =>
    let
        // Add a record with Top defined to our state
        newState = state & [ Top = count ]
    in
        @View(newState),

CalculateUrl関数が更新され、Top レコードからstate値が抽出され、クエリ文字列に適切なパラメーターが設定されます。

// Calculates the final URL based on the current state.
CalculateUrl = (state) as text => 
    let
        urlWithEntity = Uri.Combine(state[Url], state[Entity]),

        // Uri.BuildQueryString requires that all field values
        // are text literals.
        defaultQueryString = [],

        // Check for Top defined in our state
        qsWithTop =
            if (state[Top]? <> null) then
                // add a $top field to the query string record
                defaultQueryString & [ #"$top" = Number.ToText(state[Top]) ]
            else
                defaultQueryString,

        encodedQueryString = Uri.BuildQueryString(qsWithTop),
        finalUrl = urlWithEntity & "?" & encodedQueryString
    in
        finalUrl

単体テストを再実行すると、アクセスしている URL に $top パラメーターが含まれていることに注意してください。 URL エンコードにより、 $top は %24topとして表示されますが、OData サービスは自動的に変換するのに十分なスマートです。

Table.Skip と OnSkip の処理の取り扱い

OnSkip ハンドラーは、OnTakeとよく似ています。 count パラメーターを受け取ります。これは、結果セットからスキップする行数です。 このハンドラーは、OData $skip クエリ パラメーターにうまく変換されます。

単体テスト:

// OnSkip
Fact("Fold $skip 14 on Airlines",
    #table( type table [AirlineCode = text, Name = text] , {{"EK", "Emirates"}} ), 
    Table.Skip(Airlines, 14)
),
Fact("Fold $skip 0 and $top 1",
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ),
    Table.FirstN(Table.Skip(Airlines, 0), 1)
),

実装：

// OnSkip - handles the Table.Skip transform.
// The count value should be >= 0.
OnSkip = (count as number) =>
    let
        newState = state & [ Skip = count ]
    in
        @View(newState),

CalculateUrlへの更新プログラムの適合を確認:

qsWithSkip = 
    if (state[Skip]? <> null) then
        qsWithTop & [ #"$skip" = Number.ToText(state[Skip]) ]
    else
        qsWithTop,

詳細については、「 Table.Skip」を参照してください。

OnSelectColumns を使用した Table.SelectColumns の処理

OnSelectColumns ハンドラーは、ユーザーが結果セットから列を選択または削除するときに呼び出されます。 ハンドラーは、選択する 1 つ以上の列を表すlist値のtextを受け取ります。

OData 用語では、この操作は $select クエリ オプションにマップされます。

列の折りたたみの選択の利点は、多数の列を含むテーブルを扱う場合に明らかになります。 $select演算子は、選択されていない列を結果セットから削除するため、クエリの効率が向上します。

単体テスト:

// OnSelectColumns
Fact("Fold $select single column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode"}), 1)
),
Fact("Fold $select multiple column", 
    #table( type table [UserName = text, FirstName = text, LastName = text],{{"russellwhyte", "Russell", "Whyte"}}), 
    Table.FirstN(Table.SelectColumns(People, {"UserName", "FirstName", "LastName"}), 1)
),
Fact("Fold $select with ignore column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode", "DoesNotExist"}, MissingField.Ignore), 1)
),

最初の 2 つのテストでは、 Table.SelectColumns で異なる数の列を選択し、 Table.FirstN 呼び出しを含めてテスト ケースを簡略化します。

3 番目のテストでは MissingField.Ignore オプションを使用します。このオプションは、結果セットに存在しない選択した列を無視するように M エンジンに指示します。 OnSelectColumns ハンドラーは、このオプションについて心配する必要はありません。M エンジンはそれを自動的に処理します (つまり、不足している列はcolumnsリストに含まれません)。

OnSelectColumnsの実装では、次の 2 つの処理が行われます。

• 選択した列の一覧を stateに追加します。
• 適切なテーブルの種類を設定できるように、 Schema 値を再計算します。

OnSelectColumns = (columns as list) =>
    let
        // get the current schema
        currentSchema = CalculateSchema(state),
        // get the columns from the current schema (which is an M Type value)
        rowRecordType = Type.RecordFields(Type.TableRow(currentSchema)),
        existingColumns = Record.FieldNames(rowRecordType),
        // calculate the new schema
        columnsToRemove = List.Difference(existingColumns, columns),
        updatedColumns = Record.RemoveFields(rowRecordType, columnsToRemove),
        newSchema = type table (Type.ForRecord(updatedColumns, false))
    in
        @View(state & 
            [ 
                SelectColumns = columns,
                Schema = newSchema
            ]
        ),

CalculateUrl は更新されて、状態から列の一覧を取得し、それを$select パラメーター用に区切り記号で結合します。

// Check for explicitly selected columns
qsWithSelect =
    if (state[SelectColumns]? <> null) then
        qsWithSkip & [ #"$select" = Text.Combine(state[SelectColumns], ",") ]
    else
        qsWithSkip,

OnSort での Table.Sort の処理

OnSort ハンドラーは、次の種類のレコードの一覧を受け取ります。

type [ Name = text, Order = Int16.Type ]

各レコードには、列の名前を示すName フィールドと、Order または Order.Descending と等しい フィールドが含まれています。

OData 用語では、この操作は $orderby クエリ オプションにマップされます。 $orderby構文の列名の後に、昇順または降順を示すascまたはdescが続きます。 複数の列で並べ替えると、値はコンマで区切られます。 columns パラメーターに複数の項目が含まれている場合は、表示順序を維持することが重要です。

単体テスト:

// OnSort
Fact("Fold $orderby single column",
    #table( type table [AirlineCode = text, Name = text], {{"TK", "Turkish Airlines"}}),
    Table.FirstN(Table.Sort(Airlines, {{"AirlineCode", Order.Descending}}), 1)
),
Fact("Fold $orderby multiple column",
    #table( type table [UserName = text], {{"javieralfred"}}),
    Table.SelectColumns(Table.FirstN(Table.Sort(People, {{"LastName", Order.Ascending}, {"UserName", Order.Descending}}), 1), {"UserName"})
)

実装：

// OnSort - receives a list of records containing two fields: 
//    [Name]  - the name of the column to sort on
//    [Order] - equal to Order.Ascending or Order.Descending
// If there are multiple records, the sort order must be maintained.
//
// OData allows you to sort on columns that do not appear in the result
// set, so we do not have to validate that the sorted columns are in our 
// existing schema.
OnSort = (order as list) =>
    let
        // This will convert the list of records to a list of text,
        // where each entry is "<columnName> <asc|desc>"
        sorting = List.Transform(order, (o) => 
            let
                column = o[Name],
                order = o[Order],
                orderText = if (order = Order.Ascending) then "asc" else "desc"
            in
                column & " " & orderText
        ),
        orderBy = Text.Combine(sorting, ", ")
    in
        @View(state & [ OrderBy = orderBy ]),

CalculateUrlの更新:

qsWithOrderBy = 
    if (state[OrderBy]? <> null) then
        qsWithSelect & [ #"$orderby" = state[OrderBy] ]
    else
        qsWithSelect,

GetRowCount での Table.RowCount の処理

実装している他のクエリ ハンドラーとは異なり、 GetRowCount ハンドラーは 1 つの値 (結果セットに予想される行数) を返します。 M クエリでは、通常、この値は Table.RowCount 変換の結果になります。

OData クエリの一部としてこの値を処理する方法には、いくつかの異なるオプションがあります。

• $count クエリ パラメーター。結果セット内の個別のフィールドとしてカウントを返します。
• /$count パスセグメントは、合計カウントのみをスカラー値として返します。

クエリ パラメーターアプローチの欠点は、クエリ全体を OData サービスに送信する必要があるということです。 カウントは結果セットの一部としてインラインで返されるため、結果セットのデータの最初のページを処理する必要があります。 このプロセスは、結果セット全体を読み取って行をカウントするよりも効率的ですが、おそらくあなたが望むよりも多くの作業です。

パスセグメントアプローチの利点は、結果として単一のスカラー値のみを受け取ることができる点です。 この方法により、操作全体の効率が大幅に向上します。 ただし、OData 仕様で説明されているように、/$countや$topなどの他のクエリ パラメーターを含める場合、$skip パス セグメントはエラーを返します。これにより、その有用性が制限されます。

このチュートリアルでは、パス セグメントアプローチを使用して GetRowCount ハンドラーを実装しました。 他のクエリ パラメーターが含まれている場合に発生するエラーを回避するために、他の状態値を確認し、見つかった場合は "実装されていないエラー" (...) を返しました。 Table.View ハンドラーからエラーを返すと、操作を折りたためることができないことが M エンジンに通知され、代わりに既定のハンドラーにフォールバックする必要があります (この場合は行の合計数をカウントします)。

まず、単体テストを追加します。

// GetRowCount
Fact("Fold $count", 15, Table.RowCount(Airlines)),

/$count パス セグメントは JSON 結果セットではなく単一の値 (プレーン/テキスト形式) を返すので、要求を行って結果を処理するための新しい内部関数 (TripPin.Scalar) も追加する必要があります。

// Similar to TripPin.Feed, but is expecting back a scalar value.
// This function returns the value from the service as plain text.
TripPin.Scalar = (url as text) as text =>
    let
        _url = Diagnostics.LogValue("TripPin.Scalar url", url),

        headers = DefaultRequestHeaders & [
            #"Accept" = "text/plain"
        ],

        response = Web.Contents(_url, [ Headers = headers ]),
        toText = Text.FromBinary(response)
    in
        toText;

その後、実装はこの関数を使用します ( stateに他のクエリ パラメーターが見つからない場合)。

GetRowCount = () as number =>
    if (Record.FieldCount(Record.RemoveFields(state, {"Url", "Entity", "Schema"}, MissingField.Ignore)) > 0) then
        ...
    else
        let
            newState = state & [ RowCountOnly = true ],
            finalUrl = CalculateUrl(newState),
            value = TripPin.Scalar(finalUrl),
            converted = Number.FromText(value)
        in
            converted,

CalculateUrlで/$count フィールドが設定されている場合、RowCountOnly関数が更新され、URL にstateが追加されます。

// Check for $count. If all we want is a row count,
// then we add /$count to the path value (following the entity name).
urlWithRowCount =
    if (state[RowCountOnly]? = true) then
        urlWithEntity & "/$count"
    else
        urlWithEntity,

新しい Table.RowCount 単体テストはこれで通過するはずです。

フォールバック ケースをテストするには、エラーを強制する別のテストを追加します。

最初に、 try 操作の結果で折りたたみエラーをチェックするヘルパー メソッドを追加します。

// Returns true if there is a folding error, or the original record (for logging purposes) if not.
Test.IsFoldingError = (tryResult as record) =>
    if ( tryResult[HasError]? = true and tryResult[Error][Message] = "We couldn't fold the expression to the data source. Please try a simpler expression.") then
        true
    else
        tryResult;

次に、 Table.RowCount と Table.FirstN の両方を使用してエラーを強制するテストを追加します。

// test will fail if "Fail on Folding Error" is set to false
Fact("Fold $count + $top *error*", true, Test.IsFoldingError(try Table.RowCount(Table.FirstN(Airlines, 3)))),

ここでの重要な注意事項は、操作がローカル (既定) ハンドラーにフォールバックするため、falseがTable.RowCountに設定されている場合に、このテストでエラーが返されるようになったということです。 [折りたたみエラー時のエラー] をtrueに設定してテストを実行すると、Table.RowCountが失敗し、テストが成功します。

Conclusion

コネクタに Table.View を実装すると、コードが大幅に複雑になります。 M エンジンはすべての変換をローカルで処理できるため、 Table.View ハンドラーを追加しても、ユーザーの新しいシナリオは有効になりませんが、より効率的な処理が行われます (また、ユーザーが満足する可能性があります)。 Table.View ハンドラーがオプションである主な利点の 1 つは、コネクタの下位互換性に影響を与えずに新しい機能を段階的に追加できることです。

ほとんどのコネクタでは、返される行数が制限されるため、実装する重要な (および基本的な) ハンドラーは OnTake (OData の $top に変換されます)。 Power Query エクスペリエンスでは、ナビゲーターとクエリ エディターでプレビューを表示するときに常にOnTake行の1000が実行されるため、ユーザーは大規模なデータ セットを操作するときにパフォーマンスが大幅に向上する可能性があります。