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

このマルチパート チュートリアルでは、Power Query 用の新しいデータ ソース拡張機能の作成について説明します。 このチュートリアルは、順次実行することを意図したものです。各レッスンでは、それまでのレッスンで作成したコネクタを基にして、コネクタに新しい機能を段階的に追加していきます。

このレッスンの内容:

  • クエリ フォールディングの基本を学習する
  • Table.View 関数について学習する
  • 次のものに対する OData クエリ フォールディング ハンドラーをレプリケートする:
  • $top
  • $skip
  • $count
  • $select
  • $orderby

M 言語の強力な機能の 1 つは、基になるデータ ソースに変換作業をプッシュする機能です。 この機能は クエリ フォールディング と呼ばれます (他のツールやテクノロジでは、同様の機能が述語プッシュダウンやクエリ委任と呼ばれることもあります)。 OData.FeedOdbc.DataSource など、クエリ フォールディング機能が組み込まれている M 関数を使用するカスタム コネクタを作成すると、この機能が自動的に無料でコネクタに継承されます。

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

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

注意

上記のように、OData.Feed 関数ではクエリ フォールディング機能が自動的に提供されます。 TripPin シリーズでは、OData サービスが OData.Feed ではなく Web.Contents を使用する通常の REST API として扱われているので、クエリ フォールディング ハンドラーを自分で実装する必要があります。 実際に使用する場合は、可能な限り OData.Feed を使用することをお勧めします。

M でのクエリ フォールディングについて詳しくは、Table.View のドキュメントをご覧ください。

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)
    ]);

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

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 が追加されていることがわかります。 このヘルパー関数は (前のチュートリアルで紹介した) Diagnostics モジュールの中にあり、個々のハンドラーで発生したエラーを自動的にトレースする便利な手段を提供します。

関数 GetTypeGetRows は、2 つの新しいヘルパー関数 CalculateSchemaCaculateUrl を使用するように更新されています。 現在、これらの関数の実装は非常に簡単です。以前は GetEntity 関数によって行われていた処理の一部が含まれていることがわかります。

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

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

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

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

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

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

ソースに式をフォールディング処理することができませんでした。より簡単な式でもう一度お試しください。

これをテストするには、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)
)

注意

[Error on Folding Failure](フォールディング失敗でエラー) の設定は、"オール オア ナッシング" のアプローチです。 単体テストの一部として、フォールディングを行うように設計されていないクエリをテストする場合は、必要に応じてテストを有効または無効にする条件付きロジックを追加する必要があります。

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

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

  • state レコードを更新するハンドラーを、Table.View に追加します。
  • state から値を取得し、URL とクエリ文字列のパラメーターの一方または両方に追加するように、CalculateUrl を変更します。

OnTake での Table.FirstN の処理

OnTake ハンドラーは、count パラメーターを受け取ります。これは、取得する行の最大数です。 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 行にフィルター処理します。 [Error on Folding Failure](フォールディング失敗でエラー)False (既定値) に設定してこれらのテストを実行すると、テストは成功しますが、Fiddler を実行すると (またはトレース ログを調べると)、送信した要求に OData クエリ パラメーターが含まれていないことがわかります。

診断トレース。

[Error on Folding Failure](フォールディング失敗でエラー)True に設定すると、"より簡単な式でもう一度お試しください" で失敗します。 " というエラーが発生する場合があります。 これを解決するには、OnTake に対して最初の Table.View ハンドラーを定義します。

この OnTake ハンドラーは次のようなものです。

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

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

// 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 サービスによって自動的に変換されます)。

top での診断トレース。

OnSkip での Table.Skip の処理

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,

OnSelectColumns での Table.SelectColumns の処理

ユーザーが結果セットから列を選択または削除すると、OnSelectColumns ハンドラーが呼び出されます。 このハンドラーは、選択する列を表す text 値の list を受け取ります。 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 の呼び出しが含まれます。

注意

(Table.ColumnNames を使用して) 列名だけを返し、データは返さないテストの場合、OData サービスへの要求は実際には送信されません。 これは、GetType の呼び出しによってスキーマが返され、M エンジンが結果を計算するために必要なすべての情報がそれに含まれるためです。

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

注意

Table.SelectColumns のもう 1 つのオプション MissingField.UseNull では、OnAddColumn ハンドラーを実装するためのコネクタが必要です。 これについては、後のレッスンで行います。

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
            ]
        ),

state から列のリストを取得し、それら (および区切り記号) を $select パラメーターに結合するように、CalculateUrl が更新されています。

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

OnSort での Table.Sort の処理

OnSort ハンドラーは、record 値の list を受け取ります。 各レコードには、列の名前を示す Name フィールドと、Order.Ascending または Order.Descending と等しい Order フィールドが含まれます。 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 クエリの一部としてこれを処理する方法には、いくつかの異なるオプションがあります。

クエリ パラメーターの方法の欠点は、やはりクエリ全体を OData サービスに送信する必要がある点です。 カウントは結果セットの一部としてインラインで返されるため、結果セットのデータの最初のページを処理する必要があります。 それでも、結果セット全体を読み取って行をカウントするよりは効率的ですが、必要以上に作業が多くなる可能性があります。

パス セグメントの方法の利点は、結果で受け取るのが 1 つのスカラー値のみであることです。 これにより、操作全体が非常に効率的になります。 ただし、OData の仕様で説明されているように、$top$skip などの他のクエリ パラメーターを含めると /$count パス セグメントからエラーが返されるため、その便利さが制限されます。

このチュートリアルでは、パス セグメントの方法を使用して 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 関数は、stateRowCountOnly フィールドが設定されている場合は URL に /$count を追加するように更新されています。

// 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.RowCountTable.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)))),

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

まとめ

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

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