TripPin del 10 – Grundläggande frågedelegering

Kommentar

Det här innehållet refererar för närvarande till innehåll från en äldre implementering för loggar i Visual Studio. Innehållet kommer att uppdateras inom en snar framtid för att täcka den nya Power Query SDK:n i Visual Studio Code.

Den här självstudien i flera delar beskriver hur du skapar ett nytt datakällans tillägg för Power Query. Självstudien är avsedd att utföras sekventiellt – varje lektion bygger på anslutningsappen som skapades i föregående lektioner och lägger stegvis till nya funktioner i anslutningsappen.

I den här lektionen kommer du att:

• Lär dig grunderna i frågedelegering
• Läs mer om funktionen Table.View
• Replikera OData-frågedelegeringshanterare för:

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

En av de kraftfulla funktionerna i M-språket är dess förmåga att skicka transformeringsarbete till en eller flera underliggande datakällor. Den här funktionen kallas frågedelegering (andra verktyg/tekniker refererar även till liknande funktion som Predicate Pushdown eller Frågedelegering).

När du skapar en anpassad anslutningsapp som använder en M-funktion med inbyggda frågedelegeringsfunktioner, till exempel OData.Feed eller Odbc.DataSource, ärver anslutningsappen automatiskt den här funktionen kostnadsfritt.

Den här självstudien replikerar det inbyggda frågedelegeringsbeteendet för OData genom att implementera funktionshanterare för funktionen Table.View . Den här delen av självstudien implementerar några av de enklare hanterare som ska implementeras (det vill säga de som inte kräver uttrycksparsning och tillståndsspårning).

Om du vill veta mer om de frågefunktioner som en OData-tjänst kan erbjuda går du till OData v4 URL-konventioner.

Kommentar

Som tidigare nämnts tillhandahåller funktionen OData.Feed automatiskt frågedelegeringsfunktioner. Eftersom TripPin-serien behandlar OData-tjänsten som ett vanligt REST-API, med web.contents i stället för OData.Feed, måste du implementera frågedelegeringshanterare själv. För verklig användning rekommenderar vi att du använder OData.Feed när det är möjligt.

Gå till Översikt över frågeutvärdering och frågedelegering i Power Query för mer information om frågedelegering.

Använda Table.View

Med funktionen Table.View kan en anpassad anslutningsapp åsidosätta standardtransformeringshanterare för datakällan. En implementering av Table.View tillhandahåller en funktion för en eller flera av de hanterare som stöds. Om en hanterare inte implementeras eller returnerar en error under utvärderingen återgår M-motorn till standardhanteraren.

När en anpassad anslutningsapp använder en funktion som inte stöder implicit frågedelegering, till exempel Web.Contents, utförs standardtransformeringshanterare alltid lokalt. Om REST-API:et som du ansluter till stöder frågeparametrar som en del av frågan kan du lägga till optimeringar som gör att transformeringsarbetet kan push-överföras till tjänsten.

Funktionen Table.View har följande signatur:

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

Implementeringen omsluter huvudfunktionen för datakällan. Det finns två nödvändiga hanterare för Table.View:

• GetType– returnerar det förväntade table type frågeresultatet
• GetRows– returnerar det faktiska table resultatet av datakällans funktion

Den enklaste implementeringen skulle likna följande exempel:

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

Uppdatera funktionen så att den TripPinNavTable anropar TripPin.SuperSimpleView i stället GetEntityför :

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

Om du kör enhetstesterna igen ser du att funktionens beteende inte ändras. I det här fallet skickar din Table.View-implementering helt enkelt genom anropet till GetEntity. Eftersom du inte har implementerat några transformeringshanterare (ännu) förblir den ursprungliga url parametern orörd.

Inledande implementering av Table.View

Ovanstående implementering av Table.View är enkel, men inte särskilt användbar. Följande implementering används som baslinje – den implementerar inte några vikningsfunktioner, men har den scaffolding du behöver för att göra det.

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

Om du tittar på anropet till Table.View ser du en extra omslutningsfunktion runt handlers posten –Diagnostics.WrapHandlers . Den här hjälpfunktionen finns i modulen Diagnostik (som introducerades i tilläggsdiagnostiklektionen) och ger dig ett användbart sätt att automatiskt spåra eventuella fel som genereras av enskilda hanterare.

Funktionerna GetType och GetRows uppdateras för att använda två nya hjälpfunktioner–CalculateSchema och CalculateUrl. Just nu är implementeringarna av dessa funktioner ganska enkla – observera att de innehåller delar av det som tidigare gjordes av GetEntity funktionen.

Observera slutligen att du definierar en intern funktion (View) som accepterar en state parameter. När du implementerar fler hanterare anropar de rekursivt den interna View funktionen, uppdaterar och skickar vidare state när de går.

TripPinNavTable Uppdatera funktionen igen, ersätt anropet till TripPin.SuperSimpleView med ett anrop till den nya TripPin.View funktionen och kör enhetstesterna igen. Du kommer inte att se några nya funktioner ännu, men du har nu en solid baslinje för testning.

Implementera frågedelegering

Eftersom M-motorn automatiskt återgår till lokal bearbetning när en fråga inte kan vikas, måste du vidta några extra åtgärder för att verifiera att table.View-hanterare fungerar korrekt.

Det manuella sättet att verifiera vikningsbeteendet är att titta på de URL-begäranden som enhetstesterna gör med hjälp av ett verktyg som Fiddler. Alternativt genererar diagnostikloggningen som du lade till TripPin.Feed den fullständiga URL:en som körs, vilket bör innehålla de OData-frågesträngsparametrar som dina hanterare lägger till.

Ett automatiserat sätt att verifiera frågedelegering är att tvinga enhetstestkörningen att misslyckas om en fråga inte helt viks. Du kan göra detta genom att öppna projektegenskaperna och ange Fel vid vikningsfel till Sant. Med den här inställningen aktiverad resulterar alla frågor som kräver lokal bearbetning i följande fel:

Det gick inte att vika uttrycket till källan. Prova ett enklare uttryck.

Du kan testa detta genom att lägga till en ny Fact i enhetstestfilen som innehåller en eller flera tabelltransformeringar.

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

Kommentar

Inställningen Fel vid vikningsfel är en "allt eller inget"-metod. Om du vill testa frågor som inte är utformade för att vikas som en del av enhetstesterna måste du lägga till viss villkorslogik för att aktivera/inaktivera tester i enlighet med detta.

De återstående avsnitten i den här självstudien lägger till en ny Table.View-hanterare . Du använder en TDD-metod (Test Driven Development), där du först lägger till misslyckade enhetstester och sedan implementerar M-koden för att lösa dem.

Följande hanteringsavsnitt beskriver funktionerna som tillhandahålls av hanteraren, OData-motsvarande frågesyntax, enhetstesterna och implementeringen. Med hjälp av den kod för byggnadsställningar som beskrevs tidigare kräver varje hanteringsimplementering två ändringar:

• Lägga till hanteraren i Table.View som uppdaterar posten state .
• CalculateUrl Ändrar för att hämta värdena från state och lägga till i url- och/eller frågesträngsparametrarna.

Hantera Table.FirstN med OnTake

OnTake Hanteraren tar emot en count parameter, vilket är det maximala antalet rader som ska tas från GetRows. I OData-termer kan du översätta detta till frågeparametern $top.

Du använder följande enhetstester:

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

Båda dessa tester använder Table.FirstN för att filtrera efter resultatuppsättningen till det första X-antalet rader. Om du kör dessa tester med Fel vid vikningsfel inställt False på (standard) bör testerna lyckas, men om du kör Fiddler (eller kontrollerar spårningsloggarna) bör du observera att begäran du skickar inte innehåller några OData-frågeparametrar.

Om du anger Fel vid vikningsfel till Truemisslyckas testerna med Please try a simpler expression. felet. För att åtgärda det här felet måste du definiera din första Table.View-hanterare för OnTake.

Hanteraren OnTake ser ut som följande kod:

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

Funktionen CalculateUrl uppdateras för att extrahera Top värdet från state posten och ange rätt parameter i frågesträngen.

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

Om du kör enhetstesterna igen ser du att url:en som du kommer åt nu innehåller parametern $top . På grund av URL-kodning $top visas som %24top, men OData-tjänsten är smart nog att konvertera den automatiskt.

Hantera Table.Skip med OnSkip

OnSkip Hanteraren är ungefär som OnTake. Den tar emot en count parameter, vilket är antalet rader som ska hoppa från resultatuppsättningen. Den här hanteraren översätter fint till frågeparametern OData $skip .

Enhetstester:

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

Implementering:

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

Matchande uppdateringar av CalculateUrl:

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

Mer information: Table.Skip

Hantera Table.SelectColumns med OnSelectColumns

OnSelectColumns Hanteraren anropas när användaren väljer eller tar bort kolumner från resultatuppsättningen. Hanteraren tar emot en list med text värden som representerar en eller flera kolumner som ska väljas.

I OData-termer mappas den här åtgärden till frågealternativet $select .

Fördelen med att vika kolumnval blir uppenbar när du hanterar tabeller med många kolumner. Operatorn $select tar bort omarkerade kolumner från resultatuppsättningen, vilket resulterar i effektivare frågor.

Enhetstester:

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

De två första testerna väljer olika antal kolumner med Table.SelectColumns och inkluderar ett Table.FirstN-anrop för att förenkla testfallet.

Kommentar

Om testet bara skulle returnera kolumnnamnen (med Table.ColumnNames och inte några data, skickas aldrig begäran till OData-tjänsten. Det beror på att anropet till GetType returnerar schemat, som innehåller all information som M-motorn behöver för att beräkna resultatet.

Det tredje testet använder alternativet MissingField.Ignore , som talar om för M-motorn att ignorera alla markerade kolumner som inte finns i resultatuppsättningen. OnSelectColumns Hanteraren behöver inte bekymra sig om det här alternativet – M-motorn hanterar det automatiskt (det vill: saknade kolumner ingår inte i columns listan).

Kommentar

Det andra alternativet för Table.SelectColumns, MissingField.UseNull, kräver en anslutningsapp för att implementera OnAddColumn hanteraren. Detta kommer att göras i en efterföljande lektion.

Implementeringen för OnSelectColumns gör två saker:

• Lägger till listan över markerade kolumner i state.
• Beräknar Schema om värdet så att du kan ange rätt tabelltyp.

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 uppdateras för att hämta listan över kolumner från tillståndet och kombinera dem (med en avgränsare) för parametern $select .

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

Hantera Table.Sort med OnSort

Hanteraren OnSort tar emot en lista med poster av typen:

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

Varje post innehåller ett Name fält som anger namnet på kolumnen och ett Order fält som är lika med Order.Ascending eller Order.Descending.

I OData-termer mappas den här åtgärden till frågealternativet $orderby . Syntaxen $orderby har kolumnnamnet följt av asc eller desc för att indikera stigande eller fallande ordning. När du sorterar efter flera kolumner avgränsas värdena med kommatecken. Om parametern columns innehåller mer än ett objekt är det viktigt att behålla den ordning som de visas i.

Enhetstester:

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

Implementering:

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

Uppdateringar till CalculateUrl:

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

Hantera Table.RowCount med GetRowCount

Till skillnad från de andra frågehanterare som du implementerar GetRowCount returnerar hanteraren ett enda värde – det antal rader som förväntas i resultatuppsättningen. I en M-fråga skulle det här värdet vanligtvis vara resultatet av transformering av Table.RowCount .

Du har några olika alternativ för hur du hanterar det här värdet som en del av en OData-fråga:

• Frågeparametern $count som returnerar antalet som ett separat fält i resultatuppsättningen.
• Sökvägssegmentet /$count, som endast returnerar det totala antalet, som ett skalärt värde.

Nackdelen med frågeparametermetoden är att du fortfarande behöver skicka hela frågan till OData-tjänsten. Eftersom antalet kommer tillbaka i rad som en del av resultatuppsättningen måste du bearbeta den första sidan med data från resultatuppsättningen. Även om den här processen fortfarande är effektivare än att läsa hela resultatuppsättningen och räkna raderna, är det förmodligen fortfarande mer arbete än du vill göra.

Fördelen med metoden för sökvägssegment är att du bara får ett enda skalärt värde i resultatet. Den här metoden gör hela åtgärden mycket mer effektiv. Enligt beskrivningen i OData-specifikationen returnerar dock sökvägssegmentet /$count ett fel om du inkluderar andra frågeparametrar, till exempel $top eller $skip, som begränsar dess användbarhet.

I den här självstudien GetRowCount implementerade du hanteraren med hjälp av metoden för sökvägssegment. Om du vill undvika de fel du får om andra frågeparametrar ingår, sökte du efter andra tillståndsvärden och returnerade ett "ogenomfört fel" (...) om du hittade några. Om du returnerar ett fel från en Table.View-hanterare meddelar du M-motorn att åtgärden inte kan vikas och att den bör återgå till standardhanteraren i stället (vilket i det här fallet skulle räkna det totala antalet rader).

Lägg först till ett enhetstest:

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

Eftersom sökvägssegmentet /$count returnerar ett enda värde (i oformaterad/textformat) i stället för en JSON-resultatuppsättning, måste du också lägga till en ny intern funktion (TripPin.Scalar) för att göra begäran och hantera resultatet.

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

Implementeringen använder sedan den här funktionen (om inga andra frågeparametrar finns i 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,

Funktionen CalculateUrl uppdateras så att den läggs /$count till i URL:en om fältet RowCountOnly anges i 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,

Det nya Table.RowCount enhetstestet bör nu godkännas.

Om du vill testa återställningsfallet lägger du till ytterligare ett test som tvingar fram felet.

Lägg först till en hjälpmetod som kontrollerar resultatet av en try åtgärd för ett vikningsfel.

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

Lägg sedan till ett test som använder både Table.RowCount och Table.FirstN för att framtvinga felet.

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

En viktig notering här är att det här testet nu returnerar ett fel om Fel vid vikningsfel är inställt falsepå , eftersom Table.RowCount åtgärden återgår till den lokala hanteraren (standard). Att köra testerna med Fel vid vikningsfel inställt på orsaker Table.RowCount till true att det misslyckas och gör att testet kan lyckas.

Slutsats

Implementering av Table.View för anslutningsappen lägger till en betydande mängd komplexitet i koden. Eftersom M-motorn kan bearbeta alla transformeringar lokalt möjliggör inte tillägg av Table.View-hanterare nya scenarier för dina användare, utan resulterar i effektivare bearbetning (och potentiellt lyckligare användare). En av de största fördelarna med att Table.View-hanterare är valfria är att du kan lägga till nya funktioner stegvis utan att påverka bakåtkompatibiliteten för anslutningsappen.

För de flesta anslutningsappar är OnTake en viktig (och grundläggande) hanterare att implementera (vilket översätts till $top i OData), eftersom det begränsar antalet rader som returneras. Power Query-upplevelsen utför alltid en OnTake rad 1000 när du visar förhandsgranskningar i navigatören och frågeredigeraren, så dina användare kan se betydande prestandaförbättringar när de arbetar med större datamängder.