TripPin part 10 – Základné postupné posúvanie dotazov

Tento kurz s viacerými časťami sa zaoberá vytváraním nového rozšírenia zdroja údajov pre Power Query. Tento kurz sa má uskutočniť postupne – každá lekcia vychádza z konektora vytvoreného v predchádzajúcich lekciách a postupne pridáva nové možnosti do konektora.

V tejto lekcii:

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

Jednou zo výkonných funkcií jazyka M je schopnosť presúvať prácu s transformáciou do jedného alebo viacerých základných zdrojov údajov. Táto funkcia sa označuje ako Postupné posúvanie dotazov (iné nástroje alebo technológie odkazujú aj na podobnú funkciu ako Predikát Pushdown alebo Delegovanie dotazov).

Pri vytváraní vlastného konektora, ktorý používa funkciu M so vstavanými funkciami postupného posúvania dotazov, ako je napríklad OData.Feed alebo Odbc.DataSource, váš konektor túto funkciu automaticky zdedí bezplatne.

Tento kurz replikuje vstavané správanie pri postupnom posúvaní dotazov pre OData implementáciou obslužných programov funkcií pre funkciu Table.View . Táto časť kurzu implementuje niektoré jednoduchšie obslužné programy na implementáciu (t. j. tie, ktoré nevyžadujú analýzu výrazu a sledovanie stavu).

Ďalšie informácie o možnostiach dotazov, ktoré môže ponúknuť služba OData, nájdete v téme Konvencie URL adries OData v4.

Ďalšie informácie o postupnom posúvaní dotazov nájdete v téme Prehľad vyhodnocovania dotazov a postupného posúvania dotazov v doplnku Power Query.

Používanie funkcie Table.View

Funkcia Table.View umožňuje vlastnému konektoru prepísať predvolené obslužné programy transformácie zdroja údajov. Implementácia funkcie Table.View poskytne funkciu pre jeden alebo viacero podporovaných obslužných programov. Ak je obslužný program neimplementovaný alebo vráti error počas hodnotenia, nástroj M spadá späť do predvoleného obslužného programu.

Keď vlastný konektor používa funkciu, ktorá nepodporuje implicitné skladanie dotazov, ako je napríklad Web.Contents, predvolené obslužné programy transformácie sa vždy vykonávajú lokálne. Ak sa pripájate k rozhraniu REST API, ktoré ako súčasť dotazu podporuje parametre dotazu, funkcia Table.View umožňuje pridať optimalizácie, ktoré umožňujú presúvať do služby prácu transformácie.

Funkcia Table.View má nasledujúci podpis:

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

Vaša implementácia zalomí vašu hlavnú funkciu zdroja údajov. Pre Table.View existujú dva povinné obslužné programy:

• GetType— vráti očakávaný table type výsledok dotazu
• GetRows— vráti skutočný table výsledok funkcie zdroja údajov

Najjednoduchšia implementácia by bola podobná nasledujúcemu príkladu:

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

Skôr než aktualizujte funkciu tak TripPinNavTable , aby zavolala TripPin.SuperSimpleView GetEntity:

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

Ak opakovane spusťíte testy zariadenia, zistíte, že správanie funkcie sa nezmenilo. V tomto prípade implementácia Table.View jednoducho prechádza výzvou na GetEntityadresu . Keďže ste neimplementovali žiadne obslužné programy transformácie (zatiaľ), pôvodný url parameter zostáva nedotknutý.

Počiatočná implementácia funkcie Table.View

Vyššie uvedená implementácia funkcie Table.View je jednoduchá, ale nie veľmi užitočná. Nasledujúca implementácia sa používa ako váš základný plán . Neimplementuje žiadne funkcie postupného posúvania, ale má podpornú funkciu, ktorú je potrebné vykonať.

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

Ak sa pozriete na volanie funkcie Table.View, okolo záznamuDiagnostics.WrapHandlers sa zobrazí funkcia dodatočného obaluhandlers. Táto pomocná funkcia sa nachádza v module Diagnostika (ktorá bola predstavená v lekcii pridávania diagnostiky ) a poskytuje užitočný spôsob automatického sledovania všetkých chýb vzniknutých jednotlivými obslužnými programami.

Funkcie GetType a GetRows sa aktualizujú tak, aby používali dve nové pomocné funkcie –CalculateSchema a CalculateUrl. Implementácie týchto funkcií sú momentálne pomerne jednoduché – všimnite si, že obsahujú časti toho, čo funkcia predtým urobila GetEntity .

Nakoniec si všimnite, že definujete internú funkciu (View), ktorá akceptuje state parameter. Keď implementujete ďalšie obslužné programy, budú rekurzívne volať internú View funkciu, pričom aktualizujú a odovzdávajú state postupne podľa ich predstáv.

TripPinNavTable Znova aktualizujte funkciu nahradením volania TripPin.SuperSimpleView funkcie zavolaním novej TripPin.View funkcie a znova spustite testy zariadenia. Zatiaľ neuvidíte žiadne nové funkcie, ale teraz máte solídny základ na testovanie.

Implementácia postupného posúvania dotazov

Keďže nástroj jazyka M sa automaticky vráti k lokálnemu spracovaniu, keď sa dotaz nedá posunúť, musíte vykonať niekoľko ďalších krokov na overenie správneho fungovania obslužných programu Table.View .

Manuálnym spôsobom overenia správania pri postupnom posúvaní je sledovanie ŽIADOSTÍ o URL adresu, ktoré test zariadenia vykoná pomocou nástroja, ako je napríklad Fiddler. Diagnostické zapisovanie do denníka, ktoré ste pridali, TripPin.Feed prípadne vy emituje spustenú úplnú URL adresu, ktorá by mala obsahovať parametre reťazca dotazu OData, ktoré pridáte obslužný program.

Automatizovaným spôsobom overenia postupného posúvania dotazov je vynútiť, aby spustenie testu zariadenia zlyhalo, ak sa dotaz úplne nezbalí. Môžete to urobiť otvorením vlastností projektu a nastavením chyby pri zlyhaní skladania na hodnotu True. Keď je toto nastavenie povolené, každému dotazu, ktorý vyžaduje lokálne spracovanie, sa zobrazí nasledujúca chyba:

Výraz sa nepodarilo zložiť do zdroja. Skúste použiť jednoduchší výraz.

Môžete to otestovať pridaním nového Fact testovacieho súboru zariadenia, ktorý obsahuje jednu alebo viac transformácií tabuľky.

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

Ostatné časti tohto kurzu pridajú nový obslužný program Table.View . Používate prístup TDD (Test Driven Development), kde najprv pridáte neúspešné testy zariadenia a potom implementujete kód jazyka M na ich vyriešenie.

Nasledujúce časti obslužného programu popisujú funkciu poskytovanú obslužným programom, ekvivalentnú syntax dotazu OData, testy zariadenia a implementáciu. Každá implementácia obslužného programu vyžaduje dve zmeny pomocou podporného kódu opísaného vyššie:

• Pridanie obslužného programu do funkcie Table.View , ktorá záznam aktualizuje state .
• Úprava CalculateUrl na načítanie hodnôt z state tabuliek a pridanie k parametrom url adresy a/alebo reťazca dotazu.

Spracovanie funkcie Table.FirstN s funkciou OnTake

Obslužný OnTake count program dostane parameter, čo je maximálny počet riadkov, z GetRowsktorých sa má prijať. V hodnotách OData ich môžete preložiť do parametra $top dotazu.

Nasledujúce testy zariadenia:

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

V oboch týchto testoch sa na filtrovanie výsledku, ktorý je nastavený na prvý počet X riadkov, používajú Príkaz Table.FirstN . Ak ste tieto testy spustili s nastavením Chyba pri zlyhaní pri postupnom posúvaní nastavenou na False hodnotu (predvolené), testy by mali byť úspešné, ale ak spustíte aplikáciu Fiddler (alebo skontrolujete denníky sledovania), všimnite si, že odoslaná žiadosť neobsahuje žiadne parametre dotazu OData.

Ak nastavíte možnosť Chyba pri zlyhaní skladania na Truehodnotu , testy zlyhajú s chybou Please try a simpler expression. . Ak chcete túto chybu opraviť, musíte definovať svoju prvú obslužný program funkcie Table.View pre OnTake.

Obslužný OnTake program vyzerá ako nasledujúci kód:

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

Funkcia sa CalculateUrl aktualizuje a extrahuje Top hodnotu zo záznamu state a nastaví správny parameter v reťazci 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

Opätovne rozdeľte testy zariadenia, všimnite si, že URL adresa, ku ktorej teraz pristupujete, obsahuje $top parameter. Z dôvodu kódovania $top URL adresy sa zobrazuje ako %24top, ale služba OData je natoľko inteligentná, že ju automaticky skonvertuje.

Spracovanie funkcie Table.Skip s OnSkip

Obslužný OnSkip program je veľmi podobné OnTake. Dostane count parameter, čo je počet riadkov, ktoré sa majú vynechať z množiny výsledkov. Tento obslužný program sa pekne preloží na parameter dotazu OData $skip .

Testy zariadenia:

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

Vykonávanie:

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

Zodpovedajúce aktualizácie pre CalculateUrl:

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

Ďalšie informácie: Table.Skip

Spracovanie funkcií Table.SelectColumns pomocou vlastnosti OnSelectColumns

Obslužný program sa OnSelectColumns volá vtedy, keď používateľ vyberie alebo odstráni stĺpce z množiny výsledkov. Obslužný list program dostane množstvo text hodnôt, ktoré predstavujú jeden alebo viac stĺpcov, ktoré sa majú vybrať.

V podmienkach OData sa táto operácia primapuje k možnosti dotazu $select .

Výhoda výberu skladacieho stĺpca je zrejmá, keď rokujete s tabuľkami s mnohými stĺpcami. Operátor $select odstráni nevybrané stĺpce z množiny výsledkov, čo má za následok efektívnejšie dotazy.

Testy zariadenia:

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

V prvých dvoch testoch sa vyberie iný počet stĺpcov s tabuľkou Table.SelectColumns a zahrnie sa volanie Table.FirstN , ktoré zjednoduší testovací prípad.

Tretí test používa možnosť MissingField.Ignore , ktorá nástroju jazyka M povie ignorovať všetky vybraté stĺpce, ktoré v množine výsledkov neexistujú. Obslužný OnSelectColumns program si túto možnosť nemusí robiť starosti – nástroj M ju automaticky spracuje (čiže chýbajúce stĺpce nie sú v columns zozname zahrnuté).

Implementácia má OnSelectColumns dve veci:

• Pridá zoznam vybratých stĺpcov do statezoznamu .
• Prepočíta hodnotu, Schema aby ste mohli nastaviť správny typ tabuľky.

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 aktualizuje sa, aby sa načíta zoznam stĺpcov zo stavu, a skombinuje ich (s oddeľovačom) parametra $select .

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

Spracovanie funkcie Table.Sort s funkciou OnSort

Obslužný OnSort program dostáva zoznam záznamov typu:

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

Každý záznam obsahuje Name pole označujúce názov stĺpca a pole rovné parametru Order Order.Ascending alebo Order.Descending.

V podmienkach OData sa táto operácia primapuje k možnosti dotazu $orderby . Syntax $orderby obsahuje názov stĺpca, za ktorým nasleduje asc alebo desc ktorý označuje vzostupné alebo zostupné poradie. Pri zoraďovaní vo viacerých stĺpcoch sú hodnoty oddelené čiarkou. columns Ak parameter obsahuje viac ako jednu položku, je dôležité zachovať poradie, v ktorom sa zobrazujú.

Testy zariadenia:

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

Vykonávanie:

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

Aktualizácie na:CalculateUrl

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

Spracovanie funkcie Table.RowCount pomocou funkcie GetRowCount

Na rozdiel od iných obslužných programu dotazov, ktoré vykonávate, GetRowCount obslužný program vráti jednu hodnotu – počet riadkov očakávaných v množine výsledkov. V dotaze jazyka M by táto hodnota bola zvyčajne výsledkom transformácie Table.RowCount .

Na spracovanie tejto hodnoty ako súčasť dotazu OData máte niekoľko rôznych možností:

• Parameter dotazu $count, ktorý vráti počet ako samostatné pole v množine výsledkov.
• Segment cesty /$count, ktorý vracia iba celkový počet, ako skalárnu hodnotu.

Nevýhodou prístupu k parametru dotazu je, že stále musíte odoslať celý dotaz do služby OData. Keďže počet sa vráti ako súčasť množiny výsledkov, musíte spracovať prvú stranu údajov z množiny výsledkov. Hoci je tento proces ešte efektívnejší ako čítanie celej množiny výsledkov a počítanie riadkov, pravdepodobne je to ešte viac práce, ako chcete.

Výhodou prístupu segmentu cesty je, že vo výsledku získate len jednu skalárnu hodnotu. Vďaka tomuto prístupu je celá operácia oveľa efektívnejšia. Ako je však popísané v špecifikácii OData, segment cesty /$count vráti chybu, ak zahrniete iné parametre dotazu, ako $top napríklad alebo $skip, ktoré obmedzujú jeho užitočnosť.

V tomto kurze ste implementovali GetRowCount obslužný program pomocou prístupu segmentu cesty. Ak by ste sa chceli vyhnúť chybám, ktoré by sa zobrazili v prípade zahrnutia iných parametrov dotazu, skontrolovali ste iné hodnoty stavu a vrátili ste neimplementovanú chybu (...), ak nejaké našli. Vrátenie akejkoľvek chyby z obslužného programu Table.View znamená, že nástroj jazyka M znamená, že operáciu nie je možné postupne posúvať, a že by sa mala namiesto toho vrátiť k predvolenému obslužným programom (čo by v tomto prípade rátal celkový počet riadkov).

Najskôr pridajte test zariadenia:

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

/$count Keďže segment cesty vracia jednu hodnotu (vo formáte obyčajného alebo textového formátu) a nie množinu výsledkov JSON, musíte tiež pridať novú internú funkciu (TripPin.Scalar) na vytvorenie požiadavky a spracovanie 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;

Implementácia potom použije túto funkciu (ak sa v časti nenájdu žiadne iné parametre dotazu 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,

Ak CalculateUrl je pole nastavené v statepoli , funkcia sa aktualizuje a pripojí /$count sa k URL RowCountOnly adrese.

// 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 zariadenia by teraz mal prejsť.

Ak chcete otestovať záložný prípad, pridajte ďalší test, ktorý vynúti chybu.

Najskôr pridajte pomocnú metódu, ktorá skontroluje výsledok try operácie, či ide o chybu postupného posúvania.

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

Potom pridajte test, ktorý používa table.RowCount aj Table.FirstN na vynútenie chyby.

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

Dôležitou poznámkou je, že tento test teraz vráti chybu, ak je chyba pri postupnom posúvaní nastavená na falsehodnotu , pretože Table.RowCount operácia spadá späť do lokálneho (predvoleného) obslužného programu. Spustenie testov s chybou pri postupnej chybe nastavenou na true príčinu Table.RowCount zlyhania a umožňuje úspešné vykonanie testu.

Záver

Implementácia funkcie Table.View pre váš konektor pridáva do kódu značnú časť zložitosti. Keďže nástroj M môže spracovať všetky transformácie lokálne, pridanie obslužných programov Table.View nepovoľuje nové scenáre pre vašich používateľov, ale má za následok efektívnejšie spracovanie (a potenciálne aj šťastnejších používateľov). Jednou z hlavných výhod nepovinných obslužných programov Table.View je to, že umožňuje prírastkovo pridávať nové funkcie bez vplyvu na spätnú kompatibilitu pre konektor.

Pre väčšinu konektorov je OnTake na implementáciu dôležitý (a základný) obslužný program (čo sa prekladá do $top funkcie OData), pretože obmedzuje počet vrátených riadkov. Prostredie Power Query vykonáva pri zobrazovaní ukážok v navigátore a editore dotazov vždy riadky OnTake 1000 , takže používatelia môžu pri práci s väčšími množinami údajov vidieť výrazné vylepšenia výkonu.