TripPin část 10 – Základní posouvání dotazů

Poznámka:

Tento obsah aktuálně odkazuje na obsah ze starší implementace protokolů v sadě Visual Studio. Obsah se bude v blízké budoucnosti aktualizovat tak, aby zahrnoval novou sadu Power Query SDK v editoru Visual Studio Code.

Tento vícedílný kurz popisuje vytvoření nového rozšíření zdroje dat pro Power Query. Tento kurz se má provést postupně – každá lekce vychází z konektoru vytvořeného v předchozích lekcích a postupně přidává do konektoru nové funkce.

V této lekci:

• Seznamte se se základy posouvání dotazů.
• Informace o funkci Table.View
• Replikujte obslužné rutiny posouvání dotazů OData pro:

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

Jednou z výkonných funkcí jazyka M je schopnost odesílat transformace do jednoho nebo více podkladových zdrojů dat. Tato funkce se označuje jako posouvání dotazů (další nástroje nebo technologie také odkazují na podobnou funkci jako predikát pushdown nebo delegování dotazů).

Při vytváření vlastního konektoru, který používá funkci M s integrovanými možnostmi posouvání dotazů, jako jsou OData.Feed nebo Odbc.DataSource, váš konektor automaticky tuto funkci zdarma dědí.

Tento kurz replikuje předdefinované chování posouvání dotazů pro OData implementací obslužných rutin funkcí pro funkci Table.View . Tato část kurzu implementuje některé jednodušší obslužné rutiny k implementaci (to znamená těch, které nevyžadují analýzu výrazů a sledování stavu).

Další informace o možnostech dotazů, které může služba OData nabídnout, najdete v konvencích adres URL OData v4.

Poznámka:

Jak jsme uvedli dříve, funkce OData.Feed automaticky poskytuje možnosti posouvání dotazů. Vzhledem k tomu, že řada TripPin zachází se službou OData jako s běžným rozhraním REST API, bude nutné implementovat vlastní obslužné rutiny posouvání dotazů pomocí web.Contents a OData.Feed. Pro skutečné využití doporučujeme používat OData.Feed , kdykoli je to možné.

Další informace o posouvání dotazů najdete v Power Query v přehledu vyhodnocení dotazů a posouvání dotazů.

Použití Table.View

Funkce Table.View umožňuje vlastnímu konektoru přepsat výchozí obslužné rutiny transformace pro váš zdroj dat. Implementace Table.View poskytne funkci pro jeden nebo více podporovaných obslužných rutin. Pokud je obslužná rutina nekompimentovaná nebo vrací error během vyhodnocení, modul M se vrátí do výchozí obslužné rutiny.

Pokud vlastní konektor používá funkci, která nepodporuje implicitní posouvání dotazů, například Web.Contents, výchozí obslužné rutiny transformace se vždy provádějí místně. Pokud rozhraní REST API, ke kterému se připojujete, podporuje parametry dotazu jako součást dotazu, table.View umožňuje přidat optimalizace, které umožňují vložení transformační práce do služby.

Funkce Table.View má následující podpis:

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

Vaše implementace zabalí hlavní funkci zdroje dat. Pro Table.View existují dva požadované obslužné rutiny:

• GetType– vrátí očekávaný table type výsledek dotazu.
• GetRows– vrátí skutečný table výsledek funkce zdroje dat.

Nejjednodušší implementace by byla podobná následujícímu příkladu:

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

Aktualizujte TripPinNavTable funkci tak, aby volala TripPin.SuperSimpleView místo GetEntity:

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

Pokud testy jednotek spustíte znovu, uvidíte, že chování funkce se nezmění. V tomto případě implementace Table.View jednoduše prochází voláním GetEntity. Vzhledem k tomu, že jste ještě neimplementovali žádné obslužné rutiny transformace, zůstane původní url parametr nedotčený.

Počáteční implementace Table.View

Výše uvedená implementace Table.View je jednoduchá, ale není velmi užitečná. Následující implementace se používá jako základ – neimplementuje žádné skládací funkce, ale má vygenerování, které potřebujete udělat.

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

Pokud se podíváte na volání Table.View, uvidíte kolem záznamu další funkci obálky handlers –Diagnostics.WrapHandlers Tato pomocná funkce se nachází v modulu Diagnostika (která byla zavedena v lekci přidání diagnostiky ) a poskytuje užitečný způsob, jak automaticky sledovat všechny chyby vyvolané jednotlivými obslužnými rutinami.

Funkce GetType a GetRows funkce se aktualizují tak, aby využívaly dvě nové pomocné funkce –CalculateSchema a CalculateUrl. Právě teď jsou implementace těchto funkcí poměrně jednoduché – všimněte si, že obsahují části toho, co funkce dříve prováděla GetEntity .

Nakonec si všimněte, že definujete interní funkci (View), která přijímá state parametr. Při implementaci více obslužných rutin budou rekurzivně volat interní View funkci, aktualizovat a předávat dál state .

TripPinNavTable Znovu aktualizujte funkci, nahraďte voláním TripPin.SuperSimpleView nové TripPin.View funkce volání a znovu spusťte testy jednotek. Ještě neuvidíte žádné nové funkce, ale teď máte pevný směrný plán pro testování.

Implementace posouvání dotazů

Vzhledem k tomu, že modul M se automaticky vrátí k místnímu zpracování, když dotaz nejde přeložit, je nutné provést některé další kroky, abyste ověřili, že obslužné rutiny Table.View fungují správně.

Ručním způsobem, jak ověřit chování posouvání, je sledovat, jak adresa URL vyžaduje testy jednotek, které provádí pomocí nástroje, jako je Fiddler. Případně protokolování diagnostiky, které jste přidali pro TripPin.Feed generování úplné adresy URL, která by měla zahrnovat parametry řetězce dotazu OData, které vaše obslužné rutiny přidávají.

Automatizovaným způsobem, jak ověřit posouvání dotazů, je vynutit, aby provádění testů jednotek selhalo, pokud se dotaz plně nepřeloží. Můžete to provést otevřením vlastností projektu a nastavením chyby při posouvání selhání na hodnotu True. Když je toto nastavení povolené, má každý dotaz, který vyžaduje místní zpracování, následující chybu:

Výraz se nám nepovedlo přeložit do zdroje. Zkuste použít jednodušší výraz.

Můžete to otestovat přidáním nového Fact souboru testu jednotek, který obsahuje jednu nebo více transformací tabulky.

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

Poznámka:

Chyba při skládacím selhání je přístup typu "Vše nebo nic". Pokud chcete testovat dotazy, které nejsou navržené tak, aby se složily jako součást testů jednotek, budete muset přidat určitou podmíněnou logiku pro povolení nebo zakázání testů odpovídajícím způsobem.

Zbývající části tohoto kurzu přidají novou obslužnou rutinu Table.View . Používáte přístup TDD (Test Driven Development), kde nejprve přidáváte neúspěšné testy jednotek a pak implementujete kód M, který je vyřeší.

Následující části obslužné rutiny popisují funkce poskytované obslužnou rutinou, ekvivalentní syntaxi dotazu OData, testy jednotek a implementaci. Při použití kódu generování uživatelského rozhraní popsaného výše vyžaduje každá implementace obslužné rutiny dvě změny:

• Přidání obslužné rutiny do Table.View , která aktualizuje state záznam.
• Úprava CalculateUrl načtení hodnot z state adresy URL a/nebo parametrů řetězce dotazu.

Zpracování table.FirstN s ontake

Obslužná OnTake rutina count obdrží parametr, což je maximální počet řádků, ze GetRowskterých se má vzít . Pokud jde o OData, můžete to přeložit na parametr dotazu $top .

Použijete následující testy jednotek:

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

Oba testy používají Table.FirstN k filtrování podle výsledku nastaveného na první počet řádků X. Pokud tyto testy spustíte s chybou při skládání selhání nastavené na False (výchozí), testy by měly proběhnout úspěšně, ale pokud spustíte Fiddler (nebo zkontrolujete protokoly trasování), všimněte si, že požadavek, který odešlete, neobsahuje žádné parametry dotazu OData.

Pokud nastavíte chybu při skládání Selhání True , testy selžou s chybouPlease try a simpler expression.. Chcete-li tuto chybu opravit, je nutné definovat první obslužnou rutinu Table.View pro OnTake.

Obslužná rutina OnTake vypadá jako následující kód:

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

Funkce CalculateUrl se aktualizuje, aby extrahovali Top hodnotu ze záznamu state a nastavili správný parametr v řetězci dotazu.

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

Znovu spusťte testy jednotek, všimněte si, že adresa URL, ke které přistupujete, teď obsahuje $top parametr. Vzhledem k kódování adresy URL se $top zobrazí jako %24top, ale služba OData je dostatečně chytrá, aby ji automaticky převeďte.

Zpracování tabulky.Skip s OnSkip

Obslužná rutina OnSkip je hodně podobná OnTake. Obdrží count parametr, což je počet řádků, které se mají přeskočit ze sady výsledků. Tato obslužná rutina se pěkně překládá na parametr dotazu OData $skip .

Testy jednotek:

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

Implementace:

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

Odpovídající aktualizace CalculateUrl:

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

Další informace: Table.Skip

Zpracování table.SelectColumns pomocí OnSelectColumns

Obslužná rutina OnSelectColumns se volá, když uživatel vybere nebo odebere sloupce ze sady výsledků. Obslužná rutina list obdrží hodnotu text představující jeden nebo více sloupců, které se mají vybrat.

Ve výrazech OData se tato operace mapuje na možnost dotazu $select .

Výhody posouvání výběru sloupců se projeví, když pracujete s tabulkami s mnoha sloupci. Operátor $select odebere ze sady výsledků nevybrané sloupce, což vede k efektivnějším dotazům.

Testy jednotek:

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

První dva testy vyberou různé počty sloupců s table.SelectColumns a zahrnou volání Table.FirstN , které zjednoduší testovací případ.

Poznámka:

Pokud by test jednoduše vrátil názvy sloupců (pomocí Table.ColumnNames a ne žádná data, požadavek na službu OData se ve skutečnosti neodesílají. Důvodem je to, že volání GetType vrátí schéma, které obsahuje všechny informace, které modul M potřebuje k výpočtu výsledku.

Třetí test používá možnost MissingField.Ignore , která modulu M říká, aby ignoroval všechny vybrané sloupce, které v sadě výsledků neexistují. Obslužná OnSelectColumns rutina se nemusí starat o tuto možnost – modul M ji zpracuje automaticky (to znamená, že chybějící sloupce nejsou zahrnuté v columns seznamu).

Poznámka:

Druhá možnost pro Table.SelectColumns, MissingField.UseNull, vyžaduje konektor pro implementaci obslužné rutiny OnAddColumn . To se provede v další lekci.

Implementace pro OnSelectColumns dělá dvě věci:

• Přidá do seznamu statevybraných sloupců .
• Přepočítá Schema hodnotu, abyste mohli nastavit správný typ tabulky.

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 se aktualizuje, aby načetl seznam sloupců ze stavu a zkombinoval je (s oddělovačem) parametru $select .

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

Zpracování table.Sort pomocí OnSortu

Obslužná OnSort rutina obdrží seznam záznamů typu:

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

Každý záznam obsahuje Name pole označující název sloupce a Order pole, které se rovná Order.Ascending nebo Order.Descending.

Ve výrazech OData se tato operace mapuje na možnost dotazu $orderby . Syntaxe $orderby má název sloupce následovaný asc nebo desc označuje vzestupné nebo sestupné pořadí. Při řazení podle více sloupců jsou hodnoty oddělené čárkou. columns Pokud parametr obsahuje více než jednu položku, je důležité zachovat pořadí, ve kterém se zobrazují.

Testy jednotek:

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

Implementace:

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

Aktualizace naCalculateUrl:

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

Zpracování Table.RowCount pomocí GetRowCount

Na rozdíl od ostatních obslužných rutin dotazů, které implementujete, GetRowCount vrátí obslužná rutina jednu hodnotu – počet řádků očekávaných v sadě výsledků. V dotazu M by tato hodnota obvykle byla výsledkem transformace Table.RowCount .

Máte několik různých možností, jak tuto hodnotu zpracovat jako součást dotazu OData:

• Parametr dotazu $count, který vrátí počet jako samostatné pole v sadě výsledků.
• Segment cesty /$count, který vrátí pouze celkový počet jako skalární hodnotu.

Nevýhodou přístupu k parametru dotazu je, že stále potřebujete odeslat celý dotaz do služby OData. Vzhledem k tomu, že se počet vrátí jako součást sady výsledků, musíte zpracovat první stránku dat ze sady výsledků. I když je tento proces stále efektivnější než čtení celé sady výsledků a počítání řádků, pravděpodobně je to ještě více práce, než chcete udělat.

Výhodou přístupu segmentu cesty je, že ve výsledku obdržíte pouze jednu skalární hodnotu. Díky tomuto přístupu je celá operace mnohem efektivnější. Jak je však popsáno ve specifikaci OData, segment cesty /$count vrátí chybu, pokud zahrnete jiné parametry dotazu, například $top nebo $skip, které omezuje jeho užitečnost.

V tomto kurzu jste implementovali obslužnou rutinu GetRowCount pomocí přístupu segmentu cesty. Abyste se vyhnuli chybám, které byste získali, pokud jsou zahrnuté další parametry dotazu, zkontrolovali jste jiné hodnoty stavu a vrátili jste "neplementovanou chybu" (...), pokud jste nějaké našli. Vrácení jakékoli chyby z obslužné rutiny Table.View říká modulu M, že operaci nelze přeložit, a měla by se vrátit k výchozí obslužné rutině (v tomto případě by se počítá celkový počet řádků).

Nejprve přidejte test jednotek:

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

/$count Vzhledem k tomu, že segment cesty vrací jednu hodnotu (ve formátu prostého textu) místo sady výsledků JSON, musíte také přidat novou interní funkci (TripPin.Scalar) pro vytvoření požadavku a zpracování výsledku.

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

Implementace pak použije tuto funkci (pokud nejsou nalezeny žádné jiné parametry dotazu v 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,

Funkce CalculateUrl se aktualizuje tak, aby se připojila /$count k adrese URL, pokud RowCountOnly je pole nastaveno v objektu 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,

Nový Table.RowCount test jednotek by teď měl projít.

K otestování záložního případu přidáte další test, který vynutí chybu.

Nejprve přidejte pomocnou metodu, která zkontroluje výsledek try operace pro chybu skládání.

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

Pak přidejte test, který k vynucení chyby použije Table.RowCount i 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)))),

Tady je důležitá poznámka, že tento test nyní vrací chybu, pokud je chyba při posouvání chyba nastavena na false, protože Table.RowCount operace se vrátí zpět do místní (výchozí) obslužné rutiny. Spuštění testů s chybou při skládání chyb nastavených na true příčiny Table.RowCount selhání a umožňuje úspěšné testování.

Závěr

Implementace Table.View pro váš konektor přidává do kódu značné složitosti. Vzhledem k tomu, že modul M může zpracovávat všechny transformace místně, přidání obslužných rutin Table.View neumožňuje novým scénářům pro vaše uživatele, ale vede k efektivnějšímu zpracování (a potenciálně šťastnějším uživatelům). Jednou z hlavních výhod obslužných rutin Table.View , které jsou volitelné, je, že umožňuje přírůstkově přidávat nové funkce, aniž by to mělo vliv na zpětnou kompatibilitu konektoru.

U většiny konektorů je OnTake důležitou (a základní) obslužnou rutinou, která se má implementovat (což se překládá do $top OData), protože omezuje počet vrácených řádků. Prostředí Power Query při zobrazení náhledů v navigátoru a editoru dotazů vždy provádí OnTake 1000 řádky, takže uživatelé můžou při práci s většími sadami dat zaznamenat významná vylepšení výkonu.