TripPin parte 10 - Riduzione delle query di base

Questa esercitazione in più parti illustra la creazione di una nuova estensione dell'origine dati per Power Query. L'esercitazione è destinata a essere eseguita in sequenza: ogni lezione si basa sul connettore creato nelle lezioni precedenti, aggiungendo in modo incrementale nuove funzionalità al connettore.

In questa lezione verranno illustrate le procedure seguenti:

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

Una delle potenti funzionalità del linguaggio M è la possibilità di eseguire il push del lavoro di trasformazione in una o più origini dati sottostanti. Questa funzionalità è detta riduzione delle query (altri strumenti/tecnologie fanno riferimento anche a una funzione simile a Predicate Pushdown o Delega query).

Quando si crea un connettore personalizzato che usa una funzione M con funzionalità predefinite di riduzione delle query, ad esempio OData.Feed o Odbc.DataSource, il connettore eredita automaticamente questa funzionalità gratuitamente.

Questa esercitazione replica il comportamento di riduzione delle query predefinito per OData implementando i gestori di funzione per la funzione Table.View . Questa parte dell'esercitazione implementa alcuni gestori più semplici da implementare, ovvero quelli che non richiedono l'analisi delle espressioni e il rilevamento dello stato.

Per altre informazioni sulle funzionalità di query offerte da un servizio OData, passare a Convenzioni URL OData v4.

Per altre informazioni sulla riduzione delle query, vedere Panoramica della valutazione delle query e riduzione delle query in Power Query.

Uso di Table.View

La funzione Table.View consente a un connettore personalizzato di eseguire l'override dei gestori di trasformazione predefiniti per l'origine dati. Un'implementazione di Table.View fornirà una funzione per uno o più gestori supportati. Se un gestore non è implementato o restituisce un oggetto error durante la valutazione, il motore M esegue il fallback al gestore predefinito.

Quando un connettore personalizzato usa una funzione che non supporta la riduzione implicita delle query, ad esempio Web.Contents, i gestori di trasformazione predefiniti vengono sempre eseguiti localmente. Se l'API REST a cui ci si connette supporta parametri di query come parte della query, Table.View consente di aggiungere ottimizzazioni che consentono il push del lavoro di trasformazione al servizio.

La funzione Table.View ha la firma seguente:

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

L'implementazione esegue il wrapping della funzione principale dell'origine dati. Per Table.View sono disponibili due gestori necessari:

• GetType: restituisce il risultato previsto table type del risultato della query
• GetRows: restituisce il risultato effettivo table della funzione dell'origine dati

L'implementazione più semplice sarà simile all'esempio seguente:

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

Aggiornare la TripPinNavTable funzione per chiamare TripPin.SuperSimpleView anziché GetEntity:

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

Se si esegue di nuovo gli unit test, si noterà che il comportamento della funzione non viene modificato. In questo caso, l'implementazione di Table.View passa semplicemente attraverso la chiamata a GetEntity. Poiché non sono ancora stati implementati gestori di trasformazione, il parametro originale url rimane invariato.

Implementazione iniziale di Table.View

L'implementazione precedente di Table.View è semplice, ma non molto utile. L'implementazione seguente viene usata come baseline, non implementa alcuna funzionalità di riduzione, ma ha lo scaffolding necessario.

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

Se si esamina la chiamata a Table.View, viene visualizzata una funzione wrapper aggiuntiva intorno al handlers recordDiagnostics.WrapHandlers. Questa funzione helper si trova nel modulo Diagnostics (introdotto nella lezione di diagnostica di aggiunta) e offre un modo utile per tracciare automaticamente eventuali errori generati dai singoli gestori.

Le GetType funzioni e GetRows vengono aggiornate per usare due nuove funzioni helper,CalculateSchema e CalculateUrl. In questo momento, le implementazioni di queste funzioni sono piuttosto semplici: si noti che contengono parti di ciò che è stato fatto in precedenza dalla GetEntity funzione.

Infine, si noti che si sta definendo una funzione interna (View) che accetta un state parametro. Man mano che si implementano più gestori, chiameranno in modo ricorsivo la funzione interna View , aggiornando e passando il state passaggio man mano che passano.

Aggiornare nuovamente la TripPinNavTable funzione, sostituendo la chiamata a TripPin.SuperSimpleView con una chiamata alla nuova TripPin.View funzione ed eseguire di nuovo gli unit test. Non verranno ancora visualizzate nuove funzionalità, ma è ora disponibile una baseline solida per i test.

Implementazione della riduzione delle query

Poiché il motore M esegue automaticamente il fallback all'elaborazione locale quando una query non può essere piegata, è necessario eseguire alcuni passaggi aggiuntivi per verificare che i gestori Table.View funzionino correttamente.

Il modo manuale per convalidare il comportamento di riduzione consiste nel controllare l'URL richiesto dagli unit test usando uno strumento come Fiddler. In alternativa, la registrazione diagnostica aggiunta a TripPin.Feed genera l'URL completo da eseguire, che deve includere i parametri della stringa di query OData aggiunti dai gestori.

Un modo automatizzato per convalidare la riduzione delle query consiste nel forzare l'esito negativo dell'esecuzione dello unit test se una query non viene completamente piegata. A tale scopo, aprire le proprietà del progetto e impostare Error on Folding Failure su True. Con questa impostazione abilitata, qualsiasi query che richiede l'elaborazione locale genera l'errore seguente:

Non è stato possibile piegare l'espressione nell'origine. Provare un'espressione più semplice.

È possibile testarlo aggiungendo un nuovo Fact al file di unit test che contiene una o più trasformazioni di tabella.

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

Le sezioni rimanenti di questa esercitazione aggiungono ogni nuovo gestore Table.View . Si sta adottando un approccio TDD (Test Driven Development), in cui si aggiungono prima unit test non superati e quindi si implementa il codice M per risolverli.

Le sezioni del gestore seguenti descrivono le funzionalità fornite dal gestore, la sintassi di query equivalente OData, gli unit test e l'implementazione. Usando il codice di scaffolding descritto in precedenza, ogni implementazione del gestore richiede due modifiche:

• Aggiunta del gestore a Table.View che aggiorna il state record.
• Modifica per CalculateUrl recuperare i valori da state e aggiungere ai parametri url e/o stringa di query.

Gestione di Table.FirstN con OnTake

Il OnTake gestore riceve un count parametro, ovvero il numero massimo di righe da accettare da GetRows. In termini OData, è possibile convertirlo nel parametro di query $top .

Usare gli unit test seguenti:

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

Questi test usano entrambi Table.FirstN per filtrare il set di risultati sul primo numero X di righe. Se si eseguono questi test con Errore in caso di errore di riduzione impostato su False (impostazione predefinita), i test devono avere esito positivo, ma se si esegue Fiddler (o si controllano i log di traccia), si noti che la richiesta inviata non contiene parametri di query OData.

Se si imposta Error on Folding Failure su True, i test hanno esito negativo con l'errore Please try a simpler expression. . Per correggere questo errore, è necessario definire il primo gestore Table.View per OnTake.

Il OnTake gestore è simile al codice seguente:

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

La CalculateUrl funzione viene aggiornata per estrarre il Top valore dal state record e impostare il parametro corretto nella stringa di query.

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

Riesecuzione degli unit test, si noti che l'URL a cui si accede ora contiene il $top parametro . A causa della codifica URL, $top viene visualizzato come %24top, ma il servizio OData è abbastanza intelligente da convertirlo automaticamente.

Gestione di Table.Skip con OnSkip

Il OnSkip gestore è molto simile OnTakea . Riceve un count parametro, ovvero il numero di righe da ignorare dal set di risultati. Questo gestore si traduce perfettamente nel parametro di query OData $skip .

Unit test:

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

Implementazione:

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

Corrispondenza degli aggiornamenti a CalculateUrl:

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

Altre informazioni: Table.Skip

Gestione di Table.SelectColumns con OnSelectColumns

Il OnSelectColumns gestore viene chiamato quando l'utente seleziona o rimuove colonne dal set di risultati. Il gestore riceve un list di text valori, che rappresenta una o più colonne da selezionare.

In termini OData, questa operazione esegue il mapping all'opzione di query $select .

Il vantaggio della selezione delle colonne di riduzione diventa evidente quando si gestiscono tabelle con molte colonne. L'operatore $select rimuove le colonne non selezionate dal set di risultati, ottenendo query più efficienti.

Unit test:

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

I primi due test selezionano diversi numeri di colonne con Table.SelectColumns e includono una chiamata Table.FirstN per semplificare il test case.

Il terzo test usa l'opzione MissingField.Ignore , che indica al motore M di ignorare le colonne selezionate che non esistono nel set di risultati. Il OnSelectColumns gestore non deve preoccuparsi di questa opzione, ovvero il motore M lo gestisce automaticamente( ovvero le colonne mancanti non sono incluse nell'elenco columns ).

L'implementazione per OnSelectColumns esegue due operazioni:

• Aggiunge l'elenco di colonne selezionate all'oggetto state.
• Ricalcola il Schema valore in modo da poter impostare il tipo di tabella corretto.

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 viene aggiornato per recuperare l'elenco di colonne dallo stato e combinarle (con un separatore) per il $select parametro .

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

Gestione di Table.Sort con OnSort

Il OnSort gestore riceve un elenco di record di tipo:

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

Ogni record contiene un Name campo, che indica il nome della colonna e un Order campo uguale a Order.Ascending o Order.Descending.

In termini OData, questa operazione esegue il mapping all'opzione di query $orderby . La $orderby sintassi ha il nome della colonna seguito da asc o desc per indicare l'ordine crescente o decrescente. Quando si esegue l'ordinamento in più colonne, i valori vengono separati da una virgola. Se il columns parametro contiene più di un elemento, è importante mantenere l'ordine in cui vengono visualizzati.

Unit test:

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

Implementazione:

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

Aggiornamenti a CalculateUrl:

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

Gestione di Table.RowCount con GetRowCount

A differenza degli altri gestori di query che si stanno implementando, il GetRowCount gestore restituisce un singolo valore, ovvero il numero di righe previste nel set di risultati. In una query M questo valore è in genere il risultato della trasformazione Table.RowCount .

Sono disponibili alcune opzioni diverse su come gestire questo valore come parte di una query OData:

• Parametro di query $count che restituisce il conteggio come campo separato nel set di risultati.
• Segmento di percorso /$count, che restituisce solo il conteggio totale, come valore scalare.

Lo svantaggio dell'approccio ai parametri di query è che è comunque necessario inviare l'intera query al servizio OData. Poiché il conteggio torna inline come parte del set di risultati, è necessario elaborare la prima pagina di dati dal set di risultati. Anche se questo processo è ancora più efficiente rispetto alla lettura dell'intero set di risultati e al conteggio delle righe, è probabilmente ancora più lavoro di quello che si vuole fare.

Il vantaggio dell'approccio al segmento di percorso è che si riceve solo un singolo valore scalare nel risultato. Questo approccio rende l'intera operazione molto più efficiente. Tuttavia, come descritto nella specifica OData, il segmento di percorso /$count restituisce un errore se si includono altri parametri di query, ad esempio $top o $skip, che ne limita l'utilità.

In questa esercitazione è stato implementato il GetRowCount gestore usando l'approccio del segmento di percorso. Per evitare gli errori che si otterrà se sono inclusi altri parametri di query, è stato verificato se sono stati rilevati altri valori di stato e viene restituito un errore "unimplementato" () (...) se presente. Se si restituisce un errore da un gestore Table.View , il motore M indica che l'operazione non può essere piegata e deve eseguire il fallback al gestore predefinito, che in questo caso conta il numero totale di righe.

Aggiungere prima di tutto uno unit test:

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

Poiché il /$count segmento di percorso restituisce un singolo valore (in formato testo normale) anziché un set di risultati JSON, è anche necessario aggiungere una nuova funzione interna (TripPin.Scalar) per effettuare la richiesta e gestire il risultato.

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

L'implementazione usa quindi questa funzione (se non vengono trovati altri parametri di query in 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,

La CalculateUrl funzione viene aggiornata per aggiungere /$count all'URL se il RowCountOnly campo è impostato in 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,

Il nuovo Table.RowCount unit test dovrebbe essere superato.

Per testare il caso di fallback, aggiungere un altro test che forza l'errore.

Aggiungere prima di tutto un metodo helper che controlla il risultato di un'operazione try per un errore di riduzione.

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

Aggiungere quindi un test che usa sia Table.RowCount che Table.FirstN per forzare l'errore.

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

Una nota importante è che questo test restituisce ora un errore se Error on Folding Error è impostato su false, perché l'operazione esegue il Table.RowCount fallback al gestore locale (predefinito). L'esecuzione dei test con errore durante la riduzione dell'errore impostato per true causare Table.RowCount l'esito negativo e consente al test di avere esito positivo.

Conclusione

L'implementazione di Table.View per il connettore aggiunge una notevole complessità al codice. Poiché il motore M può elaborare tutte le trasformazioni in locale, l'aggiunta di gestori Table.View non abilita nuovi scenari per gli utenti, ma comporta un'elaborazione più efficiente (e potenzialmente più felici). Uno dei principali vantaggi dei gestori Table.View è che consente di aggiungere in modo incrementale nuove funzionalità senza influire sulla compatibilità con le versioni precedenti per il connettore.

Per la maggior parte dei connettori, un gestore importante (e di base) da implementare è OnTake (che si traduce $top in OData), in quanto limita il numero di righe restituite. L'esperienza di Power Query esegue sempre una OnTake delle 1000 righe durante la visualizzazione di anteprime nello strumento di spostamento e nell'editor di query, in modo che gli utenti possano visualizzare miglioramenti significativi delle prestazioni quando si utilizzano set di dati di dimensioni maggiori.