TripPin — część 10 — składanie zapytań podstawowych

Ten wieloczęściowy samouczek obejmuje tworzenie nowego rozszerzenia źródła danych dla dodatku Power Query. Samouczek ma być wykonywany sekwencyjnie — każda lekcja opiera się na łączniku utworzonym w poprzednich lekcjach, przyrostowo dodając nowe możliwości do łącznika.

W tej lekcji wykonasz następujące czynności:

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

Jedną z zaawansowanych funkcji języka M jest możliwość wypychania transformacji do co najmniej jednego bazowego źródła danych. Ta funkcja jest określana jako składanie zapytań (inne narzędzia/technologie używają również podobnych terminów, takich jak przekazywanie predykatów lub delegowanie zapytań).

Podczas tworzenia łącznika niestandardowego korzystającego z funkcji M z wbudowanymi możliwościami składania zapytań, takimi jak OData.Feed lub Odbc.DataSource, Twój łącznik automatycznie dziedziczy tę funkcjonalność bezkosztowo.

Ten samouczek replikuje wbudowane zachowanie składania zapytań dla usługi OData przez zaimplementowanie procedur obsługi funkcji dla funkcji Table.View . Ta część samouczka implementuje niektóre z łatwiejszych procedur obsługi (czyli tych, które nie wymagają analizowania wyrażeń i śledzenia stanu).

Aby dowiedzieć się więcej o możliwościach zapytań oferowanych przez usługę OData, przejdź do sekcji OData v4 URL Conventions (Konwencje adresów URL OData w wersji 4).

Przejdź do Omówienia oceny zapytań i składania zapytań w programie Power Query, aby uzyskać więcej informacji na temat składania zapytań.

Korzystanie z Table.View

Funkcja Table.View umożliwia łącznikowi niestandardowemu zastępowanie domyślnych procedur obsługi przekształceń dla źródła danych. Implementacja Table.View dostarcza funkcję dla co najmniej jednego z obsługiwanych handlerów. Jeśli program obsługi jest niezaimplementowany lub zwraca error podczas oceny, aparat M przełącza się na domyślną procedurę obsługi.

Gdy łącznik niestandardowy używa funkcji, która nie obsługuje składania niejawnych zapytań, takich jak Web.Contents, domyślne procedury obsługi przekształceń są zawsze wykonywane lokalnie. Jeśli interfejs API REST, z którym nawiązujesz połączenie, obsługuje parametry zapytania w ramach zapytania, funkcja Table.View umożliwia dodawanie optymalizacji, które umożliwiają wypchnięcie transformacji do usługi.

Funkcja Table.View ma następujący podpis:

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

Implementacja obejmuje główną funkcję źródła danych. Istnieją dwa wymagane programy obsługi dla tabeli.View:

• GetType: zwraca oczekiwany table type wynik zapytania.
• GetRows: zwraca rzeczywisty table wynik funkcji źródła danych.

Najprostsza implementacja będzie podobna do poniższego przykładu:

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

Zaktualizuj funkcję TripPinNavTable, aby wywołała TripPin.SuperSimpleView zamiast GetEntity.

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

Jeśli uruchomisz ponownie testy jednostkowe, zachowanie funkcji nie zostanie zmienione. W takim przypadku twoja implementacja Table.View po prostu przekazuje wywołanie do GetEntity. Ponieważ nie zaimplementowano jeszcze żadnych procedur obsługi transformacji, oryginalny url parametr pozostaje nietknięty.

Początkowa implementacja Table.View

Poprzednia implementacja tabeli.View jest prosta, ale nie jest bardzo przydatna. Poniższa implementacja jest używana jako bazowy punkt odniesienia — nie implementuje żadnej funkcjonalności składania, ale zawiera szkielet, który jest potrzebny do jej realizacji.

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

Jeśli przyjrzysz się wywołaniu funkcji Table.View, istnieje dodatkowa funkcja „wrapper” wokół rekordu handlers—Diagnostics.WrapHandlers. Ta funkcja pomocnicza znajduje się w module Diagnostyka (wprowadzonym w lekcji dotyczącej dodawania diagnostyki), i zapewnia przydatny sposób automatycznego śledzenia błędów zgłaszanych przez obsługiwane procedury.

Funkcje GetType i GetRows są aktualizowane w celu korzystania z dwóch nowych funkcji pomocnika —CalculateSchema i CalculateUrl. W tej chwili implementacje tych funkcji są dość proste. Zwróć uwagę, że zawierają one części tego, co zostało wcześniej wykonane przez GetEntity funkcję.

Na koniec zwróć uwagę, że definiujesz funkcję wewnętrzną (View), która akceptuje parametr state. Podczas implementacji większej ilości procedur obsługi, wywołują one rekursywnie wewnętrzną funkcję View, aktualizując i przekazując state w miarę postępu.

TripPinNavTable Ponownie zaktualizuj funkcję, zastępując wywołanie funkcji TripPin.SuperSimpleView wywołaniem nowej funkcji TripPin.View i ponownie uruchom testy jednostkowe. Nie ma jeszcze żadnych nowych funkcji, ale masz teraz solidny punkt odniesienia do testowania.

Implementowanie składania zapytań

Ponieważ silnik M automatycznie przełącza się na przetwarzanie lokalne, gdy nie można złożyć zapytania, należy wykonać kilka dodatkowych kroków, aby sprawdzić, czy obsługiwarki Table.View działają poprawnie.

Ręcznym sposobem walidacji zachowania składania jest śledzenie żądań URL generowanych przez testy jednostkowe za pomocą narzędzia takiego jak Fiddler. Alternatywnie dodane TripPin.Feed rejestrowanie diagnostyczne emituje uruchamiany pełny adres URL, który powinien zawierać parametry ciągu zapytania OData dodane przez programy obsługi.

Zautomatyzowanym sposobem sprawdzania poprawności składania zapytań jest wymuszanie niepowodzenia testu jednostkowego, jeśli zapytanie nie zostanie w pełni złożone. Aby test jednostkowy zakończył się niepowodzeniem, gdy zapytanie nie jest w pełni złożone, otwórz właściwości projektu i ustaw Błąd przy nieudanym składaniu na True. Po włączeniu tego ustawienia każde zapytanie wymagające lokalnego przetwarzania powoduje następujący błąd:

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

Możesz przetestować tę zmianę, dodając nowy element Fact do pliku testu jednostkowego, który zawiera jedną lub więcej transformacji tabel.

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

Pozostałe sekcje tego samouczka dodają nową procedurę obsługi Table.View . Wykonujesz podejście do programowania opartego na testach (TDD ), w którym najpierw dodasz testy jednostkowe zakończone niepowodzeniem, a następnie zaimplementujesz kod języka M, aby je rozwiązać.

W poniższych sekcjach obsługi opisano funkcje zapewniane przez program obsługi, równoważną składnię zapytań OData, testy jednostkowe i implementację. Korzystając z wcześniej opisanego kodu szkieletowego, każda implementacja modułu obsługi wymaga wprowadzenia dwóch zmian:

• Dodanie programu obsługi do obiektu Table.View , który aktualizuje state rekord.
• Modyfikowanie CalculateUrl w celu pobrania wartości z elementu state i dodania do parametrów adresu URL i/lub ciągu zapytania.

Zarządzanie funkcją Table.FirstN za pomocą OnTake

Mechanizm OnTake obsługi otrzymuje parametr count, który jest maksymalną liczbą wierszy do pobrania z GetRows. W kategoriach OData można to przetłumaczyć na parametr zapytania $top .

Używasz następujących testów jednostkowych:

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

Te dwa testy używają metody Table.FirstN, aby przefiltrować zestaw wyników do pierwszych X liczby wierszy. Jeśli uruchomisz te testy z ustawieniem Error on Folding Failure (wartość domyślna), testy powinny zakończyć się powodzeniem. Jednak jeśli uruchomisz program Fiddler (lub sprawdzisz dzienniki śledzenia), zwróć uwagę, że wysłane żądanie nie zawiera żadnych parametrów zapytania OData.

Jeśli ustawisz Błąd przy awarii składania na True, testy zakończą się niepowodzeniem z błędem Please try a simpler expression. . Aby naprawić ten błąd, należy zdefiniować pierwszą procedurę obsługi Table.View dla elementu OnTake.

Procedura OnTake obsługi wygląda podobnie do następującego kodu:

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

Funkcja CalculateUrl jest aktualizowana w celu wyodrębnienia Top wartości z rekordu state i ustawienia odpowiedniego parametru w ciągu zapytania.

// 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

Po ponownym uruchomieniu testów jednostkowych zwróć uwagę, że adres URL, do którego uzyskujesz dostęp, zawiera teraz parametr $top. Ze względu na kodowanie adresów URL $top jest wyświetlany jako %24top, ale usługa OData jest wystarczająco inteligentna, aby automatycznie go przekonwertować.

Obsługa funkcji Table.Skip przy użyciu narzędzia OnSkip

Procedura obsługi OnSkip jest podobna do OnTake. Otrzymuje count parametr, który jest liczbą wierszy do pominięcia z zestawu wyników. Ta procedura obsługi ładnie tłumaczy się na parametr zapytania OData $skip .

Testy jednostkowe:

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

Implementacja:

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

Dopasowywanie aktualizacji do elementu CalculateUrl:

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

Aby uzyskać więcej informacji, przejdź do strony Table.Skip.

Obsługa funkcji Table.SelectColumns za pomocą funkcji OnSelectColumns

Procedura obsługująca OnSelectColumns jest wywoływana, gdy użytkownik wybiera lub usuwa kolumny z zestawu wyników. Obsługiwacz odbiera wartości, listtext, reprezentujące jedną lub więcej kolumn do zaznaczenia.

W terminologii OData ta operacja mapuje na opcję zapytania $select.

Zaleta składania kolumn ujawnia się, gdy pracujesz z tabelami z wieloma kolumnami. Operator $select usuwa niezaznaczone kolumny z zestawu wyników, co skutkuje bardziej wydajnymi zapytaniami.

Testy jednostkowe:

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

Pierwsze dwa testy wybierają różne liczby kolumn z Table.SelectColumns i zawierają wywołanie Table.FirstN, aby uprościć testowy przypadek.

Trzeci test używa opcji MissingField.Ignore , która nakazuje aparatowi M ignorowanie wszystkich wybranych kolumn, które nie istnieją w zestawie wyników. Moduł OnSelectColumns nie musi się martwić o tę opcję — silnik M obsługuje ją automatycznie (czyli brakujące kolumny nie są uwzględniane na liście columns).

Implementacja dla OnSelectColumns wykonuje dwie rzeczy:

• Dodaje listę wybranych kolumn do elementu state.
• Ponownie oblicza Schema wartość, aby można było ustawić odpowiedni typ tabeli.

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 został zaktualizowany w celu pobrania listy kolumn ze stanu i połączenia ich (z separatorem) dla parametru $select.

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

Obsługa obiektu Table.Sort przy użyciu programu OnSort

Procedura obsługi OnSort otrzymuje listę rekordów typu:

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

Każdy rekord zawiera Name pole, wskazujące nazwę kolumny i Order pole, które jest równe Order.Ascending lub Order.Descending.

W terminologii OData operacja ta mapuje się na opcję zapytania $orderby. Składnia $orderby ma nazwę kolumny, po której następuje asc lub desc wskazująca kolejność rosnącą lub malejącą. Podczas sortowania na wielu kolumnach wartości są rozdzielane przecinkami. columns Jeśli parametr zawiera więcej niż jeden element, ważne jest zachowanie kolejności ich wyświetlania.

Testy jednostkowe:

// 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"})
)

Implementacja:

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

Aktualizacje elementu CalculateUrl:

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

Obsługa obiektu Table.RowCount za pomocą polecenia GetRowCount

W przeciwieństwie do innych procedur obsługi zapytań, które implementujesz, GetRowCount procedura obsługi zwraca pojedynczą wartość — liczbę wierszy oczekiwanych w zestawie wyników. W zapytaniu języka M ta wartość zazwyczaj będzie wynikiem przekształcenia Table.RowCount .

Istnieje kilka różnych opcji obsługi tej wartości w ramach zapytania OData:

• Parametr zapytania $count, który zwraca liczbę jako osobne pole w zestawie wyników.
• Segment ścieżki /$count, który zwraca tylko całkowitą liczbę jako wartość skalarną.

Wadą podejścia do parametru zapytania jest to, że nadal trzeba wysłać całe zapytanie do usługi OData. Ponieważ liczba jest zwracana bezpośrednio w ramach zestawu wyników, musisz przetworzyć pierwszą stronę wyników z tego zestawu. Chociaż ten proces jest nadal bardziej wydajny niż odczytywanie całego zestawu wyników i liczenie wierszy, prawdopodobnie jest to jeszcze więcej pracy niż chcesz wykonać.

Zaletą podejścia segmentu ścieżki jest to, że w wyniku otrzymujesz tylko jedną wartość skalarną. Takie podejście sprawia, że cała operacja jest o wiele wydajniejsza. Jednak zgodnie ze specyfikacją OData /$count segment ścieżki zwraca błąd, jeśli uwzględnisz inne parametry zapytania, takie jak $top lub $skip, co ogranicza jego przydatność.

W tym samouczku zaimplementowano obsługę GetRowCount przy użyciu podejścia segmentu ścieżki. Aby uniknąć błędów występujących w przypadku dołączenia innych parametrów zapytania, sprawdziłeś inne wartości stanu i zwróciłeś błąd "niezaimplementowany" ... w przypadku znalezienia jakiejkolwiek wartości. Zwrócenie jakiegokolwiek błędu z programu obsługi Table.View informuje silnik M, że operacja nie może być zrealizowana w zoptymalizowany sposób, i w takim wypadku powinien powrócić do obsługi domyślnej (co w tym przypadku oznacza zliczanie łącznej liczby wierszy).

Najpierw dodaj test jednostkowy:

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

/$count Ponieważ segment ścieżki zwraca pojedynczą wartość (w formacie zwykłego/tekstowego) zamiast zestawu wyników JSON, musisz również dodać nową funkcję wewnętrzną (TripPin.Scalar) do tworzenia żądania i obsługi wyniku.

// 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;

Implementacja następnie używa tej funkcji (jeśli nie znaleziono żadnych innych parametrów zapytania w obiekcie 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,

Funkcja CalculateUrl jest zaktualizowana, aby dołączyć /$count do adresu URL, jeśli pole RowCountOnly jest ustawione w elemencie 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,

Nowy Table.RowCount test jednostkowy powinien teraz przejść.

Aby przetestować przypadek rezerwowy, dodaj kolejny test wymuszający błąd.

Najpierw dodaj metodę pomocniczą, która sprawdza wynik operacji try pod kątem błędu składania.

// 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;

Następnie dodaj test, który używa obu tabel Table.RowCount i Table.FirstN , aby wymusić błąd.

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

Należy pamiętać, że ten test teraz zwraca błąd, jeśli ustawiono Błąd składania na false, ponieważ operacja Table.RowCount powraca do lokalnego (domyślnego) mechanizmu obsługi. Uruchomienie testów z Error on Folding Error ustawionym jako true powoduje, że Table.RowCount zawodzi, ale umożliwia pomyślne zakończenie testu.

Podsumowanie

Implementowanie elementu Table.View dla łącznika zwiększa złożoność kodu. Ponieważ silnik M może przetwarzać wszystkie przekształcenia lokalnie, dodanie obsługi Table.View nie włącza nowych scenariuszy dla użytkowników, ale może poprawić wydajność przetwarzania (i potencjalnie uczynić użytkowników szczęśliwszymi). Jedną z głównych zalet opcjonalności obsługi przez Table.View jest to, że możesz stopniowo dodawać nową funkcjonalność bez wpływu na kompatybilność wstecz dla swojego łącznika.

W przypadku większości łączników ważnym (i podstawowym) elementem do zaimplementowania jest OnTake (który odpowiada $top w OData), ponieważ ogranicza liczbę zwracanych wierszy. Środowisko dodatku Power Query zawsze wykonuje wiersze OnTake1000 podczas wyświetlania podglądów w nawigatorze i edytorze zapytań, dzięki czemu użytkownicy mogą zobaczyć znaczne ulepszenia wydajności podczas pracy z większymi zestawami danych.