TripPin deel 10: — Eenvoudige query folding

In deze meerdelige zelfstudie wordt het maken van een nieuwe gegevensbronextensie voor Power Query. De zelfstudie is bedoeld om opeenvolgend te worden uitgevoerd voor elke les die is gebaseerd op de connector die in de vorige lessen is gemaakt, en incrementeel nieuwe mogelijkheden toe te voegen — aan uw connector.

In deze les gaat u het volgende doen:

  • Meer informatie over de basisbeginselen van het vouwen van query's
  • Meer informatie over de Table.View functie
  • Repliceer OData Query Folding-handlers voor:
  • $top
  • $skip
  • $count
  • $select
  • $orderby

Een van de krachtige functies van de M-taal is de mogelijkheid om transformatiewerk naar onderliggende gegevensbron(s) te pushen. Deze mogelijkheid wordt Query Folding genoemd (andere hulpprogramma's/technologieën verwijzen ook naar een vergelijkbare functie als Predicate Pushdown of QueryDelegering). Wanneer u een aangepaste connector maakt die gebruikmaakt van een M-functie met ingebouwde mogelijkheden voor het vouwen van query's, zoals of , neemt uw connector deze functie OData.Feed Odbc.DataSource automatisch gratis over.

In deze zelfstudie wordt het ingebouwde gedrag voor het vouwen van query's voor OData gerepliceerd door functie-handlers voor de functie te Table.View implementeren. In dit deel van de zelfstudie worden enkele eenvoudigere handlers geïmplementeerd (dat wil zeggen, die waarvoor geen expressieparsering en statustracking is vereist).

Zie OData v4 URL Conventions (OData v4 URL-conventies)voor meer informatie over de querymogelijkheden die een OData-service kan bieden.

Notitie

Zoals hierboven vermeld, biedt de functie automatisch mogelijkheden voor het vouwen OData.Feed van query's. Omdat de TripPin-serie de OData-service als een normale REST API behandelt, moet u de handlers voor het vouwen van Web.Contents query's zelf implementeren met behulp van in plaats van OData.Feed . Voor het werkelijke gebruik wordt u aangeraden waar mogelijk OData.Feed te gebruiken.

Zie de table.View-documentatie voor meer informatie over het vouwen van query's in M.

Table.View gebruiken

Met de functie Table.View kan een aangepaste connector standaardtransformatie-handlers voor uw gegevensbron overschrijven. Een implementatie van Table.View biedt een functie voor een of meer ondersteunde handlers. Als een handler niet isimplementeerd of een retourneert tijdens de evaluatie, wordt de M-engine terugvallen error op de standaard-handler.

Wanneer een aangepaste connector gebruikmaakt van een functie die geen ondersteuning biedt voor impliciet vouwen van query's, zoals , worden Web.Contents standaardtransformatie-handlers altijd lokaal uitgevoerd. Als de REST API waarmee u verbinding maakt queryparameters ondersteunt als onderdeel van de query, kunt u optimalisaties toevoegen waarmee transformatiewerk naar de service kan Table.View worden pushen.

De Table.View functie heeft de volgende handtekening:

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

Uw implementatie verpakt uw belangrijkste gegevensbronfunctie. Er zijn twee vereiste handlers voor Table.View :

  • GetType—retourneert de verwachte table type van het queryresultaat
  • GetRows—retourneert het werkelijke table resultaat van uw gegevensbronfunctie

De eenvoudigste implementatie is vergelijkbaar met de volgende:

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

Werk de functie TripPinNavTable bij om aan te roepen in plaats van TripPin.SuperSimpleView GetEntity :

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

Als u de eenheidstests opnieuw uit te voeren, ziet u dat het gedrag van uw functie niet is gewijzigd. In dit geval wordt de implementatie van Table.View eenvoudigweg door de aanroep naar GetEntity doorgeven. Omdat u (nog) geen transformatie-handlers hebt geïmplementeerd, blijft de oorspronkelijke url parameter ongewijzigd.

Initiële implementatie van Table.View

De bovenstaande implementatie Table.View van is eenvoudig, maar niet erg nuttig. De volgende implementatie wordt gebruikt als basislijn en implementeert geen vouwfunctionaliteit, maar heeft de scaffolding die u — nodig hebt om dit te doen.

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

Als u de aanroep van Table.View bekijkt, ziet u een extra wrapper-functie rond de handlers record — Diagnostics.WrapHandlers . Deze helperfunctie vindt u in de diagnostische module (die is geïntroduceerd in een vorige zelfstudie) en biedt u een handige manier om automatisch eventuele fouten te traceren die worden veroorzaakt door afzonderlijke handlers.

De GetType functies en zijn bijgewerkt om gebruik te maken van twee nieuwe GetRows helperfuncties — CalculateSchema en CaculateUrl . Op dit moment zijn de implementaties van deze functies redelijk eenvoudig. U zult zien dat ze onderdelen bevatten van wat eerder — door de functie is GetEntity gedaan.

Ten slotte ziet u dat u een interne functie ( ) definieert View die een state parameter accepteert. Wanneer u meer handlers implementeert, roepen ze de interne functie recursief aan, en worden ze bijgewerkt View state en doorgevend terwijl ze worden uitgevoerd.

Werk de functie opnieuw bij, vervang de aanroep van door een aanroep van de nieuwe functie en voer TripPinNavTable TripPin.SuperSimpleView de TripPin.View eenheidstests opnieuw uit. U ziet nog geen nieuwe functionaliteit, maar u hebt nu een solide basislijn voor het testen.

Query Folding implementeren

Omdat de M-engine automatisch terugvallen op lokale verwerking wanneer een query niet kan worden gevouwen, moet u een aantal extra stappen uitvoeren om te controleren of uw Table.View handlers correct werken.

De handmatige manier om vouwgedrag te valideren, is door te kijken naar de URL-aanvragen die uw eenheidstests doen met behulp van een hulpprogramma zoals Fiddler. De diagnostische logboekregistratie waaraan u hebt toegevoegd, stuurt ook de volledige URL die wordt uitgevoerd, die de OData-queryreeksparameters moet bevatten die door uw TripPin.Feed handlers worden toegevoegd.

Een geautomatiseerde manier om het vouwen van query's te valideren, is door af te dwingen dat de uitvoering van de eenheidstest mislukt als een query niet volledig wordt gevouwen. U kunt dit doen door de projecteigenschappen te openen en Fout bij vouwen in te stellen op Waar. Als deze instelling is ingeschakeld, resulteert elke query die lokale verwerking vereist, in de volgende fout:

We kunnen de expressie niet naar de bron vouwen. Probeer een eenvoudigere expressie.

U kunt dit testen door een nieuwe toe te voegen aan uw eenheidstestbestand dat een of Fact meer tabeltransformaties bevat.

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

Notitie

De instelling Fout bij vouwen mislukt is een alles-of-niets-benadering. Als u query's wilt testen die niet zijn ontworpen om te vouwen als onderdeel van uw eenheidstests, moet u voorwaardelijke logica toevoegen om tests dienovereenkomstig in of uit te schakelen.

In de resterende secties van deze zelfstudie wordt elk een nieuwe handler Table.View toevoegen. U gebruikt een TDD-benadering (Test Driven Development), waarbij u eerst mislukte eenheidstests toevoegt en vervolgens de M-code implementeert om deze op te lossen.

In elke onderstaande handlersectie wordt de functionaliteit beschreven die wordt geleverd door de handler, de OData-equivalente querysyntaxis, de eenheidstests en de implementatie. Met behulp van de hierboven beschreven scaffoldingcode vereist elke handler-implementatie twee wijzigingen:

  • Voeg de handler toe aan Table.View om de record bij te state werken.
  • Wijzigen om de waarden op te halen uit de en toe te voegen aan de CalculateUrl state URL en/of queryreeksparameters.

Table.FirstN verwerken met OnTake

De OnTake-handler ontvangt count een parameter. Dit is het maximum aantal rijen dat moet worden gemaakt. In OData-termen kunt u dit vertalen naar de $top queryparameter.

U gebruikt de volgende eenheidstests:

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

Deze tests gebruiken beide Table.FirstN om te filteren op het resultaat dat is ingesteld op het eerste X-aantal rijen. Als u deze tests uitvoert met Error on Folding Failure ingesteld op (de standaardinstelling), moeten de tests slagen, maar als u Fiddler uitvoert (of de traceerlogboeken controleert), ziet u dat de aanvraag die u verzendt geen False OData-queryparameters bevat.

Diagnostische trace.

Als u Fout bij vouwen in stelt op , mislukken ze met de True 'Probeer een eenvoudigere expressie'. fout. Om dit op te lossen, definieert u uw eerste Table.View-handler voor OnTake .

De OnTake-handler ziet er als volgende uit:

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

De CalculateUrl functie wordt bijgewerkt om de waarde uit de record te extraheren en de juiste parameter in de Top state queryreeks in te stellen.

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

Als u de eenheidstests opnieuw wilt uitvoeren, ziet u dat de URL die u wilt openen nu de $top parameter bevat. (Vanwege URL-codering wordt weergegeven als , maar de OData-service is slim genoeg om $top %24top deze automatisch te converteren).

Diagnostische trace met bovenaan.

Table.Skip verwerken met OnSkip

De OnSkip-handler lijkt veel op OnTake. Er wordt een count parameter ontvangen. Dit is het aantal rijen dat moet worden overgeslagen uit de resultatenset. Dit wordt mooi vertaald naar de OData $skip queryparameter.

Eenheidstests:

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

Uitvoering:

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

Updates die overeenkomen met CalculateUrl :

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

Table.SelectColumns verwerken met OnSelectColumns

De handler OnSelectColumns wordt aangeroepen wanneer de gebruiker kolommen uit de resultatenset selecteert of verwijdert. De handler ontvangt een list met text waarden, die de kolom(en) vertegenwoordigen die moeten worden geselecteerd. In OData-termen wordt deze bewerking aan de $select queryoptie. Het voordeel van het vouwen van kolomselectie wordt duidelijk wanneer u te maken hebt met tabellen met veel kolommen. De $select operator verwijdert niet-geselecteerd kolommen uit de resultatenset, wat resulteert in efficiëntere query's.

Eenheidstests:

// 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 eerste twee tests selecteren verschillende aantallen kolommen met en bevatten een Table.SelectColumns Table.FirstN aanroep om de testcase te vereenvoudigen.

Notitie

Als de test alleen de kolomnamen zou retourneren (met ) en geen gegevens, wordt de aanvraag naar de Table.ColumnNames OData-service nooit daadwerkelijk verzonden. Dit komt doordat de GetType aanroep van het schema retournt, dat alle informatie bevat die de M-engine nodig heeft om het resultaat te berekenen.

De derde test maakt gebruik van de optie , waardoor de M-engine alle geselecteerde kolommen die niet MissingField.Ignore bestaan in de resultatenset moet negeren. De handler hoeft zich geen zorgen te maken over deze optie. De M-engine verwerkt deze automatisch (dat wil zeggen, ontbrekende kolommen worden niet OnSelectColumns — opgenomen in de columns lijst).

Notitie

Voor de andere optie Table.SelectColumns voor , , is een connector vereist om de MissingField.UseNull OnAddColumn handler te implementeren. Dit wordt gedaan in een volgende les.

De implementatie voor OnSelectColumns doet twee dingen:

  • Hiermee voegt u de lijst met geselecteerde kolommen toe aan de state .
  • Berekent de waarde Schema opnieuw, zodat u het juiste tabeltype kunt instellen.
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 wordt bijgewerkt om de lijst met kolommen uit de status op te halen en deze te combineren (met een scheidingsteken) voor de $select parameter .

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

Table.Sort verwerken met OnSort

De OnSort-handler ontvangt list een van record waarden. Elke record bevat een veld dat de naam van de kolom aangeeft en een Name veld dat gelijk is aan of Order Order.Ascending Order.Descending . In OData-termen wordt deze bewerking aan de $orderby queryoptie. De $orderby syntaxis heeft de kolomnaam gevolgd door asc of om desc oplopende of aflopende volgorde aan te geven. Bij het sorteren op meerdere kolommen worden de waarden gescheiden door een komma. Houd er rekening mee dat als de parameter meer dan één item bevat, het belangrijk is om de columns volgorde te handhaven waarin ze worden weergegeven.

Eenheidstests:

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

Uitvoering:

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

Updates voor CalculateUrl :

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

Table.RowCount verwerken met GetRowCount

In tegenstelling tot de andere query-handlers die u hebt geïmplementeerd, retourneert de Handler GetRowCount één waarde van het aantal rijen dat in de — resultatenset wordt verwacht. In een M-query is dit doorgaans het resultaat van de Table.RowCount transformatie. U hebt een aantal verschillende opties voor het afhandelen van dit als onderdeel van een OData-query.

Het nadeel van de benadering van queryparameters is dat u nog steeds de volledige query naar de OData-service moet verzenden. Omdat het aantal weer inline wordt weergegeven als onderdeel van de resultatenset, moet u de eerste pagina met gegevens uit de resultatenset verwerken. Hoewel dit nog steeds efficiënter is dan het lezen van de hele resultatenset en het tellen van de rijen, is het waarschijnlijk nog meer werk dan u wilt doen.

Het voordeel van de benadering van het padsegment is dat u slechts één scalaire waarde in het resultaat ontvangt. Dit maakt de hele bewerking een stuk efficiënter. Zoals beschreven in de OData-specificatie, retourneerd het padsegment /$count echter een fout als u andere queryparameters op neemt, zoals of , waardoor de bruikbaarheid $top $skip wordt beperkt.

In deze zelfstudie implementeert u de GetRowCount handler met behulp van de path segment-benadering. Om de fouten te voorkomen die u krijgt als andere queryparameters worden opgenomen, controleert u op andere statuswaarden en retourneert u een 'niet-implementeerde fout' () als u er een ... vindt. Het retourneren van een fout van een handler vertelt de M-engine dat de bewerking niet kan worden gevouwen en moet in plaats daarvan terugvallen op de standaard-handler (in dit geval wordt het totale aantal rijen Table.View geteld).

Voeg eerst een eenvoudige eenheidstest toe:

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

Omdat het padsegment één waarde retourneert (in tekst-/tekstindeling) in plaats van een JSON-resultatenset, moet u ook een nieuwe interne functie () toevoegen voor het maken van de aanvraag en het verwerken van het /$count TripPin.Scalar resultaat.

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

De implementatie gebruikt vervolgens deze functie (als er geen andere queryparameters worden gevonden in de 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,

De CalculateUrl functie wordt bijgewerkt om toe te toevoegen aan de URL als het veld is ingesteld in de /$count RowCountOnly 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,

De nieuwe Table.RowCount eenheidstest zou nu moeten slagen.

Als u de terugvalcase wilt testen, voegt u nog een test toe die de fout dwingt. Voeg eerst een helpermethode toe waarmee het resultaat van een bewerking op een try vouwfout wordt gecontroleerd.

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

Voeg vervolgens een test toe die gebruikmaakt van Table.RowCount zowel als om de fout af te Table.FirstN dwingen.

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

Een belangrijke opmerking hier is dat deze test nu een fout retourneren als Fout bij vouwen is ingesteld op , omdat de bewerking terugvallen op de lokale false Table.RowCount (standaard)handler. Als de tests worden uitgevoerd met Fout op Vouwfout ingesteld op , mislukt de test en kan de test worden true Table.RowCount uitgevoerd.

Conclusie

Als u Table.View implementeert voor uw connector, wordt uw code aanzienlijk complexer. Omdat de M-engine alle transformaties lokaal kan verwerken, maakt het toevoegen van handlers geen nieuwe scenario's mogelijk voor uw gebruikers, maar leidt dit tot een efficiëntere verwerking (en mogelijk ook voor Table.View gebruikers). Een van de belangrijkste voordelen van de optionele handlers is dat u hiermee incrementeel nieuwe functionaliteit kunt toevoegen zonder dat dit van invloed is op de compatibiliteit met eerdere gebruikers Table.View voor uw connector.

Voor de meeste connectors is een belangrijke (en eenvoudige) handler die moet worden geïmplementeerd (wat wordt omgezet OnTake in in OData), omdat dit de hoeveelheid geretourneerde $top rijen beperkt. De Power Query-ervaring voert altijd een van rijen uit bij het weergeven van previews in de navigator en query-editor, zodat uw gebruikers aanzienlijke prestatieverbeteringen kunnen zien bij het werken met grotere OnTake 1000 gegevenssets.